Fix ‘auto_increment’ lost in WordPress database

Wordpress + MySQL

Has been a while without posting and I’m here again with something totally different but useful anyway. I’m gonna talk about self experience building a web page in WordPress, it’s a short one, let’s go:

id field of wp tables has lost its auto_increment property

Maybe more people have faced this situation that I explain here, “some elements of WordPress and plugins stop working properly”, I was stuck with Polylang plugin in particular, once enabled I couldn’t manage to make it work, its settings panel was unusable cause didn’t show all options that normally does. It took me some time navigating through sources files realize that the issue was related with database.

The entries that plugin tried to store in any table of the database they were overriding in the first row with id set to 0. In that moment I went to take a look to tables structure and noted that ‘auto_increment’ property of all id fields was unchecked.
Then I thought that every new entry was storing with default value of id field (numeric) which is zero.

Before I start with the actual fix, I say that, probably, this missconfiguration came from a previous import of a backup in the database. I don’t know if it depends on how you import/export but doing this operations it doesn’t always lead me to the same unexpected result. Anyway is important take a look to the sql that you gonna import and check everything is well.

SOLUTION

Actually is simple, from PhpMyAdmin just check again the box of ‘auto_increment’ in all ids of each table affected by the issue or you can run the proper sql query too.
Also, you should modify the current value of ‘auto_increment’. Change it to some value higher than any other in table, for example:

ALTER TABLE table_name AUTO_INCREMENT = 1000;

After that, your next entries will start from that id.
It’s possible that the above query returns a error with message like “alter table causes auto_increment resequencing resulting in duplicate entry 1” (or zero), if that is the case, erase the entry with id 0 or 1 or also you can modify the value for a moment so you are able to execute the query with no errors.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.