Variable 'sql mode' can't be set to the value of 'NO AUTO CREATE USER'

Bugs Fixed

Important Change: Importing a dump from a MySQL 5.7 server to tướng 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 tướng the fact that NO_AUTO_CREATE_USER is enabled by mặc định 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 tướng true prior to tướng executing any SQL. (Bug #90337, Bug #27828236)

Source: MySQL release notes.

Verifying this:

I connected to tướng MySQL then with my schema selected by mặc định 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 tướng u the list of variables and I filtered it typing ps to tướng 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 u 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 tướng another sql tệp tin to tướng 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.