--source include/have_ndb.inc --source include/have_binlog_format_mixed_or_row.inc --source suite/ndb_rpl/ndb_master-slave.inc # # Currently test only works with NDB since it retrieves "old" # binlog positions with mysql.ndb_binlog_index and ndb_apply_status; # # create a table CREATE TABLE t1 (c1 CHAR(15), c2 CHAR(15), c3 INT, PRIMARY KEY (c3)) ENGINE = NDB ; # Replicate the table creation, then reset the master log and # slave to avoid the creation being replayed below # sync_slave_with_master; --connection slave STOP REPLICA; --connection master RESET MASTER; --connection slave RESET REPLICA; START REPLICA; --connection master # with one row INSERT INTO t1 VALUES ("row1","will go away",1); SELECT * FROM t1 ORDER BY c3; # sync slave and retrieve epoch sync_slave_with_master; SELECT MAX(epoch) FROM mysql.ndb_apply_status into @the_epoch; --replace_column 1 SELECT @the_epoch; let $the_epoch= `select @the_epoch` ; SELECT * FROM t1 ORDER BY c3; # get the master binlog pos from the epoch connection master; --disable_query_log --disable_result_log eval SELECT @the_pos:=Position, @the_file:=SUBSTRING_INDEX(REPLACE(FILE,'\\\\','/'), '/', -1) FROM mysql.ndb_binlog_index WHERE epoch = $the_epoch ; let $the_pos= `SELECT @the_pos` ; let $the_file= `SELECT @the_file` ; let $the_epoch=`SELECT MAX(epoch) FROM mysql.ndb_apply_status`; --enable_result_log --enable_query_log # insert some more values INSERT INTO t1 VALUES ("row2","will go away",2),("row3","will change",3),("row4","D",4); DELETE FROM t1 WHERE c3 = 1; UPDATE t1 SET c2="should go away" WHERE c3 = 2; UPDATE t1 SET c2="C" WHERE c3 = 3; DELETE FROM t1 WHERE c3 = 2; SELECT * FROM t1 ORDER BY c3; # check that we have it on the slave --sync_slave_with_master SELECT * FROM t1 ORDER BY c3; source include/check_slave_is_running.inc; # stop slave and reset position to before the last changes STOP REPLICA; --disable_query_log --disable_result_log eval CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE = '$the_file', SOURCE_LOG_POS = $the_pos ; --enable_result_log --enable_query_log source include/check_slave_no_error.inc; --disable_query_log call mtr.add_suppression("Replica: At SQL thread start applying epoch .*"); --enable_query_log # start the slave again # -> same events should have been applied again # e.g. inserting rows that already there # deleting a row which is not there # updating a row which is not there START REPLICA; --connection master SELECT * FROM t1 ORDER BY c3; --sync_slave_with_master SELECT * FROM t1 ORDER BY c3; STOP REPLICA; # # cleanup # --connection master DROP TABLE t1; RESET master; --connection slave DROP TABLE t1; RESET REPLICA; START REPLICA; # # Test that we can handle update of a row that does not exist on the slave # will trigger usage of AO_IgnoreError on slave side so that the INSERT # still succeeds even if the replication of the UPDATE generates an error. # --connection master CREATE TABLE t1 (c1 CHAR(15) NOT NULL, c2 CHAR(15) NOT NULL, c3 INT NOT NULL, PRIMARY KEY (c3)) ENGINE = NDB ; INSERT INTO t1 VALUES ("row1","remove on slave",1); --sync_slave_with_master --connection slave DELETE FROM t1; --connection master BEGIN; UPDATE t1 SET c2="does not exist" WHERE c3=1; INSERT INTO t1 VALUES ("row2","new on slave",2); COMMIT; --sync_slave_with_master --connection slave SELECT * FROM t1; source include/check_slave_is_running.inc; connection master; DROP TABLE IF EXISTS t1; # # Test that the ndb_binlog_index table records # the start position of an epoch transaction # as the first position after the previous # epoch transaction # --sync_slave_with_master --connection slave STOP REPLICA; --connection master RESET MASTER; --connection slave RESET REPLICA; START REPLICA; --connection master --echo Let's interleave some DDL and DML in the Binlog --echo Some DDL create table t1(a int primary key) engine=ndb; --echo First epoch transaction begin; insert into t1 values (1), (2), (3); commit; --echo Sync slave and retrieve epoch --sync_slave_with_master SELECT MAX(epoch) FROM mysql.ndb_apply_status into @first_epoch; --replace_column 1 SELECT @first_epoch; let $first_epoch= `select @first_epoch` ; --echo Slave contents select * from t1 ORDER by a; --echo Get the next master binlog pos from the epoch connection master; --disable_query_log --disable_result_log eval SELECT @the_pos:=next_position, @the_file:=SUBSTRING_INDEX(REPLACE(next_file,'\\\\','/'), '/', -1) FROM mysql.ndb_binlog_index WHERE epoch = $first_epoch ; let $the_pos= `SELECT @the_pos` ; let $the_file= `SELECT @the_file` ; --enable_result_log --enable_query_log --echo Now let's do some more DDL and DML create table t2 (a int primary key) engine=ndb; begin; insert into t2 values (1), (2), (3); commit; --sync_slave_with_master --connection slave source include/check_slave_is_running.inc; --echo Stop slave and reset position to start of the applied epoch STOP REPLICA; --disable_query_log --disable_result_log eval CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE = '$the_file', SOURCE_LOG_POS = $the_pos; --enable_result_log --enable_query_log source include/check_slave_no_error.inc; # Add a suppression for the warning that will appear in the # replicas's .err file call mtr.add_suppression("Table \'t2\' already exists.*"); call mtr.add_suppression("Worker 1 failed executing transaction.*"); START REPLICA; --let $slave_sql_errno= 1050 # Don't show error message, it varies for replica worker --let $show_slave_sql_error=0 --source include/wait_for_slave_sql_error.inc # Check that expected error message can be found SELECT COUNT(*) as FOUND_ERROR_MESSAGE FROM performance_schema.replication_applier_status_by_worker WHERE LAST_ERROR_NUMBER=1050 AND channel_name = '' AND LAST_ERROR_MESSAGE LIKE "%Table 't2' already exists%"; STOP REPLICA; # Cleanup --connection master RESET MASTER; --connection slave RESET REPLICA; START REPLICA; --connection master DROP TABLE t1; DROP TABLE t2; --sync_slave_with_master --source include/rpl_end.inc