Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Question

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?

Answer 1

If your exported .sql file is too big and cant open in editor, For windows you can try below solution using powershell.

path = "C:\Temp\abc.sql" # Specify the path to your .sql file
$findText = "NO_AUTO_CREATE_USER," # Text you want to find
$replaceText = "" # Text you want toreplace it with
 
(Get-Content $path) -replace [regex]::Escape($findText), $replaceText | Set-Content $path
 
Select-String -Path "C:\Temp\abc.sql" -Pattern "NO_AUTO_CREATE_USER," 

==>this will return 0 rows

Then try importing .sql file

Explanation:

• $path: Set this variable to the path of your .sql file.
• $findText: The text you want to find in the file.
• $replaceText: The text you want to use as a replacement.
• Get-Content: Reenter code hereads the content of the specified file.
• -replace: Performs the replacement operation. The [regex]::Escape() method is used to escape any special characters in the text you are searching for.
• Set-Content: Writes the modified content back to the file.

Answer 2

remove NO_AUTO_CREATE_USER from file.sql and try again it's work for me

Answer 3

Fix MySQL8.0 issue https://bugs.mysql.com/bug.php?id=90624 (Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER')

I recently did something similar,
The script here, It decompresses the original dump, modifies it in place using sed, recompresses the modified dump, and replaces the original file with the updated one.

gunzip < database/_backups/$DATABASE_NAME.sql.gz | sed 's/NO_AUTO_CREATE_USER//' | gzip -9 > $DATABASE_NAME.sql.gz.new
      rm -f database/_backups/$DATABASE_NAME.sql.gz
      mv -v $DATABASE_NAME.sql.gz.new database/_backups/$DATABASE_NAME.sql.gz

Hope it helps !!

Answer 4

I just ran into the same exact problem while restoring a 5.7 version dump using Workbench 8.0 in Windows environment.

I combined everyone's recommendations above as follows:

• Used Notepad++ and to universally remove the "NO_AUTO_CREATE_USER" option from the dump file.
• SET pseudo_slave_mode = true;
• SET @@SESSION.pseudo_slave_mode = true;
• SET GLOBAL log_bin_trust_function_creators = 1;

That worked (Thank you), however - some important notes:

• usage of special characters (i.e. double-quotes, back-slashes, etc.), if not properly formatted can cause the debugger to flag it, thus aborting the import.
• deprecated commands, such as "reset query cache" will also cause the debugger to throw an exception.

Any of the above is typically exhibited as: ERROR 1064 (42000) at line : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near <...>

I handled each condition by copying the individual problematic stored procedure from the dump file into a NEW stored procedure in Workbench. The native debugger immediately highlighted the offending line(s)/statement(s).

After several volleys of the above, I was able to finally import the entire .sql dump file cleanly.

Answer 5

Dillon's answer works for me, thanks

MAC OS:
sed -i old 's/\DEFINER=[^]*@[^]*//g' file_name.sql
sed 's/,NO_AUTO_CREATE_USER//g' -i file_name.sql

LINUX:
sed 's/\sDEFINER=[^]*@[^]*//g' -i file_name.sql
sed 's/,NO_AUTO_CREATE_USER//g' -i file_name.sql

Mysql:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

Answer 6

From the command line, the --force option will cause mysql to continue processing the dump and ignore the 'NO_AUTO_CREATE_USER' (as well as any other) error.

You can turn on this behavior in MySQL Workbench as well. See Continue SQL query even on errors in MySQL workbench.

Answer 7

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

Answer 8

Bugs Fixed

Important Change: Importing a dump from a MySQL 5.7 server to a server running MySQL 8.0 often failed with ER_WRONG_VALUE_FOR_VAR when an SQL mode not supported by the 8.0 server was used. This could happen frequently due to the fact that NO_AUTO_CREATE_USER is 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_mode system variable. If this is false, the server rejects the mode setting with ER_UNSUPPORTED_SQL_MODE. If pseudo_slave_mode is true, the server ignores the unsupported mode and gives a warning. Note that mysqlbinlog sets pseudo_slave_mode to true prior to executing any SQL. (Bug #90337, Bug #27828236)

Source: MySQL release notes.

Verifying this:

I connected to MySQL then with my schema selected by default I ran the following commands in a Workbench SQL tab:

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 ps to find the pseudo_slave_mode variable

Yup pseudo_slave_mode was ON now (when previously was OFF)

Then I ran the .sql and it showed me the NO_AUTO_CREATE_USER error again but this time it created everything that was required in the .sql file

Then I dumped the schema, to another sql file to verify it:

mysqldump -u root -p --no-data --routines my_database > schema.sql

Everything was ok. This time it dumped it with a modified sql_mode

I hope this can be helpful for you.

Answer 9

I too faced the similar problem. Just removed that words NO_AUTO_CREATE_USER from the import script by using find & replace option in mysql workbench and it executed fine.

Answer 10

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

Answer 11

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.