[mythtv-users] How to copy 'frontend' portion of mysql database

Jeffrey J. Kosowsky mythtv at kosowsky.org
Mon Jan 11 03:57:21 UTC 2010


Ronald Frazier wrote at about 08:26:07 -0500 on Friday, January 8, 2010:
 > > SELECT @groupidoffset := max(profilegroupid)+1 FROM displayprofilegroups;
 > > INSERT INTO displayprofilegroups (name, hostname, profilegroupid)
 > >         SELECT name, "newhost", profilegroupid+ at groupidoffset, data
 > >         FROM displayprofilegroups WHERE host="oldhost";
 > >
 > > SELECT @idoffset  := max(profileid)+1 FROM displayprofiles;
 > > INSERT INTO displayprofiles(profilegroupid, profileid, value, data)
 > >         SELECT profilegroupid+ at groupidoffset, profileid+ at idoffset, value, data
 > >         FROM displayprofiles
 > >         WHERE profilegroupid IN
 > >     (SELECT profilegroupid FROM displayprofilegroups WHERE host="oldhost");
 > >
 > > Again I don't know anything about sql queries or internal mythtv
 > > database structures -- I'm just trying to build on your work...
 > >
 > > Is this right?
 > 
 > Mostly...you just missed a few fieldnames. host should be hostname,
 > and you can remove the data field from the first subquery. Do that and
 > it should work.
 > 
 > That said, the obsessive-compulsive DB developer side of me is
 > cringing at the artificially created gaps in the numbering space (they
 > don't really cause a problem unless you run this process quite a few
 > times...just annoy me). For instance, say your displayprofilegroups
 > table has records with profilegroups ranging from 1 to 10. The records
 > for the host you are trying to duplicate are numbered 3,5,8 (the other
 > 7 records are for other hosts you aren't trying to duplicate). The
 > first time you run the copy, you will now have rows numbered
 > 1,2,3,4,5,6,7,8,9,10,14,16,19. Run it again to create another host and
 > now your numbering is 1,2,3,4,5,6,7,8,9,10,14,16,19,23,25,28. So you
 > get the idea. Not a problem until you do it enough that you start
 > running out of numbers in the 32-bit space.
 > 
 > So, that obsessive-compulsive part of me would like a better way to do
 > it. The solution: temporary tables.
 > 
 > CREATE TEMPORARY TABLE GroupIDMap (oldid INT UNSIGNED , newid INT UNSIGNED);
 > SELECT @lastgroupid := max(profilegroupid) FROM displayprofilegroups;
 > INSERT INTO GroupIDMap(oldid, newid) SELECT DISTINCT profilegroupid,
 > @lastgroupid := @lastgroupid+1 FROM displayprofilegroups WHERE
 > hostname="<OLDHOSTNAME>";
 > 
 > CREATE TEMPORARY TABLE ProfileIDMap (oldid INT UNSIGNED, newid INT UNSIGNED);
 > SELECT @lastprofileid := max(profileid) FROM displayprofiles;
 > 
 > INSERT INTO ProfileIDMap(oldid, newid) SELECT DISTINCT profileid,
 > @lastprofileid := @lastprofileid+1 FROM displayprofiles WHERE
 > profilegroupid IN
 >     (SELECT oldid FROM GroupIDMap);
 > 
 > INSERT INTO displayprofilegroups (name, hostname, profilegroupid)
 > SELECT name, "<NEWHOSTNAME>", newid FROM displayprofilegroups INNER
 > JOIN GroupIDMap ON profilegroupid=oldid;
 > 
 > INSERT INTO displayprofiles(profilegroupid, profileid, value, data)
 > SELECT G.newid, P.newid, value, data
 > FROM displayprofiles INNER JOIN GroupIDMap G ON profilegroupid=G.oldid
 > INNER JOIN ProfileIDMap P ON profileid=P.oldid;
 > 
 > 
 > There....now I can rest happily knowing I've efficiently used the
 > numbering space :)

WOW - thanks.
I think there is a bug somewhere though in your creation of the
ProfileIDMAP tables. It seems to make *multiple* copies of each entry
(since the "SELECT DISTINCT profileid, @lastprofileid :=
@lastprofileid+1" is doing an outer-product) -- see below for what happens.)

Now I am literally only learning SQL on the fly by trial-and-error,
but this code seems to work instead -- let me know if there is a
better/cleaner way to do it...

CREATE TEMPORARY TABLE ProfileIDMap (oldid INT UNSIGNED, newid INT
UNSIGNED);
SELECT @lastprofileid := max(profileid) FROM displayprofiles;

INSERT into ProfileIDMap(oldid) SELECT DISTINCT profileid FROM
displayprofiles WHERE profilegroupid IN (SELECT oldid FROM
GroupIDMap);
UPDATE ProfileIDMap set newid=(@lastprofileid := @lastprofileid+1);

--------------------------------------------------
HERE IS A DETAILED EXAMPLE OF BEFORE AND AFTER RESULTS

mysql> select * from GroupIDMap;
+-------+-------+
| oldid | newid |
+-------+-------+
|     1 |    17 |
|     2 |    18 |
|     3 |    19 |
|     4 |    20 |
|     5 |    21 |
|     6 |    22 |
|     7 |    23 |
|     8 |    24 |
|     9 |    25 |
|    15 |    26 |
|    16 |    27 |
+-------+-------+

AFTER ORIGINAL VERSION

mysql> select * from ProfileIDMap;
+-------+-------+
| oldid | newid |
+-------+-------+
|     1 |    34 |
|     1 |    35 |
|     1 |    36 |
|     1 |    37 |
|     1 |    38 |
|     1 |    39 |
|     1 |    40 |
|     1 |    41 |
|     1 |    42 |
|     1 |    43 |
|     2 |    44 |
|     2 |    45 |
|     2 |    46 |
|     2 |    47 |
|     2 |    48 |
|     2 |    49 |
|     2 |    50 |
|     2 |    51 |
|     2 |    52 |
|     2 |    53 |
|     3 |    54 |
|     3 |    55 |
|     3 |    56 |
|     3 |    57 |
|     3 |    58 |
|     3 |    59 |
|     3 |    60 |
|     3 |    61 |
|     3 |    62 |
|     3 |    63 |
|     3 |    64 |
|     4 |    65 |
|     4 |    66 |
|     4 |    67 |
|     4 |    68 |
|     4 |    69 |
|     4 |    70 |
|     4 |    71 |
|     4 |    72 |
|     4 |    73 |
|     4 |    74 |
|     4 |    75 |
...
...
|    30 |   321 |
|    30 |   322 |
|    30 |   323 |
|    30 |   324 |
|    30 |   325 |
|    30 |   326 |
|    30 |   327 |
|    30 |   328 |
|    31 |   329 |
|    31 |   330 |
|    31 |   331 |
|    31 |   332 |
|    31 |   333 |
|    31 |   334 |
|    31 |   335 |
|    31 |   336 |
|    32 |   337 |
|    32 |   338 |
|    32 |   339 |
|    32 |   340 |
|    32 |   341 |
|    32 |   342 |
|    32 |   343 |
|    32 |   344 |
|    33 |   345 |
|    33 |   346 |
|    33 |   347 |
|    33 |   348 |
|    33 |   349 |
|    33 |   350 |
|    33 |   351 |
|    33 |   352 |
|    32 |   353 |
|    32 |   354 |
|    32 |   355 |
+-------+-------+


AFTER NEW VERSION:
mysql> select * from ProfileIDMap;

+-------+-------+
| oldid | newid |
+-------+-------+
|     1 |    34 |
|     2 |    35 |
|     3 |    36 |
|     4 |    37 |
|     5 |    38 |
|     6 |    39 |
|     7 |    40 |
|     8 |    41 |
|     9 |    42 |
|    10 |    43 |
|    11 |    44 |
|    12 |    45 |
|    13 |    46 |
|    14 |    47 |
|    15 |    48 |
|    16 |    49 |
|    17 |    50 |
|    18 |    51 |
|    19 |    52 |
|    20 |    53 |
|    21 |    54 |
|    22 |    55 |
|    23 |    56 |
|    24 |    57 |
|    25 |    58 |
|    26 |    59 |
|    27 |    60 |
|    28 |    61 |
|    29 |    62 |
|    30 |    63 |
|    31 |    64 |
|    32 |    65 |
|    33 |    66 |
+-------+-------+
33 rows in set (0.00 sec)


More information about the mythtv-users mailing list