[mythtv-users] mysql error upgrading database schema on 0.25->0.26 upgrade

[Brian J. Murrell]

On 13-04-06 04:05 PM, Brian J. Murrell wrote:
> I was hoping this was going to be a nice easy uneventful upgrade from
> 0.25 to 0.26 today.  No such luck, sadly.  While trying to upgrade the
> database schema I seem to be getting a Duplicate entry error from mysql:
> 
> 
> Apr  6 16:01:38 pvr mythlogserver: mythbackend[3237]: I CoreContext schemawizard.cpp:271 (PromptForUpgrade) Console is non-interactive, can't prompt user...
> Apr  6 16:01:38 pvr mythlogserver: mythbackend[3237]: C CoreContext schemawizard.cpp:296 (PromptForUpgrade) Upgrading.
> Apr  6 16:01:38 pvr mythlogserver: mythbackend[3237]: C CoreContext dbcheck.cpp:402 (performActualUpdate) Upgrading to MythTV schema version 1304
> Apr  6 16:01:51 pvr mythlogserver: mythbackend[3237]: E CoreContext dbcheck.cpp:417 (performActualUpdate) DB Error (Performing database upgrade): #012Query was: UPDATE recordedseek SET starttime = CONVERT_TZ(starttime, 'SYSTEM', 'UTC') ORDER BY -starttime #012Error was: Driver error was [2/1062]:#012QMYSQL: Unable to execute query#012Database error was:#012Duplicate entry '2969089040-0000-00-00 00:00:00--127-16300796' for key 'PRIMARY'#012 #012new version: 1304
> Apr  6 16:01:51 pvr mythlogserver: mythbackend[3237]: E CoreContext dbcheck.cpp:502 (UpgradeTVDatabaseSchema) Database schema upgrade failed.
> Apr  6 16:01:51 pvr mythlogserver: mythbackend[3237]: E CoreContext main_helpers.cpp:548 (run_backend) Couldn't upgrade database to new schema

Yeah, so this ended up being corruption in my recordseek table.  It was
easy enough to hunt down and remove the offending records.

What did throw me though was the unprecedented amount of time it took to
convert from localtime to UTS in schema 1304 (IIRC).  I am sure glad I
was able to investigate what it was doing enough to see that it was
doing something and not hung up and therefore I did not interrupt it
thinking it was doing nothing.  A warning in the log for schema
migrations that are expected to be extraordinarily long might be helpful.

Probably a general infrastructure for a developer being able to make
logging notifications for any schema change would be useful.

Cheers,
b.

P.S. the scheduler efficiency of 0.26 is remarkable!  Excellent work there.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 263 bytes
Desc: OpenPGP digital signature
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20130407/56020837/attachment.sig>