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
You saved my day! Thanks a lot !!!!!
Very glad to hear that 🙂
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!
Yes this is always a stressful task when done on live data. Hope you solved it without too much pain.
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?
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.
I should send you half of my life savings.
Thank you.
LOL please do!
You are a lifesaver!!
🙂
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
Just do it in two steps, i.e. something like:
$result = $stm->fetch();
$key_name = $result[‘Column_name’];
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?
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
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.
Thank you! Thank you! Thank you!
With my database, I would have gone mad with manual fixing!
God save you!
Glad it helped! 🙂
Thank you !!!
Life saver 🙂
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_poststable and see if theIDcolumn is set to be the primary key.This is what the
DESCRIBEcommand should output forwp_posts:What do you get when you type
describe wp_posts;? If theIDcolumn is there but it’s not marked as a key, then you could do anALTERon 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
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!
You are awesome! You saved me so much time!
Thank you!!!!
Very glad to hear it!
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!
That’s awesome! Thanks 🙂
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, ………
Thanks for sharing your experience here. Glad you got it working!
This was a perfect fix. Thank you for your beautiful work.
And thank you for your very kind words!
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!
Very glad that the article helped you!
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!
Thank you for the additional info, glad you got it working!
Worked great, thanks a lot. I adjusted the code as follows::
‘SET sql_mode=”NO_ZERO_DATE”‘
]);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully\n”;
// get all table names
$stm = $conn->prepare(‘SHOW TABLES’);
$stm->execute();
$table_names = $stm->fetchAll(PDO::FETCH_COLUMN);
// 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();
$primary_key_info = $stm->fetch(PDO::FETCH_ASSOC);
$key_name = $primary_key_info[‘Column_name’] ?? null;
if ($key_name) {
echo “Primary key is $key_name\n”;
// get the primary key type
$stm = $conn->prepare(“SHOW FIELDS FROM `$table_name` WHERE Field = ?”);
$stm->execute([$key_name]);
$key_type_info = $stm->fetch(PDO::FETCH_ASSOC);
$key_type = $key_type_info[‘Type’];
if ($key_type) {
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(PDO::FETCH_ASSOC)[‘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 type found for $key_name in table $table_name.\n”;
}
} else {
echo “No primary key found for table $table_name.\n”;
}
}
echo “\n\nFinished\n”;
} catch (PDOException $e) {
exit(“Connection failed: ” . $e->getMessage());
}
// close the connection
$conn = null;
I adjusted the code a bit. Thanks a lot.
// change these settings
$servername = ‘localhost’;
$username = ‘dbuser’;
$password = ‘password’;
$db = ‘database’;
try {
// connect using PDO
$conn = new PDO(“mysql:host=$servername;dbname=$db”, $username, $password, [
PDO::MYSQL_ATTR_INIT_COMMAND => ‘SET sql_mode=”NO_ZERO_DATE”‘
]);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully\n”;
// get all table names
$stm = $conn->prepare(‘SHOW TABLES’);
$stm->execute();
$table_names = $stm->fetchAll(PDO::FETCH_COLUMN);
// 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();
$primary_key_info = $stm->fetch(PDO::FETCH_ASSOC);
$key_name = $primary_key_info[‘Column_name’] ?? null;
if ($key_name) {
echo “Primary key is $key_name\n”;
// get the primary key type
$stm = $conn->prepare(“SHOW FIELDS FROM `$table_name` WHERE Field = ?”);
$stm->execute([$key_name]);
$key_type_info = $stm->fetch(PDO::FETCH_ASSOC);
$key_type = $key_type_info[‘Type’];
if ($key_type) {
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(PDO::FETCH_ASSOC)[‘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 type found for $key_name in table $table_name.\n”;
}
} else {
echo “No primary key found for table $table_name.\n”;
}
}
echo “\n\nFinished\n”;
} catch (PDOException $e) {
exit(“Connection failed: ” . $e->getMessage());
}
// close the connection
$conn = null;