Print

Missing Primary Key in WordPress Table wp_options

Sometimes under certain circumstances, wp_options (or any other table) can lose its primary key index.

The exact reason for that issue is not 100% clear. It seems this can happen if the database has been migrated from one database to another manually or by using a plugin or tool that did not cover all constellations like using different MySQL versions or types like InnoDB or MyISAM.

To make sure: WP STAGING is not the reason for that error. We only help you to detect that error by showing a prominent warning in your admin dashboard when this happens:

We do this because that error is a critical and very insidious one. You will not even notice it until it is too late to solve it easily. The longer you wait to fix it, the harder it gets to solve.

That’s the case because your website will act nearly as before and only work differently under the hood.

Let me give you a quick example of why this then matters for the table wp_options:

The table wp_options has a column name option_id. This ID is usually a unique number and can only be assigned once to a row. The ID is incremented automatically for each row like this:

option_id | option_name
1                     |  option1
2                     | option2
3                     | option3

If the primary key is missing, the option_id is not incremented any longer, and all new rows will get the option_id number 0 like this:

option_id | option_name
1                     |  option1
2                     | option2
3                     | option3
0                     | option4
0                     | option5
0                     | option6

The problem appears when you try to export and import these rows into another database. As the option_id must be a unique number, it will show SQL insert errors like “Can not insert duplicate key … ” and stop the migration.

As a result, WP STAGING and any other migration plugin cannot execute the database data transfer to another database. For instance, the pushing process from the staging site to the production site will fail.

This screenshot shows a regular wp_options table with a primary key set to the option_id column:

This second screenshot shows the wp_options table with a missing primary key:

Note: You will need a database management tool in this tutorial, for example, “adminer” or PHPMyAdmin.

Rename Existing option_id’s

  1. Back up your entire database and download it.
  2. Modify the option_id values for all duplicated IDs.
    I suggest getting the largest number you have in the option_id column and increase it one by one.This screencast shows how you can do that:

Add Primary Key Index and Alter Table

  1. Open adminer and navigate to wp_options table.
  2. Click on “alter indexes” as in this screenshot:
  3. Then set the “option_id” as the primary key:
  4. If you get the error Duplicate entry ‘0’ for key PRIMARY, it means you still have duplicate ID’s in the table, and you did not change all of them:
  5. If that was the case, then find the leftovers and count up their values as well so that there are no more duplicates in the table. After that, you can repeat to add the PRIMARY KEY to the table.

After fixing the missing primary key, you are ready to push the staging site to the live site; however, it’s recommended to back up the database before doing that.

Updated on May 26, 2021