在mysql ERROR 1396（HY000）中创建和删除用户时：操作CREATE USER

I am trying to create new user in mysql,

我想在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'

ERROR 1396（HY000）：“jack”@“localhost”的操作CREATE USER失败

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

我删除了user.But我不能。它显示

    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.

如何删除表中的所有这些用户以及如何创建单个用户。这个问题的根本原因是什么？专家请帮帮我。

3 个解决方案

#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.

FLUSH PRIVILEGES不会删除用户。

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。

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.

如果您使用delete（不），则可能需要刷新权限。请记住：这不一定会撤销此用户可能具有的所有权限（例如表权限），您必须自己执行此操作 - 否则您可能无法重新创建用户。

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

“user”要求您指定帐户名称

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

补充阅读：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

#2

To me works, I set hostname in UPPERCASE:

对我来说，我在大写中设置主机名：

DROP USER 'user'@'LOCALHOST'

DROP USER'user'@'LOCALHOST'

#3

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!

让我清楚地知道发生了什么。我最终在几台主机下遇到了同一个用户。删除不需要的帮助！

#1

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