0

Fresh 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)
0

The problem is that although the user was not listed in mysql.user table, there was an entry in mysql.db with that user.

So the solution is: check also mysql.db for colliding usernames.

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.