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!

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 or phpMyAdmin

wp db query "DESCRIBE wp_options"

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 and unique constraint

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

To make the option_id column the primary key you can run this query

wp db query "ALTER TABLE wp_options MODIFY option_id INT AUTO_INCREMENT PRIMARY KEY;"

Now check if it worked

wp db query "DESCRIBE wp_options"

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

This query will attempt to add the UNIQUE constraint on the option_name column

wp db query "ALTER TABLE wp_options ADD UNIQUE (option_name);"

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_options GROUP BY option_name HAVING optioncount > 1 ORDER BY optioncount DESC;"

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

This keeps the oldest (MIN) option_id for any duplicate option_name values

DELETE
FROM wp_2_options
WHERE option_id NOT IN
    (SELECT *
     FROM
       (SELECT MIN(n.option_id)
        FROM wp_2_options n
        GROUP BY n.option_name) x)

Here is a one liner for use with WP-CLI’s wp db query command

wp db query "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)"

This keeps the newest (MAX) option_id for any duplicate option_name values

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 line for use with WP-CLI wp db query command

wp db query "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)"

Now let’s do the UNIQUE constraint again on the option_name column

wp db query "ALTER TABLE wp_options ADD UNIQUE (option_name);"

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

wp db query "DESCRIBE wp_options;"

Tada!

+--------------+---------------------+------+-----+---------+----------------+
| 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?