MySQL 8 refuses sql_mode NO_AUTO_CREATE_USER from a dump or config
ERROR 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.
Fix it fast
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.sql2Match what you see, then jump to its fix
- 1
The 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
mysqld 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
A GRANT ... IDENTIFIED BY statement fails on 8.0 even though it worked on 5.7.An old script uses GRANT to create the accountFix
- 1
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 | head2Fix
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.sql3Verify
The import runs past the top of the file without error 1231.
mysql -e "SELECT @@SESSION.sql_mode\G"Confirmed by dev.mysql.com
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.log2Fix
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix