A few days back I wrote how I almost lost my blog while it was hosted on WordPress hosting at Go Daddy. I was lucky and got all my files, and within a few hours I had uploaded all the files and the database to my dedicated server and the nameserver change had propagated so my blog was up and running again.
The only problem I had was that the punctuation wasn’t correct. WordPress was displaying some strange characters instead of semicolons, colons and apostrophes: ĂŠĂĄ. The exported database was fine and had UTF-8 character encoding. The database had the correct “utf8_general_ci” colation. WordPress files were exactly the same as before (with the correct character encoding) so I didn’t know why this was happening. I had the correct settings in my wp-config.php:
define('DB_CHARSET', 'utf8'); define('DB_COLLATE', 'utf8_general_ci');
After some testing I found that this was because I imported the database in unix without setting a character set. I was using the command “mysql -u database_user -p -h localhost database_name < database_sql_file.sql” and that probably imported the database in the default ANSI encoding.
The character set should have been set to UTF-8 while importing the database. Here is how to insert a database in UTF8 character encoding in unix (put it in one line):
mysql -u database_user -p --default_character_set utf8 -h localhost database_name < database_sql_file.sql
Everything should work as planned now.