Mailing List Archive

database connection issues
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?

TIA,
Leo
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: database connection issues [ In reply to ]
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.

--
Bill
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: database connection issues [ In reply to ]
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
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: database connection issues [ In reply to ]
Stephen Worthington <stephen_agent@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
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: database connection issues [ In reply to ]
>>> 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?
>>

A while ago (multiple years) I had issues with straight '%' working as wildcard. I started using something like '192.168.1.%' instead and have had no issues since so I never bothered looking further. Try adding a more precise host descriptor to the database?
Re: database connection issues [ In reply to ]
Mark Perkins <perkins1724@hotmail.com> writes:

>>>> 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?
>>>
>
> A while ago (multiple years) I had issues with straight '%' working as
> wildcard. I started using something like '192.168.1.%' instead and
> have had no issues since so I never bothered looking further. Try
> adding a more precise host descriptor to the database?

Thanks, Mark. When I get some time time, I will look into your idea.
Leo
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org