Bug #120128 When explicit_defaults_for_timestamp is off, inserting a null value causes replication exceptions.

• Submitted: 23 Mar 8:14
• Modified: 23 Jul 17:09
• Reporter: nanzhou lin
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0.*
• OS: Any
• CPU Architecture: Any

[23 Mar 8:14] nanzhou lin

Description:
When explicit_defaults_for_timestamp is off, inserting a null value causes replication exceptions.

ERROR log: Could not execute Write_rows event on table test.t; Column 'timestamp_col2' cannot be null, Error_code: 1048; handler error No Error!; the event's source log master-bin.000001, end_log_pos 792>) COORDINATORS:(CHANNEL:<> ERROR:<Coordinator stopped because there were error(s) in the worker(s). 

How to repeat:

CREATE TABLE `t` (
 `id_col` int NOT NULL AUTO_INCREMENT,
 `datetime_col` datetime(6) DEFAULT NULL,
 `timestamp_col2` timestamp(6) GENERATED ALWAYS AS (`datetime_col`) STORED NOT NULL,
 PRIMARY KEY (`id_col`)
);
 insert ignore into t(datetime_col) values(NULL);

[24 Mar 12:40] Jean-François Gagné

Bug#120135 looks like a duplicate of this one.

[28 May 1:13] MySQL Admin

Posted by developer: Bug status updated to 'Documenting'

[23 Jul 17:09] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 8.4.11, 9.7.2, and 26.7.0 release notes:

If explicit_defaults_for_timestamp was disabled, row-based
replica apply or mysqlbinlog replay could stop when recomputing
a stored, generated TIMESTAMP NOT NULL column from NULL input.
Errors were returned similar to the following:

Could not execute Write_rows event on table test.t; Column 'timestamp_col2' 
cannot be null, Error_code: 1048; handler error No Error!

The row is now applied using the field default and a warning is
emitted.