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

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.

This question is related to mysql xampp localhost mariadb mariasql

The answer is


->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,

  1. Go to C:\Program Files\MariaDB XX.X\data
  2. Open the my.ini file.
  3. *On the line with "sql_mode", modify the value to turn strict mode ON/OFF.
  4. Save the file
  5. **Restart the MySQL service
  6. 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,

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

on server console:

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

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.


Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with xampp tag:

Xampp localhost/dashboard phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) Is there way to use two PHP versions in XAMPP? Where to find htdocs in XAMPP Mac phpMyAdmin access denied for user 'root'@'localhost' (using password: NO) How to downgrade php from 7.1.1 to 5.6 in xampp 7.1.1? How to turn on/off MySQL strict mode in localhost (xampp)? mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it How to install mcrypt extension in xampp Fatal error: Uncaught Error: Call to undefined function mysql_connect() how can I enable PHP Extension intl? MySQL error - #1932 - Table 'phpmyadmin.pma user config' doesn't exist in engine XAMPP keeps showing Dashboard/Welcome Page instead of the Configuration Page How to setup FTP on xampp Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) XAMPP - Apache could not start - Attempting to start Apache service PHP - SSL certificate error: unable to get local issuer certificate httpd-xampp.conf: How to allow access to an external IP besides localhost? XAMPP: Couldn't start Apache (Windows 10) how to create virtual host on XAMPP How to work on UAC when installing XAMPP XAMPP installation on Win 8.1 with UAC Warning How to downgrade php from 5.5 to 5.3 error running apache after xampp install XAMPP Port 80 in use by "Unable to open process" with PID 4 PHP: maximum execution time when importing .SQL data file XAMPP MySQL password setting (Can not enter in PHPMYADMIN) XAMPP Start automatically on Windows 7 startup PhpMyAdmin not working on localhost Xampp MySQL not starting - "Attempting to start MySQL service..." MySQL Database won't start in XAMPP Manager-osx How to stop VMware port error of 443 on XAMPP Control Panel v3.2.1 New xampp security concept: Access Forbidden Error 403 - Windows 7 - phpMyAdmin How to start Apache and MySQL automatically when Windows 8 comes up XAMPP - Port 80 in use by "Unable to open process" with PID 4! 12 XAMPP Object not found error how to configuring a xampp web server for different root directory XAMPP, Apache - Error: Apache shutdown unexpectedly xampp MySQL does not start XAMPP - MySQL shutdown unexpectedly Apache is not running from XAMPP Control Panel ( Error: Apache shutdown unexpectedly. This may be due to a blocked port) Port 443 in use by "Unable to open process" with PID 4 How do I use MySQL through XAMPP? Xampp Access Forbidden php Localhost : 404 not found how to get login option for phpmyadmin in xampp Apache shutdown unexpectedly How to Set user name and Password of phpmyadmin How do I test a website using XAMPP? How to get back Lost phpMyAdmin Password, XAMPP

Questions with localhost tag:

Xampp localhost/dashboard Set cookies for cross origin requests Invalid Host Header when ngrok tries to connect to React dev server How to turn on/off MySQL strict mode in localhost (xampp)? What is IPV6 for localhost and 0.0.0.0? How do I kill the process currently using a port on localhost in Windows? How to run html file on localhost? Can't access 127.0.0.1 Server http:/localhost:8080 requires a user name and a password. The server says: XDB How to solve ERR_CONNECTION_REFUSED when trying to connect to localhost running IISExpress - Error 502 (Cannot debug from Visual Studio)? ngrok command not found Laravel 5 error SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) http://localhost/phpMyAdmin/ unable to connect Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES) Can't connect to MySQL server on '127.0.0.1' (10061) (2003) How to enable local network users to access my WAMP sites? How to send email from localhost WAMP Server to send email Gmail Hotmail or so forth? Accessing a local website from another computer inside the local network in IIS 7 http://localhost:8080/ Access Error: 404 -- Not Found Cannot locate document: / create a trusted self-signed SSL cert for localhost (for use with Express/Node) How to connect to mysql with laravel? mysqldump Error 1045 Access denied despite correct passwords etc HTTP Error 503. The service is unavailable. App pool stops on accessing website WAMP server, localhost is not working How do I use MySQL through XAMPP? Localhost : 404 not found How to change the URL from "localhost" to something else, on a local system using wampserver? Viewing localhost website from mobile device .htaccess not working on localhost with XAMPP #1045 - Access denied for user 'root'@'localhost' (using password: YES) How do I test a website using XAMPP? What is the right way to write my script 'src' url for a local development environment? Object not found! The requested URL was not found on this server. localhost IIS - can't access page by ip address instead of localhost Connect Device to Mac localhost Server? How to set xampp open localhost:8080 instead of just localhost correct configuration for nginx to localhost? Apache VirtualHost and localhost OAuth: how to test with local URLs? How do I connect to this localhost from another computer on the same network? How to change xampp localhost to another folder ( outside xampp folder)? Origin null is not allowed by Access-Control-Allow-Origin WAMP/XAMPP is responding very slow over localhost WAMP Server doesn't load localhost What is the difference between 127.0.0.1 and localhost Localhost not working in chrome and firefox Accessing localhost:port from Android emulator How do you use https / SSL on localhost? Wireshark localhost traffic capture How to connect to my http://localhost web server from Android Emulator

Questions with mariadb tag:

How to turn on/off MySQL strict mode in localhost (xampp)? Completely remove MariaDB or MySQL from CentOS 7 or RHEL 7 Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu Database corruption with MariaDB : Table doesn't exist in engine phpMyAdmin allow remote users ERROR 1452: Cannot add or update a child row: a foreign key constraint fails What's the default password of mariadb on fedora? Cast int to varchar MySQL: Grant **all** privileges on database MySQL - SELECT * INTO OUTFILE LOCAL ?

Questions with mariasql tag:

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