[mythtv-users] Recordedid conflict in attempted MythArchive import

[Stephen Worthington]

On Tue, 3 Jan 2017 18:22:12 +0000, you wrote:

>I have occasionally used the MythArchive export/import tools to transfer 
>recordings between independent Myth installations - not via DVDs but 
>using rsync.  Today one transfer worked, one didn't. The bad log shows
>
>Driver error was [2/1062]:
>QMYSQL3: Unable to execute statement
>Database error was:
>Duplicate entry '9789' for key 'PRIMARY'
>
>and then goes on to say that markup and recordedseek details have been 
>inserted and the Import completed OK.  The file is there, but doesn't 
>show in the recordings list.
>
>I suspect that I could replace the Recordedid value in the xml file with 
>some other randomish value, or perhaps just remove that line, but 
>haven't yet tried either.  Recent Recordedids on the target box approach 
>12000.  Recommendations welcome.
>
>The source installation is 'buntu ppa 0.28-fixes, target is el7 0.29-pre.
>
>John P

That sounds like the same problem MythExport/MythImport has for
OnTheGo exported recordings in MythTV 0.28.  The recorded table has a
new primary key "recordedid" which I believe is an autoincrementing
integer field.  It is referenced in at least one other table in 0.28
and I suspect will be used more in 0.29 - I have not looked yet.
Different databases will have different recordings that may be using
the same recordedid values, and an attempt to import database rows
that contain an already used recordedid key value will get this error.

I have been looking at how to fix mythimport so that it will use the
next available recordedid value in the database the recording is being
imported into, rather than the same recordedid that was exported from
the original database.  The idea I have had so far is to use an SQL
INSERT command for the "recorded" row that omits the recordedid field.
I think that will get mysql to just autoincrement its index and put
that value into the recordedid field in the newly created row.  Then I
would need to read the new recordedid value back out of that new row,
and use that new value in all the other places in the other table rows
being imported where recordedid is used.  I have not yet found enough
time to code this up and try it out, but I am hoping to get that done
some time soon as I am on holiday at the moment.

However, I do think you are asking for trouble if you try to import
database entries from one MythTV version into a different one.  You
would need to do all the database upgrades between the versions to
that data before importing it.  MythTV does that automatically when
upgrading a complete database to a new version, but there is no other
official way of doing it.  Unless the database tables involved are
actually identical between the MythTV versions, it is unlikely that
importing 0.28 data into 0.29 is going to ever work.  If you really
need to do that, you would have to backup your 0.28 database, restore
that database into a different MythTV system set up as 0.29 (eg a
virtual machine), and get the 0.29 system to upgrade the database,
before exporting your data in 0.29 format.