[mythtv-users] Ver 31 frontend cannot connect to the database

[Stephen Worthington]

On Sun, 22 Nov 2020 22:52:01 -0800, you wrote:

>
>On 11/22/20 10:40 PM, Bill Meek wrote:
>> On 11/23/20 12:07 AM, Mike Carron wrote:
>>> On 11/22/20 8:38 PM, Bill Meek wrote:
>>>> On 11/22/20 10:27 PM, Mike Carron wrote:
>>>>> On 11/22/20 7:14 PM, Bill Meek wrote:
>>>>>>> On 11/22/20 5:20 PM, Bill Meek wrote:
>>>>>>>> On 11/22/20 6:23 PM, Mike Carron wrote:
>>>>>>>>> My backend host recently failed badly enough that I was not able to
>>>>>>>>> salvage anything so I replaced the hardware and installed a new Ubuntu
>>>>>>>>> 20.04 and a new v31 mythtv-backend-master with xmltv.
>>>>>>>>>
>>>>>>>>> I then upgraded a remote frontend (Ubuntu 18.04) from MythTV 30 to 31
>>>>>>>>> but that frontend will not connect to the database.
>>>>>>>>>
>>>>>>>>> I've checked and rechecked the frontend setup screen and everything is
>>>>>>>>> correct. The config.xml files for both look like they should work. I've
>>>>>>>>> tried the backend config with </host> = localhost and = 192.168.0.222
>>>>>>>>> (the backend machine IP). The frontend config host is 192.168.0.222.
>>>>>>>>>
>>>>>>>>> I know the ip addresses are good. I can load 192.168.0.222:6544 into the
>>>>>>>>> address line of the Firefox on the frontend machine and it finds the
>>>>>>>>> database immediately.
>>>>>>>>>
>>>>>>>>> I'm no doubt missing something obvious but I can't find it. It seems
>>>>>>>>> like I have worn out 2 search engines trying.
>>>>>>>>>
>>>>>>>>> Suggestions?
>>>>>>>>>
>>>>>>>>> mike
>>>>>>>> On the backend's config.xml, always use localhost (or 127.0.0.1 if localhost
>>>>>>>> can't be resolved). That causes a socket to be used.
>>>>>>>>
>>>>>>>> See if mysql is running on the backend: systemctl status mysql mysqld mariadb
>>>>>>>> Probably is if the backend is running OK.
>>>>>>>>
>>>>>>>> One of the above ^^^ should be running. All three will work if
>>>>>>>> using mariadb and the Alias= lines in the service are setup.
>>>>>>>>
>>>>>>>> On the frontend, the following three must work: ping 192.168.0.222,
>>>>>>>> nmap --reason -p 3306 192.168.0.222 (expect a syn-ack) and then:
>>>>>>>>
>>>>>>>>       mysql --host=192.168.0.222 --user=mythtv --password=<from config.xml> mythconverg
>>>>>> On 11/22/20 8:50 PM, Mike Carron wrote:
>>>>>>> backend's config.xml uses localhost.
>>>>>>>
>>>>>>> status for mysql, mysqld and mariadb: active (running)
>>>>>>>
>>>>>>> ping 192.168.0.222 works
>>>>>>>
>>>>>>> nmap --reason -p 3306 192.168.0.222 produces:
>>>>>>>
>>>>>>> PORT     STATE  SERVICE REASON
>>>>>>> 3306/tcp closed mysql   reset ttl 64
>>>>>>> MAC Address: A8:5E:45:E3:5A:9F (Unknown)
>>>>>>>
>>>>>>> mysql --host=192.168.0.222 --user=mythtv --password=<from config.xml>
>>>>>>> mythconverg produces:
>>>>>>>
>>>>>>> ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.222' (111)
>>>>>>>
>>>>>>> This looks like the problem but I have no idea how to fix it.
>>>>>> See the answer from Ramesh.
>>>>>>
>>>>>> grep --recursive bind.address /etc/mysql
>>>>>>
>>>>>> My choice, which allows connections from any host
>>>>>> including IPv6:
>>>>>>
>>>>>> $ cat /etc/mysql/mariadb.conf.d/mythtv.cnf
>>>>>> [mysqld]
>>>>>> bind_address=::
>>>>>>
>>>>>> Restart mysql for it to take affect.
>>>>>>
>>>>> On my backend /etc/mysql/mariadb.conf.d contains: 50-client.cnf
>>>>> 50-mysql-clients.cnf  50-mysqld_safe.cnf  50-server.cnf.
>>>>>
>>>>> mythtv.cnf is in /etc/mysql/conf.d/,  the contents are:
>>>>>
>>>>> [mysqld]
>>>>> bind-address=::
>>>>> max_connections=100
>>>>>
>>>>> I restarted mysql. Nothing changed.
>>>> Need to be careful here. Try this:  tail -5 /etc/mysql/my.cnf
>>>>
>>>> That tells you the order that the sub-directories are included in.
>>>>
>>>> Any instance of bind_address AFTER the one you set will override it.
>>>>
>>>> Do the grep above to see if there are any other bind-addresses. Don't
>>>> change them, just be sure mythtv.cnf is read alphabetically after
>>>> everything else.
>>>>
>>>> Also, "Nothing changed" isn't clear enough. Did the nmap and mysql
>>>> commands fail exactly the same way. You could have moved on from
>>>> a connect failure to a password problem (for example).
>>>>
>>> Both nmap and mysql commands fail in the frontend. Both work in the
>>> backend. Here is the result of nmap:
>>>
>>> root at MythTVMBE:/# nmap --reason -p 3306 192.168.0.222
>>> Starting Nmap 7.80 ( https://nmap.org ) at 2020-11-22 21:57 PST
>>> Nmap scan report for MythTVMBE (192.168.0.222)
>>> Host is up, received localhost-response (0.000064s latency).
>>>
>>> PORT     STATE  SERVICE REASON
>>> 3306/tcp closed mysql   reset ttl 64
>>>
>>> Nmap done: 1 IP address (1 host up) scanned in 0.16 seconds
>>>
>>> If I'm reading this correctly my problem is that port 3306 is closed.
>>> Port 3306 is what the frontend is trying to talk to. How do I open it?
>>> I'm not a mysql expert and I'm way too old to become one.
>> Unfortunately, the bind-address solution is my best shot or at least my 1st.
>>
>> Lets see the command and output from this: grep --recursive bind.address /etc/mysql
>>
>> Then the command and output from this: tail -5 /etc/mysql/my.cnf
>
>These commands are run on the backend:
>
>root at MythTVMBE:/# grep --recursive bind.address /etc/mysql
>/etc/mysql/mariadb.conf.d/50-server.cnf:bind-address            = 127.0.0.1
>/etc/mysql/conf.d/mythtv.cnf:bind-address=::
>
>root at MythTVMBE:/# tail -5 /etc/mysql/my.cnf
>[client-server]
>
># Import all .cnf files from configuration directory
>!includedir /etc/mysql/conf.d/
>!includedir /etc/mysql/mariadb.conf.d/
>
>For what it's worth, etc/mysql/conf.d/ is where the bind address can be
>found. /etc/mysql/mariadb.conf.d does not contain any bind addresses
>that I could find.
>
>mike

Ok, that is the problem then.  The "bind-address=127.0.0.1" is the one
being used by MariaDB, instead of the "bind-address=::", because of
the order it reads the config files.  The last one read is the one
that gets used.  So the simple thing to do is just to comment out the
"bind-address=127.0.0.1" line in the
/etc/mysql/mariadb.conf.d/50-server.cnf file by putting a # at the
start of the line.  Then restart MariaDB:

sudo systemctl restart mariadb