[mythtv-users] mythconverg hygiene question

[Don Brett]

On 7/23/2018 12:54 PM, R. G. Newbury wrote:
> On 2018-07-22 12:46 PM, Don Brett wrote:
>>>
>> 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
>
> That option is an attempt to set up individualized security 
> restrictions for users (at least I think that that is what is 
> intended). Each user can have a folder designated in /etc/my.cnf (or 
> /~/my.cnf where they are entitled to read and write. Other folders are 
> denied by the mariadb server.
>
> So, add
> secure_file_priv=/var/lib/mysql
> to /etc/my.cnf and
> systemctl restart mariadb.service.
>
> If /var/lib/mysql is a link to say, /opt/mysql then running:
>
> MariaDB [mythconverg]> SHOW VARIABLES LIKE "secure_file_priv";
> gives:
>
> +------------------+--------------+
> | Variable_name    | Value        |
> +------------------+--------------+
> | secure_file_priv | /opt/mysql/ |
> +------------------+--------------+
> 1 row in set (0.00 sec)
>
> And you can get outfile or load infile to work by giving the full 
> folder name ie. /opt/mysql/outfile.txt.
>
> NOTE: I always link the /var/lib/mysql folder to somewhere else, *on a 
> different physical partition*, so that if there is a error-logging 
> runaway, the /var partition can fill without affecting the running of 
> the system. If '/' includes var, a full partition crashes the system 
> and makes recovery a multi-step live-OS time-consuming project.
>
> Also fedora (and probably other distros) erases and formats the /var 
> folder/partition on an install... something which may not be expected 
> (*and will wipe out your lovingly 'retained' 
> /var/lib/mysql/mythconverg folder...*). So /var must be /dev/sdax 
> where x>1.
>
> Glad you got the underlying problem fixed.
>
> Geoff
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org

I made some minor adjustments, but it worked great.

Added
secure_file_priv=/var/lib/mysql
to /home/don/don.my.cnf and
systemctl restart mysql.service.

Mysql gives:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

, so now I can use outfile:

mysql> use mythconverg;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select value, count( value) from settings group by value having 
count(value) > 1 into outfile '/var/lib/mysql-files/outfile.txt';
Query OK, 165 rows affected (0.00 sec)


Thanks Geoff, you probably saved me hours of searching to find that,

Don


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus