Hey,

We're upgrading MySQLs from 5.0 to 5.5, and running into the predictable utf8_general_ci bu^Wwe-meant-to-do-that with german ß and similar characters. The server swallowed the existing datafiles (thank god for that) so we're up and running, but "check table" does whine about needing a full rebuild, which is clearly that issue.

Now, I'm aware that this should be fixed by using the utf8_unicode_ci collation; but somehow it isn't...

The its-not-a-bug-but-heres-the-fix collation utf8_general_mysql500_ci does work, but honestly I feel that that's kind of dirty.

Any ideas why utf8_unicode_ci doesn't work as expected? The new server version is 5.5.24-1~dotdeb.1-log.

Thx,
Johan


mysql> set names UTF8;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table search_index;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| search_index | CREATE TABLE `search_index` (
`word` varchar(50) NOT NULL DEFAULT '',
`sid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(16) DEFAULT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
KEY `sid_type` (`sid`,`type`),
KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show table status like "search_index";
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| search_index | InnoDB | 10 | Compact | 6893296 | 58 | 405536768 | 0 | 725172224 | 255852544 | NULL | 2012-08-27 14:53:20 | NULL | NULL | utf8_general_ci | NULL | | |
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.07 sec)

mysql> CREATE TABLE `search_johan` ( `word` varchar(50) NOT NULL DEFAULT '', `count` float DEFAULT NULL, PRIMARY KEY (`word`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into search_johan select * from search_total;
ERROR 1062 (23000): Duplicate entry 'cœur' for key 'PRIMARY'




--

Linux Bier Wanderung 2012, now also available in Belgium!
August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be