MySQL 对于拥有 GRANT ALL PRIVILEGES 的用户，CREATE 命令被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5254537/
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
CREATE command denied for user that has GRANT ALL PRIVILEGES
提问by Brad
I am trying to set up a MySQL replication slave, and am having a very difficult time running LOAD DATA FROM MASTER;. Yes, I know it is deprecated, but I am running MySQL 5.1, and that isn't my problem at the moment.
我正在尝试设置 MySQL 复制从站，并且运行LOAD DATA FROM MASTER;. 是的，我知道它已被弃用，但我正在运行 MySQL 5.1，目前这不是我的问题。
For some reason MySQL keeps telling me the CREATEcommand is denied, but a check of SHOW GRANTS says otherwise. Check this out:
出于某种原因，MySQL 一直告诉我该CREATE命令被拒绝，但检查 SHOW GRANTS 却说相反。看一下这个：
mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@% |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD '*ABCDEFABCDEFABCDEFABCDEFBLAHBLAHBLAHBLAH' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> LOAD DATA FROM MASTER;
ERROR 1142 (42000): CREATE command denied to user 'replicator'@'localhost' for table 'aggregate'
mysql>
What I thought was odd here is that when calling LOAD DATA FROM MASTER, it thinks I am 'replicator'@'localhost', yet SHOW GRANTSsays 'replicator'@'%'. Just to be safe, I gave the same privs to 'replicator'@'localhost'as well.
我觉得奇怪的是，在调用时LOAD DATA FROM MASTER，它认为我是'replicator'@'localhost'，但SHOW GRANTS说'replicator'@'%'。为了安全起见，我也给了同样的权限'replicator'@'localhost'。
mysql> SHOW GRANTS FOR 'replicator'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@localhost |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'localhost' IDENTIFIED BY PASSWORD '*ABCDEFABCDEFABCDEFABCDEFBLAHBLAHBLAHBLAH' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
So, any thoughts on why this is all messed up? Yes, I did FLUSH PRIVILEGESmany times as well.
那么，有没有想过为什么这一切都搞砸了？是的，我也做过FLUSH PRIVILEGES很多次。
Thanks in advance for any advice you can give.
预先感谢您提供的任何建议。
回答by Camwyn
For future searchers, I found that I get this error when using a combination of MySQL Workbench and phpMyAdmin: if you 'copy SQL' from the tables list, it prepends the schema name on to the table names in the create statement (and in the foreign key commands that may be part of it).
对于未来的搜索者，我发现在使用 MySQL Workbench 和 phpMyAdmin 的组合时会出现此错误：如果您从表列表中“复制 SQL”，它将在创建语句（以及在可能是其中一部分的外键命令）。
Carefully removing the schema name cured this issue for me.
小心地删除架构名称为我解决了这个问题。
For example:
例如：
CREATE TABLE IF NOT EXISTS `schema`.`table1` (
...blah blah blah...
CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id` )
REFERENCES `schema`.`table1` (`id` )
...blah blah blah...
should be changed to:
应改为：
CREATE TABLE IF NOT EXISTS `table1` (
...blah blah blah...
CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id` )
REFERENCES `table1` (`id` )
...blah blah blah...
回答by RolandoMySQLDBA
I would like to suggest the following:
我想提出以下建议：
When you login next time run this query:
下次登录时运行此查询：
SELECT USER(),CURRENT_USER();
If you see two different usernames, you have a weird scenario
如果你看到两个不同的用户名，你会有一个奇怪的场景
This you can find in the MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), Chapter 34 or 35
您可以在 MySQL 5.0 认证学习指南 (ISBN 0-672-32812-7) 第 34 或 35 章中找到
USER()echoes what you attempted to login as
USER()与您尝试登录的内容相呼应
CURRENT_USER()echos what mysql ALLOWED YOU to login as.
CURRENT_USER()回应 mysql 允许您以什么身份登录。
Try connecting using as replicator using 127.0.0.1 and run the same query.
尝试使用 127.0.0.1 作为复制器进行连接并运行相同的查询。
You may also want to maker sure all necessary columns are present in mysql.user for the version of mysql you are using. If you did not upgrade the mysql.user table (from a migration from MySQL 4 - MySQL 5, or MySQL 5.0 to 5.1) columns in mysql.user could be out of sync.
您可能还想确保所有必需的列都存在于 mysql.user 中，适用于您正在使用的 mysql 版本。如果您没有升级 mysql.user 表（从 MySQL 4 - MySQL 5 或 MySQL 5.0 到 5.1 的迁移），mysql.user 中的列可能不同步。
回答by Brad
Oddly enough, simply QUIT-ing the client and reconnecting resolved the issue! Evidently, FLUSH PRIVILEGESis not enough.
奇怪的是，只需退出客户端并重新连接即可解决问题！显然，FLUSH PRIVILEGES还不够。
I'd like to hear any comments if you have them on why that is. I have always heard that FLUSH PRIVILEGESwill do just that. Could it have something to do with me originally logging in as 'replicator'@'%'and then later creating the 'replicator'@'localhost'account?
如果您对原因有任何意见，我想听听。我一直听说这FLUSH PRIVILEGES会做到这一点。可能与我最初登录'replicator'@'%'然后创建'replicator'@'localhost'帐户有关吗？
In any case, if you have weird permissions issues like this, I guess it is worth trying to reconnect the client.
无论如何，如果您有这样奇怪的权限问题，我想尝试重新连接客户端是值得的。
回答by WThomas
When using MySQL Workbench Migration Wizard to copy a DB from one server to another, I was presented with the same error message because the new DB name did not match the original one. The new server automatically places its domain name in front of all DB names creating a mismatch in names. Therefore when transferring DB xyzto the new server domain, it called the new DB domain_xyzand the above error message occurred for every table creation.
当使用 MySQL Workbench Migration Wizard 将数据库从一台服务器复制到另一台服务器时，我看到了同样的错误消息，因为新的数据库名称与原始数据库名称不匹配。新服务器会自动将其域名放在所有数据库名称的前面，从而造成名称不匹配。因此，当将 DB 传输xyz到新服务器时domain，它会调用新 DB，domain_xyz并且每次创建表时都会出现上述错误消息。
To fix the problem, I manually edited the new DB name after the migration schema was generated, changing it from xyzto doman_xyz.
要解决这个问题，我手动编辑新的数据库名称产生的迁移模式后，对其进行更改xyz到doman_xyz。
Tip-of-the-hat to Camwyn's response that got me looking in this direction.
Camwyn 的回应让我朝这个方向看去。
