[mythtv-users] Consolidate two databases into one
Nick Morrott
knowledgejunkie at gmail.com
Fri Sep 19 04:42:38 UTC 2008
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
--
Nick Morrott
MythTV Official wiki:
http://mythtv.org/wiki/
MythTV users list archive:
http://www.gossamer-threads.com/lists/mythtv/users
"An investment in knowledge always pays the best interest." - Benjamin Franklin
More information about the mythtv-users
mailing list