[mythtv-users] Remote frontend access denied

[Stephen Worthington]

On Wed, 20 Nov 2019 23:01:50 -0500, you wrote:

>I had a working 18.04 Ubuntu-Mate box that worked fine; after a 
>self-induced catastrophic error, I ended up rebuilding it (fe/be box 
>with one remote frontend).  The rebuild also works pretty well, but I 
>haven't gotten the remote front-end working yet.  I haven't made any 
>changes to the remote, but I'm getting database connection errors.  
>Tried lots of things, but no luck.  Any suggestions on what to look for?
>
>Don
>
>*From the logs:*
>
>*Excerpts from remote box: (/var/log/mythtv/mythfrontend.log)*
>Nov 20 12:49:34 jax mythfrontend.real: mythfrontend[2459]: E CoreContext 
>mythdbcon.cpp:239 (OpenDatabase) Driver error was [1/1045]:#012QMYSQL: 
>Unable to connect#012Database error was:#012Access denied for user 
>'mythtv'@'jax' (using password: YES)
>
>Nov 20 12:49:34 jax mythfrontend.real: mythfrontend[2459]: E CoreContext 
>mythdb.cpp:646 (GetSettingOnHost) Database not open while trying to load 
>setting: backendserverport
>
>*Excerpts from fe/be box:**(/var/log/mysql/error.log)*
>2019-11-20T17:49:34.057263Z 544 [Note] Access denied for user 
>'mythtv'@'jax' (using password: YES)
>2019-11-20T18:00:01.606694Z 545 [Note] Got an error reading 
>communication packets
>2019-11-21T00:01:41.387690Z 721 [Note] Got an error reading 
>communication packets
>2019-11-21T00:15:52.630339Z 156 [Note] Aborted connection 156 to db: 
>'mythconverg' user: 'mythtv' host: 'localhost' (Got timeout reading 
>communication packets)
>
>
>*Things I've checked:**
>**
>**/home/don/don.my.cnf*
>secure_file_priv=/var/lib/mysql
>
>*/home/don/.mythtv/config.xml (same as it was before rebuild)*
><LocalHostName>my-unique-identifier-goes-here</LocalHostName>
>   <Database>
>     <PingHost>1</PingHost>
>     <Host>localhost</Host>
>     <UserName>mythtv</UserName>
>     <Password>mythtv</Password>
>     <DatabaseName>mythconverg</DatabaseName>
>     <Port>3306</Port>
>   </Database>
>
>*/home/mythtv/.mythtv/config.xml (symlinked to /etc/mythtv/config.xml)*
><Configuration>
>   <Database>
>     <PingHost>1</PingHost>
>     <Host>localhost</Host>
>     <UserName>mythtv</UserName>
>     <Password>mythtv</Password>
>     <DatabaseName>mythconverg</DatabaseName>
>     <Port>3306</Port>
>   </Database>
>
>*/etc/mysql/mysql.conf.d/mysqld.cnf*
># removed 11/17/2019
>#bind-address           = 127.0.0.1
>
># trying this 11/18/2019...didn't help
>#bind-address           = 0.0.0.0
>
>*/etc/mysql/mysql.conf.d/mythtv.cnf*
>[mysqld]
>#bind-address=::
>max_connections=100
>#sql_mode=NO_ENGINE_SUBSTITUTION

If you are doing external access to the database, you do need either
"bind-address = 0.0.0.0" or "bind-address=::".  Use :: if you want
IPv6 to work.  Make sure that all other "bind-address=" lines in all
the MySQL/MariaDB config files are commented out.  You need to restart
MySQL or MariaDB before it will see the change:

sudo systemctl restart mysql
 or
sudo systemctl restart mariadb

However, the first error message you are reporting is "Access denied".
So that may mean that you have not done the right GRANT command to
allow that user access from that location.  I think doing this on the
backend PC should fix that problem:

sudo mysql
GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'jax';
FLUSH PRIVILEGES;
exit

If that does not work, or you want to allow access from all devices on
your network, try:

sudo mysql
GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'%';
FLUSH PRIVILEGES;
exit

The above presumes that you have actually created the user 'mythtv'.
If not, then you may need to do the following before the above GRANT
commands:

GRANT ALL PRIVILEGES ON mythconverg TO 'mythtv'@'localhost' IDENTIFIED
BY 'mythtv' WITH GRANT OPTION;

Then you need to make sure the config.xml file being used by the
remote frontend has its <Host></Host> value set to the IP address or
hostname of the backend PC.  Both the config.xml files you posted have
it set to "localhost", which will not work for a remote frontend.

Once you have database access working, you also need to ensure that
mythbackend only starts after the network is fully up.  The default
systemd file for mythbackend only waits for localhost to be up, and
mythbackend then never binds to the external IP address.  If that is
the case, just restarting mythbackend after booting is complete will
fix that until the next reboot:

sudo systemctl restart mythtv-backend

If the above fixes things, then you need to create an appropriate
systemd override file for mythbackend.  There are other threads on
this mailing list about how to do that - it is modestly complicated so
I do not want to repeat it here again unless you can not find the
proper thread.  A google search for "wait-until-pingable.py" (in
double quotes) should find it.