0

resh install of MariDB 10.4 (fedora 31). I login into mysql as root:

MariaDB [mysql]> select User, Host,Password from user;
+-------+-----------+-------------------------------------------+
| User  | Host      | Password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| mysql | localhost | invalid                                   |
+-------+-----------+-------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [mysql]

If I try to create a new user:

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

As stated from the first table, the user developer doesn't exist. The permissions of root seem fine:

MariaDB [(none)]> SHOW GRANTs;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA mysql_native_password OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                           |
+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
1

Found the problem. Before creating the user, I created a db from a dump. I checked the mysql.db tables and I found that the user 'developer' is in that table, BUT not in mysql.user.

MariaDB [mysql]> select * from db;
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host       | Db       | User      | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %          | mydb   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| 10.8.148.% | postal   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| 10.8.148.% | mydb   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| miyazaki   | mydb   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| %          | helpdesk | develop   | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| %          | helpdesk | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+

So that's the problem. I destroyed the disturbing db

MariaDB [mysql]> create user 'developer'@'localhost' identified by 'pippo';
Query OK, 0 rows affected (0.002 sec)

So, the solution is:

1 - Check for the incriminated user in these tables;

columns_priv, db, procs_priv, tables_priv

2 - When dumping, do not export unrelated tables.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.