For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then tried to change it to AUTO INCREMENT but it came up with Duplicate Entry error and then i found that it had duplicated/multiple entries with same IDs as well. Here’s the remedy i had to apply in order to delete those duplicate entries and change ID field to AUTO INCREMENT once again.
(Note: Backup you wp_users table to avoid any mis-happening.)
CREATE TABLE
bad_temp(`ID` bigint(20) unsigned NOT NULL,
`user_login` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(64) NOT NULL DEFAULT '',
`user_nicename` varchar(50) NOT NULL DEFAULT '',
`user_email` varchar(100) NOT NULL DEFAULT '',
`user_url` varchar(100) NOT NULL DEFAULT '',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(60) NOT NULL DEFAULT '',
`user_status` int(11) NOT NULL DEFAULT '0',
`display_name` varchar(250) NOT NULL DEFAULT '');
INSERT INTO bad_temp(`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) SELECT DISTINCT `ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name` FROM `wp_users`;
DELETE FROM `wp_users`;
INSERT INTO `wp_users`(`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) SELECT `ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name` FROM bad_temp;
DROP TABLE bad_temp;
I hope that helps someone.
You may also like:
How to update a mysql table with the count of another table while using like statement
A few notes on joining a MySQL table to itself to get unique combination of field data
Fixing ‘mysql’ is not recognized as an internal or external command in Windows7
Calculating difference between two mysql dates in seconds
How to get file extension using PHP
