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.

Splasher

Answer 1

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

sql_mode="TRADITIONAL"

and restart the server

Penury

I added sql-mode="TRADITIONAL" in my my.ini file, under "socket = /tmp/mysql.sock", and now the MySQL command line client won't start. – Splasher

Nevermind I got it. I added under [client] instead of [wampmysqld]. Thank you for this, I will accept your answer and upvote you by one point in accordance with the stackoverflow friendly exchange policy. – Splasher

I was going to suggest you edit to the now used sql_mode instead of sql-mode, but alas my edit must be 6 chars. Perhaps this did or does apply to some version of MySQl or deployment of it, but not the latest. – Approximation

@bitsum As far as I know it doesn't matter if you write it with a dash or an underscore. And I'm not aware, that this behaviour was changed in any version or that a certain style is dependent on a certain version. – Penury

Well I say this because I used a dash at first, following this article, but when I pulled the latest update, it error'd on me until I replaced that dash with an underscore. So, it may now apply. Of course, this is under Ubuntu and MySql 5.7, so who knows on Windows. – Approximation

On debian jessie this was found in /etc/mysql/mysql.conf.d/mysqld.cnf – Clypeate

How can this be done under Win10? See my question here – Tanhya

Answer 2

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.

Towney

how about if i want to turn it off permanently sql-mode="" does not work – Plaster

@dragonfire it's sql_mode not sql-mode. I just tried it now and setting sql_mode = "" and restarting the server works to clear all the settings. – Towney

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

Porpoise

Answer 4

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

Earshot