[mythtv-users] Remote frontend access denied

Stephen Worthington stephen_agent at jsw.gen.nz
Fri Nov 22 02:15:22 UTC 2019


On Thu, 21 Nov 2019 13:42:48 -0500, you wrote:

>On 11/21/2019 1:04 PM, Greg Oliver wrote:
>> On Thu, Nov 21, 2019 at 11:24 AM Don Brett <dlbrett at zoominternet.net 
>> <mailto:dlbrett at zoominternet.net>> wrote:
>>
>>     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
>>
>>
>> Should be :
>>
>> GRANT ALL PRIVILEGES ON mythconverg.* to 'mythtv'@'jax';
>>
>>     > 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.
>>
>>     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
>
>Am I using the correct database?

Yes.

>mysql> use mysql;
>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> select user, host from user;
>+------------------+-----------+
>| user             | host      |
>+------------------+-----------+
>| mythtv           | %         |
>| debian-sys-maint | localhost |
>| mysql.session    | localhost |
>| mysql.sys        | localhost |
>| mythtv           | localhost |
>| root             | localhost |
>+------------------+-----------+
>6 rows in set (0.00 sec)

That makes it clear that the 'mythtv'@'jax' user has not been created.
But the 'mythtv'@'%' user is there, and that should have matched
'mythtv'@'jax' and allowed access.  So what do these commands show?

SHOW GRANTS FOR 'mythtv'@'localhost';
SHOW GRANTS FOR 'mythtv'@'%';

Don is right - it needs "mythconverg.*", not "mythconverg" in the
GRANT command, so I put you wrong there, and that is likely the
problem.

You could try creating the specific 'mythtv'@'jax' user with the
correct GRANT command:

CREATE USER 'mythtv'@'jax' IDENTIFIED BY 'mythtv';
GRANT ALL PRIVILEGES ON mythconverg.* TO 'mythtv'@'jax';
FLUSH PRIVILEGES;

If that works, then you probably should delete the 'mythtv'@'%' user
(unless you really want to allow logins from anywhere):

DROP USER 'mythtv'@'%';


More information about the mythtv-users mailing list