auto_increment flag repair on primary keys of a WordPress MySQL database

December 18, 2016August 22, 2023

Here’s some code that I used to restore the auto_increment flag on the primary keys of my WordPress MySQL database after a somewhat faulty export/import cycle.

The auto_increment problem and its symptoms

When you export the WordPress database and then import it again, either via phpmyadmin or via mysqldump and the mysql CLI, all sorts of things can (and often will) go wrong. This can be understandably very stressful, especially on a live system. Please take a deep breath and read on.

I did a dump of my (fortunately development) environment and then imported it again. At first glance, all was working perfectly.

Then, all of a sudden I noticed that the Administrator user had no rights to create posts or pages. The usual Publish button was replaced with Submit for review.

Naïvely my first thought was to install a plugin to fix roles and capabilities. Sure enough, the admin user had no right to create posts or pages. When I tried to give those rights, I saw the following in my logs:

PHP message: WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO `wp_usermeta` (`user_id`, `meta_key`, `meta_value`) VALUES (1, 'wp_capabilities', 'a:1:{s:13:\"administrator\";b:1;}') made by require_once('wp-admin/admin.php'), do_action('users_page_users-user-role-editor'), User_Role_Editor->edit_roles, Ure_Lib->editor, Ure_Lib->process_user_request, Ure_Lib->permissions_object_update, Ure_Lib->update_user, WP_User->add_role, update_user_meta, update_metadata, add_metadata

(I have debug logs enabled in my development environment. If you’re in a production environment you might not see this.)

Why would a primary key be set to 0 you ask? A quick glance at the structure of the wp_usermeta table via phpmyadmin reveals that the primary key column had no auto_increment flag.

SQL INSERT statements from various plugins were inserting rows in various tables with the primary key being undefined (and therefore set to a default of 0). Since primary keys have a unique constraint, attempting to do a second insert to the same table fails, causing all sorts of havoc.

For some reason the auto_increment flag had not been preserved when I re-imported the SQL dump. Everything else seemed to be in order though. I did not investigate why this happened but decided to simply fix this.

Coding is the solution

By now I could see the front-end but was not able to login to the admin interface any more. Any INSERT query to the database, including those that store session information upon login, were failing. As I had quite a lot of tables, I decided not to do this manually, but to write a generic script.

As a side-note, the script needs to connect with the NO_ZERO_DATE SQL mode. WordPress uses a lot of DATETIME fields with a default value of 0000-00-00 00:00:00 and this script will be very unhappy if this mode is not set.

Pseudocode

for all tables in database
    get the primary key column's name and type
    get the next available primary key value
    change the row with zero primary key so it has the next available primary key
    set the auto_increment flag to the primary key column

Note: The above assumes that all the primary keys are numeric. YMMV.

What to do:

Next is a PHP listing of the above solution. Here’s what to do:

Check to see that your issue is actually one of missing auto_increment flags. This script will only repair this particular error.
Check to see that all your primary keys are numeric. There shouldn’t be an issue if some aren’t but you might have to hack the code manually or go update the structure of those tables via phpmyadmin.
Change the host, dbname, username and password in the code to those that match your system.
Backup your database (I guess you already have a backup and that’s what caused the issue, but still, you want to be able to go back if something goes wrong when running this.) You are solely responsible for any damages including data loss from running this script. Don’t blame me for corrupting your data please. Read and understand the code first!

The PHP script

Save this in a file, take another deep breath, and run it via the PHP CLI. I hope it solves your MySQL woes. Good luck buddy.

<?php

// change these settings
$servername = 'localhost';
$username = 'dbuser';
$password = 'password';
$db = 'database';

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

// get all table names
$stm = $conn->prepare('SHOW TABLES');
$stm->execute();
$table_names = array();
foreach ( $stm->fetchAll() as $row ) {
    $table_names[] = $row[0];
}

// for all tables
foreach ( $table_names as $table_name ) {
    echo "\nRepairing table $table_name...\n";

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

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

    // if there is a primary key
    if ($key_name) {
        echo "Primary key is $key_name\n";

        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";
            $stm = $conn->prepare( $sql );
            echo "$sql\n";
            $stm->execute();
            $next_id = $stm->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";
            echo "$sql\n";
            $stm = $conn->prepare( $sql );
            $stm->execute();

            // set auto_increment to the primary key
            $sql = "alter table $table_name modify column $key_name $key_type auto_increment";
            echo "$sql\n";
            $stm = $conn->prepare( $sql );
            $stm->execute();

        } catch (PDOException $e) {
            echo "\n\nQuery: $sql\nError:" . $e->getMessage() . "\n\n";
        }
    } else {
        echo "No primary key found for table $table_name.\n";
    }
}
echo "\n\nFinished\n";
$conn = null;

If I have helped you get your site up and running, you can donate a few Satoshis at: bc1qjkgp8u2jwy2n9k20avjweuy7etsjfpfplvf99q

35 thoughts on “auto_increment flag repair on primary keys of a WordPress MySQL database”

Mustafa Al. says:

August 27, 2017 at 7:21 pm

You saved my day! Thanks a lot !!!!!

Reply
1. alexg says:
  
  September 19, 2017 at 4:43 pm
  
  Very glad to hear that 🙂
  
  Reply
Ritch @ S7 Media says:

September 13, 2017 at 2:03 pm

Thanks Alex. Bookmarking this, as I’m sure we’ll find this useful down the line… just had a right ‘mare with keys and indexes going missing on a fairly big WordPress database!

Reply
1. alexg says:
  
  September 19, 2017 at 4:49 pm
  
  Yes this is always a stressful task when done on live data. Hope you solved it without too much pain.
  
  Reply
David Y. says:

October 16, 2017 at 8:08 am

Thanks! This worked for me too! But I had to manually assign the primary key and auto_increment in the wp_posts table. By the way, do you know the cause of this issue? What made the table’s primary keys and auto_increments not preserved?

Reply
1. alexg says:
  
  October 16, 2017 at 8:28 am
  
  Good to hear that. I believe this can happen if you use phpmyadmin to export and re-import your database under certain conditions. Not sure.
  
  Reply
Jeff says:

December 1, 2017 at 10:52 pm

I should send you half of my life savings.
Thank you.

Reply
1. alexg says:
  
  January 16, 2018 at 8:27 am
  
  LOL please do!
  
  Reply
Ray says:

January 9, 2018 at 5:01 pm

You are a lifesaver!!

Reply
1. alexg says:
  
  January 18, 2018 at 2:17 pm
  
  🙂
  
  Reply
cakePlease says:

January 17, 2018 at 10:40 pm

Im getting an error:
Parse error: syntax error, unexpected ‘[‘ in /var/www/html/dbscript.php on line 34

which is this line: $key_name = $stm->fetch()[‘Column_name’];

Do you happen to know an easy fix? Currently running PHP 5.3, which i suspect is the issue. On a php7 install, i didnt have this issue

Reply
1. alexg says:
  
  January 18, 2018 at 10:15 am
  
  Just do it in two steps, i.e. something like:
  
  $result = $stm->fetch();
  $key_name = $result[‘Column_name’];
  
  Reply
Eric says:

March 28, 2018 at 3:29 am

Thanks for posting this, I hope it can solve my issue. I’m getting this error when i try to post:

WordPress database error: [Duplicate entry ‘0’ for key ‘PRIMARY’]
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (14963, ‘_encloseme’, ‘1’)

However, I am not a developer, and not really sure what to do with your script, Is PHP CLI in cpanel somewhere?

Reply
1. alexg says:
  
  March 28, 2018 at 8:16 am
  
  I would highly recommend that you first take a full backup of your database (you can do a backup with the “export” command in phpmyadmin).
  
  Also it might be best to ask a developer to do this, or at least someone with some experience in SQL. It is not easy to run this script if you do not know what you are doing. You will need ssh or at least sftp access.
  
  good luck
  
  Reply
ManagedWPhosting says:

April 2, 2018 at 6:54 pm

Hi there, 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 (the ones with zeros as PK values) 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.

Reply
Siamajor says:

April 18, 2018 at 7:55 am

Thank you! Thank you! Thank you!
With my database, I would have gone mad with manual fixing!
God save you!

Reply
1. alexg says:
  
  April 20, 2018 at 1:08 pm
  
  Glad it helped! 🙂
  
  Reply
Pete says:

July 11, 2018 at 10:27 am

Thank you !!!
Life saver 🙂

Reply

Hello,
I have the same problem of missing AI flag. but two things I want to say:
1. When I imported the dump it showed an error for wp_options table. – “duplicate entry 0 for primary key”
and same error while creating new posts and pages.
2. when I ran your script to resolve this issue everything goes fine but its saying that WP_posts has no primary key.
Any suggestion will be helpful. Thank you,

Try setting the `ID` column to be the primary key.

Hi i have the same issue and ran your script. But the wp_posts table cannot be set to auto_increment, for all the others it worked.

The error appears the following:
Incorrect table definition; there can be only one auto column and it must be defined as a key

I am running out of ideas, hope you can help, thank you so much

Hello, I am not sure why you get this error.

First make sure you have a backup. Then check your wp_posts table and see if the ID column is set to be the primary key.

This is what the DESCRIBE command should output for wp_posts:

mysql> describe wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   |     | NULL                |                |
| post_title            | text                | NO   |     | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(255)        | NO   |     |                     |                |
| post_name             | varchar(200)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext            | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+
23 rows in set (0.00 sec)

What do you get when you type describe wp_posts;? If the ID column is there but it’s not marked as a key, then you could do an ALTER on your table. I’m thinking something like:

ALTER TABLE wp_posts MODIFY ID BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (ID);

If you let me know what your table structure looks like I might be able to help.

with regards

sixlegs says:

September 24, 2018 at 1:48 pm

Thank you so much!
In my opinion the AUTO_INCREMENT gets lost if we have an incomplete phpmyadmin-Data-Import.
The structure of the SQL-Import-Skript is:
——————–
DROP TABLE…
CREATE TABLE…
INSERT INTO…

// repeat this for all the other tables

ALTER TABLE `tablename`
ADD PRIMARY KEY (`id`)….

ALTER TABLE `tablename`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=167;

————-

If the import process fails after building the tables and before adding the auto-increment values, your DB looks good but breaks as soon as you start adding something new.

This happened to my Joomla website with about 100 tables – so your script was a real time saver for me!

Reply
TJ says:

February 21, 2019 at 1:16 pm

You are awesome! You saved me so much time!
Thank you!!!!

Reply
1. alexg says:
  
  February 21, 2019 at 1:18 pm
  
  Very glad to hear it!
  
  Reply
Julen says:

July 23, 2019 at 6:35 pm

I cant explain how happy i am. i’ve been trying to solve this problem for a week now. Your script worked perfectly.
thank you thank you thank you!

Reply
1. alexg says:
  
  July 24, 2019 at 6:47 am
  
  That’s awesome! Thanks 🙂
  
  Reply
Hubert Souchaud says:

September 27, 2019 at 1:46 pm

Merci, merci, merci… 1000 fois!
Combien je vous dois? 😉

Note:
1/ first I had to delete any duplicate ids (deactivate jetpack site monitor who create transient regularly, with a lot of 0 ids in my case in wp-options table)
2/ before I’d reconfigured the primary key & index of any table because failed during import
3/ run your script

Thanks a lot Alex, ………

Reply
1. alexg says:
  
  September 27, 2019 at 2:08 pm
  
  Thanks for sharing your experience here. Glad you got it working!
  
  Reply
Darek says:

July 1, 2020 at 1:14 am

This was a perfect fix. Thank you for your beautiful work.

Reply
1. alexg says:
  
  August 4, 2020 at 4:53 pm
  
  And thank you for your very kind words!
  
  Reply
José Ferreira Neto says:

August 4, 2020 at 4:16 pm

Thanks buddy!
There are days that I am angry with a website that I am exported to a local server and this script saves my week!

Reply
1. alexg says:
  
  August 4, 2020 at 4:53 pm
  
  Very glad that the article helped you!
  
  Reply
Filip says:

October 4, 2020 at 11:07 pm

Thank you so much buddy!!! I was unable to login in backend, table “yourprefix_usermeta” gets session_tokens value under ID=0 and then tries to double it, resulting in login page just being refreshed:

[04-Oct-2020 19:42:21 UTC] WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO `wp4jf_usermeta` (`user_id`, `meta_key`, `meta_value`) VALUES (1, ‘session_tokens’, ‘a:1:{s:64:\”9baffd8837197c7ddb22d813a8465ee1a…

I saw this and solution was in front of my eyes, but googling took me to your website and first sentence opened my eyes! I am sure thousands of developers are going nuts over the fact they are just getting login page refreshed after trying to log in! Never happened before with AUTOINCREMENT after migrating, strange!
Anyway, thak you very much, life saving info. I did it manually, since I had many double IDs, especially in “yourprefix_options” table. So – for some of you guys: phpmyadmin and go through it one by one, or if you’re lucky use Alex’s script!

Reply
1. alexg says:
  
  October 5, 2020 at 9:42 am
  
  Thank you for the additional info, glad you got it working!
  
  Reply