Inserting NULL into NOT NULL columns with Default

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

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

Answer 2

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...

Answer 3

If you leave out the column (both name and value) from the statement, then the default value will be used.

Some related advice:

• Don't have any "non-empty" defaults in your tables, and don't have non-null defaults for nullable columns. Let all values be set from the application.
• Don't put business logic on the database-side.

Only define a default if really needed, and only for non-nullable columns. And remove the default when no longer needed. (they come in handy with alter table runs, to set the value of a new column, but then immediately run a new (cheap!) alter to remove the default)

The "empty" mentioned above, is related to the type: - 0 for numerical columns, - '' for varchar/varbinary columns, - '1970-01-01 12:34:56' for timestamps, - etc.

That saves the application many round trips to the database. If a created row is fully predictable, then the application doesn't need to read it after creating, to find out what it has become. (this assumes: no triggers, no cascading)

With MySQL we make only a few specific exceptions to those strict rules:

• Columns called mysql_row_foo, are only set by the database. Examples:

    mysql_row_created_at  timestamp(6)  not null  default '1970-01-01 12:34:56.000000',
    mysql_row_updated_at  timestamp(6)  null      default null  on update current_timestamp,
  

• Unique indexes on not-null columns are welcome, to prevent duplicate data. For example on lookup.brand.name in a table lookup.brand that looks like (id++, name).

The mysql_row_foo columns are like column attributes. They are used by data sync tools, for example. General applications don't read them, and they store their application-side timestamps as epoch values. Examples:

 valid_until_epoch   int unsigned  not null  default 0,
 last_seen_epoch_ms  bigint        not null  default 0,

Answer 4

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)

Answer 5

MySQL actually works as intended, and that behavior seems to be there to stay. MariaDB also works the same way now.

Removing "strict mode" (STRICT_TRANS_TABLES & STRICT_ALL_TABLES) is supposed to revert to the previous behavior, but I personally haven't had any luck with it (maybe I'm doing something wrong, but both my @@GLOBAL.sql_mode & @@SESSION.sql_mode do not contain strict mode).

I think the best solution to this problem is to rely on default values at the PHP level, instead of relying on the Database to provide them. There is an existing answer that explains it pretty well. The comments are also helpful.

That way, you also gain the added benefit that your models/entities will have the default value upon instantiation instead of upon insert in the database. Also, if you want to surface those values to the user after insertion, you can do so without having to do an extra SELECT query after your INSERT.

Another alternative to surface the default values would be to use a RETURNING clause, as is available in PostgreSQL, but not in MySQL (yet). It might be added at some point in the future, but for now MariaDB only has it for DELETE statements. However, I believe that having the default values at the PHP level is still superior; even if you never insert the record, it'll still contain the default values. I've never turned back and used a database default value since putting this into practice.