Inserting NULL into NOT NULL columns with Default Value

Question

For a bit of background, we use Zend Framework 2 and Doctrine at work. Doctrine will always insert NULL for values we do not populate ourselves. Usually this is okay as if the field has a default value, then it SHOULD populate the field with this default value.

For one of our servers running MySQL 5.6.16 a query such as the one below runs and executes fine. Although NULL is being inserted into a field which is not nullable, MySQL populates the field with its default value on insert.

On another of our servers running MySQL 5.6.20, we run the query below and it falls over because it complains that 'field_with_default_value' CANNOT be null.

INSERT INTO table_name(id, field, field_with_default_value) 
VALUES(id_value, field_value, NULL);

Doctrine itself does not support passing through "DEFAULT" into the queries it builds so that is not an option. I figure this must be a MySQL server thing of some kind seeing as though it works okay in one version but not another, but unfortunately I have no idea what this could be. Our SQL Mode is also identical on both servers ('NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION').

I should probably mention, if I actually run the above SQL in Workbench it still does not work in the same way. So it's not really a Doctrine issue but definitely a MySQL issue of some sort.

Any help on this would be greatly appreciated.

Answer 1

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)