MySQL

UTF-8 and Latin1

Convert a Latin1 database to UTF-8

To:

1. convert an existing latin1 encoded MediaWiki MySQL database to utf8; and
2. remove any table_name_prefix.

Do:

shell> mysqldump -default-character-set=latin1 wiki_database |
 sed > wiki_database-utf8.sql \
  -e 's/`table_name_prefix_//' \
  -e 's/SET NAMES latin1/SET NAMES utf8/' \
  -e 's/DEFAULT CHARSET=latin1;/DEFAULT CHARSET=utf8;/'

Next, load the new database:

shell> mysqladmin drop wiki_database
shell> mysqladmin create wiki_database
shell> mysql wiki_database < wiki_database-utf8.sql

NB: For some strange (uninvestigated) reason, the "SET NAMES utf8" causes:

ERROR 1062 (23000) at line 300: Duplicate entry  for key 1

The error occurs in the values for the math table. I simply deleted the line:

INSERT INTO `math` VALUES (…

This leaves the math table empty. I have found no harm done by this. Creating a brand new MediaWiki (1.12.0) also results in a an empty math table. Perhaps there is some safe way to fill the table with the appropriate entries.

Now your MediaWiki database is utf8 too, and you can keep all character_set_% in utf8.

Testing the conversion

mysql> create database mies character set = latin1;

shell% mysql --default-character-set=latin1 mies < wikidb.sql

mysql> set names latin;
mysql> SELECT user_name, user_real_name FROM `cheetah_user` WHERE `user_name` LIKE "%Gaston%" ORDER BY `user_name`;

External Links

• http://textsnippets.com/posts/show/84
• http://wiki.refbase.net/index.php/Troubleshooting
• http://gentoo-wiki.com/TIP_Convert_latin1_to_UTF-8_in_MySQL