Menu
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
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.
How would one fix this?
881
How would one fix this? if you have access to the sed utility, try this command: sed -i 's/NO_AUTO_CREATE_USER//' mysqldump. sql to remove the "NO_AUTO_CREATE_USER" text from your dump file, and replace it with nothing. @pbnelson you should grep for NO_AUTO_CREATE_USER before you do this.
MySQL default sql_mode value NO_AUTO_CREATE_USER is the newly added value among others (F-1). S-2 The server run with the --no-defaults option must produce same values as presented in S-1.
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; happens when different version of mysql is being used. When you are transfering from one server to another you should keep in mind the versions of database use in a new environment especially the mysql.
In MySQL 4.0, a server SQL mode system variable named sql_mode was introduced to allow configuring certain aspects of how the server executes SQL statements. Initially, this variable could be set only by means of the --sql-mode startup option. As MySQL 4.1.
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.
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' 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
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;
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.
160
Best way to find & replace. Find NO_AUTO_CREATE_USER and replace it with nothing without opening the file.
Linux sed utility is the best option for that if the *.sql file is large to open.
sed -i 's/FIND_TEXT/REPLACE_TEXT/' file.sql sed -i 's/NO_AUTO_CREATE_USER//' file.sql -i for --in-place[=SUFFIX]
-s for --separate
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
