[BigBri.net] The official Brian Fields site

15Jun/070

WP => UTF-8 [and table name change]

During some down-time I was doing some minor website maintainance lately.  One of my big changes was to upgrade WordPress to the 2.2 version, which has a lot of core changes in it.  After upgrading, I noticed some problems immediately.  Suddenly, there were all these funky characters interspersed throughout my blog posts like "•".  At first I thought maybe wordpress was misinterpreting something, but a look at an .sql backup confirmed that these characters were hardcoded in the database.  Some extensive searching on the wordpress forums was fruitless.  There was a lot of talk about the changes to UTF-8 in WordPress 2.2, and some other people had been having similar problems, but I didn't see any clear-cut solutions.

Finally, I decided to export my database as an *.sql file, do a find-and-replace in a text editor, and import the database again.  I had to do this once before when I changed the URL of a wordpress blog.  All went great, but I still had some questions about UTF-8.  As far as I understand it (which is nearly enough to get myself into trouble) the switch to UTF-8 is being made to sort of "future proof" wordpress and provide better compatibility or whatever.  Suddenly, I felt like my database characterset which was "latin_swedish_ci" was somehow inferior.  A quick search on the codex turned up an article on how to convert your database to UTF-8.  The directions are obtuse to say the least, and it sounds like a good hour of typing in SQL commands, YUK!

The answer to my quest for the most upgraded version of wordpress I can get came when I was doing some other database management.  I had several instances of wordpress on my domain, and I wanted to get them all running off of the same database.  Some of them were easy enough to move over, I just copied the tables from one database to another in phpMyAdmin.  One was a bit tougher though, because I had two installations that used the same default table prefix "wp_".  I couldn't have them both in the same database.  The answer seemed simple enough: Just rename the tables, update wp-config.php to reflect the new table names, and off I go!  Unfortunately, the blog worked perfectly after the change, but the admin interface gave me the error "You do not have sufficient permissions to access this page."

Some more searching in the wordpress forums yielded mixed results.  It seemed that some people discovered that table names are coded into the usermeta table and affect the user's permissions.  Some people had found success in some pretty weird ways, mostly by creating a new copy of wordpress and copying the usermeta table to where they wanted it.  I had a much simpler solution. 

I had remembered that there was a way to export your wordpress data into an xml file through the admin interface.  I got to work.

Now, I think this goes without saying, but I don't want 100 comments about how it's my fault that your blog is broken, so BACKUP YOUR DATABASE before you begin. 

These instructions will get you up and running with a wordpress 2.2 installation running on a UTF-8 encoded database with DIFFERENT table prefixes from what you previously had. 

  1. Backup your database if you haven't already.
  2. Disable all your plugins.
  3. Create a new folder on your server (for the purposes of this example I will use the folder "/wp").
  4. Upload WordPress 2.2 to this folder.
  5. Edit the wp-config.php file, being sure to set a UNIQUE table prefix value on the line:
    $table_prefix = 'wp_'; // Only numbers, letters, and underscores please!
    (e.g. if your old website used the default 'wp_' prefix, then give this one the prefix 'newwp_')
  6. Install your new instance of wordpress.  (go to http://example.com/wp/wp-admin/install.php)
  7. Configure your new instance of wordpress, update your password, configure your options, etc.  I suggest you temporarily name your new instance something like "Newblog" so that when you have two windows open you can differentiate between the two.
  8. Open both your old blog and your new blog in new tabs of your browser so that you can easily switch between them. 
  9. Go to the admin panels of both blogs.
  10. In your old blog, go to "Manage>Export" and export your *.xml file.
    Export XML
  11. Now switch to the new blog and import the XML file at "Manage > Import".  This will import all your posts, comments, categories, upload data, etc.  It does not import your links, so we have to move on.
  12. WordPress includes the functionality to import links via OPML, but no simple way to export them (in the admin interface, at least).  In the dashboard for your new blog, first go to "Blogroll > Import Links", then enter the URL for your old blog + wp-links-opml.php so it looks like this: http://example.com/wp-links-opml.php.  Then click "Import OPML". (this assumes your old blog was in the root of your domain)
    OPML
  13. Now look everything over, clean up any problems with your link categories, confirm all your posts and comments are there, etc. You're almost done!
  14. Now, without deleting anything, let's edit the wp-config.php file of your new blog.  We will edit the location of the blog by adding the following lines to it:
    define('WP_SITEURL', 'http://www.example.com);
    define('WP_HOME', 'http://www.example.com');

    You can also go to the admin interface of the new blog and enter these in the options tab. This will transition your old installation to the new database tables.
  15. Backup the files for your old blog and upload a fresh version of WP 2.2 with the wp-config file you just created. 
  16. Test everything by going to http://example.com.  Make sure everything works, install and activate all your plugins.
  17. Now you can delete the folder example.com/wp and drop the tables with the "old" prefix from your database.
Comments (0) Trackbacks (2)

Leave a comment