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.