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.
->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';
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=
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.
Check the value with
SELECT @@GLOBAL.sql_mode;
then clear the @@global.sql_mode by using this command:
SET @@GLOBAL.sql_mode=''
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.)
You can check the local and global value of it with:
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
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
.
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
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,
C:\Program Files\MariaDB XX.X\data
my.ini
file.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,
services.msc
OK
MySQL
Restart
on server console:
$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"
For ubuntu :
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.
Source: Stackoverflow.com