Here’s the short of it for those that don’t want to read my whole epic.
While trying to upgrade from WordPress 2.1 to 2.6 I was presented with the database upgrade page when trying to get to the backend (/wp-admin). Upon clicking the Upgrade button/link a half-ream long list of “Table doesn’t exist” errors were displayed.
WordPress failed database upgrade - Table doesnt exist error messages.
The reason WordPress can’t find the tables is because it fails to create them during the database upgrade. For the full reason behind why this happens you’ll have to read the whole post. If you’re only interested in what to do to get it working the next three paragraphs and two code samples should explain all.
In WordPress 2.2 two new settings were introduced in the wp-config.php file namely DB_CHARSET and DB_COLLATE. By default, that is in the wp-config-sample.php file, it sets these variables to ‘utf8′ and ‘utf8-general’ respectively.
define('DB_CHARSET', 'utf8');
define('DB_COLLATE', 'utf8-general-ci');
If you added the DB_COLLATE option to your old wp-config.php file and retained the default option as given in the sample file then it is highly likely you experienced this error since utf8-general-ci is not a valid MySQL collation. The silly little error that causes so much grief lies in the dashes — they should be underscores.
Most MySQL installations use utf8_unicode_ci as the default collation. You’re likely to get joy by setting the DB_COLLATE option to
define('DB_COLLATE', 'utf8_general_ci');
I had an old WordPress 2.1 installation that needed to be upgraded. Besides the obvious security issues that have been fixed and the features that have been added, I wanted to use a theme that required at least WP2.5 to work.
I don’t do anything on any of my live websites without first ensuring that I won’t break anything by trying the changes on the copies I keep locally on my development machine. Upon attempting the famous 3-step upgrade process I found that not only does WordPress struggle to create a bunch of new tables for some reason, it proceeds to delete the old WP2.1 categories, link2cat, and post2cat tables even though the CREATE TABLE queries for the replacement tables, terms, term_taxonomies, and term_relationships fail.
The only relevant thread I could find at WordPress’ support forums suggested incrementally upgrading and from the responses it suggested that it worked for the original poster. But why were the tables not being created? What do the other upgrade scripts do differently from the 2.6.2 one?
After some hunting I found the CREATE TABLE queries that are supposed to create the new tables during an upgrade to the file schema.php in wp-admin/includes/.
Copying and pasting the query verbatim into the MySQL Query Browser and replacing the variables with the values I suspected them to be (determined mainly from wp-config.php) indicated that the query was indeed failing. After ensuring that no amount of quoting or backticking would fix the query, the DEFAULT CHARACTER SET and COLLATE directives at the end of the CREATE TABLE query caught my interest. Could the use of dashes rather than underscores really be breaking the query?
A quick test by removing the directives entirely, forcing MySQL to use its defaults, verified it. The defaults WordPress supplied in its sample configuration file (wp-config-sample.php) were in fact not valid.
The WordPress documentation does mention that people who are upgrading should not include the DB_CHARSET and DB_COLLATE variables in their wp-config.php files unless they know what they’re doing… But that’s only on the ‘Editing wp-config.php’ page! This should be made very clear in the Upgrading WordPress documentation. For someone that doesn’t know the format MySQL expects arguments to COLLATE this can translate to days of struggle… Bad WordPress! No treat for you!