SQL_MODE Parameter Change in 5.6

This setting has changed from 5.5 to 5.6, it includes strict_trans_tables - sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.

The default in 5.5 didn't really prohibit very much except it gives an error if the storage engine specified for any table activity (eg. create or alter) is unavailable.  In 5.6, by default, strict mode is enable (strict mode is enabled when either strict_all_tables or strict_trans_tables is used).  Strict mode controls how MySQL handles input values that are invalid or missing.  Strict_trans_tables gives an error when a value is invalid or missing.  In non strict mode it converts an invalid value to the closest valid value for the column and inserts the adjusted value.  This behaviour can change depending on if the table is transactional, but generally in strict mode an error is usually generated and the transaction is stopped or rolled back.

Strict Mode

root [(none)]> select @@sql_mode;

+--------------------------------------------+

| @@sql_mode                                 |

+--------------------------------------------+

| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

+--------------------------------------------+

root [(none)]> use test;

Database changed

root [test]> CREATE TABLE t (i INT NOT NULL);

Query OK, 0 rows affected (0.08 sec)

root [test]> INSERT INTO t VALUES();

ERROR 1364 (HY000): Field 'i' doesn't have a default value

root [test]> INSERT INTO t VALUES(DEFAULT);

ERROR 1364 (HY000): Field 'i' doesn't have a default value

root [test]> INSERT INTO t VALUES(DEFAULT(i));

ERROR 1364 (HY000): Field 'i' doesn't have a default value

root [test]> select * from t;

Empty set (0.00 sec)

Non Strict Mode

DB1> use suetest;

Database changed

DB1> select @@sql_mode;

+------------------------+

| @@sql_mode             |

+------------------------+

| NO_ENGINE_SUBSTITUTION |

+------------------------+

DB1> CREATE TABLE t (i INT NOT NULL);

Query OK, 0 rows affected (4.08 sec)

DB1> INSERT INTO t VALUES();

Query OK, 1 row affected, 1 warning (0.48 sec)

DB1> INSERT INTO t VALUES(DEFAULT);

Query OK, 1 row affected, 1 warning (0.10 sec)

DB1> INSERT INTO t VALUES(DEFAULT(i));

ERROR 1364 (HY000): Field 'i' doesn't have a default value

DB1> select * from t;

+---+

| i |

+---+

| 0 |

| 0 |

+---+

2 rows in set (0.38 sec)

This setting affects data and the way it is validated.  An important question is does it have a performance impact?

http://dev.mysql.com/doc/refman/5.5/en/faqs-sql-modes.html

B.3.6: Does strict mode impact performance?
The intensive validation of input data that some settings requires more time than if the validation is not done. While the performance impact is not that great, if you do not require such validation (perhaps your application already handles all of this), then MySQL gives you the option of leaving strict mode disabled. However—if you do require it—strict mode can provide such validation.

The documentation link is:

http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_strict_trans_tables