(opens in a new tab)

MySQL Error 1231: Variable Can't Be Set (Fix)

October 27, 2026 by Chat2DB Team

MySQL raises error 1231 when you assign a value that a system variable does not accept:

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

The message always names the variable and the rejected value, and that value is the best clue. In practice almost every 1231 falls into one of three groups:

1. sql_mode contains a mode that MySQL 8.0 removed, or a formatting mistake such as a space after a comma.
2. The value is NULL, which usually means you ran the end of a dump file without its beginning.
3. The value is spelled differently from what the variable expects, such as 'READ COMMITTED' instead of 'READ-COMMITTED'.

This guide covers each group with output from MySQL 8.4, plus the startup failure you get when the same mistake is in my.cnf.

Group 1: sql_mode After Upgrading to MySQL 8.0 or 8.4

MySQL 8.0 removed several modes that were valid in 5.7: NO_AUTO_CREATE_USER, the compatibility modes DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE and POSTGRESQL, and NO_KEY_OPTIONS, NO_TABLE_OPTIONS and NO_FIELD_OPTIONS. The 5.7 default sql_mode included NO_AUTO_CREATE_USER, so many applications, ORMs and configuration files still set it explicitly:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

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

The whole statement fails, so none of the other modes in the list are applied either. MySQL reports the first invalid name it finds, which can hide a second one further along the list:

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,MYSQL40';
-- ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'MYSQL40'

The fix is to remove the obsolete names. NO_AUTO_CREATE_USER needs no replacement: in MySQL 8.0, GRANT can no longer create users at all, so the behavior it enforced is now permanent. For the compatibility modes, list the individual modes you actually rely on, typically ANSI_QUOTES or PIPES_AS_CONCAT.

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

Where the Old Mode Comes From

The statement that sets the obsolete mode is often not in your own code. Check these places:

• my.cnf or my.ini copied from the 5.7 server (see the startup section below).
• The application's connection settings. Some frameworks and older JDBC configurations set sql_mode on every new connection, for example with sessionVariables in a Connector/J URL.
• ORM or framework defaults in old versions, which set a strict mode list including NO_AUTO_CREATE_USER when connecting.
• SET statements at the top of migration and seed scripts.

Formatting Mistakes

The value is a comma-separated list with no spaces. A space after a comma becomes part of the next name, and the error message shows it with the leading space:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY';

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

Look closely at the quotes in the message: ' ONLY_FULL_GROUP_BY' starts with a space. Mode names are not case-sensitive, and empty items such as a trailing comma are ignored, but there is no partial matching: ONLY_FULL_GROUP or ANSI_QUOTE are rejected.

The free MySQL sql_mode Checker (opens in a new tab) parses a value with the same rules as MySQL 8, shows which name is rejected and why, and builds a corrected list together with SET PERSIST and my.cnf lines.

The Same Mistake in my.cnf Stops mysqld

In an option file, an invalid sql_mode is not an error you can retry. The server refuses to start. We tested a my.cnf with NO_AUTO_CREATE_USER on MySQL 8.4:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
[ERROR] [MY-010119] [Server] Aborting

A space after a comma has exactly the same effect:

[ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION' to 'sql_mode'.

This is a common reason for an in-place upgrade from 5.7 to 8.0 that "fails to start". Search every option file the server reads (mysqld --verbose --help | grep -A1 "Default options" lists them), fix the line, and start again. The error log line Error while setting value points at the variable and the full value.

Values saved with SET PERSIST live in mysqld-auto.cnf in the data directory, not in my.cnf. If a persisted value ever blocks startup, start mysqld once with --persisted-globals-load=OFF and run RESET PERSIST sql_mode;.

Group 2: "can't be set to the value of 'NULL'"

This variant usually appears when you import a dump file:

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

A mysqldump file saves the session settings at the top and restores them at the end:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
-- ... CREATE TABLE and INSERT statements ...
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

The /*!40101 ... */ comments are executed by MySQL, so these are real statements. If a session runs the closing lines without the opening ones, the user variables were never set and are NULL:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
-- ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL'
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL'
 
SET character_set_client = @saved_cs_client;
-- ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'

This happens when:

• A large dump is split into pieces (with split or by hand), and the pieces are imported in separate sessions.
• A GUI tool runs a dump file statement by statement in different connections, or you run just a selected part of the file.
• Two dump files are concatenated in a way that runs one file's footer in a session that never saw its header.

When the error appears only at the very end of an import, the data has usually been loaded. Only the cleanup statement failed. Check the row counts, then either ignore the error or run the whole file in one session with the mysql client:

mysql -u root -p shop < shop_dump.sql

If you split a file, keep the header lines (the SET @OLD_... statements) at the top of every piece, or remove the matching footer lines from the pieces.

Group 3: Wrong Spelling for the Variable

Enumerated variables accept only their own spellings, and some differ from the SQL syntax people know:

SET SESSION transaction_isolation = 'READ COMMITTED';
-- ERROR 1231 (42000): Variable 'transaction_isolation' can't be set to the value of 'READ COMMITTED'
 
SET SESSION transaction_isolation = 'READ-COMMITTED';   -- OK

The variable uses hyphens (READ-COMMITTED, REPEATABLE-READ), while the statement SET TRANSACTION ISOLATION LEVEL READ COMMITTED uses spaces. Boolean variables accept ON, OFF, 1 and 0, but not every word that looks like a boolean:

SET autocommit = 'yes';
-- ERROR 1231 (42000): Variable 'autocommit' can't be set to the value of 'yes'
 
SET autocommit = 2;
-- ERROR 1231 (42000): Variable 'autocommit' can't be set to the value of '2'
 
SET GLOBAL read_only = 'true';
-- ERROR 1231 (42000): Variable 'read_only' can't be set to the value of 'true'
 
SET SESSION sql_log_bin = 'maybe';
-- ERROR 1231 (42000): Variable 'sql_log_bin' can't be set to the value of 'maybe'

Enum variables need one of their listed values:

SET GLOBAL binlog_format = 'ROWS';
-- ERROR 1231 (42000): Variable 'binlog_format' can't be set to the value of 'ROWS'
 
SET GLOBAL event_scheduler = 'yes';
-- ERROR 1231 (42000): Variable 'event_scheduler' can't be set to the value of 'yes'

Use ROW for the binary log format, and ON or OFF for the event scheduler. To see what a variable accepts, look it up in the server system variables reference or query the current value with SELECT @@GLOBAL.variable_name; and copy its format.

Not Every Bad Value Is Error 1231

Numeric variables usually clamp an out-of-range value and only warn:

SET GLOBAL innodb_flush_log_at_trx_commit = 5;
SHOW WARNINGS;
-- Warning 1292: Truncated incorrect innodb_flush_log_at_trx_commit value: '5'
SELECT @@GLOBAL.innodb_flush_log_at_trx_commit;   -- 2

The statement succeeded and set the variable to its maximum, 2, which is probably not what you wanted. Always read warnings after changing numeric settings. Some variables also have their own error codes, for example an unknown time zone:

SET GLOBAL time_zone = 'Mars/Olympus';
-- ERROR 1298 (HY000): Unknown or incorrect time zone: 'Mars/Olympus'

Quick Reference

When you migrate settings between servers, comparing variables side by side saves guesswork. In Chat2DB (opens in a new tab) you can run SHOW GLOBAL VARIABLES on the old and the new server in two tabs, filter by name, and ask the AI assistant whether a value is still valid on the new version.

FAQ

What is the replacement for NO_AUTO_CREATE_USER in MySQL 8?

There is none, and none is needed. MySQL 8.0 removed the ability of GRANT to create users implicitly, so the protection that NO_AUTO_CREATE_USER provided is always on. Create users with CREATE USER and then grant privileges.

Why does my application fail to connect after the upgrade with error 1231?

The application or its driver probably sets sql_mode when it opens a connection, and the list contains a mode removed in MySQL 8.0. Search the connection URL, the framework configuration and any connection init SQL for NO_AUTO_CREATE_USER and remove it.

Is it safe to ignore error 1231 at the end of a dump import?

Usually yes, if the only failing statements are the SET ... = @OLD_... lines at the end. They restore session settings and do not touch data. Check row counts to confirm that the tables were loaded completely.