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

21 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.

  4. DJ

    Thank you so much for this. I was at my wit’s end with one my sites and about to rebuild the whole thing before I stumbled across this.

  5. Jule

    Hi, im currently having a similar problem in my WP site. Everytime i try to upload an image or create a page the error “could not import post into database” keeps coming up.

    I guess it has to do with the issue on migrating your are commenting on your post. The code above didnt work for me, as a result i get this:

    “ALTER TABLE wp_comments MODIFY COLUMN comment_ID bigint(20) unsigned NOT NULL auto_increment”
    “Default Value invalid for “comment_date”

    I realised i ve got no AUTO INCREMENTS in my wp_post “ID” but when i try to activate it i get:

    “”Default Value invalid for “post_date”

    its been 2 days and still couldnt find any solution, could you help me?

    1. Tim @ PTS

      Your problem looks different, but database related. If you want to engage a professional to help then please use my contact form, otherwise I suggest you post your question on the Stack Exchange wordpress site https://wordpress.stackexchange.com/ . Make sure you include more detail than you have here – have you done a migration, when did it start, wordpress version, etc.

  6. Sally

    Hi Tim,

    Can you give me some advice, please? I have a WP Multisite and one sub-site is misbehaving. I think it has the same problem here as your describe. All other subsites work OK

    On this problem subsite, I can’t open Customise – it throws an error …

    Uncaught Exception: Illegal widget setting ID: nav_menu_item[] in *path to*/wp-includes/customize/class-wp-customize-nav-menu-item-setting.php:171

    Also, when I try to open Menus, it just freezes the page, and I can’t click on anything.

    1. Would you suggestion fix these problems?

    2. If so, do I need to change the table prefix to the number of the problem site in my multisite? eg wp_termmeta to wp_13_termmeta etc?

    Thanks. 🙂

    Sally

  7. Muhammad Farooqi

    Hello.
    Thanks for the idea.. But I assume this won’t work for me.
    Issue:
    I’ve been collecting date … of users on my website (their visit, browsing and comments) and had reached to almost 539037
    All in sudden for some unknown reason.. this filed started showing 539037000003 and then incremented to onwards.. (note 539037 + 0000003)
    Now when I’m seeing the post has number 5390377823400 and definitely their foreign keys in postmeta is all depended on this new number.

    How can i reset this and keep a continues AI with Primary key and everything.. I mean.. I just want to reset it without loosing my data.
    Q1. how the foreign key be updated or reset..
    Posts to Postmeta… and all the images with their own ID from Posts… are listed inside value of Postmeta tables.. in serialized form.
    How can i handle this..
    do you understand my question?

  8. MF

    Thank you so much! I couldn’t add posts, images, or events to my website. We recently migrated hosts and I guess I messed up somewhere. Following your instructions though opened up everything. Glad I found your post on stackoverflow.com

Leave a Reply

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