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

by

->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";

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. MySQL error 1064 syntax but everything seems fine
  3. How can I change MariaDB to MySQL in XAMPP?
  4. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0
  5. Trouble with UTF-8 characters; what I see is not what I stored
  6. How to solve “Error: MySQL shutdown unexpectedly”?
  7. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  8. MySQL: Grant **all** privileges on database
  9. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  10. xampp MySQL does not start
  11. How can I access the MySQL command line with XAMPP for Windows?
  12. Can’t connect to MySQL server on ‘localhost’ (10061)
  13. What is the location of mysql client “.my.cnf” in XAMPP for Windows?
  14. MySQL/MariaDB – order by inside subquery
  15. Cast int to varchar
  16. Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
  17. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  18. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  19. How repair corrupt xampp ‘mysql.user’ table?
  20. Higher cardinality column first in an index when involving a range?
  21. ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  22. #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
  23. MySQL – SELECT * INTO OUTFILE LOCAL ?
  24. Database corruption with MariaDB : Table doesn’t exist in engine
  25. XAMPP – Error: MySQL shutdown unexpectedly
  26. How to get back Lost phpMyAdmin Password, XAMPP
  27. django cannot connect mysql in docker-compose
  28. How to use mysql JOIN without ON condition?
  29. Define a variable within select and use it within the same select
  30. Merge two tables in one SQL query and make the date values unique

Leave a Comment