mariadb/mysql-test/suite/binlog/t/start_alter_mysqlbinlog_replay.test

62 lines
2.2 KiB
Text
Raw Permalink Normal View History

MDEV-11675 Lag Free Alter On Slave This commit implements two phase binloggable ALTER. When a new @@session.binlog_alter_two_phase = YES ALTER query gets logged in two parts, the START ALTER and the COMMIT or ROLLBACK ALTER. START Alter is written in binlog as soon as necessary locks have been acquired for the table. The timing is such that any concurrent DML:s that update the same table are either committed, thus logged into binary log having done work on the old version of the table, or will be queued for execution on its new version. The "COMPLETE" COMMIT or ROLLBACK ALTER are written at the very point of a normal "single-piece" ALTER that is after the most of the query work is done. When its result is positive COMMIT ALTER is written, otherwise ROLLBACK ALTER is written with specific error happened after START ALTER phase. Replication of two-phase binloggable ALTER is cross-version safe. Specifically the OLD slave merely does not recognized the start alter part, still being able to process and memorize its gtid. Two phase logged ALTER is read from binlog by mysqlbinlog to produce BINLOG 'string', where 'string' contains base64 encoded Query_log_event containing either the start part of ALTER, or a completion part. The Query details can be displayed with `-v` flag, similarly to ROW format events. Notice, mysqlbinlog output containing parts of two-phase binloggable ALTER is processable correctly only by binlog_alter_two_phase server. @@log_warnings > 2 can reveal details of binlogging and slave side processing of the ALTER parts. The current commit also carries fixes to the following list of reported bugs: MDEV-27511, MDEV-27471, MDEV-27349, MDEV-27628, MDEV-27528. Thanks to all people involved into early discussion of the feature including Kristian Nielsen, those who helped to design, implement and test: Sergei Golubchik, Andrei Elkin who took the burden of the implemenation completion, Sujatha Sivakumar, Brandon Nesterenko, Alice Sherepa, Ramesh Sivaraman, Jan Lindstrom.
2021-01-29 11:59:14 +00:00
#
# MENT-662: Lag Free Alter On Slave
#
--echo #
--echo # Test verifies replay of binary logs which contain
--echo # SA/RA/CA works fine.
--echo # Generate a binary log with alter events and use mysqlbinlog tool to
--echo # generate a sql file for replay. Source it on an clean master and
--echo # verify the correctness. Use the latest binlog and repeat the same
--echo # process mentioned above and observe replay works fine.
--echo #
--source include/have_log_bin.inc
--source include/have_innodb.inc
--source include/have_binlog_format_statement.inc
--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase`
set global binlog_alter_two_phase = ON;
set binlog_alter_two_phase = ON;
MDEV-11675 Lag Free Alter On Slave This commit implements two phase binloggable ALTER. When a new @@session.binlog_alter_two_phase = YES ALTER query gets logged in two parts, the START ALTER and the COMMIT or ROLLBACK ALTER. START Alter is written in binlog as soon as necessary locks have been acquired for the table. The timing is such that any concurrent DML:s that update the same table are either committed, thus logged into binary log having done work on the old version of the table, or will be queued for execution on its new version. The "COMPLETE" COMMIT or ROLLBACK ALTER are written at the very point of a normal "single-piece" ALTER that is after the most of the query work is done. When its result is positive COMMIT ALTER is written, otherwise ROLLBACK ALTER is written with specific error happened after START ALTER phase. Replication of two-phase binloggable ALTER is cross-version safe. Specifically the OLD slave merely does not recognized the start alter part, still being able to process and memorize its gtid. Two phase logged ALTER is read from binlog by mysqlbinlog to produce BINLOG 'string', where 'string' contains base64 encoded Query_log_event containing either the start part of ALTER, or a completion part. The Query details can be displayed with `-v` flag, similarly to ROW format events. Notice, mysqlbinlog output containing parts of two-phase binloggable ALTER is processable correctly only by binlog_alter_two_phase server. @@log_warnings > 2 can reveal details of binlogging and slave side processing of the ALTER parts. The current commit also carries fixes to the following list of reported bugs: MDEV-27511, MDEV-27471, MDEV-27349, MDEV-27628, MDEV-27528. Thanks to all people involved into early discussion of the feature including Kristian Nielsen, those who helped to design, implement and test: Sergei Golubchik, Andrei Elkin who took the burden of the implemenation completion, Sujatha Sivakumar, Brandon Nesterenko, Alice Sherepa, Ramesh Sivaraman, Jan Lindstrom.
2021-01-29 11:59:14 +00:00
create table t1 (f1 int primary key) engine=InnoDB;
create table t2 (f1 int primary key, constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
--error ER_CANT_CREATE_TABLE
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
drop table t2, t1;
select @@gtid_binlog_state;
FLUSH LOGS;
let MYSQLD_DATADIR= `select @@datadir;`;
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/slave_1.sql
--echo # reset the binlog
RESET MASTER;
--echo # execute the binlog
--exec $MYSQL --port=$MASTER_MYPORT --host=127.0.0.1 -e "source $MYSQLTEST_VARDIR/tmp/slave_1.sql"
SELECT @@gtid_binlog_state;
FLUSH LOGS;
--echo # Replay 1: One more time to simulate S->S case
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/slave_2.sql
RESET MASTER;
--echo # execute the binlog
--exec $MYSQL --port=$MASTER_MYPORT --host=127.0.0.1 -e "source $MYSQLTEST_VARDIR/tmp/slave_2.sql"
SELECT @@gtid_binlog_state;
FLUSH LOGS;
--echo # Replay 2: One more time to simulate S->S case
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/slave_3.sql
RESET MASTER;
--echo # execute the binlog
--exec $MYSQL --port=$MASTER_MYPORT --host=127.0.0.1 -e "source $MYSQLTEST_VARDIR/tmp/slave_3.sql"
SELECT @@gtid_binlog_state;
--echo # clean up
remove_file $MYSQLTEST_VARDIR/tmp/slave_1.sql;
remove_file $MYSQLTEST_VARDIR/tmp/slave_2.sql;
remove_file $MYSQLTEST_VARDIR/tmp/slave_3.sql;
RESET MASTER;
--eval set global binlog_alter_two_phase=$binlog_alter_two_phase