MySQL Bugs: #120757: "Start Transaction" modifier in the binary log cannot be replayed on an instance that does not have binary logs enabled.

Bug #120757	"Start Transaction" modifier in the binary log cannot be replayed on an instance that does not have binary logs enabled.
Submitted:	24 Jun 6:04	Modified:	17 Sep 16:47
Reporter:	Venkatesh Duggirala (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	8.4	OS:	Any
Assigned to:	Karolina Szczepankiewicz	CPU Architecture:	Any

Description:
A new transaction gets written to binary log when we are executing CREATE TABLE SELECT query. And when we are reapplying the same CREATE START TRANSACATION from a binary log content (and not a direct command from client), we do not want to start again a new transaction (as the binary log content already has a begin transaction). 

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc

CALL mtr.add_suppression("You need to use --log-bin to make --binlog-format work.");
### restart slave server with binlog disabled but gtid enabled
--let $rpl_server_number = 2
--let $rpl_server_parameters = --skip-log-bin
--source include/rpl_restart_server.inc

--connection slave
--source include/stop_slave.inc

# binary log (pitrbug-masterbin.000001) is generated before the fix with the following commands
# which will be used to reply with mysqlbinlog tool with fix.
#CREATE TABLE t1 (i INTEGER);
#INSERT INTO t1 VALUES (1),(2),(3);
#CREATE TABLE xyz (i INTEGER) SELECT i FROM t1;
#INSERT INTO xyz VALUES (4), (5), (6);

# Prove that the binlog contains problematic START TRANSACTION.
--exec $MYSQL_BINLOG $MYSQL_TEST_DIR/std_data/pitrbug-masterbin.000001 | grep START

--echo ==== FULL FILE ====
--exec $MYSQL_BINLOG $MYSQL_TEST_DIR/std_data/pitrbug-masterbin.000001 | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT

# Make sure we have data from the binary logs.
SELECT * FROM t1;
SELECT * FROM xyz;

#cleanup
--connection slave
DROP TABLE t1;
DROP TABLE xyz;
--let $rpl_skip_sync = 1
--source include/rpl_end.inc

Suggested fix:
Code at : 
https://github.com/mysql/mysql-server/blob/trunk/sql/sql_parse.cc#L3391-L3400

  /*
    Start a new transaction if CREATE TABLE has START TRANSACTION clause.
    Disable binlog so that the BEGIN is not logged in binlog.
   */
  if (lex->create_info && lex->create_info->m_transactional_ddl &&
      !thd->slave_thread) {
    const Disable_binlog_guard binlog_guard(thd);
    if (trans_begin(thd, MYSQL_START_TRANS_OPT_READ_WRITE)) return true;
  }

should be adjusted to skip if it is a binlog applier (not just check slave_thread) 

New code: 

 /*
    Start a new transaction if CREATE TABLE has START TRANSACTION clause.
    Disable binlog so that the BEGIN is not logged in binlog.
   */
  if (lex->create_info && lex->create_info->m_transactional_ddl &&
      !(thd->slave_thread || thd->is_binlog_applier())) {
    Disable_binlog_guard binlog_guard(thd);
    if (trans_begin(thd, MYSQL_START_TRANS_OPT_READ_WRITE)) return true;
  }

Fix for the bug (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix-for-bug120757.patch (text/x-patch), 4.80 KiB.

Contributed Patch was built on 8.0.36 but the same can be applied to 8.4 (need few modifications to test script (changes like master-slave.inc and few other deprecated words should be adjusted to work against 8.4 version)

Thank you for the bug report and contribution, verified.

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Patch approved'

Posted by developer: Bug status updated to 'Documenting'

Posted by developer:
 
Added the following note to the MySQL Server 8.4.13, 9.7.4, and 26.10.0 release notes:

Replaying a CTAS binary log through mysqlbinlog into a server with binary
logging disabled could fail instead of restoring the logged tables and
rows because an additional transaction produced an invalid transaction
boundary. Parser-started CTAS transactions are now suppressed for
binlog-applier threads.

Our thanks to Venkatesh Duggirala
and the team at Google for the contribution.