[mythtv-users] database connection issues

[Leo Butler]

Stephen Worthington <stephen_agent at jsw.gen.nz> writes:

> 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

Bill, Stephen,

I ought to have clarified in my original email. That thread I cited (and
the backend log file which showed no trace of the frontend's DB
connection attempts) suggested that I should check if mysqld were
listening on all interfaces. When I realized a script had over-ridden my
earlier alteration of mythtv.cnf, I made zz-mythtv.cnf and put my tweak
in there.

I am afraid that that is all tangential to the questions I asked in my
original email:

-why did losing ipv6 connectivity on the frontend cause a loss of DB
 connectivity, when the backend wasn't listening on an ipv6 interface
 and the frontend was trying to connect via the backend's ipv6 address?
 How was a connection made in the first place?

Speculations are fine.

-even after connectivity was restored by fixing both problems mentioned
 in the first question, why can't the mythtv user on the frontend
 connect to the mythconverg DB on the backend?

I am sure I doing something dumb here, but I can't figure out what it
is.

TIA,
Leo