[mythtv-users] Mysql/MariaDB root access denied. Mysql help needed, please.

Thu Jan 14 19:31:28 UTC 2021

Hello everyone,

Latest update: Now mysql is totally inaccessible, so the mythtv server doesn't
even start.  I left all the details of how I got to this point for
completeness.

Some time ago I was trying to fix access to another database.  Since for a
long time now at every mythtv upgrade I had database access issues that
were solved with this:

> mysql -u root
mysql> grant all privileges on mythconverg.* to 'mythtv'@'localhost' identified by 'mythtv' with grant option;
mysql> grant all privileges on mythconverg.* to 'mythtv'@'%' identified by 'mythtv' with grant option;
mysql> flush privileges;
mysql> quit

I tried:

> sudo mysql -u root
MariaDB [(none)]> grant all privileges on <other database>.* to 'www-data'@'localhost' identified by 'www-data' with grant option;
MariaDB [(none)]> grant all privileges on <other database>.* to 'www-data'@'%' identified by 'www-data' with grant option;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit;

Turned out that this was not the problem, so did the same with
'root'@'localhost and 'root'@'%' and that messed things up completely,
I couldn't log in at all even as root.

After some research found this:

https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost

and per the instructions therein had to:

vi /etc/mysql/my.cnf

added a section

[mysqld]
skip-grant-tables

I've been running in this low-security more since.  Mythtv works fine but
since packages mythtv and mythtv-database were upgraded, the configuration
fails for the same reason: Access denied for user 'root'@'localhost': 

Performing actions...
Setting up mythtv-database (30.0+fixes20201003.git622c94965e-dmo1+deb10u2) ...
Failed to connect to database: Access denied for user 'root'@'localhost' (using password: NO) at -e line 5, <> line 1.
Failed to connect to database: Access denied for user 'root'@'localhost' (using password: NO) at -e line 5, <> line 1.
dpkg: error processing package mythtv-database (--configure):
 installed mythtv-database package post-installation script subprocess returned error exit status 255
dpkg: dependency problems prevent configuration of mythtv:
 mythtv depends on mythtv-database (>= 30.0+fixes20201003.git622c94965e-dmo1+deb10u2); however:
  Package mythtv-database is not configured yet.

dpkg: error processing package mythtv (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mythtv-database
 mythtv
E: Sub-process /usr/bin/dpkg returned an error code (1)
Setting up mythtv-database (30.0+fixes20201003.git622c94965e-dmo1+deb10u2) ...
Failed to connect to database: Access denied for user 'root'@'localhost' (using password: NO) at -e line 5, <> line 1.
Failed to connect to database: Access denied for user 'root'@'localhost' (using password: NO) at -e line 5, <> line 1.
dpkg: error processing package mythtv-database (--configure):
 installed mythtv-database package post-installation script subprocess returned error exit status 255
dpkg: dependency problems prevent configuration of mythtv:
 mythtv depends on mythtv-database (>= 30.0+fixes20201003.git622c94965e-dmo1+deb10u2); however:
  Package mythtv-database is not configured yet.

dpkg: error processing package mythtv (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mythtv-database
 mythtv

In my attempts to solve this issue I found this:

https://stackoverflow.com/questions/2995054/access-denied-for-user-rootlocalhost-using-passwordno

Based on the instructions there,

Stopped mythbackend. (with ^C as it's running in the foreground in a window).
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables (from a window which then hangs)
# mysql -u root	(from another window)

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.27-MariaDB-0+deb10u1 Debian 10

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 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
MariaDB [mysql]> grant all privileges on *.* to 'root'@'localhost' identified by '' with grant option;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
MariaDB [mysql]>

But that fails even after removing the skip-grant-tables I put in
/etc/mysql/my.cnf.  I'm also reluctant to do the "truncate table user"
since at least mythtv is working and I might totally mess it up if I do that.

But then I decided to follow all the instructions.

And now this has turned into a real emergency.  I did the "truncate table user"
as specified in the above question of stackoverflow and now (predictably) I
have no users.  Trying to create one (the root user) fails in all ways that I
tried:

MariaDB [mysql]> truncate table user;
Query OK, 0 rows affected (0.005 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.006 sec)

MariaDB [mysql]> grant all privileges on *.* to root at localhost identified by '' with grant option;
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [mysql]> select * from user;
Empty set (0.001 sec)

MariaDB [mysql]> create user root identified by '';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'%'
MariaDB [mysql]> grant all privileges on *.* to root at localhost identified by '' with grant option;
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [mysql]> create user 'root'@'localhost' identified by '';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
MariaDB [mysql]> select * from user;
Empty set (0.000 sec)

MariaDB [mysql]> create user 'root'@'%' identified by '';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'%'
MariaDB [mysql]>

Before doing this, select * from user' gave a bunch of binary garbage, so
something was corrupted.  Now it returns "Empty set"

So now I have a database with no users, including mythtv.

Killed all the mysql processes and restarted per the instructions above and
tried to re-create the root user:

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.27-MariaDB-0+deb10u1 Debian 10

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 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
MariaDB [mysql]> select * from user;
Empty set (0.000 sec)

MariaDB [mysql]> create user 'root'@'localhost' identified by '';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
MariaDB [mysql]>

So if start it with the skip-grant-tables, it fails, if I start it normally
it says access denied for user root at localhost.

Now I'm stuck with a database without users.  Predictably, starting the mythtv
server now fails with "Access denied for user 'mythtv'@'localhost' (using
password: YES).

Presumably the only damage is the user table and mythconverg.* is still
undamaged.  How can I fix this? Pleast help!

Augustine