[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