setting global sql_mode in mysql

by

BTW, if you set globals in MySQL:

SET GLOBAL sql_mode="NO_ENGINE_SUBSTITUTION";
SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION";

This will not set it PERMANENTLY, and it will revert after every restart.

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after MySQL restart:

Config File: /etc/mysql/my.cnf

[mysqld] 
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

UPDATE: Newer versions of Mysql (e.g. 5.7.8 or above) may require slightly different syntax:

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Make sure that there is a dash between sql-mode not an underscore, and that modes are in double quotes.

Always reference the MySQL Docs for your version to see the sql-mode options.

More Related Contents:

  1. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  2. Count the occurrences of DISTINCT values
  3. Codeigniter – multiple database connections
  4. Column count doesn’t match value count at row 1
  5. How to add levenshtein function in mysql?
  6. How to search JSON data in MySQL?
  7. MySQL > Table doesn’t exist. But it does (or it should)
  8. Is the primary key automatically indexed in MySQL?
  9. How do I modify a MySQL column to allow NULL?
  10. Connect to mysql in a docker container from the host
  11. Is there ANY_VALUE capability for mysql 5.6?
  12. phpMyAdmin on MySQL 8.0 [duplicate]
  13. Change decimal separator in MySQL
  14. Fetching linked list in MySQL database
  15. How to skip certain database tables with mysqldump?
  16. Using MySql with Entity Framework 4 and the Code-First Development CTP
  17. How can I correct MySQL Load Error
  18. ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
  19. MySQL treats ÅÄÖ as AAO?
  20. How can write queries in MySQL that can parse JSON data in a column?
  21. Count with IF condition in MySQL query
  22. MySQL Time Zones
  23. mysql_fetch_row() vs mysql_fetch_assoc() vs mysql_fetch_array() [duplicate]
  24. Mysql select where not in table
  25. Using DISTINCT and COUNT together in a MySQL Query
  26. Which DATATYPE is better to use TEXT or VARCHAR?
  27. MySQL Alter table causes Error: Invalid use of NULL value
  28. uninitialized constant MysqlCompat::MysqlRes (using mms2r gem)
  29. Migrating data into Firebase from MySQL
  30. Inventory management with stock options

Leave a Comment