How to Fix MySQL Error 1231: Variable Can't Be Set to This Value

ERROR 1231 (42000): Variable 'variable_name' can't be set to the value of 'value' is raised when a SET statement attempts to assign a value that MySQL does not accept for the target system variable — because the value is the wrong type, outside the allowed range, or not a recognized option string. The error symbol is ER_WRONG_VALUE_FOR_VAR.

Impact

This error is returned immediately and the SET statement has no effect. The system variable retains its previous value. If the failing SET appears in an application startup sequence or in a stored procedure that initializes session parameters, the rest of that initialization may proceed with incorrect settings or may abort entirely, depending on how error handling is implemented.

Developers most often encounter this error when migrating between MySQL versions (where valid option sets change), when passing a user-supplied or config-file value to a SET call, or when running scripts that were written for a different MySQL edition (e.g., a MariaDB script applied to MySQL).

Common Causes

Invalid string option for an enumeration variable. Variables like sql_mode, transaction_isolation, binlog_format, and innodb_flush_method accept only specific string values. Passing a misspelled or unsupported token raises 1231.
Numeric value out of the allowed range. Variables such as max_connections, innodb_buffer_pool_size, and sort_buffer_size have minimum and maximum bounds. A value below the minimum or above the maximum is rejected.
Wrong type entirely. Assigning a string to a purely numeric variable (or vice versa) triggers this error.
Removed or renamed option in a newer MySQL version. For example, the sql_mode value NO_AUTO_CREATE_USER was removed in MySQL 8.0. Scripts that set it on MySQL 8.0+ will fail with error 1231.
Read-only or session-scope mismatch. Some variables are global-only or read-only at runtime. Attempting SET SESSION on a global-only variable, or trying to change a variable that can only be set at startup, raises a related but sometimes identical message.
Passing NULL to a variable that does not accept it. Most system variables do not allow NULL as a value.

Troubleshooting and Resolution Steps

Identify the exact variable and value in the error message. The message names both. For example:
```
ERROR 1231 (42000): Variable 'transaction_isolation' can't be set to the value of 'REPEATABLE READ'
```
The correct value uses hyphens, not spaces: REPEATABLE-READ.
Check the allowed values for the variable. Query the information_schema.GLOBAL_VARIABLES view or the Performance Schema, or consult SHOW VARIABLES:
```
SHOW VARIABLES LIKE 'binlog_format';
```
For variables with an enumerated set, the MySQL manual lists every accepted token. You can also inspect the variable's metadata:
```
SELECT VARIABLE_NAME, VARIABLE_TYPE, VARIABLE_COMMENT
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = 'binlog_format';
```

Fix sql_mode values when upgrading to MySQL 8.0. Remove deprecated tokens before applying the SET:

-- Bad on MySQL 8.0+
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER';

-- Good on MySQL 8.0+
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

To inspect the current mode and build a corrected value:

SELECT @@GLOBAL.sql_mode;

Correct numeric range violations. Check the documented minimum/maximum for the variable, then adjust your value. For example, innodb_buffer_pool_size must be at least 5 MB (5242880 bytes) on most builds:
```
-- Will fail if value is below the internal minimum
SET GLOBAL innodb_buffer_pool_size = 1024;

-- Correct
SET GLOBAL innodb_buffer_pool_size = 134217728; -- 128 MB
```
Verify scope (SESSION vs GLOBAL). Some variables are global-only. Use SHOW VARIABLES or check performance_schema.variables_info.VARIABLE_SCOPE:
```
SELECT VARIABLE_NAME, VARIABLE_SCOPE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';
```
If the scope is GLOBAL, a SET SESSION attempt will fail.
For startup-only variables, remove the SET statement from your application code entirely. These variables must be set in my.cnf / my.ini before the server starts and cannot be changed at runtime:
```
[mysqld]
innodb_data_file_path = ibdata1:12M:autoextend
```

Additional Information

The SQLSTATE code is 42000 (syntax error or access rule violation), shared with several other MySQL errors related to invalid statements.
Error 1193 (ER_UNKNOWN_SYSTEM_VARIABLE) is raised when the variable name itself is not recognized. Error 1231 fires only when the name is valid but the value is not.
In MySQL 8.0, many sql_mode flags that existed in 5.6/5.7 were removed (NO_AUTO_CREATE_USER, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS). Migration scripts must strip these before executing against 8.0.
ORMs and connection pool libraries that set session variables on connect (e.g., SET NAMES, SET SESSION transaction_isolation) can surface error 1231 as a connection initialization failure, making it appear as a connectivity problem rather than a configuration error. Check driver logs for the underlying SQL statement.
MariaDB shares many variable names with MySQL but sometimes accepts different value sets. Scripts written for MariaDB may produce error 1231 on MySQL and vice versa.

Frequently Asked Questions

Why does SET sql_mode = '...' fail after upgrading to MySQL 8.0? MySQL 8.0 removed several sql_mode flags that were present in 5.6 and 5.7. Any mode string that contains a removed flag (NO_AUTO_CREATE_USER is the most common) is rejected with error 1231. Remove the unsupported tokens from the mode string before setting it.

Can I silence or ignore error 1231 with strict mode settings? No. Error 1231 is not controlled by sql_mode or any permissive setting. The assignment simply fails and must be corrected.

My ORM sets transaction_isolation on every connection and now it's broken. What changed? Before MySQL 8.0.3, the variable was named tx_isolation. In 8.0.3+ the old name is an alias, but the accepted value format also changed — values must use hyphens (REPEATABLE-READ, READ-COMMITTED) not spaces or underscores. Update your driver or ORM configuration accordingly.

How do I find which value is actually in effect after a failed SET? Run SELECT @@variable_name or SHOW VARIABLES LIKE 'variable_name'. Because the failed SET had no effect, the variable still holds its previous value.