MySQL – Resolve ‘User Exists’ Warning When User Not in ‘User’ Table

MySQL

I have been having this issue for weeks. I have no idea where to look next. I have cleaned, flushed, restarted MySQL service, rebooted the Ubuntu server. What can cause this WARNING and the user to not show in the user table? I've also tried to DROP user and get 0 rows affected as a result. This is frustrating beyond belief! Where else is user info stored in the Schema and how can I purge it?

ScreenShot

UPDATE

When I grep the username in /var/lib/mysql/mysql I find the username in the db.MYD file. Although I cannot edit it . So I know the username exists somewhere OTHER than the user table.

Best Answer

This happens when a user is created and granted privileges and then deleted from mysql.user instead of being dropped:

First, create a user admin_x@localhost:

mysql> create user admin_x@localhost identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

Check if the user is in mysql.user:

mysql> select user, host from mysql.user where user='admin_x';
+---------+-----------+
| user    | host      |
+---------+-----------+
| admin_x | localhost |
+---------+-----------+
1 row in set (0.01 sec)

Okay.

Now we grant this user access to db test:

mysql> grant all on test.* to admin_x@localhost;
Query OK, 0 rows affected (0.00 sec)

And check it:

mysql> show grants for admin_x@localhost;
+-----------------------------------------------------------+
| Grants for admin_x@localhost                              |
+-----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin_x'@'localhost'      |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin_x'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

Now delete the user improperly from mysql.user:

mysql> delete from mysql.user where user='admin_x';
Query OK, 1 row affected (0.00 sec)

And the user is no longer in mysql.user:

mysql> select user from mysql.user where user='admin_x';
Empty set (0.00 sec)

But when you now try to create it new, you get an error:

mysql> create user admin_x@localhost identified by 'abc123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'admin_x'@'localhost'

That is because admin_x@localhost still has privileges stored in mysql.db:

mysql> select User from mysql.db where user='admin_x';
+---------+
| User    |
+---------+
| admin_x |
+---------+
1 row in set (0.00 sec)

Now, when you drop the user

mysql> drop user admin_x@localhost;
Query OK, 0 rows affected (0.00 sec)

it is really gone and you can create it again:

mysql> create user admin_x@localhost identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)
Related Topic