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.
ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
(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.


Posted

in

by