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
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.7.22
• OS: Any
• CPU Architecture: Any

[27 Jul 2018 2:52] kfpanda kf

Description:
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/*!*/;
BEGIN
/*!*/;
# 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/*!*/;
COMMIT
------------------------------------------------

[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
Bogdan

[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

Hi,

Thanks for the test case, I reproduced the problem

all best
Bogdan

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