变量"sql_mode"不能设置为"NO_AUTO_CREATE_USER"的值 [英] Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

问题描述

我正在使用MySQL Workbench 8.0.我正在尝试将测试数据转储到DB中，包括所有表，存储过程和带有数据的视图.

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.

当我尝试导入时，提示导入完成，但出现一个错误，错误为

When I try to import it's says import finished with one error and the error is

变量'sql_mode'不能设置为'NO_AUTO_CREATE_USER' 操作失败，退出代码为1

Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' Operation failed with exitcode 1

在导入后，如果我检查数据库，也只有表出现了，但是根本没有存储过程.

Also after importing if I check the database, only tables have come but there are no stored procedures at all.

该如何解决?

推荐答案

从MySQL Workbench 6.1 CE导出数据库，然后尝试将其导入更新版本的MySQL WorkBench 8.0.11之后，我最近也遇到了这个问题.每个都通过社区服务器安装程序msi安装.

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网站上发现了此错误报告: 在8.7.11上使用5.7.22创建的恢复转储

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

对我而言，最有效的解决方法是手动浏览转储文件并删除以下语句:

What fix worked for me was to go through my dump file manually and remove the statements:

"NO_AUTO_CREATE_USER"位于转储文件中每个常规转储的上方. 声明以删除图像示例

'NO_AUTO_CREATE_USER' which are located above each of your routine dumps within the dump file. Statement to remove image example

执行完此操作后，我收到了错误

After I did this I received the error

第318行的错误1418(HY000):此函数的声明中没有DETERMINISTIC，NO SQL或READS SQL DATA，并且启用了二进制日志记录(您可能希望使用安全性较低的代码log_bin_trust_function_creators变量)

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 might want to use the less safe log_bin_trust_function_creators variable)

但是在提到这个回答的问题之后:

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 enabled and simply entering:

SET GLOBAL log_bin_trust_function_creators = 1;

在 MySQL命令行客户端解决了该问题，并最终允许我使用所有转储的表，数据，例程和函数正确导入数据库.

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.

希望这可以为其他人节省一些时间.

Hopefully this saves others some time.

这篇关于变量"sql_mode"不能设置为"NO_AUTO_CREATE_USER"的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！