After rolling back a Wordpress database to a backup version it seems that all the tables lost their auto_increment on the primary key columns. I read on another post this could be to do with InnoDB storing the auto_increment value in memory. I've rolled back and migrated databases before without such issues. Anyone ran in a similar issue before? Thanks in advance for any help.

I had a similar issue, I solved it and since this comes up high on Google for what I was looking for it may help others.

I migrated several Wordpress databases from AWS RDS MySQL to MySQL running on an EC2 instance, using the database migration service. What I didn't know is it doesn't copy indexes, keys, auto increment, or really anything other than the basics. Of course the best approach would be to dump the database using mysqldump and import it manually, but one Wordpress install had significant changes and I didn't want to redo them. Instead I manually recreated the auto_increment values and indexes.

I've documented how I fixed Wordpress auto increment here on my website, here's a copy of what worked for me. It's possible I'll make further changes, I'll update the website but I may not remember to update this question.

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;

Notes

  • You should check your tables and make sure to set your auto_increment to a value that makes sense for that table.
  • 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.

Why did it happen? It's hard to tell for sure because there are lots of variables to consider: mistakes made in exporting or importing, MySQL version etc.

This is rather specific MySQL database question and doesn't have much to do with WordPress itself. To get a specific non-speculative answer to question why, I suggest to ask it in SO or DBA with plenty of details about your backup process.


Solution: ALTER TABLE table_name AUTO_INCREMENT = increment_number

  • This sets AUTO_INCREMENT manually to a selected number
  • increment_number value must be at least one number higher than your current highest number of that table's primary key that is auto incremeted
  • Also, don't forget to change table_name

Example: ALTER TABLE wp_posts AUTO_INCREMENT = 2043 <- biggest number in ID column + 1

Extra notes:

  • You will need to repeat this for each table that has messed up auto increment
  • There might be a way to alter all tables at once but Im not SQL guru (correct me if there is)
  • It will take some time for huge tables because ALTER TABLE causes a rebuild of the entire table

More information: here and here

I forgot to import indexes from last of the MySQL file so I fetched the same issue and its hard to fire autoincrement query one by one so created script, Its take a dynamic table and check for primary key If script found and primary key then it will be applied to autoincrement dynamically.

Take db connection variable from your wp-config.php and save in youWordPressss root and run by url.

// Database configration
$host = 'localhost';
$dbuser   = 'dbuser';
$dbpassword   = 'dbpassword';
$dbname         = 'database';

// connect to DB
$conn = new mysqli($host, $dbuser, $dbpassword);
try {
    $connection = new PDO("mysql:host=$host;dbname=$dbname", $dbuser, $dbpassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="NO_ZERO_DATE"'));
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    exit("Connection failed: " . $e->getMessage());
}

// get all tables from DB
$stmt = $connection->prepare('SHOW TABLES');
$stmt->execute();
$table_names = array();
foreach ($stmt->fetchAll() as $row) {
    $table_names[] = $row[0];
}

// for all tables
foreach ($table_names as $table_name) {

    // get the name of primary key
    $stmt = $connection->prepare("show keys from $table_name where Key_name = 'PRIMARY'");
    $stmt->execute();
    $key_name = $stmt->fetch()['Column_name'];

    // get the type of primary key
    $stmt = $connection->prepare("show fields from $table_name where Field = '$key_name'");
    $stmt->execute();
    $key_type = $stmt->fetch()['Type'];

    // primary already exist then going to add auto increment
    if ($key_name) {

        try {
            // if auto_increment was missing there might be a row with key=0 . compute the next available primary key
            $sql = "select (ifnull( max($key_name), 0)+1) as next_id from $table_name";
            $stmt = $connection->prepare($sql);
            $stmt->execute();
            $next_id = $stmt->fetch()['next_id'];

            // give a sane primary key to a row that has key = 0 if it exists
            $sql = "update $table_name set $key_name = $next_id where $key_name = 0";
            $stmt = $connection->prepare($sql);
            $stmt->execute();

            // set auto_increment to the primary key
            $sql = "alter table $table_name modify column $key_name $key_type auto_increment";
            $stmt = $connection->prepare($sql);
            $stmt->execute();

        } catch (PDOException $e) {
            echo $e->getMessage() . '\n';
        }
    } else {
        echo "primary key not found in table $table_name.\n";
    }
}
$connection = null;

Why did this happen? Here's what went wrong for me:

If you exported your database using phpadmin and had an error on reimporting it, the code that adds the primary key doesn't run because it's at the end of the SQL file, not at its creation.

Before I figured this out, I updated to the phpmyadmin 5 beta and it imported the files with the key even though I still had the error.

Lesson one is, don't let your import crash, even if your tables are there. Mine crashed on table that began with wp_w so it came after user and rekt my auto increments.

If you look at the bottom of your SQL export, you will find the alter table for adding the Primary Key and the auto increment.

You don't need to specify the auto increment it automatically knows what the next increment is like so: ALTER TABLE wp_posts CHANGE ID ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

If you had admin activity since this happened, you have zeros in your key field, which will not allow you to set a primary key, and without that, you can't auto increment. So you need to run a delete script vs each table like so: DELETE FROM wp_posts WHERE ID=0;

Here's a complete set of updates If your table has these, it will throw and error.

DELETE FROM wp_termmeta WHERE meta_id=0; DELETE FROM wp_terms WHERE term_id=0; DELETE FROM wp_term_taxonomy WHERE term_taxonomy_id=0; DELETE FROM wp_commentmeta WHERE meta_id=0; DELETE FROM wp_comments WHERE comment_ID=0; DELETE FROM wp_links WHERE link_id=0; DELETE FROM wp_options WHERE option_id=0; DELETE FROM wp_postmeta WHERE meta_id=0; DELETE FROM wp_users WHERE ID=0; DELETE FROM wp_posts WHERE ID=0; DELETE FROM wp_usermeta WHERE umeta_id=0;

ALTER TABLE wp_termmeta ADD PRIMARY KEY(meta_id); ALTER TABLE wp_terms ADD PRIMARY KEY(term_id); ALTER TABLE wp_term_taxonomy ADD PRIMARY KEY(term_taxonomy_id); ALTER TABLE wp_commentmeta ADD PRIMARY KEY(meta_id); ALTER TABLE wp_comments ADD PRIMARY KEY(comment_ID); ALTER TABLE wp_links ADD PRIMARY KEY(link_id); ALTER TABLE wp_options ADD PRIMARY KEY(option_id); ALTER TABLE wp_postmeta ADD PRIMARY KEY(meta_id); ALTER TABLE wp_users ADD PRIMARY KEY(ID); ALTER TABLE wp_posts ADD PRIMARY KEY(ID); ALTER TABLE wp_usermeta ADD PRIMARY KEY(umeta_id);

ALTER TABLE wp_termmeta CHANGE meta_id meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_terms CHANGE term_id term_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_term_taxonomy CHANGE term_taxonomy_id term_taxonomy_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_commentmeta CHANGE meta_id meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_comments CHANGE comment_ID comment_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_links CHANGE link_id link_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_options CHANGE option_id option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_postmeta CHANGE meta_id meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_users CHANGE ID ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_posts CHANGE ID ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE wp_usermeta CHANGE umeta_id umeta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

  • You have to open a new question. – Castiblanco Aug 10 at 21:51
  • This answer is extremely lengthy and its code is not formatted. Please edit your answer, be clear, be concise, format your code so it's readable. – Howdy_McGee Aug 10 at 22:20

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.