Error
Error Code:
1621
MySQL Error 1621: Modifying Read-Only Variable
Description
This error indicates an attempt to change the value of a system variable that is designated as read-only and cannot be altered at runtime or during server startup. It typically occurs when a user tries to use a `SET` statement to modify such a variable, or when a configuration option implies modification of an immutable setting.
Error Message
%s variable '%s' is read-only. Use SET %s to assign the value
Known Causes
3 known causesAttempting to Modify Immutable Variable
The system variable you are trying to change is fundamentally designed by MySQL to be constant or unmodifiable after server startup.
Internally Controlled Variable
Some variables are read-only because their values are derived from or controlled by other system settings or internal MySQL processes, preventing direct user modification.
Typo or Misidentified Variable
A common mistake is a typo in the variable name, leading to an attempt to modify an unintended, existing read-only variable.
Solutions
3 solutions available1. Identify and Avoid Modifying Read-Only Variables easy
Understand which variables are read-only and avoid attempting to change them directly.
1
Understand that some MySQL system variables are intentionally set to be read-only to maintain system stability and integrity. These variables are typically configured at server startup or are managed by the server itself.
2
When you encounter Error 1621, carefully review the variable name mentioned in the error message. This variable is the one you're attempting to modify and it's read-only.
3
Consult the MySQL documentation for the specific version you are using to confirm if a variable is read-only and to understand its purpose. The error message itself often suggests the correct way to interact with the variable, if at all.
SHOW VARIABLES LIKE 'variable_name';
-- Example: SHOW VARIABLES LIKE 'read_only';4
If the variable is truly read-only and you believe it needs to be changed for a specific operational reason, investigate if there's a corresponding configuration parameter in your `my.cnf` (or `my.ini`) file that can be set at server startup. Restart the MySQL server after making such changes.
2. Check Server Configuration for `read_only` or `super_read_only` Mode medium
Verify if the server is in a global read-only mode and disable it if necessary.
1
Connect to your MySQL server with administrative privileges.
2
Check the current status of the `read_only` and `super_read_only` system variables. These variables can put the entire server into a read-only state, preventing most write operations.
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';3
If `read_only` or `super_read_only` is set to `ON`, and you need to perform write operations, you can temporarily disable this mode using the `SET GLOBAL` statement. Note: This change is not persistent across server restarts.
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;4
To make this change permanent, you need to edit your MySQL configuration file (`my.cnf` or `my.ini`). Locate the `[mysqld]` section and ensure `read_only = OFF` and `super_read_only = OFF` are present or commented out. After modifying the configuration file, restart the MySQL server.
3. Use `SET PERSIST` for Supported Variables medium
Utilize `SET PERSIST` for dynamic variables that support persistent changes across restarts.
1
Identify the specific read-only variable that is causing the error. The error message will explicitly name it.
2
Check if the variable supports dynamic, persistent changes using `SET PERSIST`. This command allows you to change a system variable's value, and the change will be written to the `mysqld-auto.cnf` file, making it persist after a server restart. Not all variables support `SET PERSIST`.
3
If the variable supports `SET PERSIST`, use it to set the desired value. For example, if you were trying to set a variable named `some_variable` and it was read-only with `SET GLOBAL`, but supported `SET PERSIST`:
SET PERSIST some_variable = 'new_value';4
After executing `SET PERSIST`, the variable's value will be updated dynamically and also saved for future restarts. You can verify the change with `SHOW VARIABLES LIKE 'some_variable';`.
SHOW VARIABLES LIKE 'some_variable';