wp_insert_post always returns 0 on one site, extensive testing done, can't get to the bottom

Question

I have two sites. One, a fresh install, the other an existing site with errors/white screen of death on the wp-admin/post-new.php screen. I've disabled all of the active plugins, and enabled a default theme, and am still getting these errors (because the draft post is not created properly in the DB during the page load).

Notice: Trying to get property 'post_type' of non-object 
Warning: Creating default object from empty value
Notice: Trying to get property 'post_status' of non-object
Notice: Trying to get property 'ID' of non-object

I've tracked this down to an issue with wp_insert_post() when loading the wp-admin/post-new.php screen. For testing, I added a wp_die( print_r( $wpdb ) ); in the wp-includes/post.php core file (here). This outputs a lot of information about the database query. No errors are present, and it says [rows_affected] => 1, [insert_id] => 0, and the return of wp_insert_post() is always 0.

I can't figure out why with a default core theme, no plugins active, no mu-plugins directory and no core files altered, wp_insert_post() is always returning 0. The post is inserted into the database, but it always has a post ID of 0 and the post-new.php screen white screens. When I have WP_DEBUG enabled, the above errors are present. I have a feeling it has to do with the one database, but I don't see any errors that would indicate so. Both DBs are InnoDB and pretty much identical except for the data held within them.

My very basic code to test wp_insert_post() is as follows, and as stated works on one site and not the other.

$my_post = array(
    'post_title'    => 'test',
    'post_content'  => '',
    'post_status'   => 'publish',
    'post_author'   => 1,
);

// Insert the post into the database
$test = wp_insert_post( $my_post );

wp_die( 'New ID: ' . $test );

On the non-working site, the post is inserted into the database as post 0 and set to auto draft.

Var Dump from Broken $wpdb

Here is the var_dump of the $wpdb return inside of wp_insert_post() in the core file linked above: https://pastebin.com/CGGv5q4N

As you can see there are no visible errors, and it looks like the post should have been assigned a post ID, because it's in the DB, but it's always post ID 0.

Var Dump from Working $wpdb

https://pastebin.com/QTUVfdHR

Answer 1

I have a feeling it has to do with the one database, but I don't see any errors that would indicate so.

Yes, and apparently the structure of the WordPress posts table (wp_040skcp8g2_posts) in the problematic site is corrupted.

More specifically, indexes such as the primary key are missing, so you need to add them back ASAP:

• See line 26 at https://pastebin.com/CGGv5q4N which is [Key] => and should instead be [Key] => PRI and on line 28, it should be [Extra] => auto_increment.

• Try running SHOW INDEXES FROM wp_040skcp8g2_posts via phpMyAdmin and you'd notice the table indexes are missing.

And I don't know how come the indexes are gone, but I would scan the site for possible security loopholes and then ensure that WordPress core, plugins and themes are up-to-date. I might also change my WordPress/cPanel/FTP/SSH/etc. passwords.

As you can see there are no visible errors, and it looks like the post should have been assigned a post ID, because it's in the DB, but it's always post ID 0.

The post ID field was supposed to be the primary key with the value being auto-incremented for every INSERT operation, but as I said above, the primary key is missing and the ID field is also no longer auto-incremented and therefore, the post ID always a 0, i.e. zero.

So you really need to fix the table structure and ensure the other (WordPress) tables have a good structure as well.

And for example, to add the primary key, you could do ALTER TABLE wp_040skcp8g2_posts MODIFY ID bigint(20) unsigned NOT NULL auto_increment, ADD PRIMARY KEY (ID).

However, if you need further assistance with that (adding back or fixing the indexes), try asking on https://dba.stackexchange.com/, or Stack Overflow? :)