auto_increment flag repair on primary keys of a WordPress MySQL database

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.


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:

  1. Check to see that your issue is actually one of missing auto_increment flags. This script will only repair this particular error.
  2. 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.
  3. Change the host, dbname, username and password in the code to those that match your system.
  4. 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.


// 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');
$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'" );
    $key_name = $stm->fetch()['Column_name'];

    // get the primary key type
    $stm = $conn->prepare( "show fields from $table_name where Field = '$key_name'" );
    $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";
            $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 );

            // 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 );

        } 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

  1. 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?

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

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

  3. 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?

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

  4. 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:

    No need to guess the auto-increment value too.

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

  6. 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,

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

        1. 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:


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

          with regards

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

    // repeat this for all the other tables

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

    ALTER TABLE `tablename`


    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!

  8. 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!

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

    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, ………

  10. 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!

Leave a Reply

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