Fixing Wordpress Database Collation for Foreign Fonts

I wanted to write Japanese in one of my wordpress blogs. The issue was that the encoding was in latin1.

Here are the steps I used below.

Keep in mind that this is a potentially dangerous modification where you could easily lose your data. Be careful and if you try this, you do it at your own risk!

  • Make a complete backup of your site by whatever methods you normally use. Also make a backup of your wordpress site’s database.
  • Login into CPanel and select phpMyAdmin. Click the SQL tab around the top of the page on the right panel. This is where you can execute SQL code.
  • (where dbname is the name of your database)
  • That line fixes the existing database by changing the default encoding. In my case I had a latin1 variant. That step above is to avoid completely dropping and re-creating the database.
  • Login into CPanel again and download a copy of the database using the backup wizard. This consists of a tar.gz (zipped) textual SQL file. I only downloaded that single database backup file, not the full backup option.
  • Extract the SQL file and open it in a text editor. In my case I used gEdit that is pre-installed in Ubuntu.
  • Do a replace on the file by changing “CHARSET=latin1” to “CHARSET=utf8”
  • That will happen for each table definition in the SQL file.
  • Save that SQL file (Saved in UTF-8, which was the default for me) and re-zip it. You can use tar.gz or regular .zip as a compression format.
  • Select the database in phpMyAdmin and drop the existing tables (make sure you have your backup in a safe place). Now quickly import your modified backup file into that database. You should have 100+ rows inserted successfully. If you screwed something up, import the old backup file.

That’s it! My whole table structure is UTF-8 now, so I have no issues saving and displaying Japanese text on the site.