Peregrine

I finally got all of the blogs I manage updated to WordPress 2.3.1, today. Several friends who also use WordPress had experienced some difficulties upgrading to 2.3.0, so I had held off on the updates on some of the blogs until these past 3 days. There were two of them that I had some very small problems with, but they were solved very easily. One of those was this blog, the one you are reading right now.

First, when I was updating my wife’s blog, I got all in a hurry and forgot to make a backup of the database first. Then, when I connected to the wp-admin/upgrade.php script and clicked on the Upgrade WordPress button, all hell broke lose. I had DB errors left and sideways (thankfully, not quite right, though). At that moment, I realized that, “I could really use that DB backup right about now.” Well, I didn’t have it, so I tried the export function from the admin interface and that worked. At least I had her posts (there was a brand new one, too, which wasn’t in the most recent backup file that I did have).

The fix was pretty easy, though. The main problem was that there were several changes that required creating new tables in the DB (MySQL) and dropping others, but because the DB user that WordPress uses doesn’t just have full access to the database, these statements failed and prevented others from succeeding, too. For security reasons, I use GRANT statements for the tables that the app needs, which keeps it from accessing other tables in the same DB (there’s one database per user), which are for other apps. However, trying to run the wp-admin/upgrade.php script again was a no-go, as it thought the DB was fully up to date. A quick glance at the code in the wp-admin/upgrade.php script showed that this decision was based on the value in the db_version option.

I connected using the mysql command line client (over an ssh connection) and ran a couple of queries against one of the other DBs which had the same (older) version of WordPress. Once I had found the correct old value for the db_version option, I ran a simple UPDATE query (the italics indicate information that may differ on your databases):

mysql> UPDATE dbname.wp_options SET option_value='5183' WHERE option_name='db_version';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I then re-loaded the wp-admin/upgrade.php script in my browser, which now thought that the database was out of date. So far, so good. But, before trying the upgrade again, I also added a new GRANT for the WordPress user for that DB, allowing them to run CREATE statements against that user’s database:

mysql> GRANT CREATE ON database.* TO 'wp_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

This time, there were far fewer errors. It was able to all the CREATE TABLE statements. However, as there were no GRANTs to permit the WordPress database user to access the new tables, other operations failed. A quick look allowed me to pick out which tables I needed to issue GRANT statements for:

mysql> SHOW GRANTS FOR 'wp_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for wp_user@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
... snip ...
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_comments` TO 'wp_user'@'localhost'                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_categories` TO 'wp_user'@'localhost'               |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_postmeta` TO 'wp_user'@'localhost'                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_usermeta` TO 'wp_user'@'localhost'                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_posts` TO 'wp_user'@'localhost'                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_linkcategories` TO 'wp_user'@'localhost'           |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_options` TO 'wp_user'@'localhost'                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_post2cat` TO 'wp_user'@'localhost'                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_links` TO 'wp_user'@'localhost'                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `dbname`.`wp_users` TO 'wp_user'@'localhost'                    |
+------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

mysql> SHOW TABLES IN dbname;
+-----------------------+
| Tables_in_dbname      |
+-----------------------+
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
10 rows in set (0.00 sec)

Comparing those two lists showed that I needed to run a few GRANT statements:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER ON dbname.wp_term_relationships TO 'wp_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER ON dbname.wp_terrm_taxonomy TO 'wp_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER ON dbname.wp_terms TO 'wp_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

I then reset the db_version option, re-loaded the wp-admin/upgrade.php script in my browser and clicked the Upgrade WordPress button, again. This time, it worked perfectly. The database for my wife’s blog was fully repaired and upgraded for WordPress 2.3.1.

Now, I want to stress here that this would have gone a lot more smoothly, not to mention safely if I had only slowed down and made that backup of the her database before I started messing around with it. It’s very easy to do just that:

$ mysqldump -p dbname > ~/backup/$(date --iso-8601)-dbname.sql

Note, that this precise command line assumes that you are running from a user account who has a database account in MySQL with the same name as the username and that this user has access to the database in question. Adjust your usage appropriately. Also, if your server is fairly busy, you might want to use the mysqldumpslow command instead of the mysqldump one. mysqldumpslow merely takes its sweet time about things, so as to not put undue additional load on your servers, but otherwise its syntax and use is the same.

When I applied all this goodness to upgrade this blog, I ended up with just 3 errors from running the wp-admin/upgrade.php script, after I had gone through the process of adding new GRANTs for that dbuser to access my user’s databases. The errors were that 3 DROP TABLE statements had failed. These were easilly handled manually (I don’t want the applications to be able to DROP anything on their own):

mysql> USE otherdb;
mysql> DROP TABLE IF EXISTS wp_categories;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE IF EXISTS wp_link2cat;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS wp_post2cat;
Query OK, 0 rows affected (0.02 sec)

That’s it. It only took me about 15 minutes to do all of this and to extract the files for the new version of wordpress and copy and move over all the little tidbits (mostly for things found in the wp-content/ directory) to get each of the blogs updated to the latest release. Interrestingly, it took me about 2 hours to write, sanitize and proofread/edit this post.