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

[Ronald Frazier]

> That being said, it does seem like one would want to copy those over
> too.
> Why wouldn't the following work (adopting your earlier scripts and
> assuming I understood the syntax)
>
> DELETE FROM settings WHERE hostname ='<NEWHOSTNAME>';
> INSERT INTO settings(name,  hostname, profilegroupid) SELECT name,
> "<NEWHOSTNAME>", profilegroupid FROM settings WHERE hostname = "<OLDHOSTNAME>"
>
> Or is there a problem if you duplicate profilegroupids?

Well, you can do that, but the result is that you'd have 2 systems
sharing the same group id. Modify the profile on one system and it
would change on the other. Of course, maybe that's desirable but it
could also be problematic. I'm not sure if the frontend ever
regenerates its list in such a way that a given id could later end up
pointing to a different profile.

That said, it's a trivial problem to solve in a custom script...I'm
just not sure how to handle it with simple mysql queries. The only way
I could think would be to query the max(id) in the groups table, store
that in a mysql variable, and then use that in a calculation in the
followup query. My MySQL variable skills are a bit rusty, but
something like:

SELECT @idoffset := max(id)+1 FROM Table1;
INSERT INTO Table1(id, host, data) SELECT id+ at idoffset, "newhost",
data FROM Table1 WHERE host="oldhost";
INSERT INTO Table2(id, data) SELECT id+ at idoffset, data FROM Table2
WHERE id IN (SELECT id FROM Table1 WHERE host="oldhost");

Don't recall if that's exactly the correct syntax and would work.


-- 
Ron