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

[Michael T. Dean]

On 12/07/2008 02:07 PM, freedenizen wrote:
> I'm getting the following error when trying to upgrade from 21-fixes
> to trunk.  Looks like the problem is with converting to utf8.  I'm
> running mysql 5.0.60 but I am upgrading to 5.0.70-r1 incase that
> resolves the issue.  Has anyone else seen this?  I've tried searching
> the lists on dev and commit, I know there has been some work on utf8
> conversion but I couldn't find anything related to this error.
>
> TIA
>
> 2008-12-07 10:54:43.948 Newest Schema Version : 1226
> 2008-12-07 10:54:43.951 Upgrading to schema version 1217
> 2008-12-07 10:54:49.505 DB Error (Performing database upgrade):
> Query was: ALTER TABLE oldprogram  DEFAULT CHARACTER SET default,  MODIFY oldtit
> le varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '';
> Error was: Driver error was [2/1062]:
> QMYSQL: Unable to execute query
> Database error was:
> Duplicate entry 'Cl' for key 1

You have 2 choices:  the easy way and the hard way.

The easy way:

Restore a pre-upgrade database.  Then, in the mysql command-line client:

TRUNCATE TABLE oldprogram;

Then, your upgrade should succeed.


The hard way:

Restore the pre-upgrade database. Then in the mysql command-line client:

CREATE TEMPORARY TABLE tmp_op SELECT * FROM oldprogram;
ALTER TABLE tmp_op MODIFY oldtitle VARBINARY(128) NOT NULL default '';
ALTER TABLE tmp_op DEFAULT CHARACTER SET default,
  MODIFY oldtitle VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin
    NOT NULL default '';
TRUNCATE TABLE oldprogram;

and do /NOT/ exit the mysql client (when you do, your temporary table is 
lost).

Then, start mythtv-setup and allow it to (successfully) upgrade the 
database.  Exit mythtv-setup.

Then, back in the same mysql client you used above,

INSERT INTO oldprogram (oldtitle, airdate) SELECT DISTINCT oldtitle, 
airdate FROM tmp_op;

Finally, make a backup of the just upgraded database.  
http://www.mythtv.org/wiki/index.php/Database_Backup_and_Restore


Additional info:
The oldprogram data is not that important--it's a record of all 
(supposedly ;) unique program titles seen in the last 11 months, to 
allow you to tell Myth to find any "new titles" in current listings 
(since data is deleted after it's 11 months old, you can see holiday 
specials as new titles, each year).  If you don't do that with Myth (or 
don't plan to for the next 11 months or so ;), the easy way works great 
(but please keep reading before choosing the easy way).


What's wrong:
It seems that the old code that handled oldprogram was one of the areas 
where we improperly handled character set conversion in the old 
pre-Qt4/pre-(telling MySQL that the columns contain)UTF-8 Myth and we 
never got a bug report about that place so we never fixed it.  
Therefore, data in the table includes corrupt data that becomes 
duplicates.  So, if you choose the hard way, you will have some corrupt 
data (likely any row where the oldtitle contains non-latin characters), 
but you may have valid rows, too.  So, keep that in mind when deciding 
the approach.

Note that Janne is likely to make a change to do one of the above before 
long (as oldtitle has been causing sufficient problems that it's worth 
modifying the conversion to prevent the issue).

If you don't mind, I'd appreciate your doing the upgrade "the hard way" 
and reporting back success or failure (and, if so desired, you could 
truncate oldprogram /after/ testing the approach I mentioned).  It would 
be nice to have verification that this approach works properly on a 
database that's known to have conversion issues.  If you don't have 
time, feel free to just send me a copy of your oldprogram backup off 
list.  You could get it with:

grep "INSERT INTO \`oldprogram\` " mythtv_backup.sql > restore.sql

Thanks,
Mike