51

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?

Dillon
  • 1,394
  • 1
  • 11
  • 25
Rajeswari ML
  • 549
  • 1
  • 5
  • 12
  • 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 – Daniele May 14 '18 at 18:33
  • 1
    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 Apr 18 '19 at 20:06
  • 2
    @pbnelson 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. – naiz0 Sep 01 '21 at 11:15

9 Answers9

102

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.

Dillon
  • 1,394
  • 1
  • 11
  • 25
  • 4
    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. – jspinella Oct 13 '18 at 23:50
  • 1
    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'` – pbnelson Apr 18 '19 at 20:02
  • @pbnelson Did you remove all the 'NO_AUTO_CREATE_USER' variables within the dump file as well? – Dillon Apr 20 '19 at 03:00
  • 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. – pbnelson Apr 21 '19 at 11:02
  • @pbnelson did you manage to find a resolution? I'm in the same situation as you. – Christopher Thomas May 28 '19 at 16:04
  • 7
    cleaning mysqldump file from mysql5.7 `sed -i 's/,NO_AUTO_CREATE_USER//g' path/da_name.sql` – bortunac Nov 30 '19 at 23:58
  • 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) – manou Jan 27 '21 at 18:46
  • 1
    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`. – Leith May 01 '21 at 06:15
  • 3
    @Leith 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! – Oscar Olim Aug 10 '21 at 10:53
  • 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 – James Stewart May 04 '22 at 12:00
  • Thanks for your answer, worked like a charm. – anil Jul 07 '23 at 04:26
18

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

Shojib Flamon
  • 1,237
  • 3
  • 12
  • 18
  • 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. – Marek Nov 21 '22 at 18:03
13

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.

Suresh
  • 1,491
  • 2
  • 22
  • 27
9

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

enter image description here

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.

Community
  • 1
  • 1
Pedro Trujillo
  • 1,559
  • 18
  • 19
4

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.

nurikabe
  • 3,802
  • 2
  • 31
  • 39
2

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

pbnelson
  • 1,649
  • 16
  • 14
1

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;

jra6809
  • 11
  • 1
0

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

Probably would've also work to update the driver.

Danon
  • 2,771
  • 27
  • 37
0

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.

warpedcore
  • 1
  • 1