[mythtv-users] Database update schema error with trunk utf8

[freedenizen]

On Sun, Dec 7, 2008 at 4:54 PM, Michael T. Dean <mtdean at thirdcontact.com> wrote:
<snip>
> INSERT INTO oldprogram (oldtitle, airdate) SELECT DISTINCT oldtitle, airdate
> FROM tmp_op;

all worked fine until there:

mysql> INSERT INTO oldprogram (oldtitle, airdate) SELECT DISTINCT
oldtitle, airdate FROM tmp_op;
ERROR 1062 (23000): Duplicate entry 'Cl' for key 1

I created the temp tables for both tables and truncated both tables
and the upgrade of the schema went fine, but copying the database
still failed.  I will send you the requested data offlist.  Here is
the output of all the commands run in mysql:

mysql> CREATE TEMPORARY TABLE tmp_op SELECT * FROM oldprogram;
Query OK, 32129 rows affected (0.03 sec)
Records: 32129  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tmp_op MODIFY oldtitle VARBINARY(128) NOT NULL default '';
Query OK, 32129 rows affected (0.03 sec)
Records: 32129  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tmp_op DEFAULT CHARACTER SET default,
    ->  MODIFY oldtitle VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin
    ->   NOT NULL default '';
Query OK, 32129 rows affected, 727 warnings (0.14 sec)
Records: 32129  Duplicates: 0  Warnings: 0

mysql> TRUNCATE TABLE oldprogram;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TEMPORARY TABLE tmp_ppl SELECT * FROM people;Query OK,
110246 rows affected (0.09 sec)
Records: 110246  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tmp_ppl MODIFY name VARBINARY(128) NOT NULL default '';
Query OK, 110246 rows affected (0.06 sec)
Records: 110246  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tmp_ppl DEFAULT CHARACTER SET default,
    ->  MODIFY name VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin
    ->   NOT NULL default '';
Query OK, 110246 rows affected, 2431 warnings (0.36 sec)
Records: 110246  Duplicates: 0  Warnings: 0

mysql> TRUNCATE TABLE people;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO oldprogram (oldtitle, airdate) SELECT DISTINCT
oldtitle, airdate FROM tmp_op;
ERROR 1062 (23000): Duplicate entry 'Cl' for key 1