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.

Michael Currie · Answer 1 · 2017-12-11T13:45:33.283

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.

how about if i want to turn it off permanently sql-mode="" does not work — DragonFire, May 01 '17 at 11:03
@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. — Michael Currie, May 02 '17 at 09:09

fancyPants · Accepted Answer · 2017-07-26T22:20:32.597

30

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

sql_mode="TRADITIONAL"

and restart the server

edited Jul 26 '17 at 22:20

answered Dec 04 '13 at 11:04

fancyPants

50,732
33
89
96

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. – George Newton Dec 04 '13 at 11:11
1

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. – George Newton Dec 04 '13 at 11:13
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. – dyasta Jul 26 '17 at 21:49
@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. – fancyPants Jul 26 '17 at 22:01
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. – dyasta Jul 26 '17 at 22:05
On debian jessie this was found in /etc/mysql/mysql.conf.d/mysqld.cnf – Xedecimal Oct 15 '18 at 15:25
How can this be done under Win10? See my question [here](https://stackoverflow.com/questions/75766115/mysql-setting-sql-mode-permanently-under-windows-10) – Jörg Mar 19 '23 at 05:01

score 0 · Answer 3 · answered Mar 03 '22 at 10:11

0

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

answered Mar 03 '22 at 10:11

Edmunds22

715
9
10

score 0 · Answer 4 · answered Jun 17 '23 at 15:55

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

MySQL: Setting sql_mode permanently

4 Answers4

Linked