WordPress and MySQL: fixing the wp_options table

We had a weird problem when moving a WordPress website for a customer: after DUMPing it out of MySQL, we noted that the wp_options table had a problem, namely the table had no primary key and no index on the option_name field. We noted that because the WordPress website, once moved, looked akward and wrong, missing some key pieces and it also seemed to use settings that were old. The reason was that, with primary key missing, lots of duplicate records were stored inside the table and I’m pretty sure it was not what the website was meant to do. Field option_id was set to 0 in most cases when it should have been an auto-incremented integer plus it seemed that the website was not updating settings, rather it was storing a new copy of them each time.

The akward look was probably due to WordPress not being able to load-up the most recent settings but the weird thing was that it was working like a charm on its existing server. However, we noticed that the source server was running MySQL 5.1 while the destination was instead running MySQL 5.7. MySQL is that weird beast where everything can happen but anyway that 5.1 version was really really old. I would expect such things not to happen in an enterprise world (never had such problems with SQL Server) but…

The problem

So the first problem we had to solve was re-enabling primary key for field option_id and its indices but we couldn’t add a primary key on that field since thousands of record were using the same key (zero) so we had to find a way to update them with a unique value. The best solution would be to have consecutive integers though that wouldn’t ensure that we are restoring the right order. But I’m afraid there’s no real way to be sure about that. However, when enumerating records one might assume that the table scan would populate results from the oldest to the newest. Not sure about this but we might try.

So we basically developed a stored procedure that would scan that table and update each record to set a new option_id. One of the key problems with this is that you need to perform only an update at a time because you cannot use any relevant key to uniquely get the record given that option_id is zero. Other fields might be the same for multiple records so for example if we try to select records based on option_name we might have multiple records update with the same option_id and we wouldn’t solve our problems.

We might just hope that we’re lucky enough so that scanning for zero keys and updating just a single record would just update in the same order. Anyway, the key to change the table design is to have different keys, it is not strictly necessary to have the same order though quirks might happen if we don’t do that. The real important thing is to ensure we won’t create more duplicates with the same id.

The solution : a stored procedure

First of all, we must give credits where dued. The base for this is a Stack Overflow example we found on the Web. Here’s the code. Caution because by executing this one you will delete any update_options stored proc that you have defined inside your database.

DELIMITER $$

DROP PROCEDURE IF EXISTS `update_options` $$
CREATE PROCEDURE `update_options`(arg1 INT, arg2 INT) -- 1 input arguments; 
BEGIN

-- declare the program variables where we'll hold the values we're sending into the procedure;
-- declare as many of them as there are input arguments to the second procedure,
-- with appropriate data types. 
-- val1 is the option_value we will look for
-- val2 is the option_name we will look for
-- counter is the option_id we will start counting from 
DECLARE val1 longtext;
DECLARE val2 VARCHAR(65535);
DECLARE counter INT DEFAULT arg2;

-- we need a boolean variable to tell us when the cursor is out of data

DECLARE done TINYINT DEFAULT FALSE;

-- declare a cursor to select the desired columns from the desired source table1
-- the input argument (which you might or might not need) is used in this example for row selection

DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
 CURSOR FOR
 SELECT t1.option_value, t1.option_name
   FROM wp_options t1
  WHERE option_id=0 LIMIT arg1; 

-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
-- and since this is a CONTINUE handler, execution continues with the next statement.   

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- open the cursor

OPEN cursor1;

my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
LOOP

  -- read the values from the next row that is available in the cursor

  FETCH NEXT FROM cursor1 INTO val1, val2;

  IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
    LEAVE my_loop; 
  ELSE -- val1 and val2 will be the next values in table t1, 
       -- so now we will attempt to set this row to the counter we want
    UPDATE wp_options SET option_id=counter WHERE option_value=val1 AND option_name=val2 AND option_id=0 LIMIT 1;
    -- we will ensure that only exactly one row will be changed
    -- to avoid duplicates with the same option_id
  END IF;
  set counter=counter+1;
END LOOP;

-- execution continues here when LEAVE my_loop is encountered;
-- we're done doing changes so we have nothing else to do here

-- the cursor is implicitly closed when it goes out of scope, or can be explicitly closed if desired

CLOSE cursor1;

END $$

DELIMITER ;

Ok, so we basically have one stored proc and we can call that by issuing

call update_options(1, 1000);

The first input parameter sets how many records we will try to update, the second argument will set the option_id value we will start counting from. This is an important parameter because you need to know the max id you already have inside your table to avoid creating more duplicates. Now before you run this one it is important to take a backup of your database. Do it now: we will wait for you…

How to get the important parameters

Ok so basically now you have to first determine the starting value for option_id. You can do that by issuing

SELECT MAX(option_id) FROM wp_options;

If you get 980 here or whatever, just add 1 (so, it would be 981) and use that as the second input parameter. Now the second value you need is the total number of records that you will need to update. This is easy:

SELECT Count(*) FROM wp_options WHERE option_id=0;

Note: we are assuming that your options table is named wp_options here however this probably isn’t the case as table prefix is usually set at installation time. You need to change that inside your stored procedure as well.

We tried to chunk updates by monitoring the effects of the changes. Assuming that your start id is 1000, we would first issue a

call update_options(1,1000);

in order to update just one record and check that everything is going fine. Then we will add more records, for example 20 or 40 and then start the procedure with bigger chunks, say 300 or 500 depending on how many records you need to update.

What happens next

If everything goes fine the first thing you have to do is to set option_id as primary key, enable auto-increment for that field and set the new starting index. You will easily find how to do that on the Internet. Nothing very difficult.

Once done, you also have to enable indexing on option_name for performance reasons: we noted that usually both of such settings went lost.

What can go wrong

Basically, the first thing that can go wrong is that you won’t be able to make WordPress work fine once you do all this. Database will be fixed but, for any reason, the order of the settings is not what WP used on source server. Annoying but in my experience you should be able to fix this or at least have just a few things to restore to their former configuration.

Sometimes I also experienced a problem with option_value data, usually when you had some troubles with converting data from latin1 to utf8. Stored procedure might stop reporting an error with HEX-looking data. I didn’t investigate why that happens, actually, since it usually happens on a handful of records (usually 2 or 3 per database) so it is easier to issue a command like this:

UPDATE wp_options SET option_id=newvalue WHERE option_id=0 LIMIT 1;

where newvalue is the highest counter plus one you currently have inside wp_options. After updating this single record, you can call the stored procedure again, updating with the new arguments.

Summary

abc