3

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.

1 Answer 1

9

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.

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .