But after upgrading to MySQL 5, character sets and collations are difficult.
ALTER TABLE hoge CONVERT TO charset utf8
I thought that would work, but it did not. Defining the collation for each column did not work either.
In the end, I had no choice but to use the method described in MySQL Character Sets (Ogawa::Memoranda).
To quote it:
$ mysqldump -uUser -pPassword –compact –default-character-set=binary hageDb > hage.sql
$ sed ‘s/DEFAULT CHARSET=ujis/DEFAULT CHARSET=utf8/g’ hage.sql > hagehoge.sql
(If a field has a character set specified, convert that as well.)
(drop and create)
$ mysql -uUser -pPassword hageDb < hagehoge.sql
Only the last line here,
$ mysql -uUser -pPassword --default-character-set=utf8 hageDb
needed to be changed in this way before it finally worked.
Before starting this procedure, I had been trying various things and messed up the data. Restoring it from a backup and so on was quite a hassle. I wasted many hours 🙁 rz
Incidentally, for tables where I had not done anything strange manually, it actually worked simply by creating
/etc/mysql/conf.d/charset.cnf
and putting the following inside:
[mysqld]
default-character-set=utf8
Perhaps this alone should have been enough from the beginning, though.
Also,
skip-character-set-client-handshake
when included, caused garbled characters between the Xoops database created with ujis and Xoops. Or rather…
In the first place, when using Xoops 2.0.16aJP, setting the database character set to ujis does not work. I tried many of the same things as above, but no luck. It works when it remains ujis. Why???
orz orz orz