[mythtv-users] mythconverg hygiene question
Don Brett
dlbrett at zoominternet.net
Sun Jul 22 16:46:55 UTC 2018
On 7/18/2018 1:24 AM, R. G. Newbury wrote:
> On 2018-07-16 11:03 AM, Don Brett wrote:
>> 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):
>>
>> | AC3PassThru | 0 | andy |
>> | AC3PassThru | 0 | andy |
>> | AC3PassThru | 0 | kate |
>> | AC3PassThru | 0 | localhost |
>> | AC3PassThru | 0 | nick |
>>
>> | AdjustFill | 0 | andy |
>> | AdjustFill | 0 | andy |
>> | AdjustFill | 0 | andy |
>> | AdjustFill | 0 | kate |
>> | AdjustFill | 0 | localhost |
>> | AdjustFill | 0 | nick |
>>
>>
>> On each migration, I installed a separate box, copied everything
>> over, then changed the hostname. Just to add a little detail:
>>
>> Install database process is:
>> drop default database on new box, install blank from script (mc.sql),
>> restore current with mythconverg_restore.pl.
>>
>> Change hostname command is:
>> $ sudo /usr/share/mythtv/mythconverg_restore.pl --change_hostname
>> --old_hostname="nick" --new_hostname="andy"
>>
>>
>> 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?
>
> As Mike Dean pointed out, the actual answer is 'NO'.
> 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:
>
> mysql> delete * from settings where hostname = "kate"; (or nick)
> DON'T delete hostname="localhost" or "NULL"; those could be active
> settings;
> Also do not delete any hostname entries for remote frontends.
>
> To see the duplicates, run:
>
> mysql > select value, count( value), hostname from settings group by
> value having count(value) > 1;
>
> (That is not dangerous.)
>
> 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!)
> Using an outfile for the output:
>
> mysql > select value, count( value) from settings group by value
> having count(value) > 1 into outfile 'outfile.txt';
> (Note: no hostname)
> Then exit mysql:
> awk '{ $2 = ""; print }' outfile.txt > outfile2.txt
> (Strips the count field, leaving only the setting entry)
>
> And then to indiscriminately delete the FIRST duplicate entry:
> A small script:
> #!/bin/bash
> for line in `awk '{ print }' outfile2.txt`; # back-ticks
> do mysql -u mythtv -p mythtv mythconverg << EOF
> delete from settings where value = '$line' limit 1;
> EOF
> done
>
> Run again if the dup count was 3 etc.
>
> And if you really want to live dangerously you can try all this
> *without* making a backup.
> Here. Hold my beer!
>
> Geoff
>
>
>
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.
mysql> SELECT DISTINCT hostname from settings;
+-----------+
| hostname |
+-----------+
| andy |
| kate |
| localhost |
| nick |
| NULL |
| katie |
+-----------+
mysql> SELECT distinct hostname, COUNT(hostname) from settings group by
hostname having count(hostname) > 1;
+-----------+-----------------+
| hostname | COUNT(hostname) |
+-----------+-----------------+
| andy | 985 |
| kate | 183 |
| katie | 8 |
| localhost | 167 |
| nick | 228 |
+-----------+-----------------+
5 rows in set (0.00 sec)
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.
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.
mysql> select value, count( value) from settings group by value having
count(value) > 1 into outfile 'outfile.txt';
ERROR 1290 (HY000): The MySQL server is running with the
--secure-file-priv option so it cannot execute this statement
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.
mysql> delete from settings where hostname = "andy";
Query OK, 985 rows affected (0.03 sec)
mysql> delete from settings where hostname = "kate";
Query OK, 183 rows affected (0.01 sec)
mysql> delete from settings where hostname = "katie";
Query OK, 8 rows affected (0.00 sec)
mysql> SELECT DISTINCT hostname from settings;
+-----------+
| hostname |
+-----------+
| localhost |
| nick |
| NULL |
+-----------+
mysql> SELECT distinct hostname, COUNT(hostname) from settings group by
hostname having count(hostname) > 1;
+-----------+-----------------+
| hostname | COUNT(hostname) |
+-----------+-----------------+
| localhost | 167 |
| nick | 228 |
+-----------+-----------------+
2 rows in set (0.00 sec)
*Re-running the change_hostname script throws some errors now:*
don at andy:~$ sudo /usr/share/mythtv/mythconverg_restore.pl
--change_hostname --old_hostname="nick" --new_hostname="andy"
DBD::mysql::st execute failed: Duplicate entry 'OpenGL Slim-andy' for
key 'PRIMARY' at /usr/share/mythtv/mythconverg_restore.pl line 1394.
Unable to update hostname in table: displayprofilegroups
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
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
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
Duplicate entry 'TV Playback-BACK-andy' for key 'PRIMARY'
*....checking those tables gives similar results with the hostname column;*
mysql> SELECT distinct hostname, COUNT(hostname) from
displayprofilegroups group by hostname having count(hostname) > 1;
+-------------------------+-----------------+
| hostname | COUNT(hostname) |
+-------------------------+-----------------+
| andy | 10 |
| kate | 10 |
| localhost | 10 |
| nick | 10 |
| tanson.zoominternet.net | 3 |
+-------------------------+-----------------+
5 rows in set (0.00 sec)
mysql> SELECT distinct hostname, COUNT(hostname) from housekeeping
group by hostname having count(hostname) > 1;
+----------+-----------------+
| hostname | COUNT(hostname) |
+----------+-----------------+
| andy | 2 |
| kate | 2 |
| nick | 2 |
+----------+-----------------+
3 rows in set (0.00 sec)
mysql> SELECT distinct hostname, COUNT(hostname) from jumppoints group
by hostname having count(hostname) > 1;
+-------------------------+-----------------+
| hostname | COUNT(hostname) |
+-------------------------+-----------------+
| andy | 43 |
| kate | 32 |
| localhost | 22 |
| nick | 58 |
| tanson.zoominternet.net | 38 |
+-------------------------+-----------------+
5 rows in set (0.00 sec)
mysql> SELECT distinct hostname, COUNT(hostname) from keybindings group
by hostname having count(hostname) > 1;
+-------------------------+-----------------+
| hostname | COUNT(hostname) |
+-------------------------+-----------------+
| andy | 337 |
| kate | 323 |
| katie | 63 |
| localhost | 273 |
| nick | 393 |
| tanson.zoominternet.net | 258 |
+-------------------------+-----------------+
6 rows in set (0.01 sec)
*Manually deleting records with old hostnames from those tables: *
mysql> delete from displayprofilegroups where hostname = "andy";
Query OK, 10 rows affected (0.00 sec)
mysql> delete from housekeeping where hostname = "andy";
Query OK, 2 rows affected (0.00 sec)
mysql> delete from jumppoints where hostname = "andy";
Query OK, 43 rows affected (0.01 sec)
mysql> delete from keybindings where hostname = "andy";
Query OK, 337 rows affected (0.01 sec)
*Also did the same for kate, katie, and tanson.zoominternet.net. Re-ran
the change_hostname script again with no errors:*
don at andy:~$ sudo /usr/share/mythtv/mythconverg_restore.pl
--change_hostname --old_hostname="nick" --new_hostname="andy"
[sudo] password for don:
Successfully changed hostname.
At first glance, Mythbackend setup, Mythfilldatabase, and Mythfrontend
seems to work. I'll know more after finishing the build. Thanks for
the help,
Don
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20180722/03d01d84/attachment.html>
More information about the mythtv-users
mailing list