Bug #91812 Slave replay stops because of explicit_defaults_for_timestamp between B and C
Submitted: 27 Jul 2018 2:52 Modified: 22 Mar 2021 13:05
Reporter: kfpanda kf Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.22 OS:Any
Assigned to: CPU Architecture:Any

[27 Jul 2018 2:52] kfpanda kf
I get the following error on the slave and replay stops:

ERROR: The system variable explicit_defaults_for_timestamp cannot be set when there is an ongoing transaction.

When I used the tool mysqlbinlog to parse binlog, I found that the parameter explicit_defaults_for_timestamp was set between the begin and commit.

I have yet to figure out how to reproduce the problem reliably.

How to repeat:

Here is the binlog entry that fails:
SET @@session.explicit_defaults_for_timestamp=0/*!*/;
# at 1246
#180726  2:26:46 server id 1532572195  end_log_pos 1404 CRC32 0x5bb4f8f1        Query   thread_id=286   exec_time=2937  error_code=0
SET TIMESTAMP=1532572006/*!*/;
SET @@session.explicit_defaults_for_timestamp=0/*!*/;
[7 Aug 2018 9:12] kfpanda kf
I don't know if it's related to explicit_defaults_for_timestamp=ON in my.cnf?
Did you find the reason?
[7 Aug 2018 10:44] MySQL Verification Team
I cannot reproduce this. I assume you have an application connecting to your mysqld doing this. But this does not look like a bug.

all best
[15 Mar 2021 19:21] Jean-François Gagné
I shared a test-case in Bug#102988 for reproducing this.

As a side comment, I think this should not have been closed as "Not a Bug" but as "Can't repeat".
[16 Mar 2021 0:31] MySQL Verification Team
Bug#102988 is marked as a duplicate of this bug
[22 Mar 2021 13:05] MySQL Verification Team

Thanks for the test case, I reproduced the problem

all best
[22 Mar 2021 14:50] Jean-François Gagné
Copying things from Bug#102988 to make sure this is not forgotten.

A very ugly patch would be to have mysqlbinlog not output the explicit_defaults_for_timestamp line for a COMMIT.

IMHO, the problem is in the way the empty transaction is generated.  When generating an empty transaction from a DDL, MySQL should probably reset the bits that make mysqlbinlog output explicit_defaults_for_timestamp.

Full analysis and test case for this in Bug#102988.