MySQL: Setting sql_mode permanently

Question

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.

Answer 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.