10

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.

Community
  • 1
  • 1
saravanakumar
  • 1,747
  • 4
  • 20
  • 38
  • What does `select user, host from mysql.user` show? The duplicate users in the table should have different hosts. – Barmar Sep 02 '13 at 07:26

3 Answers3

12
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

Xevelion
  • 859
  • 6
  • 9
  • Thanks for your answer.I unfortunately delete all users including root,finally I have read permission root only. Because of I while deleting user it shows some error internally delete users.I saw above link, it have a bug about this issue in FEDORA and CENTOS while delete user by query in lower case.Finally I uninstall the mysql and reinstall it. Now it works fine. – saravanakumar Sep 02 '13 at 08:05
2

To me works, I set hostname in UPPERCASE:

DROP USER 'user'@'LOCALHOST'

Luferquisa
  • 73
  • 7
0
select User, Host from mysql.user;

Made it clear for me what was happening. I had ended up with the same user under several hosts. Deleting unwanted ones helped!

user1960812
  • 21
  • 1