How to turn on/off MySQL strict mode in localhost (xampp)?
Solution 1
->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';
Solution 2
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=
Solution 3
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.
Solution 4
Check the value with
SELECT @@GLOBAL.sql_mode;
then clear the @@global.sql_mode by using this command:
SET @@GLOBAL.sql_mode=''
Solution 5
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.)
Related videos on Youtube
Ipsita Rout
Updated on July 16, 2022Comments
-
Ipsita Rout almost 2 years
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.
-
Milan Soni over 7 yearsif you want to check strict mode then use: SELECT @@sql_mode
-
Milan Soni over 7 yearsthis query return your sql_mode and returns blank if it off
-
Milan Soni over 7 yearscheck this like [shopplugin.net/kb/mysql-strict-mode-issues/] to turn it on or off.
-
Ipsita Rout over 7 years@Milan Soni: Tx for your response. The link u shared gives 'Sorry, that page doesn’t exist!'
-
Milan Soni over 7 yearssorry it was giving you "]" at last in url that's why. Please remove last character and check again.
-
Ipsita Rout over 7 years@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?
-
-
DragonFire about 7 yearsIf you restart mysql server then settings change back again, is there a way to change this permanently
-
solo over 6 yearsYou 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: dev.mysql.com/doc/refman/5.7/en/option-files.html
-
thelonglqd about 6 yearsMy command
set global sql_mode=''
does not work (my version is 5.7), butset sql_mode=''
does. Any explanation ? -
Raja Anbazhagan about 6 years@thelonglqd you may not have permissions?
-
Aleksandar Pavić over 5 yearsOn 16.04 in /etc/mysql/mysql.conf.d/mysqld.cnf
-
emix almost 5 yearsYou don't remove
STRICT_TRANS_TABLES
but you clear sql_mode completely. That's a serious difference. -
Ajith almost 4 yearsCan you add it to a specific database or globally?
-
Zon over 3 yearsTo disable some mode -
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
-
Lajos Mészáros over 2 yearsdownvote, because it's a temporary solution
-
gadi about 2 yearsNicely written! And practical too.