Re: Migrating from a 'big5' encoded DB to a 'utf-8' one.

[Shuichi Tamagawa]

Hi Leszek

If you set any options in my.cnf file please show it also.

Assuming that you didn't add any character set related option in my.cnf....

> 2) I downloaded source of '4.1.13a' MySQL and
> configured it , following the advice given here,
> with:
>
> CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3
> -felide-constructors \
> -fno-exceptions -fno-rtti" ./configure \
> --prefix=/usr/local/mysql
> --enable-assembler \
> --with-mysqld-ldflags=-all-static
> --with-charset=utf8

This should make all the character set variables to utf8 by default.

> 8) I logged onto the new DB as root and restored
> the old data:
>
> mysql -u root -p

At this point, if you execute the following command:

mysql> show variables like 'char%';

It shuld be like:
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /path_to_the_charset_dir |
+--------------------------+---------------------------------------+
7 rows in set (0.01 sec)

> mysql> source backup.sql

So, when you insert the data from backup.sql mysql client thought "Oh, this file is encoded in utf8". But actually, the data is big5.

I think if you execute the following command and set the client character set variables to big5 before restoring backup data, it should work.

mysql> set names big5;

Can you try this?

> 11) I've tried compiling DB for both 'big5' and
> 'utf8' , in hope that I can run it as 'big5'
> first, import the data, and then issue a 'alter
> table XXXX convert to character set utf8' , but
> that didn't result in any visible change.

Hmm, weird... Compiling --with-charset=big5 option should work ...