[mythtv-users] Recordedid conflict in attempted MythArchive import

[John Pilkington]

On 04/01/17 17:14, John Pilkington wrote:
> 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.
>

After more investigation I can add a bit more to this.  Both recordings 
to be transferred had recordedids on the source system that had been 
duplicated on the target.  Neither of the conflicting recordings was 
still present.  Only one transfer had failed.

It now appears that, by pure chance, the obstructing recording was one 
that I wrote to DVD and deleted from the HD only yesterday.  It would 
have been present at the time of my failed import attempts.  A new 
import attempt has succeeded.  Clearly all is for the best in this best 
of all possible worlds...

... but the imports still don't show as 'previously recorded'

... and looking at 'previously recorded' really hits system 
responsivity.  I suppose I need more RAM.

Cheers,

John