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;
Thank you, this is a golden post.
It solved lots of issues including featured image, image uploads and media library corruption.
Good to hear it helped someone, it took quite a while to work through so I thought it could help others 🙂
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;
I love you buddy, thanks a lot!
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
If you can run the script one line at a time and tell me which line causes the error message I can probably work it out for you Chris.
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.
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.
I wrote an update for this.
I uses the build in WP Core schema to ensure _all_ WP Core tables are there (even in the future when 5.0 or higher is released ). It removes corrupt rows and re-adds keys an primary keys. The script (and more explanation) can be seen over here: https://wpindexfixer.tools.managedwphosting.nl/
No need to guess the auto-increment value too.
That script is a no-brainer, thanks for posting it!
Thank you, saved me a lot of time.
Great to hear it helped 🙂
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.
Good to hear this post is helping quite a few people 🙂
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?
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.
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
Sally, the problem you’re having seems completely different from what this article covers. You might try https://wordpress.stackexchange.com/ for help.
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?
Hi Muhammad. Your question is quite different to the topic of my post. I suggest you ask for help on the Stack Exchange WordPress Site.
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