How to change sql_mode in MySQL Workbench

SQL mode (sql_mode) is a MySQL system variable. By means of this varriable the MySQL Server SQL mode is controlled. Many operational characteristics of MySQL Server can be configured by setting the SQL mode. By setting the SQL mode appropriately, a client program can instruct the server how strict or forgiving to be about accepting input data, enable or disable behaviors relating to standard SQL conformance, or provide better compatibility with other database systems. By default, the server uses a sql_mode value of  ' '  (the empty string), which enables no restrictions. Thus, the server operates in forgiving mode (non-strict mode) by default. In non-strict mode, the MySQL server converts erroneous input values to the closest legal values (as determined from column definitions) and continues on its way. For example, if you attempt to store a negative value into an UNSIGNED column, MySQL converts it to zero, which is the nearest legal value for the column.

The SQL mode is controlled by means of the sql_mode system variable. To assign a value to this variable, SET statement is used. The value should be an empty string, or one or more mode names separated by commas. If the value is empty or contains more than one mode name, it must be quoted. If the value contains a single mode name, quoting is optional. SQL mode values are not case sensitive. Here are some examples:

To clear the SQL mode or to set non-strict mode:

SET sql_mode = '';

The most general means of enabling input value restrictions is by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES modes:

SET sql_mode = STRICT_TRANS_TABLES;
SET sql_mode = STRICT_ALL_TABLES;

The term "strict mode" refers collectively to both of these modes. They prevent entry of invalid values such as those that are out of range, or NULL specified for NOT NULL columns.

To set the SQL mode using a single mode value:

SET sql_mode = ANSI_QUOTES;
SET sql_mode = TRADITIONAL;

The TRADITIONAL mode, enables strict mode plus other restrictions on date checking and division by zero. Setting the sql_mode system variable to TRADITIONAL causes MySQL to act like more traditional database servers in its input data handling.  If you want your MySQL server to be as restrictive as possible about input data checking, the simplest way to achieve this is to enable TRADITIONAL mode rather than a list of individual more-specific modes.

To set the SQL mode using multiple mode names:

SET sql_mode = 'IGNORE_SPACE, ANSI_QUOTES';
SET sql_mode = 'STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO';

To check the current sql_mode setting, select its value like this:

SELECT @@sql_mode;

Some SQL mode values are composite modes that actually enable a set of modes. Values in this category include ANSI and trADITIONAL. To see which mode values a composite mode consists of, retrieve the value after setting it:

SET sql_mode=TRADITIONAL;
SELECT @@sql_mode;

If you want relaxed date checking that requires only that month and day values be in the respective ranges of 1 to 12 and 1 to 31, enable the ALLOW_INVALID_DATES SQL mode value:

I was working on a legacy project recently and needed to import some data from MySQL 5.5. All the queries in the code worked perfectly in MySQL 5.5, so I assumed an upgrade to 5.7 would be seamless. Not so.

First I got errors due to DateTime columns being populated with zeros during import, then when running this query:

select * 
  from ebay_order_items 
  where 
    z_shipmentno is null 
    and ExternalTransactionID is not null 
    and orderstatus = 'Completed' 
    and timestamp > '2015-02-25' 
  group by ExternalTransactionID 
  order by timestamp desc

I got this:

Expression #1 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 
'1066export.ebay_order_items.TransactionID' which 
is not functionally dependent on columns in GROUP BY 
clause; this is incompatible with sql_mode=only_full_group_by

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.

How to set default sql_mode?

What is the default sql_mode in MySQL?

What is sql_mode MySQL?

How to set sql_mode in MySQL 8?