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

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

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.