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:
None 
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
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.112 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.