MySQL: Setting sql_mode permanently

by

MySQL sql_mode "TRADITIONAL", a.k.a. “strict mode”, is defined by the MySQL docs as:

“give an error instead of a warning” when inserting an incorrect value into a column.

Here’s how to ensure that your sql_mode is set to "TRADITIONAL".

First, check your current setting:

mysql
mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

This returned blank, the default, that’s bad: your sql_mode is not set to “TRADITIONAL”.

So edit the configuration file:

sudo vim /etc/mysql/my.cnf

Add this line in the section labelled [mysqld]: sql_mode="TRADITIONAL" (as fancyPants pointed out)

Then restart the server:

sudo service mysql restart

Then check again:

mysql
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Success! You are golden now.

More Related Contents:

  1. How to get a ranking result for special record in mysql [closed]
  2. SQL Query needed for fetch value from Two identical table but not fully identical table
  3. MySQL ERROR 1045 (28000): Access denied for user ‘bill’@’localhost’ (using password: YES)
  4. How to reset AUTO_INCREMENT in MySQL
  5. MySQL: Insert record if not exists in table [duplicate]
  6. MySQL Query GROUP BY day / month / year
  7. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  8. MySQL Server has gone away when importing large sql file
  9. MySQL InnoDB not releasing disk space after deleting data rows from table
  10. What is MySQL row order for “SELECT * FROM table_name;”?
  11. MySQL Like multiple values
  12. Error: select command denied to user ”@” for table ”
  13. Problems with contenttypes when loading a fixture in Django
  14. Dump all tables in CSV format using ‘mysqldump’
  15. How to convert time to the time zone of the iPhone device?
  16. MySQL: Sort GROUP_CONCAT values
  17. Select columns across different databases
  18. Entity Framework wizard crashes on MySQL
  19. Check for x consecutive days – given timestamps in database
  20. Mysql to select month-wise record even if data not exist
  21. Facebook like notifications tracking (DB Design)
  22. MySql update two tables at once
  23. MySQL – how many rows can I insert in one single INSERT statement?
  24. How do I show the schema of a table in a MySQL database?
  25. MySQL Stored Procedure Error Handling
  26. Mysql delete statement with limit
  27. How can I cast an int to a bit in MySQL 5.1?
  28. update a column by subtracting a value
  29. Insert data into table with result from another select query
  30. MySQL LIKE vs LOCATE

Leave a Comment