Unable to insert NULL values to a NOT NULL date field after upgrading from MySQL 5.7 to MySQL 8

Question

I upgraded from MySQL 5.7.26 to 8.0.20.

One of the tables have not null column with TIMESTAMP data type with a default value as CURRENT_TIMESTAMP.

On 5.7.26 , if an insert statement like the following was run, it used to execute successfully and the not null column used to be populated with default value i.e. CURRENT_TIMESTAMP.

mysql>insert into test_field ( object_type, dataType, fieldSize, hiddenFlag , fieldLabel, fieldName, modified_date) values ('LEAD', 13, 44, 1, 'Date Last Updated_testing1', 'test_Insert_test1', NULL);

Query OK, 1 row affected, 1 warning (0.00 sec)

After upgrading to 8.0.20, I am getting error :

mysql>insert into test_field ( object_type, dataType, fieldSize, hiddenFlag , fieldLabel, fieldName, modified_date) values ('LEAD', 13, 44, 1, 'Date Last Updated_testing1', 'test_Insert_test1', NULL);

ERROR 1048 (23000): Column 'modified_date' cannot be null

Answer 1

I don't think that the behavior of MySQL as regard to inserting null to a non-nullable column for which a default value is set up has changed between versions 5.7 and 8.0. However, it is documented that what happens in this case depends on whether strict mode is enabled:

• 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 are inserted.

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

Probably, your fresh 8.0 installation has strict mode enabled, while your it was disable in your 5.7 database.

A possible workaround is to use DEFAUT instead of NULL:

insert into test_field (object_type, dataType, fieldSize, hiddenFlag , fieldLabel, fieldName, modified_date) 
values ('LEAD', 13, 44, 1, 'Date Last Updated_testing1', 'test_Insert_test1', DEFAULT);