How to disable global system variables to reset after restarting MySQL?

Question

I wanted to disable the ONLY_FULL_GROUP_BY value of sql-mode permanently even restart the MySQL server. following things I have tried to do but which are not working. that set to the default value when restarting the MySQL.

Answer 1

Persisted settings are permanent. They apply across server restarts.

set PERSIST sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

have you tried this?

Answer 2

Using SET GLOBAL to change a variable only changes it for the current MySQL Server process. If the server restarts, changes are reverted. Global variable settings are read from the MySQL Server options file upon startup.

In MySQL 8.0, they added a SET PERSIST command so you can change a global variable and add the setting to the options file at once. Read https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html

If you use an older version of MySQL, you'll have to edit the options file. Editing the options file alone does not change the option in the running MySQL Server instance. You would need to restart the MySQL Server to get it to re-read the options file.

My usual habit is to do both — edit the options file and then also run SET GLOBAL to change it to the same value. That ensures it will be the same after a restart, but it allows me to make the change immediately without restarting.

That said, I recommend you should not disable ONLY_FULL_GROUP_BY. That mode protects you from writing invalid SQL queries.

Answer 3

You can use "SET PERSIST" since MySQL 8.0 to persist "sql_mode" global system variable without ONLY_FULL_GROUP_BY to "mysqld-auto.cnf" as shown below so that "sql_mode" global system variable is not reset after restarting MySQL:

SET PERSIST sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

In addition, with the command below, you can check all persisted system variables in "mysqld-auto.cnf":

mysql> SELECT * FROM performance_schema.persisted_variables;
+-----------------+----------------------------------------------------------------------------------------------------+
| VARIABLE_NAME   | VARIABLE_VALUE                                                                                     |
+-----------------+----------------------------------------------------------------------------------------------------+
| sql_mode        | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| max_connections | 500                                                                                                |
+-----------------+----------------------------------------------------------------------------------------------------+

And, with the command below, you can remove all persisted system variables from "mysqld-auto.cnf":

RESET PERSIST;

And, with the command below, you can remove only "sql_mode" persisted system variable from "mysqld-auto.cnf":

RESET PERSIST sql_mode;

And, with the command below, you can reset "sql_mode" global system variable:

SET @@GLOBAL.sql_mode = DEFAULT;

Now, "sql_mode" global system variable has the default values with ONLY_FULL_GROUP_BY as shown below:

mysql> SELECT @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+