MySQL Bugs: #43593: dump/backup/restore/upgrade tools fails

Bug #43593	dump/backup/restore/upgrade tools fails
Submitted:	12 Mar 13:48	Modified:	3 Nov 12:57
Reporter:	Susanne Ebrecht
Status:	In progress
Category:	Server: Charsets	Severity:	S1 (Critical)
Version:	5.1	OS:	Any
Assigned to:	Alexander Barkov	Target Version:	5.1+
Triage:	Triaged: D1 (Critical)

Description:
dump/backup/restore/upgrade tools fails because of duplicate key errors.

This is the main intention for bug #43306.

Because the discussion run out of topic in bug #43306 I opened this bug report here.

We need a solution for utf8_general_ci and all dump/backup/restore/upgrade tools, that
these won't fail during upgrading from 5.0 to 5.1.

Reason for it are changes in utf8_general_´ci.

How to repeat:
MySQL 5.0:

Create a table:

CREATE TABLE t (a integer, b char(5) CHARSET UTF8, PRIMARY KEY(a,b))ENGINE=MYISAM;

INSERT INTO t VALUES (23, 0xC39654C5A13261);
INSERT INTO t VALUES (23, 0xC39674C39F32C385);

Use mysqlupgrade to upgrade to 5.1.

Or dump the table and try to restore in 5.1.

Or use MA Backup for 5.0 and MA Restore for 5.1.

Suggested fix:
Already discussed in bug #43306

Bug #43810 is set as duplicate of this bug here

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73714

2892 Alexander Barkov	2009-05-10
      Bug#43593 dump/backup/restore/upgrade tools fails
      
      This is a preliminary patch intended to help to those users:
      - Having indexes on columns with collations
        utf8_general_ci or ucs2_general_ci
      - Having German letter SHARP S (SZLIG) in these columns
      - Upgrading from MySQL from version
        5.1.23 (and earlier) to version 5.1.24 (and higher).
      
      This patch introduces new collations utf8_german3_ci
      and ucs2_german2_ci which reproduce the "old"
      sorting order provided by pre-5.1.24 versions of xxx_general_ci.
      
      
      In order to start using new MySQL-5.1.24+ please do the following:
      
      - Start new version of mysqld
      - Convert all affected tables using this query (in case of utf8):
      
        ALTER TABLE t1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_german3_ci;
      
        Or if you need to apply changes per-column level, using this example:
      
        ALTER TABLE t1 MODIFY c1 CHAR(N) CHARACTER SET utf8 COLLATE utf8_german3_ci;
      
        (Make sure you're using the old data type and size,
         NULL/NOT NULL constraints, etc).