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;
}
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; }