[mythtv-users] mythconverg_restore.pl: Duplicate entry for key 'PRIMARY'

[Stephen Worthington]

On Sun, 1 Mar 2015 22:17:54 +1100, you wrote:

>
>> 
>> I would try decompressing your backup file somewhere, for example:
>> 
>> cd ~
>> mkdir tempsql
>> cd tempsql
>> gzip -d /home/jon/mythconverg-1317-20150301163436.sql.gz
>> 
>> The .sql file is huge, but just a text file.  Then search it for the
>> duplicate key '2684354578-0000-00-00 00:00:00--51-589824':
>> 
>> grep "'2684354578-0000-00-00 00:00:00--51-589824'"
>> mythconverg-1317-20150301163436.sql
>> 
>> The .sql file is not formatted for human readability and has some very
>> long lines, so the grep output may not be easy to understand.
>
>I’ve tried unzipping the backup and piping the output through grep. The first series of numbers would appear to be a channel ID, but I don’t have any channels with that ID. Either way, the grep comes back with this:
>
>INSERT INTO `recordedseek` (`chanid`, `starttime`, `mark`, `offset`, `type`) VALUES (1191248128,'7502-02-27 31:36:64',0,0,0),(1209139200,'í+-*-*,-\'. 64:59:28',77,3762094592,0),(1007,'@406-87-80 16:19:60',589824,14378225595496791551,-63),(2348810258,'1537-12-05 34:53:14',589824,5492390434229714687,-68),(1744830482,'7408-39-52 49:28:03',589824,9078343154616499711,-62),(2684354578,


>
>
 and many lines more, I’ve just truncated the output at the first instance of the search term. So it looks like it’s in the recorded seek table. Would it be a big deal if I just edited the backup file to remove all the records going into the recorded seek table? I recall that you can manually rebuild seek tables, which I’d be happy to do once I’ve migrated to the new back end.
>
>Cheers,
>
>Jon 

That would work.  But it would probably be better to just remove the
rows in recordedseek that have the duplicate primary keys, and leave
all the rest.  It would take ages to rebuild the recordedseek table
for all your recordings, and they will not play properly until that is
done.

I would suggest cutting out the recordedseek data to another .sql
file, then using grep on that file with the -v (invert match) option
to get another file with all the good "INSERT INTO" lines, and without
-v to get all the bad lines.  Then there should be relatively few bad
lines where you will need to break them down into each recordedseek
row's data and remove only the ones that have the bad keys.  Then you
can merge all the good INSERT INTO lines and all the edited bad lines
back into the backup file and proceed with the restore.

Another possibility is that mysql may have an option somewhere to make
it ignore duplicate key errors (and just drop the bad data) and
continue processing the other data.  If so, I would cut the
recordedseek data out of the backup file and do the rest of the
restore as normal, then restore the recordedseek data manually using
that option.  Then you would need to get a list of all the recordings
affected using the (chanid, starttime) data from the bad recordedseek
data and rebuild the seek table for those recordings.

A third way might be to create a table like recordedseek (say
seeknokey), but with no primary key specified.  Load the recordedseek
data into that table, then use SQL commands to find and delete all the
records that have duplicate (chanid, starttime, mark, type) values,
which is the primary key for recordedseek.  Then copy the remaining
data into the recordedseek table and drop the seeknokey table.