1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
|
connection node_2;
connection node_1;
connection node_1;
SET GLOBAL wsrep_mode = REPLICATE_MYISAM;
connection node_2;
SET GLOBAL wsrep_mode = REPLICATE_MYISAM;
connection node_1;
CREATE TABLE t1 (f1 INT PRIMARY KEY) Engine=MyISAM;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2), (3);
INSERT INTO t1 SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL;
connection node_2;
SELECT COUNT(*) AS EXPECT_5 FROM t1;
EXPECT_5
5
DROP TABLE t1;
connection node_1;
CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 VARCHAR(100)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1, 'abc'),(2,'abc'), (3, 'xxx');
REPLACE INTO t1 VALUES (1, 'klm'), (2,'xyz');
REPLACE INTO t1 SELECT 3, 'yyy' FROM DUAL;
connection node_2;
SELECT COUNT(*) AS EXPECT_3 FROM t1;
EXPECT_3
3
SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 1 AND f2 = 'klm';
EXPECT_1
1
SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 2 AND f2 = 'xyz';
EXPECT_1
1
SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f1 = 3 AND f2 = 'yyy';
EXPECT_1
1
connection node_1;
UPDATE t1 SET f2 = 'zzz' WHERE f2 = 'yyy';
connection node_2;
SELECT COUNT(*) AS EXPECT_1 FROM t1 WHERE f2 = 'zzz';
EXPECT_1
1
connection node_1;
DELETE FROM t1 WHERE f2 = 'zzz';
connection node_2;
SELECT COUNT(*) AS EXPECT_0 FROM t1 WHERE f2 = 'zzz';
EXPECT_0
0
connection node_1;
TRUNCATE TABLE t1;
connection node_2;
SELECT COUNT(*) AS EXPECT_0 FROM t1;
EXPECT_0
0
DROP TABLE t1;
connection node_1;
CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE t2 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
SET AUTOCOMMIT=OFF;
START TRANSACTION;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
connection node_2;
SELECT COUNT(*) AS EXPECT_1 FROM t1;
EXPECT_1
1
SELECT COUNT(*) AS EXPECT_1 FROM t2;
EXPECT_1
1
connection node_1;
START TRANSACTION;
INSERT INTO t1 VALUES (2);
INSERT INTO t2 VALUES (2);
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
connection node_2;
SELECT COUNT(*) AS EXPECT_2 FROM t1;
EXPECT_2
2
SELECT COUNT(*) AS EXPECT_1 FROM t2;
EXPECT_1
1
DROP TABLE t1;
DROP TABLE t2;
connection node_1;
CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE t2 (f2 INTEGER PRIMARY KEY) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
connection node_2;
INSERT INTO t1 VALUES (1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
connection node_1;
COMMIT;
DROP TABLE t1, t2;
connection node_1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=MyISAM;
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
PREPARE upd from 'update t1 set b = 100 where id = 5';
PREPARE ins from 'insert into t1 values (11,11)';
PREPARE del from 'delete from t1 where id = 4';
PREPARE rep from 'replace into t1 values (12,12),(6,600)';
EXECUTE upd;
EXECUTE ins;
EXECUTE del;
EXECUTE rep;
SELECT * FROM t1 ORDER BY id;
id b
1 1
2 2
3 3
5 100
6 600
7 7
8 8
9 9
10 10
11 11
12 12
connection node_2;
SELECT * FROM t1 ORDER BY id;
id b
1 1
2 2
3 3
5 100
6 600
7 7
8 8
9 9
10 10
11 11
12 12
DROP TABLE t1;
connection node_1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=MyISAM;
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
CREATE PROCEDURE proc()
BEGIN
UPDATE t1 set b = 100 WHERE id = 5;
INSERT INTO t1 VALUES (11,11);
DELETE FROM t1 WHERE id = 4;
REPLACE INTO t1 VALUES (12,12),(6,600);
COMMIT;
END|
CALL proc();
SELECT * FROM t1 ORDER BY id;
id b
1 1
2 2
3 3
5 100
6 600
7 7
8 8
9 9
10 10
11 11
12 12
connection node_2;
SELECT * FROM t1 ORDER BY id;
id b
1 1
2 2
3 3
5 100
6 600
7 7
8 8
9 9
10 10
11 11
12 12
DROP PROCEDURE proc;
DROP TABLE t1;
connection node_1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT) ENGINE=MyISAM;
CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT) ENGINE=MyISAM;
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, NEW.b);
CREATE TRIGGER tr2 BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, OLD.b),(NULL, NEW.b);
CREATE TRIGGER tr3 BEFORE DELETE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, OLD.b);
INSERT INTO t1 VALUES (11,11);
UPDATE t1 SET b = 200 WHERE id = 2;
DELETE FROM t1 where id = 5;
SELECT * FROM t1 ORDER BY id;
id b
1 1
2 200
3 3
4 4
6 6
7 7
8 8
9 9
10 10
11 11
SELECT * FROM t2 ORDER BY id;
id b
1 11
2 2
3 200
4 5
connection node_2;
SELECT COUNT(*) FROM t1;
COUNT(*)
10
SELECT * FROM t1 ORDER BY id;
id b
1 1
2 200
3 3
4 4
6 6
7 7
8 8
9 9
10 10
11 11
SELECT * FROM t2 ORDER BY id;
id b
1 11
2 2
3 200
4 5
DROP TRIGGER tr1;
DROP TRIGGER tr2;
DROP TRIGGER tr3;
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT, b INT, UNIQUE(a)) ENGINE=MyISAM;
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
INSERT INTO t1 (a,b) VALUES (10,20);
SELECT * from t1;
a b
1 20
connection node_2;
SELECT * from t1;
a b
1 20
connection node_1;
DROP TABLE t1;
#
# MDEV-11152: wsrep_replicate_myisam: SELECT gets replicated using TO
#
connection node_1;
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
i
1
DROP TABLE t1;
SET GLOBAL wsrep_mode = DEFAULT;
connection node_2;
SET GLOBAL wsrep_mode = DEFAULT;
|
