Debian Codegraph @

mysql-test/suite/group_replication/t/gr_runtime_verifications.test

raw
1 ################################################################################
2 # The intent of this test is to verify if the validations that were
3 # created within the Group Replication plugin in the runtime process
4 # are fully functional.
5 #
6 # It will test the invalid scenarios:
7 # - Table with no primary key
8 # - Table without InnoDB storage engine
9 # - Table without both
10 # - Table with 'CASCADE' referential key
11 # - Table with 'SET NULL' referential key
12 #
13 # It will cycle all cases in which the runtime validation is verified.
14 #
15 # Afterwards the plugin will be stopped and one of the cases will be tested in
16 # order to assess that the verification is no longer accomplished.
17 #
18 # One will test in a valid table that dynamic variables that can change in
19 # runtime will also cause a failure in validation if they have incorrect values.
20 #
21 # Table t4 in the test case was added because of BUG#21909427 DMLS ON
22 # BLACKHOLE TABLES ARE ALLOWED IN GR, as BLACKHOLE storage engine tables are
23 # considered to be transactional.
24
25 # Tables t5, t6, t7 and t8 in the test case were added because of
26 # BUG#21918361 GR CAN GENERATE INCONSISTENCIES IF THERE ARE MULTIPLE FK
27 # DEPENDENCIES. The decision is to block DML updates if the table contains
28 # foreign key with 'CASCADE' clause.
29 #
30 # Test:
31 # 0. The test requires one server.
32 #
33 # 1. With member being ONLINE.
34 # - Create table without primary key.
35 # - Create table with non-transactional(MyISAM) engine.
36 # - Create table without primary key and with non-transactional(MyISAM) engine.
37 # - Create table without InnoDB storage engine.
38 # - Create auxiliary table with primary key and with InnoDB engine.
39 # - Create table with 'ON UPDATE CASCADE' foreign key.
40 # - Create table with 'ON DELETE CASCADE' foreign key.
41 # - Create table with 'ON DELETE SET NULL' foreign key.
42 # - Create table with 'ON UPDATE SET NULL' foreign key.
43 #
44 # 2. Testing all tables that will fail with following DMLs:
45 # INSERT, UPDATE, INSERT...SELECT, DELETE, LOAD DATA, REPLACE,
46 # REPLACE...SELECT, DELETE_MULTI, UPDATE_MULTI
47 #
48 # 3. Test global variables that might change in runtime and which values are
49 # not allowed in GR. Following settings are tested with DML operation on
50 # auxiliary table:
51 # binlog_format=STATEMENT,
52 # transaction_write_set_extraction=OFF
53 #
54 # 4. Stop GR on member. Now, check following must succeed or fail with other
55 # errors that not related to GR:
56 # INSERT, UPDATE, INSERT...SELECT, DELETE, LOAD DATA, REPLACE,
57 # REPLACE...SELECT, DELETE_MULTI, UPDATE_MULTI
58 #
59 # 5. Clean up.
60 ################################################################################
61
62 --let $group_replication_group_name= 36236980-4307-11e4-916c-0800200c9a67
63 --source include/have_group_replication_plugin.inc
64
65 --source include/start_and_bootstrap_group_replication.inc
66
67 #Lets create several tables. They will all breach 1 or more rules
68 SET SESSION sql_log_bin= 0;
69 call mtr.add_suppression("Table .* does not use the InnoDB storage engine. This is not compatible with Group Replication.");
70 SET SESSION sql_log_bin= 1;
71
72 #Wrong table without Primary Key
73 CREATE TABLE t1 (c1 char(50)) ENGINE=InnoDB;
74
75 #Wrong table without Transactional engine
76 CREATE TABLE t2 (c1 char(50) NOT NULL PRIMARY KEY) ENGINE=MyISAM;
77
78 #Wrong table with both criteria
79 CREATE TABLE t3 (c1 char(50)) ENGINE=MyISAM;
80
81 #Wrong table without InnoDB storage engine
82 CREATE TABLE t4 (c1 char(50) NOT NULL PRIMARY KEY) ENGINE=Blackhole;
83
84 #Auxiliary test table
85 CREATE TABLE tn (cn char(50) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
86
87 # Wrong table with 'ON UPDATE CASCADE' foreign key.
88 CREATE TABLE t5 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES tn(cn) ON UPDATE CASCADE) ENGINE = InnoDB;
89
90 # Wrong table with 'ON UPDATE CASCADE' foreign key.
91 CREATE TABLE t6 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES
92 tn(cn) ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE = InnoDB;
93
94 # Wrong table with 'ON DELETE CASCADE' foreign key.
95 CREATE TABLE t7 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES tn(cn) ON DELETE CASCADE) ENGINE = InnoDB;
96
97 # Wrong table with 'ON DELETE CASCADE' foreign key.
98 CREATE TABLE t8 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES
99 tn(cn) ON DELETE CASCADE ON UPDATE RESTRICT) ENGINE = InnoDB;
100
101 # Wrong table with 'ON DELETE SET NULL' foreign key.
102 CREATE TABLE t9 (c1 char(50), c2 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES
103 tn(cn) ON DELETE SET NULL) ENGINE = InnoDB;
104
105 # Wrong table with 'ON UPDATE SET NULL' foreign key.
106 CREATE TABLE t10 (c1 char(50), c2 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES
107 tn(cn) ON UPDATE SET NULL) ENGINE = InnoDB;
108
109 #
110 # The tests begin here.
111 #
112 # The use cases to test are the following:
113 # - SQLCOM_UPDATE
114 # - SQLCOM_INSERT
115 # - SQLCOM_INSERT_SELECT
116 # - SQLCOM_DELETE
117 # - SQLCOM_LOAD
118 # - SQLCOM_REPLACE
119 # - SQLCOM_REPLACE_SELECT
120 # - SQLCOM_DELETE_MULTI
121 # - SQLCOM_UPDATE_MULTI
122 #
123
124 --echo #
125 --echo # Testing all tables that will fail.
126 --echo #
127
128 --let $wrong_tables_count=10
129 while ($wrong_tables_count)
130 {
131 #Test the INSERT instruction
132 if ($wrong_tables_count >= 9)
133 {
134 --error ER_BEFORE_DML_VALIDATION_ERROR
135 --eval INSERT INTO t$wrong_tables_count VALUES('a','a')
136 }
137 if ($wrong_tables_count < 9)
138 {
139 --error ER_BEFORE_DML_VALIDATION_ERROR
140 --eval INSERT INTO t$wrong_tables_count VALUES('a')
141 }
142
143 #Test the UPDATE instruction
144 --error ER_BEFORE_DML_VALIDATION_ERROR
145 --eval UPDATE t$wrong_tables_count SET c1 = 'a'
146
147 #Test the INSERT...SELECT instruction
148 --error ER_BEFORE_DML_VALIDATION_ERROR
149 --eval UPDATE t$wrong_tables_count SET c1 = 'a'
150
151 #Test the INSERT...SELECT instruction
152 if ($wrong_tables_count >= 9)
153 {
154 --error ER_BEFORE_DML_VALIDATION_ERROR
155 --eval INSERT INTO t$wrong_tables_count (c1,c2) SELECT tn.cn,'a' FROM tn
156 }
157 if ($wrong_tables_count < 9)
158 {
159 --error ER_BEFORE_DML_VALIDATION_ERROR
160 --eval INSERT INTO t$wrong_tables_count (c1) SELECT tn.cn FROM tn
161 }
162
163 #Test the DELETE instruction
164 --error ER_BEFORE_DML_VALIDATION_ERROR
165 --eval DELETE FROM t$wrong_tables_count
166
167 #Test the load instruction
168 --error ER_BEFORE_DML_VALIDATION_ERROR
169 --eval LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t$wrong_tables_count
170
171 #Test the REPLACE instruction
172 --error ER_BEFORE_DML_VALIDATION_ERROR
173 --eval REPLACE INTO t$wrong_tables_count(c1) VALUES('a')
174
175 #Test the REPLACE...SELECT instruction
176 --error ER_BEFORE_DML_VALIDATION_ERROR
177 --eval REPLACE INTO t$wrong_tables_count (c1) SELECT tn.cn FROM tn
178
179 #Test the DELETE_MULTI instruction
180 --error ER_BEFORE_DML_VALIDATION_ERROR
181 --eval DELETE t$wrong_tables_count, tn FROM t$wrong_tables_count, tn
182
183 #Test the UPDATE_MULTI instruction
184 --error ER_BEFORE_DML_VALIDATION_ERROR
185 --eval UPDATE t$wrong_tables_count, tn SET c1 = 'a'
186
187 #clean up this table
188 --eval DROP TABLE t$wrong_tables_count
189
190 --dec $wrong_tables_count
191 }
192
193 --echo #
194 --echo # Lets test the global variables that might change in
195 --echo # runtime and which values are not allowed.
196 --echo #
197
198 #Test the value of binlog format.
199
200 --let $binlog_format_backup= `SELECT @@GLOBAL.binlog_format`
201
202 SET SESSION binlog_format= STATEMENT;
203
204 --error ER_BEFORE_DML_VALIDATION_ERROR
205 INSERT INTO tn VALUES ('a');
206
207 --eval SET SESSION binlog_format= "$binlog_format_backup"
208
209 #Test the value of transaction_write_set_extraction cannot be changed
210
211 --let $transaction_write_set_extraction_backup= `SELECT @@GLOBAL.transaction_write_set_extraction`
212
213 --error ER_GROUP_REPLICATION_RUNNING
214 SET SESSION transaction_write_set_extraction=OFF;
215
216 INSERT INTO tn VALUES ('hash');
217
218 --echo #
219 --echo # Now, lets repeat all the tests with group replication stopped.
220 --echo # They all must succeed or fail with other errors that not the
221 --echo # ones from validation.
222 --echo #
223 --echo # It will be a single table simpler test just to assess that the
224 --echo # hook is not longer active.
225 --echo #
226 --source include/stop_group_replication.inc
227
228 #Wrong table without Primary Key
229 CREATE TABLE t1 (c1 char(50)) ENGINE=InnoDB;
230
231 #Test the UPDATE instruction
232 --eval UPDATE t1 SET c1 = 'a'
233
234 #Test the INSERT instruction
235 --eval INSERT INTO t1 VALUES('a')
236
237 #Test the INSERT...SELECT instruction
238 --eval INSERT INTO t1 (c1) SELECT tn.cn FROM tn
239
240 #Test the DELETE instruction
241 --eval DELETE FROM t1
242
243 #Test the load instruction
244 --eval LOAD DATA INFILE '../../std_data/words2.dat' INTO TABLE t1
245
246 #Test the REPLACE instruction
247 --eval REPLACE INTO t1(c1) VALUES('a')
248
249 #Test the REPLACE...SELECT instruction
250 --eval REPLACE INTO t1 (c1) SELECT tn.cn FROM tn
251
252 #Test the DELETE_MULTI instruction
253 --eval DELETE t1, tn FROM t1, tn
254
255 #Test the UPDATE_MULTI instruction
256 --eval UPDATE t1, tn SET c1 = 'a'
257
258 #clean up
259 DROP TABLE t1, tn;
260
261 call mtr.add_suppression("Table.*is not transactional. This is not compatible with Group Replication");
262 call mtr.add_suppression("Table.*does not have any PRIMARY KEY. This is not compatible with Group Replication.");
263 call mtr.add_suppression("Table.*has a foreign key with 'CASCADE', 'SET NULL' or 'SET DEFAULT' clause. This is not compatible with Group Replication.");
264 call mtr.add_suppression("Binlog format should be ROW for Group Replication");
265 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
266
267 --source include/gr_clear_configuration.inc