MySQL database backup made on 5.0.x may not be restored on 5.1.x because of some unicode characters if column use utf8_general_ci collation

Refers to:

  • Plesk 11.5 for Linux
  • Plesk 12.0 for Linux
  • Plesk 10.3 for Linux

Created:

2016-11-16 12:38:46 UTC

Modified:

2016-12-21 18:52:25 UTC

0

Was this article helpful?


Have more questions?

Submit a request

MySQL database backup made on 5.0.x may not be restored on 5.1.x because of some unicode characters if column use utf8_general_ci collation

Symptoms

If there is Plesk with MySQL 5.0 version, and there are databases with some unicode characters, migration or backup restore to Plesk server, where MySQL 5.1 version is used, may fail with error like:

ERROR 1062 (23000): Duplicate entry 'ц÷' for key 'PRIMARY'

Cause

Root cause is a bug in MySQL 5.0, which leads to wrong processing of collation utf8_general_ci . The bug is fixed in MySQL 5.1:

http://bugs.mysql.com/bug.php?id=43593

http://bugs.mysql.com/bug.php?id=27877

Resolution

  1. Create backup of databases with mysqldump utility on the source server.

  2. Change collation for databases and tables from utf8_general_ci to utf8_bin on the source server:

    2.1. Getting collation of database:

    mysql> SHOW VARIABLES LIKE 'collation_database';

    2.2. Changing collation to utf8_bin for database:

    mysql> ALTER DATABASE db_name COLLATE utf8_bin;

    2.3. Getting list of tables with utf8_general_ci collation:

    mysql> SHOW TABLE STATUS WHERE Collation='utf8_general_ci';

    2.4. Changing collation to utf8_bin for table:

    mysql> ALTER TABLE tbl_name COLLATE 'utf8_bin';

    2.5. Getting columns with utf8_general_ci collation (performed for each table regardless of the collation):

    mysql> SHOW TABLES;
    (for each table): mysql> SHOW FULL COLUMNS IN tbl_name WHERE Collation='utf8_general_ci';

    2.6. Changing collation to utf8_bin for columns:

    mysql> ALTER TABLE tbl_name MODIFY column_name <column_specification> COLLATE 'utf8_bin';

    can be taken from:

    mysql> SHOW CREATE TABLE tbl_name;
  3. Perform migration.

  4. Return the collation back to utf8_general_ci on the source server.

  5. Change the collation from utf8_bin to utf8_general_mysql500_ci on the destination server.

Have more questions? Submit a request
Please sign in to leave a comment.