Bug #42633 Mysql recreate user error
Submitted: 5 Feb 2009 22:32 Modified: 5 Feb 2009 22:53
Reporter: Marian Lander Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.67 OS:Linux
Assigned to: CPU Architecture:Any

[5 Feb 2009 22:32] Marian Lander
Description:
Creating some MySQL user, Dropping it later from mysql.user and trying to create again the same user, cause an MySQL Error message. This matter doesn't solved when you log out and log in again. You're able to create the user again when MySQL server is restarted.

How to repeat:
marian@marian:~/pepe$ mysql -u admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select host, user from mysql.user;
+-----------+-------+
| host      | user  |
+-----------+-------+
| localhost | admin |
+-----------+-------+
1 row in set (0.00 sec)

mysql> create user 'marian';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user from mysql.user;
+-----------+--------+
| host      | user   |
+-----------+--------+
| %         | marian |
| localhost | admin  |
+-----------+--------+
2 rows in set (0.00 sec)

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

mysql> select host, user from mysql.user;
+-----------+-------+
| host      | user  |
+-----------+-------+
| localhost | admin |
+-----------+-------+
1 row in set (0.00 sec)

mysql> create user 'marian';
ERROR 1396 (HY000): Operation CREATE USER failed for 'marian'@'%'
mysql> create user 'pepe';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user from mysql.user;
+-----------+-------+
| host      | user  |
+-----------+-------+
| %         | pepe  |
| localhost | admin |
+-----------+-------+
2 rows in set (0.00 sec)

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

mysql> create user 'pepe';
ERROR 1396 (HY000): Operation CREATE USER failed for 'pepe'@'%'
[5 Feb 2009 22:53] MySQL Verification Team
Thank you for the bug report. Please see below:

http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.78-Win x64 bzr revno 2737-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > select host, user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| hegel     |      |
| hegel     | root |
| localhost |      |
| localhost | root |
+-----------+------+
5 rows in set (0.00 sec)

mysql 5.0 > create user 'marian';
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 >  select host, user from mysql.user;
+-----------+--------+
| host      | user   |
+-----------+--------+
| %         | marian |
| 127.0.0.1 | root   |
| hegel     |        |
| hegel     | root   |
| localhost |        |
| localhost | root   |
+-----------+--------+
6 rows in set (0.00 sec)

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

mysql 5.0 > flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create user 'marian';
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create user 'pepe';
Query OK, 0 rows affected (0.00 sec)

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

mysql 5.0 > create user 'pepe';
ERROR 1396 (HY000): Operation CREATE USER failed for 'pepe'@'%'
mysql 5.0 > flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create user 'pepe';
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 >
[28 Feb 2010 1:51] [ name withheld ]
I run into the described behaviour with 5.0.77 (RHEL5). I created a 'auser'@'somehost' and granted him privileges. Then I granted privileges to 'auser'@'otherhost' without having created the user first. Both times I flushed privileges. So 'auser'@'otherhost' didn't work. I realised that I should have created the user before granting him rights, deleted from user where user = 'auser', and tried to create the user again. Now I got ERROR 1396 (HY000): Operation CREATE USER failed for 'auser'@'otherhost'. Flushing privileges and restarting the server didn't help. At the end it turned out that both delete users were still in the db table. "delete from db where user = 'auser'; flush privileges;" solved the problem and I could re-create the users without problems. 

The way I run into this is not really a bug. On the other hand, if the reporter used "drop user" instead of just deleting the user the way I did, it is a bug.