Re: Upgrade to MySQL 5.0

[Andrei Toutoukine]

Solution is found.

The upgrade to MySQL5 went wrong way and all the data in the tables were already "twice encoded" to the moment I experience troubles with my web-application.

Great confusion appeared because of the default settings (for example character_set_results=latin1): MySQL server was forced to convert the data in tables (as if they were utf8) to latin1. That's why client received correct utf8 data.

New version of the web-application changed to the correct values of "character_set_%", and the data were sent to it as is.

How did I manage this problem? Shortly speaking, I dumped wrong-encoded data wrong way, obtaining them back in utf8:

mysqldump --opt -v --default-character-set=latin1 -u dbuser -p database > database.sql

Then I replaced all occurences of the 'latin1' string with 'utf8' in the file database.sql.

After that I recreated the db:
drop database database; create database database character set utf8;

And, finally, fed the data back to the server:

mysql -u dbuser -p database < database.sql