MySQL 变量“sql_mode”不能设置为“NO_AUTO_CREATE_USER”的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50336378/
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
Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
提问by Rajeswari ML
I am using MySQL Workbench 8.0. I am trying to dump test data to DB including all the tables, stored procedures and views with data.
我正在使用 MySQL Workbench 8.0。我正在尝试将测试数据转储到数据库，包括所有表、存储过程和带有数据的视图。
When I try to import it's says import finished with one error and the error is
当我尝试导入时，它说导入完成了一个错误，错误是
Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' Operation failed with exitcode 1
变量“sql_mode”无法设置为“NO_AUTO_CREATE_USER”的值操作失败，退出代码为 1
Also after importing if I check the database, only tables have come but there are no stored procedures at all.
同样在导入后，如果我检查数据库，只有表来了，但根本没有存储过程。
How would one fix this?
如何解决这个问题？
回答by Dillon
I recently had this problem as well after exporting my database from MySQL Workbench 6.1 CE and then trying to import it into a newer version of MySQL WorkBench 8.0.11. Each were installed with the community server installer msi.
我最近在从 MySQL Workbench 6.1 CE 导出我的数据库然后尝试将它导入到较新版本的 MySQL WorkBench 8.0.11 后也遇到了这个问题。每个都安装了社区服务器安装程序 msi。
After doing some searching I came across this bug report on the MySQL website: Restaure dump created with 5.7.22 on 8.0.11
在做了一些搜索之后，我在 MySQL 网站上发现了这个错误报告： Restaure dump created with 5.7.22 on 8.0.11
What fix worked for me was to go through my dump file manually and remove the statements:
对我有用的修复方法是手动检查转储文件并删除以下语句：
'NO_AUTO_CREATE_USER' which are located above each of your routine dumps within the dump file. Statement to remove image example
“NO_AUTO_CREATE_USER”位于转储文件中每个例程转储的上方。 声明删除图像示例
After I did this I received the error
在我这样做之后，我收到了错误
ERROR 1418 (HY000) at line 318: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you mightwant to use the less safe log_bin_trust_function_creators variable)
第 318 行的 ERROR 1418 (HY000)：此函数的声明中没有 DETERMINISTIC、NO SQL 或 READS SQL DATA，并且启用了二进制日志记录（您可能希望使用安全性较低的 log_bin_trust_function_creators 变量）
But after referring to this answered question: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabledand simply entering:
但是在参考了这个已回答的问题后： 此函数在其声明中没有 DETERMINISTIC、NO SQL 或 READS SQL DATA，并且启用了二进制日志记录，只需输入：
SET GLOBAL log_bin_trust_function_creators = 1;
SET GLOBAL log_bin_trust_function_creators = 1;
in the MySQL command line client solved that issue and finally allowed me to properly import my database with all the dumped tables, data, routines, and functions.
在 MySQL 命令行客户端解决了这个问题，并最终允许我正确导入我的数据库以及所有转储的表、数据、例程和函数。
Hopefully this saves others some time.
希望这可以为其他人节省一些时间。
回答by pbnelson
I found a workaround, if not the solution. Use Linux to get the sed utility, and run the two sed commands as mentioned in my previous comment. Also, I needed to use the mysqldump option: --set-gtid-purged=OFF
如果不是解决方案，我找到了解决方法。使用 Linux 获取 sed 实用程序，并运行我之前评论中提到的两个 sed 命令。另外，我需要使用 mysqldump 选项：--set-gtid-purged=OFF
回答by mannedear
I too faced the similar problem. Just removed that words NO_AUTO_CREATE_USERfrom the import script by using find& replaceoption in mysql workbench and it executed fine.
我也遇到了类似的问题。刚刚NO_AUTO_CREATE_USER通过在 mysql 工作台中使用find&replace选项从导入脚本中删除了这些词，它执行得很好。
回答by Pedro Trujillo
Bugs Fixed
Important Change:Importing a dump from a MySQL 5.7server to a server running MySQL 8.0often failed with
ER_WRONG_VALUE_FOR_VARwhen an SQL mode not supported by the 8.0 server was used. This could happen frequently due to the fact thatNO_AUTO_CREATE_USERis enabled by default in MySQL 5.7 but not supported in MySQL 8.0.The behavior of the server in such circumstances now depends on the setting of the
pseudo_slave_modesystem variable. If this is false, the server rejects the mode setting withER_UNSUPPORTED_SQL_MODE. Ifpseudo_slave_modeis true, the server ignores the unsupported mode and gives a warning. Note that mysqlbinlogsetspseudo_slave_modeto true prior to executing any SQL. (Bug #90337, Bug #27828236)
错误已修复
重要更改：当使用 8.0 服务器不支持的 SQL 模式时，将转储从MySQL 5.7服务器导入运行MySQL 8.0的服务器通常会失败
ER_WRONG_VALUE_FOR_VAR。由于NO_AUTO_CREATE_USER在 MySQL 5.7 中默认启用但在 MySQL 8.0 中不支持，这可能经常发生。服务器在这种情况下的行为现在取决于
pseudo_slave_mode系统变量的设置。如果这是错误的，服务器将拒绝模式设置ER_UNSUPPORTED_SQL_MODE。如果pseudo_slave_mode为 true，则服务器忽略不支持的模式并发出警告。请注意，mysqlbinlogpseudo_slave_mode在执行任何 SQL 之前设置为 true。（错误＃90337，错误＃27828236）
Source: MySQL release notes.
来源：MySQL 发行说明。
Verifying this:
验证这一点：
I connected to MySQL then with my schema selected by default I ran the following commands in a Workbench SQL tab:
我连接到 MySQL，然后默认选择了我的架构，我在 Workbench SQL 选项卡中运行了以下命令：
SET pseudo_slave_mode = true;
SET @@SESSION.pseudo_slave_mode = true;
To make sure it worked I verified it with other command in other tab:
为了确保它有效，我在其他选项卡中使用其他命令对其进行了验证：
SHOW VARIABLES;
It showed to me the list of variables and I filtered it typing psto find the pseudo_slave_modevariable
它向我展示了变量列表，我过滤了它，输入ps来查找pseudo_slave_mode变量
Yup pseudo_slave_modewas ONnow (when previously was OFF)
是啊pseudo_slave_mode是ON现在（当先前未OFF）
Then I ran the .sqland it showed me the NO_AUTO_CREATE_USERerror again but this time it created everything that was required in the .sqlfile
然后我运行.sql并NO_AUTO_CREATE_USER再次向我显示错误，但这次它创建了.sql文件中 所需的所有内容
Then I dumped the schema, to another sql file to verify it:
然后我将模式转储到另一个 sql 文件以验证它：
mysqldump -u root -p --no-data --routines my_database > schema.sql
mysqldump -u root -p --no-data --routines my_database > schema.sql
Everything was ok. This time it dumped it with a modified sql_mode
一切正常。这次它用修改后的sql_mode
I hope this can be helpful for you.
我希望这对你有帮助。
