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

[Mark Perkins]

> On 2 Mar 2015, at 12:45 am, "Stephen Worthington" <stephen_agent at jsw.gen.nz> wrote:
> 
>> 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.
> _____________________________________________

But is it just me or is there something really wrong with the data that was grep-ed? Ie 10digit chanid? Mark types of -62,-63,-68? Accented characters in the starttime? Or does MySQL do some sort of magic translation on all that? Isn't one year 1537 and the next 7408 and another @406?

Did the grep only return data from recordedseek? Or are other occurrences in other tables?

Is this a backup from a still functioning system that you can look at the live database?