[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