Fixing WordPress indexes, foreign keys and auto_increment fields

Fixing WordPress indexes, foreign keys and auto_increment fields

I recently migrated a number of WordPress websites and a custom PHP website from AWS RDS to MySQL on an EC2 instance using the AWS Database Migration Service. I didn’t know beforehand that secondary indexes, foreign keys, and auto_increment fields aren’t migrated – this made a heck of a mess, with me unable to post, upload images, or do much else. This led to me fixing WordPress indexes, foreign keys and auto_increment fields.

As the sites had changed it was too late to use the best migration method, which would’ve been a simple dump and load. I could’ve dumped the schema and loaded the data from the new database but that was too much messing around.

Based on the WordPress schema.php I’ve put together a script that should fix things up. It’s not as good as doing the migration properly, and it may miss something, but WordPress generally appears to work ok now. It adds the critical auto_increment fields back, sets the auto_increment to start at 10,000 (you should check each of your tables and choose a number higher than the highest ID in that table) and recreates indexes.

Note: this may be updated from time to time if I find other problems. This was done with WordPress 4.7.

If you get the error “alter table causes auto_increment resequencing resulting in duplicate entry 1” (or 0, or something else). This is usually fixed by deleting the entry with the ID 0 or 1 in the table. Note that you should be careful doing this as it could delete an important row.

 

ALTER TABLE wp_termmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_terms MODIFY COLUMN term_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_term_taxonomy MODIFY COLUMN term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_commentmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_comments MODIFY COLUMN comment_ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_links MODIFY COLUMN link_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_options MODIFY COLUMN option_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_postmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_users MODIFY COLUMN ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_posts MODIFY COLUMN ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_usermeta MODIFY COLUMN umeta_id bigint(20) unsigned NOT NULL auto_increment;

CREATE INDEX term_id on wp_termmeta (term_id);
CREATE INDEX meta_key on wp_termmeta (meta_key(191));
CREATE INDEX slug on wp_terms (slug(191));
CREATE INDEX name on wp_terms (name(191));
CREATE UNIQUE INDEX term_id_taxonomy on wp_term_taxonomy (term_id, taxonomy);
CREATE INDEX taxonomy on wp_term_taxonomy (taxonomy );
CREATE INDEX comment_id on wp_commentmeta (comment_id);
CREATE INDEX meta_key on wp_commentmeta (meta_key(191));
CREATE INDEX comment_post_ID on wp_comments (comment_post_ID);
CREATE INDEX comment_approved_date_gmt on wp_comments (comment_approved,comment_date_gmt);
CREATE INDEX comment_date_gmt on wp_comments (comment_date_gmt);
CREATE INDEX comment_parent on wp_comments (comment_parent);
CREATE INDEX comment_author_email on wp_comments (comment_author_email(10));
CREATE INDEX link_visible on wp_links (link_visible);
CREATE UNIQUE INDEX option_name on wp_options (option_name);
CREATE INDEX post_id on wp_postmeta (post_id);
CREATE INDEX meta_key on wp_postmeta (meta_key);
CREATE INDEX post_name on wp_posts (post_name(191));
CREATE INDEX type_status_date on wp_posts (post_type,post_status,post_date,ID);
CREATE INDEX post_parent on wp_posts (post_parent);
CREATE INDEX post_author on wp_posts (post_author);
CREATE INDEX user_login_key on wp_users (user_login);
CREATE INDEX user_nicename on wp_users (user_nicename);
CREATE INDEX user_email on wp_users (user_email);
CREATE INDEX user_id on wp_usermeta (user_id);
CREATE INDEX meta_key on wp_usermeta (meta_key(191));

ALTER TABLE wp_terms AUTO_INCREMENT = 10000;
ALTER TABLE wp_term_taxonomy AUTO_INCREMENT = 10000;
ALTER TABLE wp_commentmeta AUTO_INCREMENT = 10000;
ALTER TABLE wp_comments AUTO_INCREMENT = 10000;
ALTER TABLE wp_links AUTO_INCREMENT = 10000;
ALTER TABLE wp_options AUTO_INCREMENT = 10000;
ALTER TABLE wp_postmeta AUTO_INCREMENT = 10000;
ALTER TABLE wp_users AUTO_INCREMENT = 10000;
ALTER TABLE wp_posts AUTO_INCREMENT = 10000;
ALTER TABLE wp_usermeta AUTO_INCREMENT = 10000;
Facebook Comments

11 thoughts on “Fixing WordPress indexes, foreign keys and auto_increment fields

  1. Percy

    Saved couple of day buddy. Thank you for your post.

    One might also face primary key error to fix it please also append primary key
    E.g. ALTER TABLE wp_termmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment PRIMARY KEY;

  2. Chris McCreery

    Any updates on this for 4.9.4?

    I get the following error when running

    #1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key

  3. David Burg

    Very helpful post indeed for a tricky issue. Why setting the auto_increment values at the end of the script to a magical 10k value? I was weary of that change and kept the auto_increment values generated by MySQL at the conclusion of the first two query blocks. I also use Joao’s fix for duplicate entries. Finally I drafted but ended not using the following query to possibly increment to the next value:

    SELECT @meta_id_max = MAX(meta_id) + 1 FROM wp_commentmeta;
    SET @statementString = CONCAT(“ALTER TABLE wp_commentmeta AUTO_INCREMENT=”, @meta_id_max);
    PREPARE statement FROM @statementString;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;

    This statement is not tested. If someone ends up validating or fixing it, do share.

    1. Tim

      Hi David, the 10K values for the autoincrement fields were picked because none of my tables had IDs anywhere near that high and it seemed like an easy solution. It’s almost certainly not the best solution, but it was quick and easy. Your SQL looks interesting, but would need to be done for every table with an autoincrement field. If anyone does it and validates it I’m happy to include it in the body of the blog article.

Leave a Reply

Your email address will not be published. Required fields are marked *