[mythtv-users] database connection issues

[Stephen Worthington]

On Wed, 20 Oct 2021 14:35:19 -0500, you wrote:

>On 10/20/21 2:15 PM, Leo Butler via mythtv-users wrote:
>> Hello,
>> 
>> I have run into a connection issue with between my frontend and a
>> separate backend recently. It appears to be connected to loss of the
>> ipv6 interface on the frontend (weirdly, NM has just starting dropping
>> the ipv6 interface at random). The frontend continues to try to use ipv6
>> to connect, although there is a fallback ipv4 address it could use.
>> 
>> I investigated, based on the comments here:
>> 
>> http://lists.mythtv.org/pipermail/mythtv-users/2021-August/407651.html
>> 
>> and sure enough mysqld on the backend was only listening on one
>> interface--ipv4! Apparently, a script had overwritten the bind-address
>> setting that I had made 3-4 years ago. Anyhow, once I set
>> 
>> bind-address = ::
>> 
>> and restarted mysqld, I could connect from mythfrontend on the frontend.
>> 
>> There are a couple strange things here: I have no idea why this ever
>> worked, once mysqld was only listening on 0.0.0.0; why would this be
>> linked to network issues on the frontend?
>> 
>> My fear is that I haven't really fixed anything, that I have gotten
>> lucky, and Murphy will show up when I least want him to.
>> 
>> ----
>> 
>> On a related note, when I try to connect from the frontend using the
>> mysql client, I get:
>> 
>> mysql -umythtv -p --host mythtv-server.lan --port 3306 mythconverg
>> Enter password:
>> ERROR 1045 (28000): Access denied for user 'mythtv'@'asus.lan' (using
>> password: YES)
>> 
>> While on the server, I have:
>> 
>> select * from mysql.user \G
>> 
>> gives:
>> 
>> *************************** 5. row ***************************
>>                   Host: %
>>                   User: mythtv
>> etc.
>> 
>> so as near as I can see, user mythtv has the privilege to access.
>> Can someone explain what I am doing wrong?
>
>At least for the pipermail suggestion to change my.cnf, I disagree
>(assuming that refers the one in /etc/mysql. If you tail that file,
>you'll find the  order of additional directories searched and in
>the last one, make the change in something like mythtv.cnf. Never
>touch the one that the package manager man update. Restart mysql
>to activate.

If you look in /etc/mysql/conf.d you should see a mythtv.cnf file,
which is part of the mythtv-database package.  You want any settings
you are adding to MySQL or MariaDB to be loaded after that file, so
you want to create a file in /etc/mysql/conf.d that is after
mythtv.cnf in the collating sequence used by your filesystem (normally
ext4, or btrfs on new Fedora installs).  Mine is called
mythtv-tweaks-jsw.cnf (jsw = my initials), as I also have a
mythtv-tweaks.cnf file created by a MySQL optimisation script.  Put
your "bind-address = ::" there, along with any other changes you might
want.  I have some extra logging options, for example:

# Logging Options
log-queries-not-using-indexes
log_error
long_query_time         = 5
slow_query_log          = ON
slow_query_log_file     = /var/log/mysql/mysql-slow.log
general_log_file        = /var/log/mysql/mysql.log
general_log             = OFF
log_error               = /var/log/mysql/error.log
binlog_do_db            = include_database_name