Debian Codegraph @

mysql-test/suite/galera/t/galera_ddl_fk_conflict.inc

raw
1 #
2 # Test for MDL BF-BF lock conflict
3 # There are some DDL statements, which take extensive MDL lock for
4 # a table referenced by foreign key constraint from the actual affetec table.
5 # This extensive MDL lock may cause MDL BF-BF confclict situations, if the
6 # FK parent table is not listed as certification key in the replication write set.
7 # i.e. if replication allows such DDL to apply in parallel with regular DML operating
8 # on the FK parent table.
9 #
10 # This test has two scenarios, where DML modifies FK parent table in node 1,
11 # and offending DDL for FK child table is sent from node 2.
12 #
13 # param: $table_admin_command
14 # DDL table command to test, script will build full SQL statement:
15 # $table_admin_command TABLE c;
16 #
17 # param: $table_admin_command_end
18 # Optional additional SQL syntax to end the SQL statement, if any
19 # $table_admin_command TABLE c $table_admin_command_end;
20 #
21 # scenario 1, can be used to test if a DDL statement causes such MDL locking vulnerability.
22 # call this test script with some table DDL command in $table_admin_command
23 # if scenario 1 passes (especially COMMIT does fail for ER_LOCK_DEADLOCK),
24 # then this particular DDL is vulnerable. scenraio 2 should fail for this DDL
25 # unless code has not been fixed to append parent table certification keys for it.
26 #
27
28 --echo ######################################################################
29 --echo # Test for $table_admin_command $table_admin_command_end
30 --echo ######################################################################
31
32
33 --echo ######################################################################
34 --echo #
35 --echo # Scenario #1: DML working on FK parent table BF aborted by DDL
36 --echo # over child table
37 --echo #
38 --echo ######################################################################
39
40 --connection node_1
41 SET SESSION wsrep_sync_wait=0;
42 FLUSH STATUS;
43
44 CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
45 INSERT INTO p1 VALUES (1, 'INITIAL VALUE');
46
47
48 CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30));
49 INSERT INTO p2 VALUES (1, 'INITIAL VALUE');
50 INSERT INTO p2 VALUES (2, 'INITIAL VALUE');
51
52 CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
53 INSERT INTO c1 VALUES (1,1);
54
55 CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk));
56 INSERT INTO c2 VALUES (1,1,1), (2,1,2);
57
58 --connection node_1
59 SET AUTOCOMMIT=ON;
60 START TRANSACTION;
61
62 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
63
64 --connection node_2
65 SET SESSION wsrep_sync_wait=0;
66 # wait for tables to be created in node 2 and all rows inserted as well
67 --let $wait_condition = SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/c%'
68 --source include/wait_condition.inc
69 --let $wait_condition = SELECT COUNT(*) = 2 FROM c2
70 --source include/wait_condition.inc
71
72 # replicate the DDL to be tested
73 --eval $table_admin_command c1 $table_admin_command_end
74
75 --connection node_1
76 --error ER_LOCK_DEADLOCK
77 COMMIT;
78
79 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
80 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
81
82 --connection node_2
83 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
84 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
85
86 --connection node_1
87 --disable_warnings
88 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
89 INSERT IGNORE INTO c1 VALUES (1,1);
90 --enable_warnings
91
92 --echo ######################################################################
93 --echo #
94 --echo # Scenario #2: DML working on FK parent table tries to replicate, but
95 --echo # fails in certification for earlier DDL on child table
96 --echo #
97 --echo ######################################################################
98
99 --connection node_1
100 BEGIN;
101
102 # Block the applier on node #1 and issue DDL on node 2
103 --let $galera_sync_point = apply_monitor_slave_enter_sync
104 --source include/galera_set_sync_point.inc
105
106 --connection node_2
107 --eval $table_admin_command c1 $table_admin_command_end
108
109 --connection node_1a
110 --source include/galera_wait_sync_point.inc
111 --source include/galera_clear_sync_point.inc
112
113 --let $expected_cert_failures = `SELECT VARIABLE_VALUE+1 FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'`
114
115 --connection node_1
116 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
117 --send COMMIT
118
119 --connection node_1a
120 --let $wait_condition = SELECT VARIABLE_VALUE = $expected_cert_failures FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'
121 --let $wait_condition_on_error_output = SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'
122 --source include/wait_condition_with_debug.inc
123
124 --let $galera_sync_point = apply_monitor_slave_enter_sync
125 --source include/galera_signal_sync_point.inc
126
127 --connection node_1
128 --error ER_LOCK_DEADLOCK
129 --reap
130
131 SELECT 'I deadlocked';
132
133 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
134 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
135
136 --connection node_2
137 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
138 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
139
140 --connection node_1
141 --disable_warnings
142 CREATE TABLE IF NOT EXISTS c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk));
143 INSERT IGNORE INTO c1 VALUES (1,1);
144 --enable_warnings
145
146 --echo ######################################################################
147 --echo #
148 --echo # Scenario #3: 2 DMLs working on two FK parent tables try to replicate,
149 --echo # but fails in certification for earlier DDL on child table
150 --echo # which is child to both FK parents
151 --echo #
152 --echo ######################################################################
153
154 --connection node_1
155 BEGIN;
156
157 --connection node_1b
158 BEGIN;
159
160 --connection node_1a
161 # Block the applier on node #1 and issue DDL on node 2
162 --let $galera_sync_point = apply_monitor_slave_enter_sync
163 --source include/galera_set_sync_point.inc
164
165 --connection node_2
166 --eval $table_admin_command c2 $table_admin_command_end
167
168 --connection node_1a
169 --source include/galera_wait_sync_point.inc
170 --source include/galera_clear_sync_point.inc
171 --let $expected_cert_failures = `SELECT VARIABLE_VALUE+2 FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'`
172 --let $wait_condition_on_error_output = SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'
173 --source include/wait_condition_with_debug.inc
174
175 --connection node_1
176 UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1;
177 --send COMMIT
178
179 --connection node_1b
180 UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2;
181 --send COMMIT
182
183 --connection node_1a
184 --let $wait_condition = SELECT VARIABLE_VALUE = $expected_cert_failures FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'
185 --let $wait_condition_on_error_output = SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'
186 --source include/wait_condition_with_debug.inc
187
188 --let $galera_sync_point = apply_monitor_slave_enter_sync
189 --source include/galera_signal_sync_point.inc
190
191 --connection node_1
192 --error ER_LOCK_DEADLOCK
193 --reap
194 SELECT 'I deadlocked';
195
196 --connection node_1b
197 --error ER_LOCK_DEADLOCK
198 --reap
199 SELECT 'I deadlocked';
200
201 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
202 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
203
204 --connection node_2
205 SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE';
206 SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE';
207
208 DROP TABLE IF EXISTS c1, c2;
209 DROP TABLE p1, p2;