MySQL Charset Issues

December 24th, 2008

And yet another MySQL character set problem solved. I had a remote FreeBSD server running MySQL 4.0 and a local Windows server running MySQL 5.0 (the XAMPP web servers package). I had no problems doing an export from MySQL 4.0 (running on cp1251 charset) to MySQL 5.0 (running utf8 charset) with something like this:

mysqldump -u -p --default-character-set=cp1251 dbname tablename > table.sql

Adding a few lines using vi at the beginning of the created file:

SET NAMES cp1251;
SET CHARACTER SET cp1251;

I imported the table dump into MySQL 5.0 and was able to see windows-1251 encoded characters using MySQL Query Browser. I’ve deleted two rows from the resultset, applied, then dumped back using mysqldump.exe:

mysqldump -u -p dbname tablename > dump.sql

When I tried to import the dump back to my remote MySQL 4.0 server

mysql -u -p dbname < dump.sql

I got some errors concerning the unavailable in MySQL 4 and lower SET NAMES and SET CHARACTER SET commands, which was fixed using the compatible flag during export:

mysqldump -u -p --compatible=mysql40 dbname tablename > dump.sql

That kept me going. Anyways, the dump back to MySQL 4 succeeded, but the character set didn’t match. I got utf8-coded cp1251 characters which looked weird, so I tried the following:

mysqldump -u -p --compatible=mysql40 --default-character-set=cp1251 dbname tablename > dump.sql

And got an error: mysqldump: Character set ‘cp1251′ is not a compiled character set and is not specified in the ‘C:\mysql\\share\charsets\Index.xml’ file. MySQL 5 was installed in C:/xampp/mysql, so I tried changing the [mysql] and [mysqldump] sections in the my.cfg file, but that didn’t help. It seems that mysql.exe, mysqldump.exe and other mysql utilities for windows are pre-compiled using a specific configuration.

The following solved my problem:

mysqldump -u -p --compatible=mysql40 --default-character-set=cp1251 --character-sets-dir=C:/xampp/mysql/share/charsets/ dbname tablename > dump.sql

Which got me a plain cp1251 encoded MySQL 4 compatible dump containing all my data. I’m not sure, but this seems to be a MySQL for Windows issue, cause I’ve seen posts stating same errors but using a standalone MySQL 5.0 server installation (unlike the XAMPP built-in). There you go ;)

Related posts:

1. From MySQL GUI Tools to MySQL Workbench
2. Cloud Tips: Amazon EC2 Email & S3 CNAME Issues
3. Cloud Tips: Backing Up MySQL on Amazon EC2 to S3
4. Foller.me: MySQL Tweaking & Optimization
5. How to Generate Quality Data for MySQL

Tagged: charset, encoding, mysql
Shortlink: http://kovshenin.com/136

• 
• 
• 
• 
• 

Permalink, comment (2) or share: