| 1 |
################################################################################ |
| 2 |
# Bug#24466187 MTS WORKERS FAILS TO WAIT FOR COMPLETION OF DEPENDENT |
| 3 |
# TRANSACTIONS |
| 4 |
# Test cases to verify that dependent transaction check and |
| 5 |
# conflict detection algorithm is working properly when the foreign key is |
| 6 |
# referring to a unique key. |
| 7 |
# |
| 8 |
# Test: |
| 9 |
# 0. The test requires two servers: M1 and M2. |
| 10 |
# 1. With both members ONLINE. Create two tables on M1. |
| 11 |
# 2. With different test combinations, |
| 12 |
# call gr_parallel_local_and_remote_transactions.inc |
| 13 |
# and test the behaviour as expected or not. |
| 14 |
# 2.1. Scenario 1: FOREIGN KEY referring an UNIQUE KEY |
| 15 |
# conflicting case: updating referring tuple while |
| 16 |
# the tuple with referring key is getting inserted |
| 17 |
# in child table. |
| 18 |
# 2.2. Scenario 2: FOREIGN KEY referring an UNIQUE KEY |
| 19 |
# non-conflicting case: inserting a tuple in child's table |
| 20 |
# while updating a tuple in parent's table which is not |
| 21 |
# getting referred. |
| 22 |
# 3. Check data sanity after the above scenarios. |
| 23 |
# |
| 24 |
# Check that the group replication parallel applier indexes of the DMLs |
| 25 |
# that involves foreign key which is referring an unique key is calculated |
| 26 |
# properly. |
| 27 |
# |
| 28 |
# 0. The test requires two servers: M1 and M2. |
| 29 |
# 1. With both members ONLINE. Create two tables on M1. |
| 30 |
# 2. Execute few DMLs on these two tables on M1. |
| 31 |
# 3. Dump Group Replication applier channel relay log. |
| 32 |
# 4. Validate indexes on M2. |
| 33 |
################################################################################ |
| 34 |
|
| 35 |
--source include/have_debug_sync.inc |
| 36 |
--source include/have_group_replication_plugin.inc |
| 37 |
--source include/group_replication.inc |
| 38 |
|
| 39 |
--echo |
| 40 |
--echo ############################################################ |
| 41 |
--echo # Create two tables on server1. |
| 42 |
--echo # t1 : parent table with a primary key and unique key. |
| 43 |
--echo # t2 : child table with a primary key and foreign key referring |
| 44 |
--echo # t1's unique key. |
| 45 |
--echo |
| 46 |
--let $rpl_connection_name= server1 |
| 47 |
--source include/rpl_connection.inc |
| 48 |
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT UNIQUE KEY NOT NULL); |
| 49 |
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c2)); |
| 50 |
INSERT INTO t1 VALUES(1, 1); |
| 51 |
INSERT INTO t1 VALUES(2, 2); |
| 52 |
--source include/rpl_sync.inc |
| 53 |
|
| 54 |
--echo |
| 55 |
--echo ############################################################ |
| 56 |
--echo # Scenario 1: FOREIGN KEY referring an UNIQUE KEY |
| 57 |
--echo # conflicting case: updating referring tuple while |
| 58 |
--echo # the tuple with referring key is getting inserted |
| 59 |
--echo # in child table |
| 60 |
--let $local_server_connection1=server1 |
| 61 |
--let $local_server_connection2=server_1 |
| 62 |
--let $remote_server_connection=server2 |
| 63 |
--let $local_transaction= INSERT INTO t2 VALUES (1, 1) |
| 64 |
--let $remote_transaction=UPDATE t1 SET c2=3 WHERE c1=1 |
| 65 |
--let $conflict_test=1 |
| 66 |
--source include/gr_parallel_local_and_remote_transactions.inc |
| 67 |
|
| 68 |
--echo ############################################################ |
| 69 |
--echo # check data sanity |
| 70 |
--let $rpl_connection_name= server1 |
| 71 |
--source include/rpl_connection.inc |
| 72 |
|
| 73 |
--let $assert_text= Table t1 will contain update row after the above execution |
| 74 |
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c2 = 3, count, 1]" = "1" |
| 75 |
--source include/assert.inc |
| 76 |
|
| 77 |
--let $assert_text= Table t2 should have zero rows after the above conflict |
| 78 |
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t2, count, 1]" = "0" |
| 79 |
--source include/assert.inc |
| 80 |
|
| 81 |
--echo |
| 82 |
--echo ############################################################ |
| 83 |
--echo # Scenario 2: FOREIGN KEY referring an UNIQUE KEY |
| 84 |
--echo # non-conflicting case: inserting a tuple in child's table |
| 85 |
--echo # while updating a tuple in parent's table which is not getting |
| 86 |
--echo # referred |
| 87 |
--let $local_server_connection1=server1 |
| 88 |
--let $local_server_connection2=server_1 |
| 89 |
--let $remote_server_connection=server2 |
| 90 |
--let $local_transaction= INSERT INTO t2 VALUES (1, 3) |
| 91 |
--let $remote_transaction=UPDATE t1 SET c2=4 WHERE c1=2 |
| 92 |
--let $conflict_test=0 |
| 93 |
--source include/gr_parallel_local_and_remote_transactions.inc |
| 94 |
|
| 95 |
--echo ############################################################ |
| 96 |
--echo # check data sanity |
| 97 |
--let $rpl_connection_name= server1 |
| 98 |
--source include/rpl_connection.inc |
| 99 |
|
| 100 |
--let $assert_text= Table t1 will contain update row after the above execution |
| 101 |
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t1 WHERE t1.c2 = 4, count, 1]" = "1" |
| 102 |
--source include/assert.inc |
| 103 |
|
| 104 |
--let $assert_text= Table t2 will contain one tuple |
| 105 |
--let $assert_cond= "[SELECT COUNT(*) AS count FROM t2 WHERE t2.c2= 3, count, 1]" = "1" |
| 106 |
--source include/assert.inc |
| 107 |
|
| 108 |
--echo |
| 109 |
--echo ############################################################ |
| 110 |
--echo # Clean up. |
| 111 |
DROP TABLE t2; |
| 112 |
DROP TABLE t1; |
| 113 |
--source include/rpl_sync.inc |
| 114 |
|
| 115 |
--echo |
| 116 |
--echo ################################################################## |
| 117 |
--echo # Scenario: Check the group replication parallel applier indexes |
| 118 |
--echo # of the DMLs that involves foreign key which is referring |
| 119 |
--echo # an unique key. |
| 120 |
--echo |
| 121 |
--let $rpl_connection_name= server2 |
| 122 |
--source include/rpl_connection.inc |
| 123 |
--source include/stop_group_replication.inc |
| 124 |
# This creates new relay log and view_id will have |
| 125 |
# last_committed=0 sequence_number=0 |
| 126 |
--source include/start_group_replication.inc |
| 127 |
|
| 128 |
--echo |
| 129 |
--echo ##################################################################### |
| 130 |
--echo # Create two tables on server1. |
| 131 |
--echo # t1 : parent table with a primary key and unique key. |
| 132 |
--echo # t2 : child table with a primary key and foreign key referring |
| 133 |
--echo # t1's unique key. |
| 134 |
--echo |
| 135 |
--let $rpl_connection_name= server1 |
| 136 |
--source include/rpl_connection.inc |
| 137 |
# last_committed=1 sequence_number=2 |
| 138 |
CREATE TABLE t1 (a INT,b INT UNIQUE NOT NULL, PRIMARY KEY(a)); |
| 139 |
# last_committed=2 sequence_number=3 |
| 140 |
CREATE TABLE t2 (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t1(b)); |
| 141 |
|
| 142 |
--echo |
| 143 |
--echo ##################################################################### |
| 144 |
--echo # Execute few DMLs on these two tables |
| 145 |
--echo |
| 146 |
# last_committed=3 sequence_number=4 |
| 147 |
INSERT INTO t1 VALUES(11,1); |
| 148 |
# last_committed=3 sequence_number=5 |
| 149 |
INSERT INTO t1 VALUES(12,2); |
| 150 |
# last_committed=4 sequence_number=6 |
| 151 |
INSERT INTO t2 VALUES(31,1); |
| 152 |
# last_committed=5 sequence_number=7 |
| 153 |
INSERT INTO t2 VALUES(32,2); |
| 154 |
# last_committed=6 sequence_number=8 |
| 155 |
UPDATE t2 SET c1=21 WHERE c2=1; |
| 156 |
# last_committed=7 sequence_number=9 |
| 157 |
DELETE FROM t2 WHERE c2=2; |
| 158 |
# last_committed=9 sequence_number=10 |
| 159 |
DELETE FROM t1 WHERE b=2; |
| 160 |
# last_committed=8 sequence_number=11 |
| 161 |
DELETE FROM t2 WHERE c1=21; |
| 162 |
# last_committed=11 sequence_number=12 |
| 163 |
DROP TABLE t2; |
| 164 |
# last_committed=12 sequence_number=13 |
| 165 |
DROP TABLE t1; |
| 166 |
--source include/rpl_sync.inc |
| 167 |
|
| 168 |
--echo |
| 169 |
--echo ##################################################### |
| 170 |
--echo # Dump Group Replication applier channel relay log |
| 171 |
--echo |
| 172 |
--let $rpl_connection_name= server2 |
| 173 |
--source include/rpl_connection.inc |
| 174 |
--let $dump_file=$MYSQLTEST_VARDIR/tmp/group_replication_parallel_applier_indexes.dump |
| 175 |
--exec $MYSQL_BINLOG --force-if-open $server_2_datadir/server-relay-log-group_replication_applier.000003 > $dump_file |
| 176 |
--let $grep_file= $dump_file |
| 177 |
--let $grep_pattern= last_committed |
| 178 |
--let $grep_output= print_each |
| 179 |
--replace_regex /# at [0-9]*/# at #/ /#[0-9]+/#time#/ /[[:space:]]+[0-9]+:[0-9]+:[0-9]+/ ##:##:##/ /end_log_pos [0-9]+/end_log_pos ##/ /original_committed_timestamp=[0-9]*/original_committed_timestamp=##:##:##/ /immediate_commit_timestamp=[0-9]*/immediate_commit_timestamp=##:##:##/ /transaction_length=[0-9]*/transaction_length=#/ |
| 180 |
--source include/grep_pattern.inc |
| 181 |
|
| 182 |
--echo |
| 183 |
--echo ################################ |
| 184 |
--echo # Validate indexes on member 2. |
| 185 |
--echo # |
| 186 |
--let $binlog_file= server-relay-log-group_replication_applier.000003 |
| 187 |
--let $logical_timestamps= 0 0;1 2;2 3;3 4;3 5;4 6;5 7;6 8;7 9;9 10;8 11;11 12;12 13 |
| 188 |
--source include/assert_logical_timestamps.inc |
| 189 |
|
| 190 |
# Cleanup |
| 191 |
--remove_file $dump_file |
| 192 |
--source include/group_replication_end.inc |