How to enable mysql strict mode globally and have it stay on?

mysql strict
Chris Muench picture Chris Muench · Apr 19, 2011 · Viewed 8.5k times · Source

How do I enable MySQL strict mode globally, and have it stay on?

I have tried these commands:

SET sql_mode = TRADITIONAL;
SET sql_mode = ANSI_QUOTES;

But they only set the mode for the current session.

Answer

ABC picture ABC · Jul 1, 2015

To set the SQL mode at server startup, use: - the --sql-mode="modes" option on the command line, or - sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). ("modes" is a list of different modes separated by commas.)

Reference: https://dev.mysql.com/doc/refman/5.0/en/sql-mode.html

Related questions

How to turn off MySQL strict mode in Rails

Upgrading to Rails 4, it seems MySQL strict mode is now on by default for Rails connections. I say this because my Rails app is getting "Mysql2::Error: Data too long for column" when saving a string value longer than 255 characters. …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More