MySQL
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
UTF-8 and Latin1
Convert a Latin1 database to UTF-8
To:
- convert an existing latin1 encoded MediaWiki MySQL database to utf8; and
- 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`;