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)
