Debian Codegraph @

mysql-test/suite/galera/t/galera_var_replicate_myisam_on.test

raw
1 #
2 # Simple test for wsrep-mode = REPLICATE_MYISAM
3 #
4
5 --source include/galera_cluster.inc
6 --source include/have_innodb.inc
7
8 --connection node_1
9 SET GLOBAL wsrep_mode = REPLICATE_MYISAM;
10 --connection node_2
11 SET GLOBAL wsrep_mode = REPLICATE_MYISAM;
12
13 #
14 # Simple INSERT
15 #
16
17 --connection node_1
18 CREATE TABLE t1 (f1 INT PRIMARY KEY) Engine=MyISAM;
19 INSERT INTO t1 VALUES (1);
20 INSERT INTO t1 VALUES (2), (3);
21 INSERT INTO t1 SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL;
22
23 --connection node_2
24 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
25 --source include/wait_condition.inc
26 --let $wait_condition = SELECT COUNT(*) = 5 FROM t1;
27 --source include/wait_condition.inc
28
29 SELECT COUNT(*) AS EXPECT_5 FROM t1;
30
31 DROP TABLE t1;
32
33 #
34 # REPLACE
35 #
36
37 --connection node_1
38 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 VARCHAR(100)) ENGINE=MyISAM;
39 INSERT INTO t1 VALUES (1, 'abc'),(2,'abc'), (3, 'xxx');
40 REPLACE INTO t1 VALUES (1, 'klm'), (2,'xyz');
41 REPLACE INTO t1 SELECT 3, 'yyy' FROM DUAL;
42
43 --connection node_2
44 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
45 --source include/wait_condition.inc
46 --let $wait_condition = SELECT COUNT(*) = 3 FROM t1;
47 --source include/wait_condition.inc
48 --let $wait_condition = SELECT COUNT(*) = 1 FROM t1 WHERE f1 = 3 AND f2 = 'yyy';
49 --source include/wait_condition.inc
50
51 SELECT COUNT(*) AS EXPECT_3 FROM t1;
52 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 1 AND f2 = 'klm';
53 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 2 AND f2 = 'xyz';
54 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 3 AND f2 = 'yyy';
55
56 #
57 # UPDATE
58 #
59
60 --connection node_1
61 UPDATE t1 SET f2 = 'zzz' WHERE f2 = 'yyy';
62
63 --connection node_2
64 --let $wait_condition = SELECT COUNT(*) = 1 FROM t1 WHERE f2 = 'zzz';
65 --source include/wait_condition.inc
66
67 SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f2 = 'zzz';
68
69 #
70 # DELETE
71 #
72
73 --connection node_1
74 DELETE FROM t1 WHERE f2 = 'zzz';
75
76 --connection node_2
77 --let $wait_condition = SELECT COUNT(*) = 0 FROM t1 WHERE f2 = 'zzz';
78 --source include/wait_condition.inc
79
80 SELECT COUNT(*) AS EXPECT_0 FROM t1 WHERE f2 = 'zzz';
81
82 #
83 # TRUNCATE
84 #
85
86 --connection node_1
87 TRUNCATE TABLE t1;
88
89 --connection node_2
90 --let $wait_condition = SELECT COUNT(*) = 0 FROM t1;
91 --source include/wait_condition.inc
92
93 SELECT COUNT(*) AS EXPECT_0 FROM t1;
94 DROP TABLE t1;
95
96 #
97 # Transaction
98 #
99
100 --connection node_1
101 CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM;
102 CREATE TABLE t2 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
103 SET AUTOCOMMIT=OFF;
104 START TRANSACTION;
105 INSERT INTO t1 VALUES (1);
106 INSERT INTO t2 VALUES (1);
107 COMMIT;
108
109 --connection node_2
110 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
111 --source include/wait_condition.inc
112 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't2';
113 --source include/wait_condition.inc
114 --let $wait_condition = SELECT COUNT(*) = 1 FROM t1;
115 --source include/wait_condition.inc
116 --let $wait_condition = SELECT COUNT(*) = 1 FROM t2;
117 --source include/wait_condition.inc
118
119 SELECT COUNT(*) AS EXPECT_1 FROM t1;
120 SELECT COUNT(*) AS EXPECT_1 FROM t2;
121
122 #
123 # Transaction rollback
124 #
125
126 --connection node_1
127 START TRANSACTION;
128 INSERT INTO t1 VALUES (2);
129 INSERT INTO t2 VALUES (2);
130 ROLLBACK;
131
132 --connection node_2
133 --let $wait_condition = SELECT COUNT(*) = 2 FROM t1;
134 --source include/wait_condition.inc
135 --let $wait_condition = SELECT COUNT(*) = 1 FROM t2;
136 --source include/wait_condition.inc
137
138 SELECT COUNT(*) AS EXPECT_2 FROM t1;
139 SELECT COUNT(*) AS EXPECT_1 FROM t2;
140
141 DROP TABLE t1;
142 DROP TABLE t2;
143
144 #
145 # Transaction conflict
146 #
147
148 --connection node_1
149 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=MyISAM;
150 CREATE TABLE t2 (f2 INTEGER PRIMARY KEY) ENGINE=InnoDB;
151
152 START TRANSACTION;
153 INSERT INTO t1 VALUES (1);
154 INSERT INTO t2 VALUES (1);
155
156 --connection node_2
157 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
158 --source include/wait_condition.inc
159 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't2';
160 --source include/wait_condition.inc
161 --let $wait_condition = SELECT COUNT(*) = 1 FROM t1;
162 --source include/wait_condition.inc
163 # The MyISAM update is replicated when executed, so a duplicate key error happens even before the COMMIT
164 --error ER_DUP_ENTRY
165 INSERT INTO t1 VALUES (1);
166
167 --connection node_1
168 COMMIT;
169 DROP TABLE t1, t2;
170
171 #
172 # Test prepared staments
173 #
174 --connection node_1
175 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=MyISAM;
176 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
177
178 PREPARE upd from 'update t1 set b = 100 where id = 5';
179 PREPARE ins from 'insert into t1 values (11,11)';
180 PREPARE del from 'delete from t1 where id = 4';
181 PREPARE rep from 'replace into t1 values (12,12),(6,600)';
182
183 EXECUTE upd;
184 EXECUTE ins;
185 EXECUTE del;
186 EXECUTE rep;
187
188 SELECT * FROM t1 ORDER BY id;
189
190 --connection node_2
191 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
192 --source include/wait_condition.inc
193 --let $wait_condition = SELECT COUNT(*) = 11 FROM t1;
194 --source include/wait_condition.inc
195 SELECT * FROM t1 ORDER BY id;
196
197 DROP TABLE t1;
198
199 #
200 # Test procedure
201 #
202 --connection node_1
203 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=MyISAM;
204 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
205
206 DELIMITER |;
207 CREATE PROCEDURE proc()
208 BEGIN
209 UPDATE t1 set b = 100 WHERE id = 5;
210 INSERT INTO t1 VALUES (11,11);
211 DELETE FROM t1 WHERE id = 4;
212 REPLACE INTO t1 VALUES (12,12),(6,600);
213 COMMIT;
214 END|
215 DELIMITER ;|
216
217 CALL proc();
218 SELECT * FROM t1 ORDER BY id;
219
220 --connection node_2
221 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
222 --source include/wait_condition.inc
223 --let $wait_condition = SELECT COUNT(*) = 11 FROM t1;
224 --source include/wait_condition.inc
225 SELECT * FROM t1 ORDER BY id;
226
227 DROP PROCEDURE proc;
228 DROP TABLE t1;
229
230 #
231 # Test trigger
232 #
233 --connection node_1
234 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=MyISAM;
235 CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT) ENGINE=MyISAM;
236 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
237 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, NEW.b);
238 CREATE TRIGGER tr2 BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, OLD.b),(NULL, NEW.b);
239 CREATE TRIGGER tr3 BEFORE DELETE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, OLD.b);
240
241 INSERT INTO t1 VALUES (11,11);
242 UPDATE t1 SET b = 200 WHERE id = 2;
243 DELETE FROM t1 where id = 5;
244 SELECT * FROM t1 ORDER BY id;
245 SELECT * FROM t2 ORDER BY id;
246
247 --connection node_2
248 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
249 --source include/wait_condition.inc
250 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't2';
251 --source include/wait_condition.inc
252 SELECT COUNT(*) FROM t1;
253 --let $wait_condition = SELECT COUNT(*) = 10 FROM t1;
254 --source include/wait_condition.inc
255 SELECT * FROM t1 ORDER BY id;
256 SELECT * FROM t2 ORDER BY id;
257 DROP TRIGGER tr1;
258 DROP TRIGGER tr2;
259 DROP TRIGGER tr3;
260 DROP TABLE t1, t2;
261
262 CREATE TABLE t1 (a INT, b INT, UNIQUE(a)) ENGINE=MyISAM;
263 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
264 INSERT INTO t1 (a,b) VALUES (10,20);
265 SELECT * from t1;
266
267 --connection node_2
268 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
269 --source include/wait_condition.inc
270 --let $wait_condition = SELECT COUNT(*) = 1 FROM t1;
271 --source include/wait_condition.inc
272
273 SELECT * from t1;
274 --connection node_1
275 DROP TABLE t1;
276
277 --echo #
278 --echo # MDEV-11152: wsrep_replicate_myisam: SELECT gets replicated using TO
279 --echo #
280 --connection node_1
281 CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=INNODB;
282 INSERT INTO t1 VALUES(1);
283 # This command should not get replicated.
284 SELECT * FROM t1;
285 DROP TABLE t1;
286
287 SET GLOBAL wsrep_mode = DEFAULT;
288
289 --connection node_2
290 SET GLOBAL wsrep_mode = DEFAULT;