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

[Leszek koltunski]

The machine is a HP server running Red Hat 7.3 w/ XFS.
Here are the exact steps I took ( I am an old school command-line diehard so everything is going to be from Linux bash ) :

1) An existing 4.0.18 MySQL was using 'big5' . I backed its contents with 'mysqldump --all-databases > backup.sql' ( in my previous post I incorrectly wrote that it was a '4.0.24' - sorry about that )

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

3) I compiled the DB:

make

4) I stopped the old MySQL process and removed its RPM:

mysqladmin -u admin -p shutdown
rpm -e mysql-4.0.24-x386.rpm

5) I installed the new DB:

make install

6) I generated the grant tables:

mysql_install_db

7) I set up root password, etc, etc

8) I logged onto the new DB as root and restored the old data:

mysql -u root -p
mysql> source backup.sql

9) I made a series of checks if the new DB is functioning properly. It was, in the meaning that new user actions prompted correct insertion / retrieval to/from the DB, and all, including the Chinese characters, looked ok.

The old data, however, precisely the one restored from 'backup.sql' either looks like ?????, or in some cases it is actual Chinese characters, but my Chinese co-workers here tell me they are mangled and make no sense ( I do not speak Chinese myself ).

10) what is interesting is that when I take the 'backup.sql' file and transfer it to a Windows machine, and try to view it from Firefox or IE using 'big5' encoding, the Chinese characters are still mangled!!
That makes me suspect that the whole backup is toast!

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.

I'd be really thankful for any ideas!

Leszek