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

Add this to your my.cnf file (or my.ini if you're using windows):

sql_mode="TRADITIONAL"

and restart the server

Answer 2

If you are using Digital Ocean, the mysql cnf files are emulated and don't actually really interact with the database. In this case, you set your sql-mode with (for a blank sql mode)...

doctl databases sql-mode set DB-UUID ""

If you don't know your DB UUID, check it out with:

GET DB UUID with: doctl databases list

Source: digitalocean.com: doctl databases sql-mode set

Answer 3

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

Answer 4

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.