[mythtv-users] Consolidate two databases into one

[Nick F]

On Thu, Sep 18, 2008 at 11:42 PM, Nick Morrott <knowledgejunkie at gmail.com>wrote:

> On 19/09/2008, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> > On 09/18/2008 08:14 PM, Nick Morrott wrote:
> >
> > > On 18/09/2008, Nick F wrote:
> >  >> Is there a way to copy all the recorded shows from one machine to the
> other
> >  >> then insert all the database records relating to recorded shows into
> that
> >  >> database?  I realise this is something that needs to be done
> manually, but
> >  >> are there any mysql commands that could make it easier?  What tables
> would I
> >  >> need to copy?
> >  >>
> >  > One possible method is in the MythTV documentation:
> >  >
> >  > http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.7
> >  >
> >  > The 'record' table holds your recording schedules, the others details
> >  > of your actual recordings.
> >
> >
> > Though you may have index/key uniqueness violations if both systems use
> >  the same chanid(s) (and you happened to have a recording on at the same
> >  time on both).  Note, also that there's a better approach than 23.7 at
> >  http://www.mythtv.org/wiki/index.php/Database_Backup_and_Restore , but
> I
> >  didn't mention it because I can't guarantee it will work because of the
> >  index/key issue and I don't have time to do much more than provide a
> >  link, now.
>
> The thread http://www.gossamer-threads.com/lists/mythtv/users/349161
> from some days ago on the exact same topic provides some discussion
> and warning about these issues. If you don't have many entries, the
> manual procedure described is fairly straightforward, but it does
> require editing the raw data and updating the database directly. As
> Mike states, you have to be very careful to ensure there are no
> clashes between unique fields in the two datasets, and to update any
> related table data you are also importing if you do have to change one
> of the unique values. You will also have to ensure that you update any
> host names in the data to point to the new machine.
>
> I'll post information below about my understanding of the key
> relations of the tables that the 'Moving your data to new hardware'
> section mentions so that it hits the list archive, in the hope that it
> is of use to someone in the future (this is from a 0.21-fixes system)
> planning to merge data from multiple machines manually:
>
> TABLE: record
> UNIQUE FIELD: record.recordid (autoincremented)
> INHERITS/LINKS: record.chanid from channel.chanid
> RELATED TABLES: record.recordid used in recorded.recordid,
> oldrecorded.recordid;
> NOTABLE FIELDS: storagegroup
>
> TABLE: recorded
> UNIQUE FIELD: recorded.chanid + recorded.starttime
> INHERITS/LINKS: recorded.chanid from channel.chanid
> RELATED TABLES:
> NOTABLE FIELDS: hostname and storagegroup
>
> TABLE: recordedprogram
> UNIQUE FIELD: recordedprogram.chanid + recordedprogram.starttime +
> recordedprogram.manualid
> INHERITS/LINKS: recordedprogram.chanid from channel.chanid
>
> TABLE: recordedrating
> UNIQUE FIELD: recordedrating.chanid + recordedrating.starttime +
> recordedrating.system + recordedrating.rating
> INHERITS/LINKS: recordedrating.chanid from channel.chanid
>
> TABLE: recordedmarkup
> UNIQUE FIELD: recordedmarkup.chanid + recordedmarkup.starttime +
> recordedmarkup.type + recordedmarkup.mark
> INHERITS/LINKS: recordedmarkup.chanid from channel.chanid
>
> TABLE: recordedseek
> UNIQUE FIELD: recordedseek.chanid + recordedseek.starttime +
> recordedseek.type + recordedseek.mark
> INHERITS/LINKS: recordedseek.chanid from channel.chanid
>
> TABLE: oldrecorded
> UNIQUE FIELD: oldrecorded.station + oldrecorded.starttime +
> oldrecorded.title
> INHERITS/LINKS: oldrecorded.chanid from channel.chanid,
> oldrecorded.recordid from record.recordid, oldrecorded.station from
> channel.callsign
>
>
> The record.recordid field is an autoincrement field, therefore care
> must be taken to update conflicting values to values that either slot
> into gaps in the range of recordid values, or that the autoincrement
> counter for the record table is updated to reflect an increased
> recordid maximum value (see
> http://www.gossamer-threads.com/lists/mythtv/users/349238#349238).
>
> The related recorded* tables essentially include one or more values
> (chanid, starttime...) from the recorded table, so these values should
> agree across all of these tables. If one of the values in
> mythconverg.recorded has to be updated, also update the value across
> the other related recorded* tables.
>
> The oldrecorded table keeps details of your recording history, so for
> some users this may be the only table they need to import if they do
> not want to also import their existing recordings and schedules. To
> ensure that duplicate matching continues to work properly the station
> field should agree with the new installation.
>
> Cheers,
> Nick
>

Thanks all for the helpful links, suggestions, and details.

I'll back up my database and give it a try when I've got some time to sit
down.  Unfortunately I've got a lot of recordings to merge, so the purely
manual method will be difficult.  I'll report back with my success or
failure!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20080919/ae72f712/attachment.htm