WordPress Database Schema Frustration

A client reported a permissions issue with a WordPress site that I ported to Pantheon about 2 years ago. When she tried to create a new Page, an error message appeared.

It took a few hours over the course of two days until I was able resolve this and return the site to its fully operational state. And because, as it is so often the case, I found no single source of information that helped me, here is story of how I puzzled it together from pieces.

I began to investigate and soon noticed, that there was more wrong with the site. None of the “Add” pages worked, the editor was missing, and instead there was this weird message on the Post page when I tried to add a new Page:

You are currently editing the page that shows your latest posts.

Where I did manage to submit a new element without getting an error (e.g. a new user), it seemed to disappear.

At this point, I was still hopeful for a quick fix, and began to search the web for occurrences of these messages. Especially the “You are currently editing the page that shows your latest posts” seemed promising. So I tried a methodical step-by-step approach.

Step 1 – Local instance

There was no local development instance because there was no development, so I had to create one. Pantheon does not let you fiddle with themes and plugins in their environment. I pulled the site and database down into a local TurnKey Linux appliance where it showed the exact same behavior.

Step 2 – Turned on Debugging and Logging

I saw database insert errors and primary key/duplicate key violations, but it took me a bit longer to find out what caused them.

Step 3 – Eliminate possible causes

I disabled all plugins and removed them from the plugin folder, switched to the twentyseventeen theme and removed all others from the themes folder. No change.

I downloaded a fresh copy of WordPress and started it with the original wp_config.php.

Step 4 – It is probably the database, but how to fix it?

Some sources reported success after installing WP-DBManager and running the repair function, but without explaining what the issue was and what the tool repaired. Some reports claimed that the problem began after the WP 4.2 update. The tool did nothing for me.

Step 5 – Get into the weeds of the WordPress database schema

I had noticed earlier that after a failed “Add Page” attempt, there was a row in the wp_posts table with post ID  of 0.

I initially suspected a primary key or index issue, because this is what some sources claimed. I queried the data, looked for orphaned or duplicate rows, or any other oddities. You see where this is going. Then I finally came across sources that attributed this issue to a missing Auto Increment property on the primary key, which explained all the symptoms. I let WP generate a  fresh database instance and compared the schemas. And indeed, all the primary key columns in all tables were missing this setting. Because WordPress does not pass a value for the primary key on insert (I saw this in the error message on the screen), it can’t work without this.

This StackOverflow question and this one on Stackexchange were helpful in figuring out how to add the auto-increments back in. I was not able to simply add it back in, because two tables had duplicate values (0) that prevent the database from making the column increment automatically. I decided to ignore these two tables, wp_options and wp_usermeta, because they don’t seem to get referenced from other tables and I did not notice any issues with site. So here is my script that I ran remotely in the Pantheon databases (dev, test, live):

ALTER TABLE wp_termmeta AUTO_INCREMENT = 10000, MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_terms AUTO_INCREMENT = 10000, MODIFY COLUMN term_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_term_taxonomy AUTO_INCREMENT = 10000, MODIFY COLUMN term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_commentmeta AUTO_INCREMENT = 10000, MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_comments AUTO_INCREMENT = 10000, MODIFY COLUMN comment_ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_links AUTO_INCREMENT = 10000, MODIFY COLUMN link_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_postmeta AUTO_INCREMENT = 10000, MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_users AUTO_INCREMENT = 10000, MODIFY COLUMN ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_posts AUTO_INCREMENT = 10000, MODIFY COLUMN ID bigint(20) unsigned NOT NULL auto_increment;

Once this ran, adding users and posts worked normally again.