knowledgejunkie at gmail
Sep 13, 2008, 4:50 PM
Post #6 of 7
(813 views)
Permalink
|
|
Re: Merging data from two mythbackend servers
[In reply to]
|
|
On 13/09/2008, Gary Holmlund <gary.holmlund [at] gmail> wrote:
> Mike Perkins wrote:
> > Gary Holmlund wrote:
> >
> >> Nick Morrott wrote:
> >>
> >>> On 13/09/2008, Gary Holmlund <gary.holmlund [at] gmail> wrote:
> >>>
> >>>
> >>>> I have had two independent mythbackend servers. I just built a new
> >>>> computer with lots of disk space and I would like to move the recorded
> >>>> files from the old servers to the new. The documentation has a great
> >>>> procedure for moving data from one server to a new one.
> >>>> http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.7
> >>>>
> >>>> This does not work for the second server's data because of duplication
> >>>> of keys in the database. Specifically I get the following error:
> >>>> ERROR 1062 (23000) at line 1: Duplicate entry '81' for key 1
> >>>>
> >>>> Does anyone know of a procedure that would allow me to load the second
> >>>> server's data onto the new server?
> >>>>
> >>>>
> >>> It'll probably have to be a manual procedure. First task is to check
> >>> the SQL backup file from the second server, and determine which table,
> >>> and which entry has the value '81'. It's likely a recording rule with
> >>> recordid=81. As recordid is the primary key (PK) in the record table,
> >>> you can't have duplicates, hence the error.
> >>>
> >>> What I would do is check the tables you are trying to import, and see
> >>> whether there are any other duplicates for the primary keys on the
> >>> imported tables (you can check the structure from the mysql command
> >>> line, or using a tool such as phpMyAdmin). You will also need to
> >>> ensure that hostnames are corrected for the new host in the recorded
> >>> table, so that the new installation can find all of the imported
> >>> recordings.
> >>>
> >>> It's certainly possible, but you'll need to do some careful planning
> >>> for the process to go smoothly.
> >>>
> >>>
> >> Yes, it is the recorded table. I suppose there could be more
> >> errors in other tables if I get that one loading.
> >>
> >> So, is the requirement for recordid only to be unique? If I
> >> could add 10,000 to the recordid's on the data I am loading,
> >> would that be a reasonable approach to getting the data loaded?
> >> 2600 is the highest recordid on the new backend.
> >>
> >>
> > Not going to work. Recordid is an autoincrement primary key on that field
> > (IIRC). However, you have bigger problems, since your list of channel ids, tuner
> > card ids, etc is almost certainly going to overlap. What this is going to do to
> > the identity of video files you carry forward from the old systems I hate to think
>
> All the video on my second system is many episodes of 5 specific shows.
> I can guarantee that there are not show overlaps between the servers.
> But there are over 400 shows so any manual process would be very tedious.
>
> I see that nuvexport can do this one at a time. Perhaps I can adapt it
> to do
> do many at once. I already have the video files moved via rsync.
To get around the autoincrement issue, you can:
Omit the recordid value from the values you want to insert, and do a
full insert for the remaining fields, naming both the fields and
values in order so that the recordid value is generated automatically
for each insert,
- OR -
Ensure you have no duplicate values for recordid, insert the data as
you were, and then update the autoincrement value so that it is larger
than the largest recordid value. This option requires upating the
table like:
mysql> SELECT MAX( `recordid` ) FROM `record`;
to determine the largest recordid value;
mysql> ALTER TABLE `record` AUTO_INCREMENT =<largest_recordid_plus1>;
MySQL will then start using recordid values for new rules that will
not clash with your existing rules.
You will likely lose information connecting record rules and the
recordings that they generated, but you should at least be able to
aggregate your data.
--
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
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
|
