[mythtv-users] Recordedid conflict in attempted MythArchive import

[John Pilkington]

On 04/01/17 15:16, Stephen Worthington wrote:
> 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.

I agree that this appears to be a result of the adoption of Recordedid 
as the primary key for some tables, and I'm aware that this has caused 
difficulties with, iirc, the python bindings.

But I don't think that what I was hoping to do should be as dangerous as 
you suggest. Importing a native MythArchive has worked reasonably well 
for me in the past - and an archive limited to the current Myth version 
is hardly worthy of the name.  Essentially all I want to replicate is 
the creation of a slot for a new recording and the insertion of the old 
data into that slot.  That would have a new Recordedid, and I perhaps 
need to know how the system tracks that.

I can do something similar using existing tools, but would likely lose 
at least some metadata.  Since I don't collect that, its loss doesn't 
really bother me; but it does look like a bug.