Inserting NULLs into NOT NULL columns in 5.6: refused by default
By user12626240 on Sep 16, 2013
MySQL 5.6 ships with a default config file that sets the SQL mode to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES . Here is what happens if you try to insert NULL values into a table with NOT NULL columns:
mysql> create table safetyfirst( -> id int primary key not null auto_increment, -> country varchar(60) NOT NULL, -> product varchar(60) NOT NULL ); Query OK, 0 rows affected (0.24 sec) mysql> insert into safetyfirst(country) values('Sweden'); ERROR 1364 (HY000): Field 'product' doesn't have a default value
If someone tells you that MySQL 5.6 by default allows you to do this, ask them to prove it using the default settings we use for new installations and check their claim by asking them for the output of SHOW VARIABLES LIKE 'sql%'; .
We would like to use NO_ZERO_DATE, NO_ZERO_IN_DATE, NO_AUTO_VALUE_ON_ZERO, ERROR_FOR_DIVISION_BY_ZERO as well but we know that many web applications use some of these things, so we did not do that for 5.6. If you are doing new application development or provide a development framework please strongly consider using these as well.
(2013-09-18 rewrote unclear last paragraph)