[mythtv-users] mythconverg hygiene question

[R. G. Newbury]

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