Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I tried to migrate some tables from one MySQL database to the other, but I encountered an error:

ERROR 1062 (23000) at line 108: Duplicate entry 'außer' for key 'PRIMARY'

And I tried to find why, in the target database, i ran

mysql> select 'außer' = 'auser';
+--------------------+
| 'außer' = 'auser'  |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.07 sec)

As you can see, MySQL think both of them are same, and I checked the configuration variables

mysql> show variables like 'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

mysql> show variables like 'character%';
+--------------------------+------------------------------------------+
| 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       | /rdsdbbin/mysql-5.5.8.R1/share/charsets/ |
+--------------------------+------------------------------------------+

Then, I goes back to the origin database and try

mysql> select 'außer' = 'auser';
+--------------------+
| 'außer' = 'auser'  |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like 'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show variables like 'haracter%';
Empty set (0.00 sec)

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

The original version of MySQL is 5.0.77, and the migration target is 5.5.8. I have no idea how could this happened. Why they compare string differently? How can I solve this problem? Thanks.

share|improve this question

1 Answer 1

up vote 9 down vote accepted

As outlined in http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html this seems to be the correct behavior:

utf8_general_ci also is satisfactory for both German and French, except that “ß” is equal to “s”, and not to “ss”. If this is acceptable for your application, you should use utf8_general_ci because it is faster. Otherwise, use utf8_unicode_ci because it is more accurate.

share|improve this answer
    
See also: 213.136.52.24/bug.php?id=57860 (while that is 5.1.51, it sounds exactly like what you are experiencing). –  faker Jun 14 '11 at 13:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.