[Solved] Remote client unable to connect to database

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

Post Reply
gpw928
Junior
Posts: 51
Joined: Tue Feb 12, 2019 5:59 am
Location: Rural NSW
Australia

[Solved] Remote client unable to connect to database

Post by gpw928 »

Hi,

I have just cut over to a brand new release of MythTV going from Debian and git source/fixes29 to the packages in Ubuntu 22.04 (fixes/32).

I built, and configured, and tested, a completely new server on spare disks. It was fully functional. I also built a new remote client. It was also fully functional. I used new host names and new IP addresses.

I then followed the instructions in mythconverg_restore.pl to restore and convert a backup copy of the database from my old server, and change the host name. It worked, without any problems.

The mythtv password changed when I re-initialised the database (from a random string to "mythtv"), but I have dealt with that.

The IP address of the server also changed. I'm wondering if this may be significant (i.e. old IP address lurking somewhere in the tables).

Everything is working on the server, and its local frontend. No issues.

The backend mythtv-setup general Setup reflects the new IP address of the server:

Code: Select all

Security PIN: 0000
Allow connections from all subnets: Yes
Listen on all IP addresses: Yes
Primary IP Address/DNS name: 192.168.1.38
This server is the Master Backend: Yes
I can't connect to the server database form remote clients.

In summary, my problem is demonstrated on the command line of the remote client (password entered was "mythtv"):

Code: Select all

[umtvc1.569] mysql --host 192.168.1.38 --port 3306 -u mythtv -p mythconverg
Enter password: 
ERROR 1045 (28000): Access denied for user 'mythtv'@'umtvc1.my.domain.name' (using password: YES)
The trace from running mythfrontend on the remote client is:

Code: Select all

[umtvc1.570] $ mythfrontend
2023-02-02 16:59:10.443905 I  Display: Requesting EGL for vendor 'Mesa Project, 1.5'
2023-02-02 16:59:10.527969 I  Setup Interrupt handler
2023-02-02 16:59:10.527974 I  Setup Terminated handler
2023-02-02 16:59:10.527978 I  Setup Segmentation fault handler
2023-02-02 16:59:10.527982 I  Setup Aborted handler
2023-02-02 16:59:10.527984 I  Setup Bus error handler
2023-02-02 16:59:10.527989 I  Setup Floating point exception handler
2023-02-02 16:59:10.527992 I  Setup Illegal instruction handler
2023-02-02 16:59:10.527996 I  Setup Real-time signal 0 handler
2023-02-02 16:59:10.528000 I  Setup User defined signal 1 handler
2023-02-02 16:59:10.528003 I  Setup User defined signal 2 handler
2023-02-02 16:59:10.528005 I  Setup Hangup handler
2023-02-02 16:59:10.528211 C  mythfrontend version: fixes/32 [v32.0+fixes.20220325.f69ce764b7-0ubuntu1] www.mythtv.org
2023-02-02 16:59:10.528214 C  Qt version: compile: 5.15.2, runtime: 5.15.3
2023-02-02 16:59:10.528246 I  Ubuntu 22.04.1 LTS (x86_64)
2023-02-02 16:59:10.528248 N  Enabled verbose msgs:  general
2023-02-02 16:59:10.528255 N  Setting Log Level to LOG_INFO
2023-02-02 16:59:10.538556 I  Added logging to the console
2023-02-02 16:59:10.538576 N  Using runtime prefix = /usr
2023-02-02 16:59:10.538579 N  Using configuration directory = /home/phil/.mythtv
2023-02-02 16:59:10.538605 I  Assumed character encoding: en_AU.UTF-8
2023-02-02 16:59:10.538796 I  Added syslogging
2023-02-02 16:59:10.539095 I  Loading en_us translation for module mythfrontend
2023-02-02 16:59:10.539474 I  Empty LocalHostName. This is typical.
2023-02-02 16:59:10.539477 I  Using a profile name of: 'umtvc1' (Usually the same as this host's name.)
2023-02-02 16:59:10.539512 I  Start up testing connections. DB 192.168.1.38, BE , attempt 0, status dbAwake, Delay: 2000
2023-02-02 16:59:11.054234 E  [DBManager0] Unable to connect to database!
2023-02-02 16:59:11.054250 E  Driver error was [1/1045]:
QMYSQL: Unable to connect
Database error was:
Access denied for user 'mythtv'@'umtvc1.my.domain.name' (using password: YES)

2023-02-02 16:59:12.054475 I  Start up testing connections. DB 192.168.1.38, BE , attempt 1, status dbStarted, Delay: 2000
2023-02-02 16:59:12.063738 E  [DBManager1] Unable to connect to database!
2023-02-02 16:59:12.063743 E  Driver error was [1/1045]:
QMYSQL: Unable to connect
Database error was:
Access denied for user 'mythtv'@'umtvc1.my.domain.name' (using password: YES)

2023-02-02 16:59:17.064606 I  Start up testing connections. DB 192.168.1.38, BE , attempt 2, status dbStarted, Delay: 2000
2023-02-02 16:59:17.082426 I  MySQL server disconnected
2023-02-02 16:59:17.091007 I  MySQL server disconnected
2023-02-02 16:59:17.091026 E  DB Error (StorageGroup::StorageGroup()):
Query was:

No error type from QSqlError?  Strange...
...
On the MythTV server, umtvs1, I have done this:

Code: Select all

# sudo mysql -u root -p mysql	# password entered was for root
grant all on mythconverg.* to 'mythtv'@'%';
flush privileges;
exit;

[umtvs1.615] $ mysql --port 3306 -u mythtv -p mythconverg # password is mythtv
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 711
Server version: 8.0.32-0ubuntu0.22.04.2 (Ubuntu)
[...]
mysql> exit

[umtvs1.614] $ sudo lsof -n -P -i TCP | egrep "mysql|myth" | cat
mysqld    1039           mysql   31u  IPv4  30122      0t0  TCP 127.0.0.1:33060 (LISTEN)
mysqld    1039           mysql   37u  IPv4  32188      0t0  TCP *:3306 (LISTEN)
mythbacke 4581          mythtv   31u  IPv4  44788      0t0  TCP *:6544 (LISTEN)
mythbacke 4581          mythtv   32u  IPv6  44789      0t0  TCP *:6544 (LISTEN)
mythbacke 4581          mythtv   33u  IPv4  44790      0t0  TCP *:6554 (LISTEN)
mythbacke 4581          mythtv   34u  IPv6  44791      0t0  TCP *:6554 (LISTEN)
mythbacke 4581          mythtv   35u  IPv4  44792      0t0  TCP *:6549 (LISTEN)
mythbacke 4581          mythtv   36u  IPv6  44793      0t0  TCP *:6549 (LISTEN)
mythbacke 4581          mythtv   49u  IPv4  44807      0t0  TCP *:6543 (LISTEN)
mythbacke 4581          mythtv   50u  IPv6  44808      0t0  TCP *:6543 (LISTEN)
mythbacke 4581          mythtv   51u  IPv4  49904      0t0  TCP 192.168.1.38:54306->192.168.1.41:65001 (ESTABLISHED)
mythbacke 4581          mythtv   55u  IPv4  45560      0t0  TCP *:6744 (LISTEN)
mythbacke 4581          mythtv   56u  IPv6  45561      0t0  TCP *:6744 (LISTEN)
On the client I have:

Code: Select all

[umtvc1.569] $  id
uid=1000(phil) gid=1000(phil) groups=1000(phil),0(root),4(adm),24(cdrom),27(sudo),30(dip),44(video),46(plugdev),122(lpadmin),134(lxd),135(sambashare),139(mythtv)

[umtvc1.568] $ cat ~/.mythtv/config.xml
<Configuration>
  <Database>
    <PingHost>1</PingHost>
    <Host>192.168.1.38</Host>
    <UserName>mythtv</UserName>
    <Password>mythtv</Password>
    <DatabaseName>mythconverg</DatabaseName>
    <Port>3306</Port>
  </Database>
  <WakeOnLAN>
    <Enabled>0</Enabled>
    <SQLReconnectWaitTime>0</SQLReconnectWaitTime>
    <SQLConnectRetry>5</SQLConnectRetry>
    <Command>echo 'WOLsqlServerCommand not set'</Command>
  </WakeOnLAN>
  <LocalHostName/>
  <UPnP>
    <UDN>
      <MediaRenderer>007f8b3e-eacf-45bb-95b9-c9455da245b7</MediaRenderer>
    </UDN>
  </UPnP>
</Configuration>
I'm stumped. I know that client connections are a frequent issue, and I have tried to address all that I could find. But I have run out of ideas.
User avatar
paulh
Developer
Posts: 913
Joined: Thu Feb 06, 2014 6:09 pm
Great Britain

Re: Remote client unable to connect to database

Post by paulh »

One common problem is the mysql bind-address is set to localhost so it will only allow connections from the local PC. Change it to either the IP address of the server (looks like 192.168.1.38 in your case) or you can use 0.0.0.0 to mean listen on all addresses.

You will find it in one or more of the *.cnf files in /etc/mysql. Exactly where depends on whether you are using MySQL or MariaDB and even different versions of Ubuntu keep moving it about but look for a config file called something like mysql.cnf or 50-server.cnf. The way the settings files work is they are all parsed in order and if a setting is in more than one file the last one parsed wins so it's important to make sure you check all the *.cng files for the setting you want to change.
mtvf2894
Junior
Posts: 81
Joined: Sat Apr 10, 2021 2:33 pm
Great Britain

Re: Remote client unable to connect to database

Post by mtvf2894 »

This is what works for me.

Code: Select all

sudo netstat -tlpn
As above you do *not* want 127.0.0.1:3306

Code: Select all

sudo nano /etc/mysql/mariadb.conf.d/99999-myconfig.cnf

Code: Select all

[mysqld]
bind-address = 0.0.0.0

Code: Select all

sudo systemctl restart mysql

Code: Select all

sudo netstat -tlpn
Check now says 0.0.0.0:3306
Current: v32 now v33.1 / Lubuntu 22.04 / AMD APU
Previous: v31 / Lubuntu 20.10 / AMD APU
Historic: Mythbuntu / NVIDIA GFX
gpw928
Junior
Posts: 51
Joined: Tue Feb 12, 2019 5:59 am
Location: Rural NSW
Australia

Re: Remote client unable to connect to database

Post by gpw928 »

Thanks for the responses. I configured the client and got it running on the brand new server before restoring the database from the old server. It was fully working at that stage, and the SQL port on the server was then, and still is, open to all comers (sorry, I should have documented this in my original post):

Code: Select all

[umtvs1.632] # cat /etc/mysql/mysql.conf.d/z55-mythserver.cnf
# Phil was here.  Tue 31 Jan 2023 18:02:57 AEDT.
# localhost doesn't work for remote front ends.
# Last so it over-rides everything else.
[mysqld]
bind-address = 0.0.0.0

[umtvs1.633] # sudo netstat -tlpn | grep sql | cat
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      1039/mysqld         
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1039/mysqld
Last edited by gpw928 on Thu Feb 02, 2023 9:30 pm, edited 2 times in total.
gpw928
Junior
Posts: 51
Joined: Tue Feb 12, 2019 5:59 am
Location: Rural NSW
Australia

Re: Remote client unable to connect to database

Post by gpw928 »

It's an authentication issue. This is done on the client, connecting to port 3306 on the server:

Code: Select all

[umtvc1.579] $ grep umtvs1 /etc/hosts
192.168.1.38 umtvs1.my.domain.name umtvs1

[umtvc1.577] $ nc umtvs1 3306
[
8.0.32-0ubuntu0.22.04.2?&2qR????+CI24.{kcaching_sha2_password
2??#08S01Got timeout reading communication packets
gpw928
Junior
Posts: 51
Joined: Tue Feb 12, 2019 5:59 am
Location: Rural NSW
Australia

Re: Remote client unable to connect to database

Post by gpw928 »

I'm suspecting that the password for mythtv on localhost (which is definitely "mythtv") is different to all other hosts:

Code: Select all

mysql> use mysql;
Database changed
mysql> SELECT User,Host,plugin,authentication_string FROM user WHERE User= 'mythtv';
+--------+-----------+-----------------------+-------------------------------------------+
| User   | Host      | plugin                | authentication_string                     |
+--------+-----------+-----------------------+-------------------------------------------+
| mythtv | %         | mysql_native_password | *C39B699CA6C794948F4C1467A4487FAD1912C987 |
| mythtv | localhost | mysql_native_password | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
+--------+-----------+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)[code]
gpw928
Junior
Posts: 51
Joined: Tue Feb 12, 2019 5:59 am
Location: Rural NSW
Australia

Re: Remote client unable to connect to database

Post by gpw928 »

Code: Select all

mysql> UPDATE User SET Password=PASSWORD('mythtv') WHERE User='mythtv';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('mythtv') WHERE User='mythtv'' at line 1
Help please...
User avatar
bill6502
Developer
Posts: 2348
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Remote client unable to connect to database

Post by bill6502 »

Code: Select all

CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@localhost;
Try the 2nd line. Changing localhost to whatever you want, e.g.%
If that doesn't work, then lines 1 and 2. Not sure 3 is necessary, but that's how
a new user is added in this projects mc.sql file.

I recall that PASSWORD() was removed in MySQL and MariaDB.
gpw928
Junior
Posts: 51
Joined: Tue Feb 12, 2019 5:59 am
Location: Rural NSW
Australia

[Solved] Re: Remote client unable to connect to database

Post by gpw928 »

Code: Select all

ALTER USER 'mythtv'@'%' IDENTIFIED BY 'mythtv';
That worked. Thank you.
Post Reply