<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">On 7/18/2018 1:24 AM, R. G. Newbury
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:38362331-429c-03b1-457d-5098b73e332a@mandamus.org">On
2018-07-16 11:03 AM, Don Brett wrote:
<br>
<blockquote type="cite">I have a mythconverg database that's been
migrated from several versions. It works fine, but on my latest
migration, I notice some lingering hostnames from years ago.
For example, the settings table should have hostname as nick,
but it also has andy, kate, katie, null, and localhost (all are
earlier boxes). I also see cases of multiple entries of the
same property, here are some examples (from settings table):
<br>
<br>
| AC3PassThru | 0 | andy |
<br>
| AC3PassThru | 0 | andy |
<br>
| AC3PassThru | 0 | kate |
<br>
| AC3PassThru | 0 | localhost |
<br>
| AC3PassThru | 0 | nick |
<br>
<br>
| AdjustFill | 0 | andy |
<br>
| AdjustFill | 0 | andy |
<br>
| AdjustFill | 0 | andy |
<br>
| AdjustFill | 0 | kate |
<br>
| AdjustFill | 0 | localhost |
<br>
| AdjustFill | 0 | nick |
<br>
<br>
<br>
On each migration, I installed a separate box, copied everything
over, then changed the hostname. Just to add a little detail:
<br>
<br>
Install database process is:
<br>
drop default database on new box, install blank from script
(mc.sql), restore current with mythconverg_restore.pl.
<br>
<br>
Change hostname command is:
<br>
$ sudo /usr/share/mythtv/mythconverg_restore.pl
--change_hostname --old_hostname="nick" --new_hostname="andy"
<br>
<br>
<br>
Mythconverg_restore.pl does a pretty good job of changing
hostnames (loops through all tables), but I don't see a way
resolve duplicates. Should I be concerned?
<br>
</blockquote>
<br>
As Mike Dean pointed out, the actual answer is 'NO'.
<br>
But if you wish to live dangerously ( and we are guys, so OF
COURSE we want to do that every once in a while!), you can:
<br>
<br>
mysql> delete * from settings where hostname = "kate"; (or
nick)
<br>
DON'T delete hostname="localhost" or "NULL"; those could be active
settings;
<br>
Also do not delete any hostname entries for remote frontends.
<br>
<br>
To see the duplicates, run:
<br>
<br>
mysql > select value, count( value), hostname from settings
group by value having count(value) > 1;
<br>
<br>
(That is not dangerous.)
<br>
<br>
If you really want to delete the duplicates, all at once (and if
you have one setting value duplicated you will have a couple of
hundred of them!)
<br>
Using an outfile for the output:
<br>
<br>
mysql > select value, count( value) from settings group by
value having count(value) > 1 into outfile 'outfile.txt';
<br>
(Note: no hostname)
<br>
Then exit mysql:
<br>
awk '{ $2 = ""; print }' outfile.txt > outfile2.txt
<br>
(Strips the count field, leaving only the setting entry)
<br>
<br>
And then to indiscriminately delete the FIRST duplicate entry:
<br>
A small script:
<br>
#!/bin/bash
<br>
for line in `awk '{ print }' outfile2.txt`; # back-ticks
<br>
do mysql -u mythtv -p mythtv mythconverg << EOF
<br>
delete from settings where value = '$line' limit 1;
<br>
EOF
<br>
done
<br>
<br>
Run again if the dup count was 3 etc.
<br>
<br>
And if you really want to live dangerously you can try all this
*without* making a backup.
<br>
Here. Hold my beer!
<br>
<br>
Geoff
<br>
<br>
<br>
<br>
</blockquote>
Sorry for the delay, got kinda busy. I agree, no idea how it got
that way, it's been through lots of versions, so....who knows. I
had to brush-up on sql and perl to follow your recommendations (it's
been a while); here's what I found.<br>
<br>
mysql> SELECT DISTINCT hostname from settings;<br>
+-----------+<br>
| hostname |<br>
+-----------+<br>
| andy |<br>
| kate |<br>
| localhost |<br>
| nick |<br>
| NULL |<br>
| katie |<br>
+-----------+<br>
<br>
mysql> SELECT distinct hostname, COUNT(hostname) from settings
group by hostname having count(hostname) > 1;<br>
+-----------+-----------------+<br>
| hostname | COUNT(hostname) |<br>
+-----------+-----------------+<br>
| andy | 985 |<br>
| kate | 183 |<br>
| katie | 8 |<br>
| localhost | 167 |<br>
| nick | 228 |<br>
+-----------+-----------------+<br>
5 rows in set (0.00 sec)<br>
<br>
<br>
I agree with Mike's comments about keeping old settings (since
they're benign), but in my experience, the main part of the database
that's important to me is the recording history. I generally build
a new box in parallel, then when ready, swap out the production
box. Inevitably, I change video cards, or re-configure drives, or
something that inspires me to walk through mythbackend setup again.<br>
<br>
I tried to export the records you suggested, but ran into the latest
security measures. Looking for a way to turn it off, it turned into
a black hole, so abandoned it for the moment.<br>
<br>
mysql> select value, count( value) from settings group by value
having count(value) > 1 into outfile 'outfile.txt';<br>
ERROR 1290 (HY000): The MySQL server is running with the
--secure-file-priv option so it cannot execute this statement<br>
<br>
No, I don't care to live dangerously, so I did the deletes on the
new build. By the way, it threw an error when using *, apparently,
mysql's delete does the entire record without it.<br>
<br>
mysql> delete from settings where hostname = "andy";<br>
Query OK, 985 rows affected (0.03 sec)<br>
<br>
mysql> delete from settings where hostname = "kate";<br>
Query OK, 183 rows affected (0.01 sec)<br>
<br>
mysql> delete from settings where hostname = "katie";<br>
Query OK, 8 rows affected (0.00 sec)<br>
<br>
mysql> SELECT DISTINCT hostname from settings;<br>
+-----------+<br>
| hostname |<br>
+-----------+<br>
| localhost |<br>
| nick |<br>
| NULL |<br>
+-----------+<br>
<br>
mysql> SELECT distinct hostname, COUNT(hostname) from settings
group by hostname having count(hostname) > 1;<br>
+-----------+-----------------+<br>
| hostname | COUNT(hostname) |<br>
+-----------+-----------------+<br>
| localhost | 167 |<br>
| nick | 228 |<br>
+-----------+-----------------+<br>
2 rows in set (0.00 sec)<br>
<br>
<b>Re-running the change_hostname script throws some errors now:</b><br>
<br>
don@andy:~$ sudo /usr/share/mythtv/mythconverg_restore.pl
--change_hostname --old_hostname="nick" --new_hostname="andy"<br>
<br>
DBD::mysql::st execute failed: Duplicate entry 'OpenGL Slim-andy'
for key 'PRIMARY' at /usr/share/mythtv/mythconverg_restore.pl line
1394.<br>
Unable to update hostname in table: displayprofilegroups<br>
<br>
Duplicate entry 'OpenGL Slim-andy' for key 'PRIMARY' DBD::mysql::st
execute failed: Duplicate entry 'JobQueueRecover-andy' for key
'task' at /usr/share/mythtv/mythconverg_restore.pl line 1394. Unable
to update hostname in table: housekeeping<br>
<br>
Duplicate entry 'JobQueueRecover-andy' for key 'task' DBD::mysql::st
execute failed: Duplicate entry 'Reload Theme-andy' for key
'PRIMARY' at /usr/share/mythtv/mythconverg_restore.pl line 1394.
Unable to update hostname in table: jumppoints<br>
<br>
Duplicate entry 'Reload Theme-andy' for key 'PRIMARY' DBD::mysql::st
execute failed: Duplicate entry 'TV Playback-BACK-andy' for key
'PRIMARY' at /usr/share/mythtv/mythconverg_restore.pl line 1394.
Unable to update hostname in table: keybindings<br>
<br>
Duplicate entry 'TV Playback-BACK-andy' for key 'PRIMARY'<br>
<br>
<br>
<b>....checking those tables gives similar results with the hostname
column;</b><br>
<br>
mysql> SELECT distinct hostname, COUNT(hostname) from
displayprofilegroups group by hostname having count(hostname) >
1;<br>
+-------------------------+-----------------+<br>
| hostname | COUNT(hostname) |<br>
+-------------------------+-----------------+<br>
| andy | 10 |<br>
| kate | 10 |<br>
| localhost | 10 |<br>
| nick | 10 |<br>
| tanson.zoominternet.net | 3 |<br>
+-------------------------+-----------------+<br>
5 rows in set (0.00 sec)<br>
<br>
mysql> SELECT distinct hostname, COUNT(hostname) from
housekeeping group by hostname having count(hostname) > 1;<br>
+----------+-----------------+<br>
| hostname | COUNT(hostname) |<br>
+----------+-----------------+<br>
| andy | 2 |<br>
| kate | 2 |<br>
| nick | 2 |<br>
+----------+-----------------+<br>
3 rows in set (0.00 sec)<br>
<br>
mysql> SELECT distinct hostname, COUNT(hostname) from jumppoints
group by hostname having count(hostname) > 1;<br>
+-------------------------+-----------------+<br>
| hostname | COUNT(hostname) |<br>
+-------------------------+-----------------+<br>
| andy | 43 |<br>
| kate | 32 |<br>
| localhost | 22 |<br>
| nick | 58 |<br>
| tanson.zoominternet.net | 38 |<br>
+-------------------------+-----------------+<br>
5 rows in set (0.00 sec)<br>
<br>
mysql> SELECT distinct hostname, COUNT(hostname) from
keybindings group by hostname having count(hostname) > 1;<br>
+-------------------------+-----------------+<br>
| hostname | COUNT(hostname) |<br>
+-------------------------+-----------------+<br>
| andy | 337 |<br>
| kate | 323 |<br>
| katie | 63 |<br>
| localhost | 273 |<br>
| nick | 393 |<br>
| tanson.zoominternet.net | 258 |<br>
+-------------------------+-----------------+<br>
6 rows in set (0.01 sec)<br>
<br>
<b>Manually deleting records with old hostnames from those tables: </b><br>
<br>
mysql> delete from displayprofilegroups where hostname = "andy";<br>
Query OK, 10 rows affected (0.00 sec)<br>
<br>
mysql> delete from housekeeping where hostname = "andy";<br>
Query OK, 2 rows affected (0.00 sec)<br>
<br>
mysql> delete from jumppoints where hostname = "andy";<br>
Query OK, 43 rows affected (0.01 sec)<br>
<br>
mysql> delete from keybindings where hostname = "andy";<br>
Query OK, 337 rows affected (0.01 sec)<br>
<br>
<br>
<b>Also did the same for kate, katie, and tanson.zoominternet.net.
Re-ran the change_hostname script again with no errors:</b><br>
<br>
don@andy:~$ sudo /usr/share/mythtv/mythconverg_restore.pl
--change_hostname --old_hostname="nick" --new_hostname="andy"<br>
[sudo] password for don:<br>
<br>
Successfully changed hostname.<br>
<br>
At first glance, Mythbackend setup, Mythfilldatabase, and
Mythfrontend seems to work. I'll know more after finishing the
build. Thanks for the help,<br>
<br>
Don<br>
<br>
<div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br />
<table style="border-top: 1px solid #D3D4DE;">
<tr>
<td style="width: 55px; padding-top: 13px;"><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient&utm_term=icon" target="_blank"><img src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif" alt="" width="46" height="29" style="width: 46px; height: 29px;" /></a></td>
<td style="width: 470px; padding-top: 12px; color: #41424e; font-size: 13px; font-family: Arial, Helvetica, sans-serif; line-height: 18px;">Virus-free. <a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient&utm_term=link" target="_blank" style="color: #4453ea;">www.avast.com</a>
</td>
</tr>
</table><a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"> </a></div></body>
</html>