Fixing wp_options Table Missing Unique Column and Primary Key

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-root

This 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-root

Now check if it worked

wp db query "DESCRIBE $(wp db prefix --allow-root)options" --allow-root

Looks 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-root

If 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-root

In 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-root

Keeping 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-root

Verifying 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-root

If you didn’t get any errors then check the table again

wp db query "DESCRIBE $(wp db prefix --allow-root)options;" --allow-root

Tada! 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?

2 thoughts on “Fixing wp_options Table Missing Unique Column and Primary Key”

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

Comments are closed.