[mythtv-users] 0.26 upgrade: Database schema upgrade failed. Duplicate entry for primary key.

[Bill Meek]

On 10/05/2012 10:12 PM, Jon Whitear wrote:
...
> Now the backend starts, but the schema upgrade fails. The log is below. The problem seems to be here:-
>
> Oct  6 12:58:50 media mythlogserver: mythbackend[3826]: C CoreContext dbcheck.cpp:402 (performActualUpdate) Upgrading to MythTV schema version 1304
> Oct  6 12:58:51 media mythlogserver: mythbackend[3826]: E CoreContext dbcheck.cpp:417 (performActualUpdate) DB Error (Performing database upgrade):
>   Query was: UPDATE recordedseek SET starttime = CONVERT_TZ(starttime, 'SYSTEM', 'UTC') ORDER BY starttime
 >   Error was: Driver error was [2/1062]:
>   QMYSQL: Unable to execute query
>   Database error was:
>   Duplicate entry '71-0000-00-00 00:00:00-0-0' for key 'PRIMARY'
 >   new version: 1304
> Oct  6 12:58:51 media kernel: [ 2447.724713] init: mythtv-backend main process (3825) terminated with status 133
>
> Any idea how I go about fixing that? Thanks in advance.
...

Hi,

1st, I'd put copies of the automatic database backup in safe places.

Next, I'd verify that the time zone conversion is correct by using the
SELECT statement in the 3rd gray box here (and checking the response):

   http://www.mythtv.org/wiki/MySQL_Time_Zone_Tables

That looks line junk in the recordedseek table. Try:

   $ mysql -u mythtv -p mythconverg -e \
    "SELECT COUNT(*) FROM recordedseek WHERE starttime = '0000-00-00 00:00:00'"

to see how many all zero timestamps you have. If its not a huge number, then
replace the COUNT(*) with just *.  The chanid of 71 also looks strange to me
(mine are 1xxx and 2xxx.) I tried the command above and no rows were found (
as expected.)

This would tell you how many chanids below 1000 you have:

   $ mysql -u mythtv -p mythconverg -e \
    "SELECT COUNT(*) FROM recordedseek WHERE chanid < 1000"

Without knowing more, I wouldn't start deleting any records.

-- 
Bill