Monday Sep 16, 2013

Inserting NULLs into NOT NULL columns in 5.6: refused by default

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)

About

I'm James Day, one of the more senior support engineers in Oracle's MySQL team. A former developer, I've had the pleasure of being featured at a MySQL User Conference twice to receive awards, once in 2005 on behalf of Wikipedia as Application of the Year and in 2012 as one of the Community Contributors of the Year for my comments on the blog entries of other people.

Search

Top Tags
Categories
Archives
« September 2013
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
17
18
19
20
21
22
23
24
25
26
27
28
29
30
     
       
Today