'MySQL: Setting sql_mode permanently

Via the MySQL command line client, I am trying to set the global mysql_mode:

SET GLOBAL sql_mode = TRADITIONAL;

This works for the current session, but after I restart the server, the sql_mode goes back to its default: '', an empty string.

How can I permanently set sql_mode to TRADITIONAL?

If relevant, the MySQL is part of the WAMP package.

Thank you.

mysqlsql-mode


Solution 1:[1]

MySQL sql_mode "TRADITIONAL", a.k.a. "strict mode", is defined by the MySQL docs as:

“give an error instead of a warning” when inserting an incorrect value into a column.

Here's how to ensure that your sql_mode is set to "TRADITIONAL".

First, check your current setting:

mysql
mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

This returned blank, the default, that's bad: your sql_mode is not set to "TRADITIONAL".

So edit the configuration file:

sudo vim /etc/mysql/my.cnf

Add this line in the section labelled [mysqld]: sql_mode="TRADITIONAL" (as fancyPants pointed out)

Then restart the server:

sudo service mysql restart

Then check again:

mysql
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Success! You are golden now.

Solution 2:[2]

For mysql8 on windows:

# Set the SQL mode to strict
sql-mode="NO_ENGINE_SUBSTITUTION"

It's a dash, not an underscore: sql_mode / sql-mode

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Smith

Related Questions