Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL 8 refuses sql_mode NO_AUTO_CREATE_USER from a dump or config

ErrorERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Short answer

MySQL 8.0 removed the NO_AUTO_CREATE_USER SQL mode, so any place that still lists it now fails. Find the source: a my.cnf or my.ini line, a SET sql_mode line inside a dump or import, or a GRANT that tries to create the account. Delete the mode from the list, and create users with CREATE USER first.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the SQL mode the running server uses now. It must not contain NO_AUTO_CREATE_USER. Then search the config files and the dump for the old word.

    mysql -e "SELECT @@GLOBAL.sql_mode\G"
    grep -rn "NO_AUTO_CREATE_USER" /etc/mysql /etc/my.cnf* dump.sql
  2. 2Match what you see, then jump to its fix

    1. 1The error appears while importing a .sql file made on MySQL 5.7 or earlier, usually near the top of the file or in a Workbench import log.A dump or import script sets the old modeFix
    2. 2mysqld will not start after an upgrade to 8.0, and the server error log mentions sql_mode.my.cnf or my.ini still lists the modeFix
    3. 3A GRANT ... IDENTIFIED BY statement fails on 8.0 even though it worked on 5.7.An old script uses GRANT to create the accountFix

What does the error look like?

  • ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
  • Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Cause 1 of 3

A dump or import script sets the old mode

1Check

Look for a SET sql_mode line that lists NO_AUTO_CREATE_USER. MySQL 8.0 no longer has this mode, so a file that sets it cannot run.

grep -n -i "sql_mode" dump.sql | head

2Fix

Remove NO_AUTO_CREATE_USER from that list (keep the other modes), or remove the whole line, then import again.

sed -i 's/NO_AUTO_CREATE_USER,\?//g' dump.sql
mysql < dump.sql

3Verify

The import runs past the top of the file without error 1231.

mysql -e "SELECT @@SESSION.sql_mode\G"

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

my.cnf or my.ini still lists the mode

1Check

The MySQL 8.0 notes say an error is written to the log when this value in the option file stops mysqld from starting. Find the line.

grep -rn "sql.mode" /etc/mysql /etc/my.cnf /etc/my.cnf.d 2>/dev/null
sudo tail -n 30 /var/log/mysql/error.log

2Fix

Edit the sql-mode line in the [mysqld] group so that it lists only modes that still exist, then restart the server.

[mysqld]
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3Verify

The service starts and reports the new mode.

sudo systemctl restart mysql
mysql -e "SELECT @@GLOBAL.sql_mode\G"

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

An old script uses GRANT to create the account

1Check

MySQL 8.0 removed creating users with GRANT. The account has to exist before you grant to it.

grep -rn -i "grant .* identified by" scripts/

2Fix

Split the statement: CREATE USER with the password first, then GRANT the privileges.

CREATE USER IF NOT EXISTS 'app'@'localhost' IDENTIFIED BY 'change-me';
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app'@'localhost';

3Verify

SHOW GRANTS lists the privileges for the new account.

SHOW GRANTS FOR 'app'@'localhost';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Dump with a current mysqldump or MySQL Shell when you move to 8.0, instead of re-using files from the old server.
  • Keep sql-mode in one option file and read it again after each major upgrade.
  • Use CREATE USER and GRANT as two statements in every script.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.