Re: utf8_general_ci (MySQL 5.0.x vs. 5.1.36)

[Bozana Bokan]

Hi Rick,

Thank you very much for your answer!
The links contain very useful information :-)))

Unfortunately, I couldn't solve the problem, i.e. I couldn't figure out where is the source of the problem :-(((
Here what I've just tested using 'mysql' client on both DBs, MySQL 5.0.26 and MySQL 5.1.36:
-- Both DBs show the same when doing show variables like 'charachter%'; :
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

-- Both DBs show the same when doing show variables like 'collation%'; :
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+

-- I created a new database and then a table 'article_search_keyword_list' on both DBs:
CREATE TABLE `article_search_keyword_list` (`keyword_id` bigint(20) NOT NULL auto_increment, `keyword_text` varchar(60) NOT NULL, PRIMARY KEY (`keyword_id`), UNIQUE KEY `article_search_keyword_text` (`keyword_text`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then I inserted the following entry in the table on both DBs:
INSERT INTO `article_search_keyword_list` values (1, 'das');

Then I tried to insert the following entry in the table on both DBs:
INSERT INTO `article_search_keyword_list` values (2, 'daß');

The entry 'daß' was successfully inserted on the old MYSQL 5.0.26 but not successfully inserted on the new MySQL 5.1.36 (ERROR 1062 (23000): Duplicate entry 'daß' for key 'article_search_keyword_text').

-- Doing SELECT * FROM article_search_keyword_list WHERE keyword_text = 'daß'; on the old MySQL 5.0.26 shows this result:
+------------+--------------+
| keyword_id | keyword_text |
+------------+--------------+
| 2 | daß |
+------------+--------------+

And doing the same query on the new MySQL 5.1.36 shows this result:
+------------+--------------+
| keyword_id | keyword_text |
+------------+--------------+
| 1 | das |
+------------+--------------+

-- Dong SELECT keyword_text, HEX(keyword_text), LENGTH(keyword_text), CHAR_L ENGTH(keyword_text) FROM article_search_keyword_list; on the old MySQL 5.0.26 shows:

+--------------+-------------------+----------------------+---------------------------+
| keyword_text | HEX(keyword_text) | LENGTH(keyword_text) | CHAR_LENGTH(keyword_text) |
+--------------+-------------------+----------------------+---------------------------+
| das | 646173 | 3 | 3 |
| daß | 6461C39F | 4 | 3 |
+--------------+-------------------+----------------------+---------------------------+

And on the new MySQL 5.1.36:
+--------------+-------------------+----------------------+---------------------------+
| keyword_text | HEX(keyword_text) | LENGTH(keyword_text) | CHAR_LENGTH(keyword_text) |
+--------------+-------------------+----------------------+---------------------------+
| das | 646173 | 3 | 3 |
+--------------+-------------------+----------------------+---------------------------+

Unfortunately I don't know where and what to look/search/investigate next :-( Any suggestion?

Thanks a lot!