[mythtv-users] Remote frontend access denied

Thu Nov 21 17:22:03 UTC 2019

On 11/21/2019 3:32 AM, Stephen Worthington wrote:
> 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.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org

Did the changes for bind-address=::, no difference.  Tried granting 
privileges and got:

mysql> GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'jax';
ERROR 1046 (3D000): No database selected
mysql>
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'jax';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>
mysql> GRANT ALL PRIVILEGES ON mythconverg TO 'mythtv'@'localhost' 
IDENTIFIED
     -> BY 'mythtv' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> GRANT ALL PRIVILEGES ON mythconverg to 'mythtv'@'%';
Query OK, 0 rows affected (0.00 sec)

Then rebooted, still no difference.

Did I use the correct database?

Checked /etc/mythtv/config.xml on the remote, it was using localhost, so 
changed to 192.168.0.177 (backend address).  No difference.

During the rebuild, I used your systemd procedure, so 
"wait-until-pingable.py" was already in place.

Still getting access denied messages from mysql:

*From /var/log/mysql/error.log (on backend)*
2019-11-21T16:46:18.681726Z 525 [Note] Access denied for user 
'mythtv'@'jax' (using password: YES)

By the way, should this be working?  It's from the remote box to the 
backend box.

don at jax:~$ sudo mysql -h192.168.0.177 -umythtv -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'jax' (using 
password: YES)

Don