Playing with the WordPress Database

After initially solving my database character encoding problems by ignoring the specific strings in the wp-config.php file, I was finally forced to alter the characters in the database during a recent reshuffle. Whilst there are two automated solutions available via plugin, namely g30rg3x‘s UTF-8 Database Converter and the Modified UTF8 Sanitize Plugin, sadly neither worked in my particular instance, and indeed the former is no longer supported for current versions of WordPress, though reports on the WordPress support forum suggest there should be no issues.

Fortunately, an excellent guide was available on Alex King’s blog. For more information and follow-up comments, you should definitely read the full post, but here’s a summary of the method that worked for me.

Note that you should always backup your database before attempting any such conversions. WP-DBManager comes highly recommended.

If you have access to phpMyAdmin:

  1. Use phpMyAdmin to dump the database (this should be in UTF-8 by default).
  2. Open the dumped file in Notepad or similar, and save it to ANSI format. In Notepad++ you can find this under “Format” and “Convert to ANSI”.
  3. Open the saved file in your web browser, and then change the character encoding to UTF-8.
  4. Copy the contents of the current display, and paste this into a new document in Notepad. Check that any and all references to CREATE TABLE within this file use the character set ‘utf8’. If this is not the case, find and replace all references of the previous character set (e.g. from ‘latin1’ to ‘utf8’).
  5. Save this new file to yourfilename.sql using UTF-8.
  6. If you are moving the database, import the newly changed file into the new database, modify your wp-config.php file and then check your site.
  7. If you are changing your database in situ, drop the affected tables (you already backed up, right?), then import the new file and check your site.

If you have shell access, you could also try using the script detailed here or here.

Random Quote

God gave men both a penis and a brain, but unfortunately not enough blood supply to run both at the same time.

— Robin Williams
Fips Written by:

The only current author of this personal blog.