MySQLで Illegal mix of collations エラー

debian etch に apt でmysql-serverを入れて動かしたら

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ujis_japanese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '<>'

とかいうエラー。 collations って何よ。 で、文字コードを調べると のきなみ latin1 !

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | latin1                     | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | 
| collation_database   | latin1_swedish_ci | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+
3 rows in set (0.00 sec)

とりあえず、 /etc/mysql/my.cnf を修正

[client]
default-character-set=ujis

[mysqld]
skip-character-set-client-handshake
default-character-set=ujis

再起動する # /etc/init.d/mysql restart 直った様子。

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | ujis                       | 
| character_set_connection | ujis                       | 
| character_set_database   | ujis                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | ujis                       | 
| character_set_server     | ujis                       | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+------------------+
| Variable_name        | Value            |
+----------------------+------------------+
| collation_connection | ujis_japanese_ci | 
| collation_database   | ujis_japanese_ci | 
| collation_server     | ujis_japanese_ci | 
+----------------------+------------------+
3 rows in set (0.00 sec)

最近MySQLの文字コード周りがめんどくさくて仕方ない。 昔はそんなに困らなかったんだけどなー 最近使ってないけど、Postgres でもそんなに困った記憶がないし、なんなの最近のMySQL!