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?

Loganiaceous

Is your file very large? If possible you can go through your dump and find where it tries to set that NO_AUTO_CREATE_USER value, and remove that part – Crusty

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. – Headwork

@Headwork you should grep for NO_AUTO_CREATE_USER before you do this. because if you have more options, you also have to remove the comma. i.e i had 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' in my dumpfle. – Barrack

Answer 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.

Sanctitude

This is the answer and I wish someone would mark it as such- this was very hard to find amongst the Laravel-tied version of this question. I'll also add that if your backup file is large (mine was over 200MB), it's going to be basically unopenable in MySQL Workbench, it will eventually open but you will not be able to actually edit it - use something like Visual Studio Code instead, it opened just about instantly for me and I was able to Find and Replace All for NO_AUTO_CREATE_USER to fix the import error. – Corymb

I've already got log_bin_trust_function_creators = 1; and I still got the error message Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' – Headwork

@Headwork Did you remove all the 'NO_AUTO_CREATE_USER' variables within the dump file as well? – Sanctitude

Yes, the file was importable once I ran sed -i 's/NO_AUTO_CREATE_USER//' mydump.sql . And I also had to run sed -i 's/\sDEFINER=[^]*@[^]*//g' mydump.sql. I had to do these things, despite having already set log_bin_trust_function_creators = 1; in the database parameters. – Headwork

@Headwork did you manage to find a resolution? I'm in the same situation as you. – Galer

cleaning mysqldump file from mysql5.7 sed -i 's/,NO_AUTO_CREATE_USER//g' path/da_name.sql – Woodsum

Thanks for your research, in my case just removing the non accepted sql mode 'NO_AUTO_CREATE_USER' was good enough. My dump was created on a Distrib 5.7.17, for Win64 (x86_64) and imported on a Distrib 8.0.19, for Win64 (x86_64) – Halfprice

For anyone who finds this looking at some old Laravel versions, the relevant file to remove NO_AUTO_CREATE_USER from for me was only src/Illuminate/Database/Connectors/MySqlConnector.php. – Stonecrop

@Stonecrop instead of changing the files on the vendor folder, you can edit the database.php on the config folder and change strict from true to false. However your comment did send me in the right path to figure that out! – Aldehyde

For Laravel (8, I think), I had to remove NO_AUTO_CREATE_USER from the database config to get it working again. Haven't found a permanent solution yet – Mezuzah

You saved my time too, that is very detailed thanks a lot! – Sima

Answer 2

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

Cuirassier

Well... now I have error "Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation" so this find & replace wasn't good idea. – Grillage

Answer 3

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.

Coprophilous

Answer 4

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.

Clova

Answer 5

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.

Fula

Answer 6

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

Headwork

Answer 7

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;

Mexicali

Answer 8

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.

Clupeid

Answer 9

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 !!

Audacity

Answer 10

Worked for me when I downgraded the mysql, to more compatible version.

Probably would've also work to update the driver.

Propylene