How to turn on/off MySQL strict mode in localhost (xampp)?

Question

I want to know how to check whether MySQL strict mode is on or off in localhost(xampp).

If on then for what modes and how to off.

If off then how to on.

I already followed http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-full and https://mariadb.com/kb/en/mariadb/sql_mode/ and other related sites too. But I didn't get an exact answer for my question.

if you want to check strict mode then use: SELECT @@sql_mode — Milan Soni, Nov 30 '16 at 07:35
check this like [https://shopplugin.net/kb/mysql-strict-mode-issues/] to turn it on or off. — Milan Soni, Nov 30 '16 at 07:44
@Milan Soni: Tx for your response. The link u shared gives 'Sorry, that page doesn’t exist!' — Ipsita Rout, Nov 30 '16 at 08:05
sorry it was giving you "]" at last in url that's why. Please remove last character and check again. — Milan Soni, Nov 30 '16 at 09:02
@Milan Soni: Thanks for your timely response.But I have a concern that if we enable in the corresponding file and disable through sql query in the run time, then it obeys what it has done through run time. Hence I think changing the setting through sql should be followed. Again it also works for all the server. We don't need to worry about the file name and location for different server type. What do you say? — Ipsita Rout, Nov 30 '16 at 13:23

score 140 · Accepted Answer · edited Aug 24 '18 at 12:24

140

->STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.

->To check whether strict mode is enabled or not run the below sql:

SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave

+--------------+------------------------------------------+ 
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.

->To disable strict mode run the below sql:

set global sql_mode='';

[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]

->To again enable strict mode run the below sql:

set global sql_mode='STRICT_TRANS_TABLES';

edited Aug 24 '18 at 12:24

Damjan Pavlica

31,277
10
71
76

answered Nov 30 '16 at 13:19

Ipsita Rout

4,945
4
36
40

3

If you restart mysql server then settings change back again, is there a way to change this permanently – DragonFire May 01 '17 at 10:37
1

You can do it in the server config file. Example how this would look like: [mysqld] sql_mode="STRICT_TRANS_TABLES" File location varies depending on your operating system, more on where to find it here: https://dev.mysql.com/doc/refman/5.7/en/option-files.html – solo Dec 07 '17 at 11:34
9

My command `set global sql_mode=''` does not work (my version is 5.7), but `set sql_mode=''` does. Any explanation ? – thelonglqd Mar 01 '18 at 10:43
@thelonglqd you may not have permissions? – Raja Anbazhagan Mar 24 '18 at 16:31
To disable some mode - `SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));` – Zon Jan 13 '21 at 08:31
downvote, because it's a temporary solution – Lajos Mészáros Dec 19 '21 at 13:22

score 70 · Answer 2 · answered May 01 '17 at 11:13

70

To Change it permanently in ubuntu do the following

in the ubuntu command line

sudo nano /etc/mysql/my.cnf

Then add the following

[mysqld]
sql_mode=

answered May 01 '17 at 11:13

DragonFire

3,722
2
38
51

4

On 16.04 in /etc/mysql/mysql.conf.d/mysqld.cnf – Aleksandar Pavić Nov 14 '18 at 07:20
`printf "[mysqld]\nsql_mode=\n" | sudo tee /etc/mysql/conf.d/sql_mode.cnf` – kenorb Sep 18 '22 at 21:05

score 20 · Answer 3 · answered Oct 27 '17 at 00:16

20

First, check whether the strict mode is enabled or not in mysql using:

     SHOW VARIABLES LIKE 'sql_mode';

If you want to disable it:

     SET sql_mode = '';

or any other mode can be set except the following. To enable strict mode:

     SET sql_mode = 'STRICT_TRANS_TABLES';

You can check the result from the first mysql query.

answered Oct 27 '17 at 00:16

Kasumi Gunasekara

630
8
10

Can you add it to a specific database or globally? – Ajith Jun 18 '20 at 09:39
only for temporary use, after restart xampp it is reset to default – Rohit Jul 16 '22 at 13:43

Toundrake · Answer 4 · 2019-05-17T14:54:36.077

9

Check the value with

SELECT @@GLOBAL.sql_mode;

then clear the @@global.sql_mode by using this command:

SET @@GLOBAL.sql_mode=''

edited May 17 '19 at 14:54

answered Jan 22 '19 at 16:02

Toundrake

125
1
4

7

You don't remove `STRICT_TRANS_TABLES` but you clear sql_mode completely. That's a serious difference. – Mike Doe May 16 '19 at 11:52

score 5 · Answer 5 · answered Jan 08 '19 at 19:14

To change it permanently in Windows (10), edit the my.ini file. To find the my.ini file, look at the path in the Windows server. E.g. for my MySQL 5.7 instance, the service is MYSQL57, and in this service's properties the Path to executable is:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

I.e. edit the my.ini file in C:\ProgramData\MySQL\MySQL Server 5.7\. Note that C:\ProgramData\ is a hidden folder in Windows (10). My file has the following lines of interest:

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

Remove STRICT_TRANS_TABLES, from this sql-mode line, save the file and restart the MYSQL57 service. Verify the result by executing SHOW VARIABLES LIKE 'sql_mode'; in a (new) MySQL Command Line Client window.

(I found the other answers and documents on the web useful, but none of them seem to tell you where to find the my.ini file in Windows.)

score 4 · Answer 6 · edited Jun 14 '19 at 05:49

4

In my case, I need to add:

sql_mode="STRICT_TRANS_TABLES"

under [mysqld] in the file my.ini located in C:\xampp\mysql\bin.

edited Jun 14 '19 at 05:49

Cà phê đen

1,883
2
21
20

answered Jun 14 '19 at 03:38

Khoa trần đăng

51
3

score 3 · Answer 7 · answered Oct 07 '20 at 11:19

I want to know how to check whether MySQL strict mode is on or off in localhost(xampp).

SHOW VARIABLES LIKE 'sql_mode';

If result has "STRICT_TRANS_TABLES", then it's ON. Otherwise, it's OFF.

If on then for what modes and how to off.

If off then how to on.

For Windows,

Go to C:\Program Files\MariaDB XX.X\data
Open the my.ini file.
*On the line with "sql_mode", modify the value to turn strict mode ON/OFF.
Save the file
**Restart the MySQL service
Run SHOW VARIABLES LIKE 'sql_mode' again to see if it worked;

*3.a. To turn it ON, add STRICT_TRANS_TABLES on that line like this: sql_mode=STRICT_TRANS_TABLES. *If there are other values already, add a comma after this then join with the rest of the value.

*3.b. To turn it OFF, simply remove STRICT_TRANS_TABLES from value. *Remove the additional comma too if there is one.

**6. To restart the MySQL service on your computer,

Open the Run command window (press WINDOWS + R button).
Type services.msc
Click OK
Right click on the Name MySQL
Click Restart

Nicely written! And practical too. – gadi Apr 21 '22 at 09:07 — gadi, Apr 21 '22 at 09:07

score 2 · Answer 8 · answered Oct 05 '18 at 11:54

2

You can check the local and global value of it with:

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

answered Oct 05 '18 at 11:54

Eric Korolev

713
8
13

score 1 · Answer 9 · answered Mar 12 '20 at 04:40

on Debian 10 I start mysql from ./opt/lampp/xampp start

I do strace ./opt/lampp/sbin/mysqld and see that my.cnf is there:

stat("/opt/lampp/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=5050, ...}) = 0
openat(AT_FDCWD, "/opt/lampp/etc/my.cnf", O_RDONLY|O_CLOEXEC) = 3

hence, I add sql_mode config to /opt/lampp/etc/my.cnf instead of /etc/mysql/my.cnf

score 1 · Answer 10 · answered Sep 10 '20 at 02:15

1

on server console:

$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

answered Sep 10 '20 at 02:15

Leonardo Filipe

1,534
15
9

score 0 · Answer 11 · answered Jun 22 '21 at 05:08

Today I was trying to set the sql_mode=TRADITIONAL permanently but all efforts were in vain not because there are wrong answers but due to the way xampp configured the mysqld startup script. Let me explain in detail.

Of course you all try our best before coming to SO, so do I. I followed the comments in A:\xampp\mysql\bin\my.ini (given below):

# You can copy this file to
# A:/xampp/mysql/bin/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is A:/xampp/mysql/data) or
# ~/.my.cnf to set user-specific options.

So I tried A:/xampp/mysql/bin/my.cnf, A:/xampp/mysql/data/my.cnf but it wasn't even reading those files. Hours wasted in creating .cnf files in above locations. Worst part was it wasn't even working if I edit those my.ini files (i.e A:/xampp/mysql/bin/my.ini and A:/xampp/mysql/data/my.ini)

Then I checked all the folders to know how that control panel works and found thta xampp uses the mysql_start.bat script to start the msql deamon. Here is the bat file contents:

@echo off
cd /D %~dp0
echo Diese Eingabeforderung nicht waehrend des Running beenden
echo Please dont close Window while MySQL is running
echo MySQL is trying to start
echo Please wait  ...
echo MySQL is starting with mysql\bin\my.ini (console)

mysql\bin\mysqld --defaults-file=mysql\bin\my.ini --standalone

if errorlevel 1 goto error
goto finish

:error
echo.
echo MySQL konnte nicht gestartet werden
echo MySQL could not be started
pause

:finish

Here we can clearly see that it is explicitly using the argument --defaults-file to tell MySQL daemon from where to read the files. Now I hope you have plenty of ideas to fix this.

Note: I've already added A:/xampp/mysql/bin to my PATH.

Now we have several options as I've mentioned below:

Add the exact path to the --defaults-file (i.e. --defaults-file=mysql\bin\my.cnf)
You can just ommit the flag and let mysqld read from default locations (can see those using mysql --help) Now you've 2 options:
- either edit those default my.ini files or
- follow the comments to create my.cnf files according to your installation directory.

I just deleted that --defaults-file flag and let it run with MySQL's default configuration instead of xampp's. By the was I also have to change A:\xampp\mysql\data\my.ini from this:

[mysqld]
datadir=C:/xampp/mysql/data
[client]

to

[mysqld]
datadir=A:\xampp\mysql\data
[client]

to update the data directory. After that I just created a my.conf file in A:\xampp\mysql\data (data dir). with sql_mode option in it. It also worked with my.cnf in the A:\xampp\mysql\bin.

I have attached some screenshots for better understanding:

Updated data dir in A:\xampp\mysql\data\my.ini:

(option 1) Add my.cnf in A:\xampp\mysql\data:

(option 2) Add my.cnf in A:\xampp\mysql\bin:

You may found another solution too. Hope you are able to fix whatever issue you have regarding those config files.

score -2 · Answer 12 · edited Nov 25 '20 at 06:32

For ubuntu :

Once you are connected to your VPS via SSH, please try connecting to your mysql with "root"

user: mysql -u root -p

Enter "root" user password and you will be in the mysql environment (mysql>), then simply check what is sql_mode, with the following command:

SHOW VARIABLES LIKE 'sql_mode';

Basically, you will see the table as your result, if the table has a value of STRICT_TRANS_TABLES, it means that this option is enabled, so you need to remove the value from this table with the following command:

set global sql_mode='';

This will set your table's value to empty and disable this setting. Like this:

 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | sql_mode      |       |
 +---------------+-------+

Please make sure to perform these commands within the MySQL environment and not simply via SSH. I think this moment was missed in the article provided below and the author assumes that the reader understands it intuitively.

How to turn on/off MySQL strict mode in localhost (xampp)?

12 Answers12

Linked

Related