MySQL 错误 1396 (HY000)：操作 CREATE USER 对 'jack'@'localhost' 失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5555328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR 1396 (HY000): Operation CREATE USER failed for 'Hyman'@'localhost'
提问by Russ Bateman
I seem to be unable to re-create a simple user I've deleted, even as root in MySQL.
我似乎无法重新创建我删除的简单用户，即使是 MySQL 中的 root 用户。
My case: user 'Hyman' existed before, but I deleted it from mysql.user in order to recreate it. I see no vestiges of this in that table. If I execute this command for some other, random username, say 'jimmy', it works fine (just as it originally did for 'Hyman').
我的情况：用户 'Hyman' 以前存在过，但我从 mysql.user 中删除了它以重新创建它。我在那张桌子上看不到这方面的痕迹。如果我为其他一些随机用户名执行此命令，例如“jimmy”，它可以正常工作（就像它最初对“Hyman”所做的一样）。
What have I done to corrupt user 'Hyman' and how can I undo that corruption in order to re-create 'Hyman' as a valid user for this installation of MySQL?
我做了什么来破坏用户 'Hyman' 以及如何撤消该损坏以便重新创建 'Hyman' 作为此 MySQL 安装的有效用户？
See example below. (Of course, originally, there was much time between the creation of 'Hyman' and his removal.)
请参阅下面的示例。（当然，最初，“Hyman”的创建和他被移除之间有很长的时间。）
mysql> CREATE USER 'Hyman'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+------------------+-----------------+
| user | host |
+------------------+-----------------+
| root | 127.0.0.1 |
| debian-sys-maint | localhost |
| Hyman | localhost |
| root | localhost |
| root | russ-elite-book |
+------------------+-----------------+
5 rows in set (0.00 sec)
mysql> delete from user where user = 'Hyman';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host from user;
+------------------+-----------------+
| user | host |
+------------------+-----------------+
| root | 127.0.0.1 |
| debian-sys-maint | localhost |
| root | localhost |
| root | russ-elite-book |
+------------------+-----------------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'Hyman'@'localhost' IDENTIFIED BY 'test123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'Hyman'@'localhost'
mysql> CREATE USER 'jimmy'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+------------------+-----------------+
| user | host |
+------------------+-----------------+
| root | 127.0.0.1 |
| debian-sys-maint | localhost |
| jimmy | localhost |
| root | localhost |
| root | russ-elite-book |
+------------------+-----------------+
5 rows in set (0.00 sec)
采纳答案by QuantumMechanic
Try doing a FLUSH PRIVILEGES;. This MySQL bug post on that error codeappears to report some success in a case similar to yours after flushing privs.
尝试做一个FLUSH PRIVILEGES;. 这个错误代码上的 MySQL 错误帖子似乎在刷新 privs 后在类似于您的情况下报告了一些成功。
回答by tver3305
yes this bug is there. However, I found a small workaround.
是的，这个错误在那里。但是，我找到了一个小的解决方法。
- Assume the user is there, so drop the user
- After deleting the user, there is need to flush the mysql privileges
- Now create the user.
- 假设用户在那里，所以删除用户
- 删除用户后，需要刷新mysql权限
- 现在创建用户。
That should solve it. Assuming we want to create the user admin @ localhost, these would be the commands:
那应该可以解决它。假设我们要创建用户 admin @ localhost，这些将是命令：
drop user admin@localhost; flush privileges; create user admin@localhost identified by 'admins_password'
Cheers
干杯
回答by user1969061
This bug has been sitting on bugs.mysql.com since 2007 and this thread is mainly just a parroting of all those wrong answers even up to a year ago.
自 2007 年以来，这个错误一直存在于 bugs.mysql.com 上，这个帖子主要是对所有这些错误答案的鹦鹉学舌，甚至直到一年前。
According to the MySQL documentation, commands like CREATE USER, GRANT, REVOKE, and DROP USERdo not require a subsequent FLUSH PRIVILEGEScommand. It's quite clear why, if one reads the docs. It's because altering the MySQL tables directly does not reload the info into memory; yet the plethora of solutions to this bug claim that FLUSH PRIVILEGESis the answer.
据MySQL文档，命令一样CREATE USER，GRANT，REVOKE，并且DROP USER不需要后续的FLUSH PRIVILEGES命令。如果有人阅读文档，原因就很清楚了。这是因为直接更改 MySQL 表不会将信息重新加载到内存中；然而，这个错误的众多解决方案声称这FLUSH PRIVILEGES就是答案。
This also may not even be a bug. It is a documentation conspiracy - docs vary in one critical place from version to version.
这甚至可能不是一个错误。这是一个文档阴谋 - 文档在不同版本的一个关键位置有所不同。
13.7.1.2. DROP USER Syntax
13.7.1.2. 删除用户语法
...
...
DROP USER user [, user] ...
删除用户用户 [, 用户] ...
...
...
DROP USER 'jeffrey'@'localhost';
删除用户 'jeffrey'@'localhost';
If you specify only the user name part of the account name, a host name part of '%' is used.
如果您仅指定帐户名的用户名部分，则使用“%”的主机名部分。
DROP USERas present in MySQL 5.0.0 removes only accounts that have no privileges. In MySQL 5.0.2, it was modified to remove account privileges as well. This means that the procedure for removing an account depends on your version of MySQL.
DROP USERMySQL 5.0.0 中存在的仅删除没有权限的帐户。在 MySQL 5.0.2 中，它也被修改为删除帐户权限。这意味着删除帐户的过程取决于您的 MySQL 版本。
As of MySQL 5.0.2, you can remove an account and its privileges as follows:
从 MySQL 5.0.2 开始，您可以删除帐户及其权限，如下所示：
DROP USER user;
删除用户用户；
The statement removes privilege rows for the account from all grant tables.
该语句从所有授权表中删除帐户的权限行。
The only time I get this error is when I do DROP USER user; like the doc suggests, but MySQL does not treat the '%' as a wildcard in a way that would drop all users at all hosts. It's not so wild after all. Or, it may be that it sometimes works when it deletes the localhost user and then tries to delete the one at %.
我唯一一次收到此错误是在我这样做时DROP USER user；就像文档建议的那样，但 MySQL 不会将 '%' 视为通配符，这种方式会删除所有主机上的所有用户。毕竟没那么狂野。或者，当它删除 localhost 用户然后尝试删除 % 的用户时，它有时会起作用。
It's clear to me that when it tries to delete the user at %, it issues an error message and quits. Subsequent CREATE USERat localhost will fail because the localhost user was never deleted. There seems to be no need to waste time digging in the grant tables looking for ghosts as one poster suggested.
我很清楚，当它试图删除 % 的用户时，它会发出一条错误消息并退出。后续CREATE USER在 localhost 将失败，因为 localhost 用户从未被删除。似乎没有必要像一位海报所建议的那样浪费时间在拨款表中寻找幽灵。
I see 7 votes for:
我看到 7 票赞成：
DROP USER 'Hyman@localhost'; // completely delete the account
删除用户 'Hyman@localhost'; // 彻底删除账户
Which is interpreted as DROP USER 'Hyman@localhost'@'%';# wrong
这被解释为DROP USER 'Hyman@localhost'@'%';# 错误
There actually seems to be a real bug that generates the same error message, but it has to do with the first created user (after a new mysql server install) being dropped. Whether that bug has been fixed, I don't know; but I don't recall that happening lately and I'm up to ver 5.5.27 at this time.
实际上似乎有一个真正的错误会生成相同的错误消息，但这与第一个创建的用户（在新的 mysql 服务器安装之后）被删除有关。我不知道那个错误是否已经修复；但我不记得最近发生过这种情况，此时我的版本是 5.5.27。
回答by Anthony Rutledge
If you use a DELETEstatement on the mysql.usertable in an attempt to remove a user, then attempt to re-establish the user with CREATE USER, you will get a 1396error. Get rid of this error by running DROP USER 'username'@'host';
如果您DELETE在mysql.user表上使用语句试图删除用户，然后尝试使用 重新建立用户CREATE USER，您将收到1396错误消息。通过运行摆脱这个错误DROP USER 'username'@'host';
DELETE
FROM mysql.user
WHERE user = 'Hyman';
(You will get 1396 errors if you attempt to re-create Hyman)
（如果您尝试重新创建 Hyman，您将收到 1396 错误）
CREATE USER 'Hyman'@'localhost' IDENTIFIED BY PASSWORD '*Fi47ytFF3CD5B14E7EjkjkkC1D3F8086A5C0-krn';
(Get out of this situation by running DROP USER)
（通过跑步摆脱这种情况DROP USER）
DROP USER 'Hyman'@'localhost';
(I suppose FLUSH PRIVILEGEScan't hurt, but definitely drop the user first.)
（我想FLUSH PRIVILEGES不会受到伤害，但一定要先放弃用户。）
回答by Marc B
You shouldn't be manually deleting users that way. MySQL has REVOKEsyntax for removing privileges and DROP USERfor deleting them:
您不应该以这种方式手动删除用户。MySQL 具有REVOKE删除权限和DROP USER删除权限的语法：
REVOKE priv1,priv2,priv3,etc... FROM 'Hyman@localhost'; // remove certain privileges
DROP USER 'Hyman@localhost'; // completely delete the account
Best to use the tools provided rather than mucking around in the background.
最好使用提供的工具，而不是在后台乱搞。
回答by u1217541
Drop the user, flush the privileges; then, create the user. It does work!
删除用户，刷新权限；然后，创建用户。它确实有效！
回答by a1ex07
try delete from mysql.db where user = 'Hyman'and then create a user
尝试delete from mysql.db where user = 'Hyman'然后创建一个用户
回答by Joilson Cardoso
In MySQL 5.6 using Drop user userid;does not work. Use: Drop user 'userid'@'localhost';and/or Drop user 'userid'@'%';. In this way I was able to drop the user and recreate it.
在 MySQL 5.6 中使用Drop user userid;不起作用。使用：Drop user 'userid'@'localhost';和/或Drop user 'userid'@'%';。通过这种方式，我能够删除用户并重新创建它。
回答by TonyWu
two method
one :
setp 1: drop user 'Hyman'@'localhost';
setp 2: create user 'Hyman'@localhost identified by 'ddd';
two:
setp 1: delete from user where user='Hyman'and host='localhost';
setp 2: flush privileges;
setp 3: create user 'Hyman'@'localhost' identified by 'ddd';
回答by fely
If you want to delete a user with sql, you need to delete the related data in these tables: columns_priv, db, procs_priv, tables_priv. Then execute flush privileges;
如果你想用sql删除一个用户，你需要删除这些表中的相关数据：columns_priv, db, procs_priv, tables_priv。然后执行flush privileges;
