I have had the opportunity and pleasure of doing a lot of WordPress and WooCommerce database cleanups on Codeable to speed up sites. This was one of my favorite projects to complete for a large multisite on Kinsta (Review) that gets around 1 million page views per week.
If you see this error in phpMyAdmin ‘Current selection does not contain a unique column’ then this post is for you!
Please take a backup of your options table first!
If you would like help with this feel free to reach out here 🙂
Fixing wp_options Table Missing Unique Column and Primary Key
First check the structure of the wp_options table with this command, I’m using WP-CLI but you can use Adminer (plugin) or phpMyAdmin
wp db query "DESCRIBE $(wp db prefix --allow-root)options" --allow-rootThis output shows us that there is no primary key (the option_id) and no unique constraint imposed on the option_name column
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| option_id | bigint(20) unsigned | NO | | NULL | |
| option_name | varchar(191) | YES | | NULL | |
| option_value | longtext | NO | | NULL | |
| autoload | varchar(20) | NO | | yes | |
+--------------+---------------------+------+-----+---------+-------+This is what a correct wp_options table looks like with the PRIMARY key (PRI) and UNIQUE constraint (UNI)
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| option_name | varchar(191) | YES | UNI | | |
| option_value | longtext | NO | | NULL | |
| autoload | varchar(20) | NO | | yes | |
+--------------+---------------------+------+-----+---------+----------------+Fixing wp_options Missing Primary Key
To make the option_id column the primary key you can run this query
wp db query "ALTER TABLE $(wp db prefix --allow-root)options MODIFY option_id INT AUTO_INCREMENT PRIMARY KEY;" --allow-rootNow check if it worked
wp db query "DESCRIBE $(wp db prefix --allow-root)options" --allow-rootLooks like the primary key is now enabled, time for the unique constraint on the option_name column
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| option_id | int(11) | NO | PRI | NULL | auto_increment |
| option_name | varchar(191) | YES | | NULL | |
| option_value | longtext | NO | | NULL | |
| autoload | varchar(20) | NO | | yes | |
+--------------+--------------+------+-----+---------+----------------+Fixing wp_options Missing Unique Column
This query will attempt to add the UNIQUE constraint on the option_name column
wp db query "ALTER TABLE $(wp db prefix --allow-root)options ADD UNIQUE (option_name);" --allow-rootIf you get an error like ERROR 1062 (23000) at line 1: Duplicate entry 'jetpack_available_modules' for key 'option_name'
then there are some duplicate option_name values, you can view them with this query
wp db query "SELECT option_name, COUNT(*) optioncount FROM $(wp db prefix --allow-root)options GROUP BY option_name HAVING optioncount > 1 ORDER BY optioncount DESC;" --allow-rootIn ascending order here are all of the duplicates, we are going to remove them automatically
+---------------------------------------------+-------------+
| option_name | optioncount |
+---------------------------------------------+-------------+
| jetpack_callables_sync_checksum | 45094 |
| jetpack_sync_full_config | 52 |
| jetpack_sync_full_enqueue_status | 49 |
| jpsq_sync_checkout | 12 |
| widget_wpex_bbpress_forum_info | 5 |
| widget_wpex_users_grid | 5 |
| jetpack_sync_full__params | 4 |
| jetpack_sync_settings_sync_via_cron | 3 |
| jetpack_sync_full__started | 3 |
| widget_wpex_bbpress_topic_info | 2 |
| jetpack_sync_settings_max_enqueue_full_sync | 2 |
| optin_monster | 2 |
| wpmandrill-stats | 2 |
| widget_wpex_social_widget | 2 |
| widget_pages | 2 |
| widget_wpex_flickr | 2 |
| widget_calendar | 2 |
| widget_wpex_video | 2 |
| widget_shapely_recent_posts | 2 |
| widget_tag_cloud | 2 |
| widget_wpex_recent_posts_thumb_grid | 2 |
| widget_shapely-cats | 2 |
| widget_akismet_widget | 2 |
| widget_shapely_home_parallax | 2 |
| widget_bbp_views_widget | 2 |
| rewrite_rules | 2 |
| widget_shapely_home_features | 2 |
| jetpack_sync_settings_cron_sync_time_limit | 2 |
| _transient_jetpack_sync_callables_await | 2 |
| wpb_js_templates | 2 |
| widget_shapely_home_cfa | 2 |
| widget_bbp_forums_widget | 2 |
| _transient_doing_cron | 2 |
| widget_bbp_login_widget | 2 |
| widget_shapely_home_clients | 2 |
| yst_ga_top_pageviews | 2 |
| widget_media_image | 2 |
| widget_shapely_video_widget | 2 |
| jetpack_available_modules | 2 |
| widget_media_video | 2 |
| widget_shapely_home_testimonial | 2 |
| widget_shapely_home_portfolio | 2 |
| widget_text | 2 |
| widget_rss | 2 |
| wpseo_onpage | 2 |
| image-lazy-load | 2 |
+---------------------------------------------+-------------+Automatically removing duplicate values in the option_name table can be done in two ways: using the oldest option_id value (smallest number) or the newest option_id value (largest number).
Keeping Oldest Duplicate option_name
This SQL query shows you all but the oldest rows (MIN) option_id for any duplicate option_name values
SELECT *
FROM wp options
WHERE option_id NOT IN
(SELECT *
FROM
(SELECT MIN(n.option_id)
FROM wp_options n
GROUP BY n.option_name) x)This SQL query keeps the oldest (MIN) option_id for any duplicate option_name values and deletes all of the newer duplicate rows
DELETE
FROM wp options
WHERE option_id NOT IN
(SELECT *
FROM
(SELECT MIN(n.option_id)
FROM wp_options n
GROUP BY n.option_name) x)Here is a one liner for use with WP-CLI’s wp db query command to keep the oldest duplicate option_name
wp db query "DELETE FROM $(wp db prefix --allow-root)options WHERE option_id NOT IN (SELECT * FROM (SELECT MIN(n.option_id) FROM $(wp db prefix --allow-root)options n GROUP BY n.option_name) x)" --allow-rootKeeping Newest Duplicate option_name
This SQL query shows all but the newest rows (MAX) option_id for any duplicate option_name values
SELECT *
FROM wp_options
WHERE option_id NOT IN
(SELECT *
FROM
(SELECT MAX(n.option_id)
FROM wp_options n
GROUP BY n.option_name) x)This SQL query keeps the newest (MAX) option_id for any duplicate option_name values and deletes all of the older duplicate rows
DELETE
FROM wp_options
WHERE option_id NOT IN
(SELECT *
FROM
(SELECT MAX(n.option_id)
FROM wp_options n
GROUP BY n.option_name) x)Here is a one-liner for use with WP-CLI wp db query command for keeping the newest duplicate option_name
wp db query "DELETE FROM $(wp db prefix --allow-root)options WHERE option_id NOT IN (SELECT * FROM (SELECT MAX(n.option_id) FROM $(wp db prefix --allow-root)options n GROUP BY n.option_name) x)" --allow-rootVerifying wp_options Keys and Constraints
Now let’s do the UNIQUE constraint again on the option_name column
wp db query "ALTER TABLE $(wp db prefix --allow-root)options ADD UNIQUE (option_name);" --allow-rootIf you didn’t get any errors then check the table again
wp db query "DESCRIBE $(wp db prefix --allow-root)options;" --allow-rootTada! Now the WordPress wp_options database table has the required PRIMARY KEY on the option_id table and the UNIQUE constraint on the option_name table
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| option_name | varchar(191) | NO | UNI | | |
| option_value | longtext | NO | | NULL | |
| autoload | varchar(20) | NO | | yes | |
+--------------+---------------------+------+-----+---------+----------------+Here is the long list of sources I consulted when resolving this issue
Sources
How to Delete Duplicates on a MySQL Table
WP-CLI Search and Replace Tables Skipped
Set Column as Primary Key in MySQL
W3 Schools Unique Column MySQL
Alter Column to Auto Increment
Finding Duplicate Values in MySQL
Delete all Duplicate Rows Except for One
How Can I Remove Duplicate Rows?
Mike, thanks for this great detailed article. Is there a benefit or detriment to keeping newest or oldest duplicate option name? I’m not sure where my client’s database took a bad turn, but in reviewing numerous tables, it looks like it might have been a while back so my plan presently is to keep the oldest duplicates. I’d be interested to know your thoughts.