Inserting NULL into NOT NULL columns with Default Value

I came across the same problem after a MySQL upgrade. Turns out there is a setting to allow NULL inserts against NOT NULL timestamp fields and get the default value.

explicit_defaults_for_timestamp=0

This is documented at https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

According to the documentation, everything works as expected.

Test case:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.16    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
              @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION      |
+------------------------+------------------------+
| NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
              @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION                                                                                               |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `table_name`;
+------------+----------------------------------------------------------------------------+
| Table      | Create Table                                                               |
+------------+----------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` (                                                |
|            |        `id` INT(11) UNSIGNED NOT NULL,                                     |
|            |        `field` VARCHAR(20) DEFAULT NULL,                                   |
|            |        `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=latin1                                     |
+------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
       VALUES
       (1, 'Value', NULL);
ERROR 1048 (23000): Column 'field_with_default_value' cannot be null

Is it possible to post the relevant part of the structure of your table to see how we can help?

UPDATE

MySQL 5.7, using triggers, can provide a possible solution to the problem:

Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)

...

• If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug #6295, Bug#11744964).

...

Possible solution:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.4-m14 |
+-----------+
1 row in set (0.00 sec)

mysql> DELIMITER $$

mysql> CREATE TRIGGER `trg_bi_set_default_value` BEFORE INSERT ON `table_name`
       FOR EACH ROW
       BEGIN
          IF (NEW.`field_with_default_value` IS NULL) THEN
             SET NEW.`field_with_default_value` := 
                (SELECT `COLUMN_DEFAULT`
                 FROM `information_schema`.`COLUMNS`
                 WHERE `TABLE_SCHEMA` = DATABASE() AND 
                       `TABLE_NAME` = 'table_name' AND
                       `COLUMN_NAME` = 'field_with_default_value');
          END IF;
       END$$

mysql> DELIMITER ;

mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
       VALUES
       (1, 'Value', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `id`, `field`, `field_with_default_value` FROM `table_name`;
+----+-------+--------------------------+
| id | field | field_with_default_value |
+----+-------+--------------------------+
|  1 | Value | myDefault                |
+----+-------+--------------------------+
1 row in set (0.00 sec)

Based on my research, I would say it could both be a "you" thing and a "MySQL" thing. Check your table definitions with SHOW CREATE TABLE table_name;. Take note of any fields defined with NOT NULL.

The MySQL 5.6 Reference Manual: 13.2.5 INSERT syntax states:

Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

This would imply that it does not matter which SQL mode you are using. If you are doing a single row INSERT (as per your sample code) and inserting a NULL value into a column defined with NOT NULL, it is not supposed to work.

In the same breath, ironically, if you were to simply omit the value from the values list, the MySQL manual says the following, and the SQL mode does matter in this case:

If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 11.6, “Data Type Default Values”. See also Section 1.7.3.3, “Constraints on Invalid Data”.

Thus, you can't win! ;-) Kidding. The thing to do is to accept that NOT NULL on a MySQL table field really means I will not accept a NULL value for a field while performing a single row INSERT, regardless of SQL mode.'

All that being said, the following from the manual is also true:

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

So, take heart. Set your defaults in the business logic (objects), and let the data layer take direction from that. Database defaults seem like a good idea, but if they did not exist, would you miss them? If a tree falls in the forest...