polardbxengine/mysql-test/suite/rpl/t/rpl_binlog_transaction_dependency_tracking.test

# ==== Purpose ====
#
# Test timestamps generated by binlog-transaction-dependency-tracking
# modes WRITESET, WRITESET_SESSION and COMMIT_ORDER.
#
# The following properties are tested:
# - With binlog-transaction-dependency-tracking=WRITESET, the logical timestamps
#   are generated as expected, marking each transaction as conflicting with
#   transactions having intersecting writesets.
#   (type = 0)
# - With binlog-transaction-dependency-tracking=WRITESET_SESSION, transactions
#   are additionally marked as conflicting with all previous transactions in the
#   same session.
#   (type = 1)
# - Additionally, transactions are marked as conflicting with any DDL.
#   (step = 2)
# - Additionally, transactions are marked as conflicting if one appears before
#   and the other after a binlog rotation.
#   (step = 3)
# - Additionally, transactions are marked as conflicting if the writeset history
#   was cleared between the transactions.
#   (step = 4)
#
# - It tests this for INSERT, UPDATE and DELETE statements.
#
# ==== Implementation ====
#
# 1. Apply 8 transactions with different data dependencies and check the binary
#    log generated (step 1). Do the same with DDL in the middle (step 2), with
#    rotation (step 3) and when writeset history becomes full (step 4).
#
# 2. Test Bug#2561672: POST RESET MASTER/FLUSH LOGS SETTING BTDT=WRITESET
#    BEHAVES LIKE COMMIT_ORDER
#
# 3. Test ROLLBACK TO SAVEPOINT.
#
# ==== References ====
#
# WL#9556: Writeset-based MTS dependency tracking on master
#

--source include/master-slave.inc
--source include/have_binlog_format_row.inc

--echo #### INITIALIZE ####

--connection slave
call mtr.add_suppression("Transaction is tagged with inconsistent logical timestamps");
call mtr.add_suppression("Cannot execute the current event group in the parallel mode.");

--source include/stop_slave.inc

SET @save_slave_parallel_type = @@GLOBAL.slave_parallel_type;
SET @save_slave_parallel_workers = @@GLOBAL.slave_parallel_workers;
SET @save_slave_transaction_retries = @@GLOBAL.slave_transaction_retries;

SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
SET GLOBAL slave_parallel_workers = 3;
SET GLOBAL slave_transaction_retries = 0;

--connection master

SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking;
SET @save_binlog_transaction_dependency_history_size = @@GLOBAL.binlog_transaction_dependency_history_size;
SET @save_transaction_write_set_extraction = @@GLOBAL.transaction_write_set_extraction;

# for step 4
SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;

SET GLOBAL transaction_write_set_extraction = XXHASH64;
SET LOCAL transaction_write_set_extraction = XXHASH64;

--connection master1
SET LOCAL transaction_write_set_extraction = XXHASH64;

--connection server_1
SET LOCAL transaction_write_set_extraction = XXHASH64;

--connection master

CREATE TABLE tests (`id` INT NOT NULL, `description` VARCHAR(50), `results` VARCHAR(100), PRIMARY KEY(`id`));
INSERT INTO `tests` (`id`, `description`, `results`) VALUES
(1,  'Writeset',                  '0 1;1 2;1 3;1 4;3 5;3 6;4 7;7 8;6 9;9 10'),
(2,  'Writeset+DDL',              '0 1;1 2;1 3;1 4;4 5;5 6;6 7;7 8;4 9;7 10;10 11;9 12;12 13'),
(3,  'Writeset+rotation',         '0 1;0 2;1 3;3 4;2 5;5 6'),
(4,  'Writeset+history',          '0 1;1 2;1 3;1 4;4 5;4 6;6 7;7 8;6 9;9 10'),
(5,  'Writeset_session',          '0 1;1 2;1 3;2 4;3 5;3 6;5 7;7 8;7 9;9 10'),
(6,  'Writeset_session+DDL',      '0 1;1 2;1 3;2 4;4 5;5 6;6 7;7 8;4 9;8 10;10 11;10 12;12 13'),
(7,  'Writeset_session+rotation', '0 1;0 2;1 3;3 4;3 5;5 6'),
(8,  'Writeset_session+history',  '0 1;1 2;1 3;2 4;4 5;4 6;6 7;7 8;7 9;9 10'),
(9,  'Commit_order',              '0 1;1 2;1 3;3 4;4 5;4 6;6 7;7 8;7 9;9 10'),
(10, 'Commit_order+DDL',          '0 1;1 2;1 3;3 4;4 5;5 6;6 7;7 8;4 9;9 10;10 11;10 12;12 13'),
(11, 'Commit_order+rotation',     '0 1;0 2;2 3;3 4;3 5;5 6'),
(12, 'Commit_order+history',      '0 1;1 2;1 3;3 4;4 5;4 6;6 7;7 8;7 9;9 10');

# flush logs to clear previous transactions
FLUSH LOGS;

--let $idx= 0

--echo #### TEST ####

# loop between WRITESET and WRITESET_SESSION
--let $type= 0
while ($type < 3)
{
  --inc $type

  --connection master
  if ($type == 1)
  {
    SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
    --echo ######## 1. WRITESET ########
  }
  if ($type == 2)
  {
    SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION;
    --echo ######## 2. WRITESET_SESSION ########
  }
  if ($type == 3)
  {
    SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
    --echo ######## 3. COMMIT_ORDER ########
  }

  # loop between steps
  --let $step= 0
  while ($step < 4)
  {
    --inc $step
    --inc $idx

    --let $testname=`SELECT description FROM tests WHERE id=$idx`
    --echo #### STEP $type.$step TEST $testname ####

    SET GLOBAL binlog_transaction_dependency_history_size=25000;
    if ($step == 4)
    {
      --echo History size reduced to 5 to test when it becomes full
      SET GLOBAL binlog_transaction_dependency_history_size=5;
    }

    SELECT @@local.transaction_write_set_extraction,
           @@global.transaction_write_set_extraction,
           @@binlog_transaction_dependency_tracking,
           @@binlog_transaction_dependency_history_size;

    CREATE TABLE `t1` (`id` int(11) NOT NULL, `val` int(11) NOT NULL,
      PRIMARY KEY (`id`)) ENGINE=InnoDB;

    --connection master
    --echo #TRX2 (session A, independent)
    BEGIN;
    INSERT INTO t1(id, val) VALUES (1, 0),(2, 0),(3, 0);

    --connection master1
    --echo #TRX1 (session B, independent)
    BEGIN;
      INSERT INTO t1(id, val) VALUES (4, 0),(5, 0);
    COMMIT;

    --connection master
    --echo finish TRX2 after TRX1
    COMMIT;

    --connection master1
    --echo #TRX3 (session B, no data dependency, depends on TRX1 (session))
    BEGIN;
      INSERT INTO t1(id, val) VALUES (6, 0),(7, 0);
    COMMIT;

    --connection master
    --echo #TRX5 (session A, depends on TRX2 (session and data) and TRX1(data))
    BEGIN;
      INSERT INTO t1(id, val) VALUES (8, 1);
      UPDATE t1 SET val=val+1 WHERE id=1;
      UPDATE t1 SET val=val+1 WHERE id=4;

    if ($step == 2)
    {
      --connection master1
      --echo Inserted a DDL after TRX5 and check that they run in parallel due to commit_order
      CREATE TABLE `t2` (`id` int(11) NOT NULL, `val` int(11) NOT NULL) ENGINE=InnoDB;
      ALTER TABLE t2 ADD COLUMN b INT;
      DROP TABLE `t2`;
    }

    # flush logs in the middle
    if ($step == 3)
    {
      --connection master1
      --echo Flushing logs in the middle of the set
      FLUSH LOGS;
    }

    --connection server_1
    --echo #TRX4 (session C, depends on TRX2 (data))
    BEGIN;
      UPDATE t1 SET val=val+1 WHERE id=3;
      INSERT INTO t1(id, val) VALUES (9, 1);
    COMMIT;

    --connection master
    --echo finish TRX5 after TRX4
    COMMIT;

    --connection server_1
    --echo #TRX6 (session C, depends on TRX4 (session) and TRX3(data))
    BEGIN;
      DELETE FROM t1 WHERE id=6;
    COMMIT;

    --connection server_1
    --echo #TRX8 (session C, depends on TRX5 (data) and TRX6 (session))
    BEGIN;
      UPDATE t1 SET val=val+1 WHERE id=4;

    --connection master1
    --echo #TRX7 (session B, depends on TRX1 and TRX6 (data) and TRX3 (session))
    BEGIN;
      UPDATE t1 SET val=val+1 WHERE id=5;
      INSERT INTO t1(id, val) VALUES (6, 0);
    COMMIT;

    --connection server_1
    --echo finish TRX8 after TRX7
    COMMIT;

    --connection master
    --echo Drop table and flush logs to force binlog to rotate
    DROP TABLE t1;

    --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
    --echo Processing binlog $binlog_file
    FLUSH LOGS;

    --let $source_file= $server_1_datadir/$binlog_file
    --let $logical_timestamps=`SELECT results FROM tests WHERE id=$idx`
    --source include/assert_logical_timestamps.inc
  }
}

--echo
--echo #### Verify that replication was correct ####

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

--connection master
--source include/sync_slave_sql_with_master.inc

--let $diff_tables= master:test.tests, slave:test.tests
--source include/diff_tables.inc

--connection master
DROP TABLE `tests`;
--source include/sync_slave_sql_with_master.inc

--connection master
SET GLOBAL binlog_transaction_dependency_tracking= WRITESET;
SET GLOBAL binlog_transaction_dependency_history_size= DEFAULT;
FLUSH LOGS;

--echo
--echo #### Bug#25616372, PART1 ####
CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT); # 0 1
INSERT INTO t1 VALUES (null); # 1 2
INSERT INTO t1 VALUES (null); # 1 3
set global binlog_transaction_dependency_tracking=WRITESET;
INSERT INTO t1 VALUES (null); # 3 4
INSERT INTO t1 VALUES (null); # 3 5
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
-- echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;1 3;3 4;3 5
--source include/assert_logical_timestamps.inc
FLUSH LOGS;

--echo
--echo #### Bug#25616372, PART2 ####
INSERT INTO t1 VALUES (null); # 0 1
INSERT INTO t1 VALUES (null); # 0 2
set global binlog_transaction_dependency_tracking=WRITESET;
INSERT INTO t1 VALUES (null); # 2 3
INSERT INTO t1 VALUES (null); # 3 4. # It should be 2 4
INSERT INTO t1 VALUES (null); # 4 5. # It should be 2 5
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
-- echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;2 3;2 4;2 5
--source include/assert_logical_timestamps.inc
DROP TABLE t1;
FLUSH LOGS;


--echo
--echo #### Bug#26277771, PART1 ####
# Bug#26277771 original issue due to missing collation in generating hashes
CREATE TABLE t1 (c1 int(10) NOT NULL, c2 varchar(20) NOT NULL, PRIMARY KEY (c1),
                 UNIQUE KEY (c1, c2)) ENGINE=InnoDB DEFAULT CHARSET=latin1; # 0 1
INSERT INTO t1 VALUES (1, 'a'); # 1 2
DELETE FROM t1 WHERE c1=1 AND c2='a'; # 2 3
INSERT INTO t1 VALUES (1, 'A'); # 3 4
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
-- echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;2 3;3 4
--source include/assert_logical_timestamps.inc
DROP TABLE t1;
FLUSH LOGS;

--echo
--echo #### Bug#26277771, PART2 ####
# Bug#26277771 issue due to wrong string length calculation
CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL PRIMARY KEY); # 0 1
INSERT INTO t1 VALUES ('a'); # 1 2
INSERT INTO t1 VALUES ('A'); # 1 3
INSERT INTO t1 VALUES ('b'); # 1 4
INSERT INTO t1 VALUES ('B'); # 1 5
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
-- echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;1 3;1 4;1 5
--source include/assert_logical_timestamps.inc
DROP TABLE t1;
FLUSH LOGS;

--echo
--echo #### Bug#26277771, PART3 ####
# test for unset charset, padding and null terminaton
CREATE TABLE t1 (c1 VARCHAR(20) NOT NULL PRIMARY KEY); # 0 1
INSERT INTO t1 VALUES ('a'); # 1 2
INSERT INTO t1 VALUES ('b'); # 1 3

CREATE TABLE t2 (c1 BINARY(3) NOT NULL PRIMARY KEY); # 3 4
INSERT INTO t2 VALUES ('a'); # 4 5
# copy using strmake will generate same hash for '\0a' and '\0'
# as it was copying till null termination character and binary
# doesn't consider null as temination character.
INSERT INTO t2 VALUES ('\0a'); # 4 6
INSERT INTO t2 VALUES ('\0'); # 4 7
INSERT INTO t2 VALUES ('a '); # 4 8

--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
-- echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;1 3;3 4;4 5;4 6;4 7;4 8
--source include/assert_logical_timestamps.inc
DROP TABLE t1;
DROP TABLE t2;
FLUSH LOGS;

--echo
--echo #### Bug#26277771, PART4 ####
CREATE TABLE t1 (c1 varchar(20) NOT NULL PRIMARY KEY)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; # 0 1
INSERT INTO t1 VALUES ('a'); # 1 2
INSERT INTO t1 VALUES ('á'); # 1 3
--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
--echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;1 3
--source include/assert_logical_timestamps.inc
DROP TABLE t1;
FLUSH LOGS;


#
# Test ROLLBACK TO SAVEPOINT. The rolled-back rows will still have their
# hashes in the writeset and may cause spurious conflicts.
#
--echo
-- echo #### Test ROLLBACK TO SAVEPOINT ####
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE nopk (a INT);
CREATE TABLE parent (a INT PRIMARY KEY);
CREATE TABLE child (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES parent(a));

--echo # 1. Hashes of rolled-back rows are removed from writeset
FLUSH LOGS;
INSERT INTO t1 VALUES (1);
BEGIN;
  SAVEPOINT sp;
    UPDATE t1 SET a = 2;
  ROLLBACK TO sp;
  INSERT INTO t1 VALUES (2);
COMMIT;
INSERT INTO t1 VALUES (3);

--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
--echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;1 3
--source include/assert_logical_timestamps.inc

--echo # 2. If transaction is flagged as updating a non-index table, the flag is not rolled-back
FLUSH LOGS;
INSERT INTO t1 VALUES (4);
BEGIN;
  SAVEPOINT sp;
    INSERT INTO nopk VALUES (1);
  ROLLBACK TO sp;
  INSERT INTO t1 VALUES (5);
COMMIT;
INSERT INTO t1 VALUES (6);

--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
--echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;1 3
--source include/assert_logical_timestamps.inc

--echo # 3. If transaction is flagged as updating a foreign key parent table, the flag is not rolled-back
FLUSH LOGS;
INSERT INTO t1 VALUES (7);
BEGIN;
  SAVEPOINT sp;
    INSERT INTO parent VALUES (1);
  ROLLBACK TO sp;
  INSERT INTO t1 VALUES (8);
COMMIT;
INSERT INTO t1 VALUES (9);

--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
--echo Processing binlog $binlog_file
--let $source_file= $server_1_datadir/$binlog_file
--let $logical_timestamps=0 1;1 2;2 3
--source include/assert_logical_timestamps.inc

--echo # Clean up SAVEPOINT tests
DROP TABLE t1;
DROP TABLE nopk;
DROP TABLE child;
DROP TABLE parent;

#
# Cleanup
#
--echo
--echo #### CLEANUP ####

--source include/sync_slave_sql_with_master.inc
--source include/stop_slave.inc

SET @@GLOBAL.slave_parallel_type= @save_slave_parallel_type;
SET @@GLOBAL.slave_parallel_workers= @save_slave_parallel_workers;
SET @@GLOBAL.slave_transaction_retries= @save_slave_transaction_retries;

--source include/start_slave.inc

--connection master
SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
SET @@GLOBAL.transaction_write_set_extraction= @save_transaction_write_set_extraction;
SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking;
SET @@GLOBAL.binlog_transaction_dependency_history_size= @save_binlog_transaction_dependency_history_size;

--source include/rpl_end.inc