Bug #28331 Unclear error message when CREATE USER fails due to duplicate key
Submitted: 9 May 2007 11:37 Modified: 5 Dec 2007 18:56
Reporter: Johan Idrén Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[9 May 2007 11:37] Johan Idrén
Description:
When using CREATE USER to create a user account when it already exists, the error message is very unclear.

How to repeat:
mysql> create user 'world';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'world';
ERROR 1396 (HY000): Operation CREATE USER failed for 'world'@'%'

Suggested fix:
mysql> create user 'world';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'world';
ERROR 1396 (HY000): Operation CREATE USER failed for 'world'@'%', user already exists
[30 Nov 2007 13:09] Zoltán Zsizsik
I got this message even if the user does not exists any more.
[16 Jan 2008 10:21] 1_bug_post, _delete_this_profile
I found this bug entry while web-searching for ERROR 1396 (HY000).  And it lead me to find another case where this error message is confusing;  it appears to bork on reserved words also.

Test case that includes the word "user";
  mysql> CREATE USER spamuser@localhost;
  ERROR 1396 (HY000): Operation CREATE USER failed for 'spamuser'@'localhost'

Test case that keeps "use" but not "user";
  mysql> CREATE USER spamuse@localhost;
  Query OK, 0 rows affected (0.00 sec)

Test case that shows the original length was not the fault;
  mysql> CREATE USER spamuset@localhost;
  Query OK, 0 rows affected (0.01 sec)

Which now explains why this was working at 3am this morning when I was using single letter descriptors ;-)
[16 Jan 2008 12:10] 1_bug_post, _delete_this_profile
Actually, after more frustrated testing, it seems that the real cause isn't a reserved word - it seems to be a latent entry in the mysql.db table for a user that has been removed from the mysql.user table.  

Success case;

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

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

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

Alternate success case;

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

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

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

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

Failed case;

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

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

  mysql> create user bugme@localhost;
  ERROR 1396 (HY000): Operation CREATE USER failed for 'bugme'@'localhost'

  mysql> select * from mysql.user where user='bugme' and host='localhost';
  Empty set (0.00 sec)

FYI - I'm currently running 5.0.45;

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.45    | 
+-----------+
1 row in set (0.00 sec)

Something about ERROR 1396 should be updated to reflect the fault's cause; either its immediate error status message, or the web lookup for that error code.
[7 Jul 2008 19:36] Bryan Kearney
Is there a workaround for this? I am seeing this as well on Fedora 9, version 5.0.45.
[28 Aug 2008 17:08] Shaun Carlson
I've been running into these same kinds of problems and couldn't figure out why (other than I could reproduce them regularly as has been described already).  I ended up having to flush the tables, privileges, and query cache to fix the problem (you may not have to flush all of these, I just did out of desperation).
[26 Dec 2008 6:48] Santo Leto
This bug affects me too. Would be very nice to have better error messages.

Thanks,
Santo.
[13 Jan 2009 18:41] MySQL Verification Team
Same problem Fedora 10. Was able to create the user again after a "FLUSH PRIVILEGES".

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

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

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

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

 mysql> select version();
 +------------+
 | version()  |
 +------------+
 | 5.0.45-log | 
 +------------+
 1 row in set (0.00 sec)
[11 Mar 2009 19:58] John Scott
Better error messages would be great in this case.  It took me a while to figure out what is going on.  In a previous poster's comments, the success cases are demonstrative.

One thing that always works - a "drop user" command, as of mysql >= 5.0.2, when the functionality was added to remove all grant permissions for a user upon issuing the "drop user" command, instead of only removing the user from the mysql.user table, which was how it worked in previous versions.

Suggestion: Documentation in create user and drop user syntax in the MySQL manual should suggest that removing a user from mysql.user does not fully remove the user and that DROP USER should instead be used.

It seems that simply deleting the user row from the mysql.user table is inadequate and therefore should not be used unless the steps of either revoking privileges for the user prior to the deletion of the row is first done or a FLUSH PRIVILEGES command is run after the deletion of the row.

In any case, After the problems I have had, I consider deleting the user row from mysql.user a deprecated method of removing users.  DROP USER always works.
[1 Apr 2009 10:13] Alessandro Vercelli
I had to delete an account, so i used 'drop user <username>', but the deleted username still appeared both in mysql.user and MySQL Administrator.

So i tried deleting username references from mysql.user and mysql.db and after re-inserting account failed with:

ERROR 1396 (HY000): Operation CREATE USER failed for '<username>'@'<IP_ADDR>'
[1 Apr 2009 10:21] Alessandro Vercelli
Update:

I tried to create the account with MySQL Administrator and it succeded; after i deleted it again with MySQL Admin and re-created by command line successfully.
[1 Apr 2009 10:22] Alessandro Vercelli
Update:

I tried to create the account with MySQL Administrator and it succeded; after i deleted it again with MySQL Admin and re-created by command line successfully.
[19 Oct 2009 7:30] matthew setter
I had a similar experience. 

I received the error on trying to create the user. I had previously ran drop user '<username>'; 

Then I'd tried running create user to re-add them. 

Following this, I received the error: 'ERROR 1396 (HY000): Operation CREATE USER failed ...'. 

After searching the User table, I found that the user was still listed. After deleting them from the user table by hand, I could re-add them. I would say that this is definitely a bug with drop user at least. 

Some install specifics:

Server Version: 5.0.51a-3ubuntu5.4
Operating System: Ubuntu 8.04.1

cheers, 

Matt
[22 Nov 2010 17:52] Francis Huston
After 3 years can we have useful error messages?
[1 Mar 2011 19:19] N Delfeld
There is another wrinkle to this bug.

On a new install (version 5.5), if the first user that gets created is immediately dropped, the entry *does not appear* in the database.

Adding another user (and dropping that user), allows the first user to show up in the user.user table.  It also shows up as an entry before the "root" user.  Still having trouble deleting the user.
[1 Mar 2011 19:19] N Delfeld
There is another wrinkle to this bug.

On a new install (version 5.5), if the first user that gets created is immediately dropped, the entry *does not appear* in the database.

Adding another user (and dropping that user), allows the first user to show up in the user.user table.  It also shows up as an entry before the "root" user.  Still having trouble deleting the user.
[5 May 2014 13:16] Thomas Gerber
Hi,
i get the error message because there where records in tables_priv for that User before creating the user.

- Records deletet from tables_priv
- flush priviliges
- create user

and its work fine

Prime1009
[22 Sep 2016 10:28] MySQL Verification Team
Bug #82974 marked as duplicate of this
[15 Oct 2018 20:45] Jesper wisborg Krogh
Posted by developer:
 
The error in 8.0 is worse:

mysql-sql> CREATE USER testuser@localhost IDENTIFIED BY 'password';
ERROR: 1396: Operation CREATE USER failed for 'testuser'@'localhost'
[15 Oct 2018 20:50] Jesper wisborg Krogh
Posted by developer:
 
Sorry, ignore the previous comment - it is the same error message in 8.0.