while creating and deleting user in mysql ERROR 1396 (HY000): Operation CREATE USER

Question

I am trying to create new user in mysql,

    create user 'saravanakumar'@'localhost' identified by 'saravanakumar';

it shows error as,

    ERROR 1396 (HY000): Operation CREATE USER failed for 'saravanakumar'@'localhost'

after I read this

ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'

I delete user.But I can't.It shows

    mysql> SELECT User FROM mysql.user;
    +---------------+
    | User          |
    +---------------+
    | root          |
    | saravanakumar |
    | saravanakumar |
    |               |
    | root          |
    | saravanakumar |
    |               |
    | root          |
    +---------------+
    8 rows in set (0.00 sec)

    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT User FROM mysql.user;
    +---------------+
    | User          |
    +---------------+
    | root          |
    | saravanakumar |
    | saravanakumar |
    |               |
    | root          |
    | saravanakumar |
    |               |
    | root          |
    +---------------+
    8 rows in set (0.00 sec)

how can i delete all these user in table and how can i create a single user.What is the root cause of this problem? experts please help me.

Answer 1

ERROR 1396 (HY000): Operation CREATE USER failed for 'saravanakumar'@'localhost'

Does indeed indicate that the user already exists or did exist.

FLUSH PRIVILEGES doesn't delete users.

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT, CREATE USER, 
CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released 
by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN 
statements, so for a server that executes many instances of the statements 
that cause caching, there will be an increase in memory use. 
This cached memory can be freed with FLUSH PRIVILEGES.

You are looking for DROP USER.

DROP USER user [, user] ...

http://dev.mysql.com/doc/refman/5.1/en/drop-user.html

---

Order of buisness would be:

DROP USER 'saravanakumar'@HOSTNAME;
CREATE USER 'saravanakumar'@HOSTNAME [IDENTIFIED BY 'password'];

---

You will probably need to flush privileges if you use delete from (do not). Remember: this does not necessarily revoke all the privileges this user may have (like table privileges), you will have to do this yourself - if you don't you may not be able to recreate the user.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'saravanakumar'@HOSTNAME;
DELETE FROM mysql.user WHERE user='saravanakumar';
FLUSH PRIVILEGES;
CREATE USER 'saravanakumar'@HOSTNAME [IDENTIFIED BY 'password'];

---

"user" requires you to specify an account name

Syntax for account names is 'user_name'@'host_name'

and

An account name consisting only of a user name is equivalent 
to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.

Additional reading: http://dev.mysql.com/doc/refman/5.1/en/account-names.html

---

Please read those bug reports for further clarification

http://bugs.mysql.com/bug.php?id=28331

http://bugs.mysql.com/bug.php?id=62255

Answer 2

To me works, I set hostname in UPPERCASE:

DROP USER 'user'@'LOCALHOST'