Debian Codegraph @

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

raw
1 --source include/galera_cluster.inc
2 --source include/have_innodb.inc
3 --source include/have_sequence.inc
4 --source include/have_aria.inc
5
6 --disable_ps2_protocol
7 #
8 # MDEV-19353 : Alter Sequence do not replicate to another nodes with in Galera Cluster
9 #
10
11 --connection node_1
12 CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB;
13 SHOW CREATE SEQUENCE seq;
14
15 --connection node_2
16 SHOW CREATE SEQUENCE seq;
17
18 --connection node_1
19 ALTER SEQUENCE seq MAXVALUE = 10000 NOCACHE;
20 SHOW CREATE SEQUENCE seq;
21
22 --connection node_2
23 SHOW CREATE SEQUENCE seq;
24
25 --connection node_1
26 DROP SEQUENCE seq;
27 --error ER_NO_SUCH_TABLE
28 SHOW CREATE SEQUENCE seq;
29
30 --connection node_2
31 --error ER_NO_SUCH_TABLE
32 SHOW CREATE SEQUENCE seq;
33
34 #
35 # MDEV-18848 : Galera: 10.4 node crashed with Assertion `client_state.transaction().active()` after altering SEQUENCE table's engine to myisam and back to innodb
36 #
37 --connection node_1
38 CREATE SEQUENCE Seq1_1 START WITH 1 INCREMENT BY 1 NOCACHE;
39 select NEXT VALUE FOR Seq1_1;
40 --error ER_NOT_SUPPORTED_YET
41 alter table Seq1_1 engine=myisam;
42 select NEXT VALUE FOR Seq1_1;
43 alter table Seq1_1 engine=innodb;
44 select NEXT VALUE FOR Seq1_1;
45
46 --connection node_2
47 SHOW CREATE SEQUENCE Seq1_1;
48 select NEXT VALUE FOR Seq1_1;
49
50 --connection node_1
51 SHOW CREATE SEQUENCE Seq1_1;
52 DROP SEQUENCE Seq1_1;
53
54 #
55 # MDEV-24045 : Assertion client_state_.mode() != wsrep::client_state::m_toi failed in int wsrep::transaction::before_commit()
56 #
57
58 --connection node_1
59 CREATE TABLE t2 (d CHAR(1)KEY);
60 SET SESSION autocommit=0;
61 INSERT INTO t2 VALUES(1);
62 CREATE TEMPORARY SEQUENCE seq1 NOCACHE ENGINE=INNODB;
63 CREATE SEQUENCE seq2 NOCACHE ENGINE=INNODB;
64 COMMIT;
65 SET SESSION AUTOCOMMIT=1;
66 SHOW CREATE TABLE seq1;
67 --connection node_2
68 --error ER_NO_SUCH_TABLE
69 SHOW CREATE SEQUENCE seq1;
70 SHOW CREATE SEQUENCE seq2;
71 --connection node_1
72 SET SESSION autocommit=1;
73 DROP SEQUENCE seq1;
74 DROP SEQUENCE seq2;
75 DROP TABLE t2;
76 #
77 # Case2
78 #
79 --connection node_2
80 SET SESSION AUTOCOMMIT=0;
81 SET SESSION wsrep_OSU_method='RSU';
82 CREATE TABLE t1(c1 VARCHAR(10));
83 create temporary sequence sq1 NOCACHE engine=innodb;
84 create sequence sq2 NOCACHE engine=innodb;
85 COMMIT;
86 SET SESSION wsrep_OSU_method='TOI';
87 SHOW CREATE SEQUENCE sq1;
88 SHOW CREATE SEQUENCE sq2;
89 --connection node_1
90 --error ER_NO_SUCH_TABLE
91 SHOW CREATE SEQUENCE sq1;
92 --error ER_NO_SUCH_TABLE
93 SHOW CREATE SEQUENCE sq2;
94 --connection node_2
95 SET SESSION AUTOCOMMIT=1;
96 DROP TABLE t1;
97 DROP SEQUENCE sq1;
98 DROP SEQUENCE sq2;
99
100 #
101 # MDEV-30388 Assertion `!wsrep_has_changes(thd) || (thd->lex->sql_command == SQLCOM_CREATE_TABLE
102 # && !thd->is_current_stmt_binlog_format_row()) ||
103 # thd->wsrep_cs().transaction().state() == wsrep::transaction::s_aborted' failed
104 #
105 --connection node_1
106 CREATE TABLE t (f INT) engine=innodb;
107 LOCK TABLE t WRITE;
108 CREATE OR REPLACE SEQUENCE t MAXVALUE=13 INCREMENT BY 1 NOCACHE engine=innodb;
109 --error ER_NOT_SUPPORTED_YET
110 LOCK TABLE t WRITE;
111 INSERT INTO t VALUES (0,0,1,1,1,0,0,0);
112 SELECT * from t;
113 SELECT NEXTVAL(t);
114 UNLOCK TABLES;
115 DROP TABLE t;
116
117 CREATE SEQUENCE t INCREMENT BY 0 NOCACHE ENGINE=INNODB;
118 CREATE TABLE t1(a int not null primary key default nextval(t),
119 b int) engine=innodb;
120 INSERT INTO t1(b) VALUES (1),(2),(3);
121 SELECT * FROM t1;
122
123 --connection node_2
124 SELECT * FROM t1;
125 INSERT INTO t1(b) VALUES (4),(5),(6);
126 SELECT * FROM t1;
127
128 --connection node_1
129 SELECT * FROM t1;
130 DROP TABLE t1;
131 DROP SEQUENCE t;
132
133 #
134 # Test Galera SEQUENCE support
135 #
136 #
137 # No MyISAM SEQUENCES
138 #
139 --error ER_NOT_SUPPORTED_YET
140 CREATE SEQUENCE t ENGINE=MYISAM;
141 --error ER_NO_SUCH_TABLE
142 SHOW CREATE SEQUENCE t;
143 --error ER_NO_SUCH_TABLE
144 SHOW CREATE TABLE t;
145
146 --connection node_2
147 # Verify that above MyISAM sequence does not replicate
148 --error ER_NO_SUCH_TABLE
149 SHOW CREATE SEQUENCE t;
150 --error ER_NO_SUCH_TABLE
151 SHOW CREATE TABLE t;
152
153 --connection node_1
154 CREATE SEQUENCE t NOCACHE ENGINE=InnoDB;
155 --error ER_NOT_SUPPORTED_YET
156 ALTER TABLE t ENGINE=MyISAM;
157 SHOW CREATE SEQUENCE t;
158 SHOW CREATE TABLE t;
159
160 --connection node_2
161 SHOW CREATE SEQUENCE t;
162 SHOW CREATE TABLE t;
163
164 --connection node_1
165 DROP SEQUENCE t;
166 # Don't use NOCACHE as it may produce duplicate entries from multiple nodes
167 CREATE SEQUENCE t INCREMENT BY 0 CACHE 1 ENGINE=INNODB;
168 CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
169
170 --connection node_2
171 --echo # Wait DDL to replicate
172 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't'
173 --source include/wait_condition.inc
174 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'
175 --source include/wait_condition.inc
176
177 #
178 # Below we do not care order of INSERTs we care only that values are unique
179 #
180 --connection node_1
181 SELECT @@auto_increment_increment;
182 SELECT @@auto_increment_offset;
183 --let $wsrep_sync_wait_orig_1 = `SELECT @@wsrep_sync_wait`
184 SET SESSION wsrep_sync_wait=0;
185
186 --connection node_2
187 SELECT @@auto_increment_increment;
188 SELECT @@auto_increment_offset;
189 --let $wsrep_sync_wait_orig_2 = `SELECT @@wsrep_sync_wait`
190 SET SESSION wsrep_sync_wait=0;
191
192 --let $count = 20
193 --disable_query_log
194 while ($count)
195 {
196 --connection node_1
197 INSERT INTO t1(b) values (1);
198 --connection node_2
199 INSERT INTO t1(b) values (2);
200 INSERT INTO t1(b) values (2);
201 --connection node_1
202 INSERT INTO t1(b) values (1);
203 --dec $count
204 }
205 --enable_query_log
206
207 --connection node_1
208 --disable_query_log
209 --eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_1
210 --enable_query_log
211
212 --connection node_2
213 --disable_query_log
214 --eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_2
215 --enable_query_log
216
217 --connection node_1
218 DROP SEQUENCE t;
219 DROP TABLE t1;
220 CREATE SEQUENCE t INCREMENT BY 0 NOCACHE ENGINE=INNODB;
221 DROP SEQUENCE t;
222 --error ER_NOT_SUPPORTED_YET
223 CREATE SEQUENCE t INCREMENT BY 1 CACHE=20 ENGINE=INNODB;
224
225 CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
226 CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
227
228 --connection node_2
229 --echo # Wait DDL to replicate
230 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't'
231 --source include/wait_condition.inc
232 --let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'
233 --source include/wait_condition.inc
234
235 #
236 # Below we do not care order of INSERTs we care only that values are unique
237 #
238 --connection node_1
239 --let $wsrep_sync_wait_orig_1 = `SELECT @@wsrep_sync_wait`
240 SET SESSION wsrep_sync_wait=0;
241
242 --connection node_2
243 --let $wsrep_sync_wait_orig_2 = `SELECT @@wsrep_sync_wait`
244 SET SESSION wsrep_sync_wait=0;
245
246 --let $count = 5
247 --disable_query_log
248 while ($count)
249 {
250 --connection node_1
251 INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
252 --connection node_2
253 INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
254 INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
255 --connection node_1
256 INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
257 --dec $count
258 }
259 --enable_query_log
260
261 --connection node_1
262 --disable_query_log
263 --eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_1
264 --enable_query_log
265
266 --connection node_2
267 --disable_query_log
268 --eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_2
269 --enable_query_log
270
271 --connection node_1
272 DROP SEQUENCE t;
273 DROP TABLE t1;
274
275 #
276 # Test ALTER table to sequence and ALTER SEQUENCE
277 #
278 CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
279 --error ER_NOT_SUPPORTED_YET
280 ALTER TABLE t ENGINE=MYISAM;
281 --error ER_NOT_SUPPORTED_YET
282 ALTER SEQUENCE t INCREMENT BY 1 CACHE=10;
283 ALTER SEQUENCE t INCREMENT BY 1 NOCACHE;
284 ALTER SEQUENCE t INCREMENT BY 0 NOCACHE;
285 ALTER SEQUENCE t INCREMENT BY 0 CACHE=10;
286 DROP SEQUENCE t;
287
288 #
289 # Test transactions
290 #
291 CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
292 CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
293 #
294 # ROLLBACK TRX
295 #
296 BEGIN;
297 INSERT INTO t1(b) VALUES (1);
298 INSERT INTO t1(b) VALUES (2);
299 INSERT INTO t1(b) VALUES (3);
300 INSERT INTO t1(b) VALUES (4);
301 INSERT INTO t1(a,b) VALUES (2,2);
302 --error ER_DUP_ENTRY
303 INSERT INTO t1(a,b) VALUES (3,2);
304 ROLLBACK;
305 SELECT * FROM t1;
306 SELECT NEXTVAL(t);
307
308 --connection node_2
309 SELECT * FROM t1;
310 SELECT NEXTVAL(t);
311
312 --connection node_1
313 DROP TABLE t1;
314 DROP SEQUENCE t;
315
316 --connection node_2
317 --let $wsrep_sync_wait_orig_2 = `SELECT @@wsrep_sync_wait`
318 SET SESSION wsrep_sync_wait=15;
319
320 --connection node_1
321
322 CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
323 CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
324 #
325 # COMMIT TRX
326 #
327 BEGIN;
328 INSERT INTO t1(b) VALUES (1);
329 INSERT INTO t1(b) VALUES (2);
330 INSERT INTO t1(b) VALUES (3);
331 INSERT INTO t1(b) VALUES (4);
332 INSERT INTO t1(a,b) VALUES (2,2);
333 --error ER_DUP_ENTRY
334 INSERT INTO t1(a,b) VALUES (3,2);
335 COMMIT;
336
337 SELECT * FROM t1;
338 SELECT NEXTVAL(t);
339
340 --connection node_2
341 SELECT * FROM t1;
342 SELECT NEXTVAL(t);
343
344 --disable_query_log
345 --eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_2
346 --enable_query_log
347
348 --connection node_1
349 DROP TABLE t1;
350 DROP SEQUENCE t;
351
352 --echo
353 --echo MDEV-33245 SIGSEGV in wsrep_check_sequence | Sql_cmd_alter_sequence::execute
354 --echo
355
356 CREATE TABLE t (a INT) ENGINE=InnoDB;
357 INSERT INTO t VALUES (0);
358
359 --error ER_PARSE_ERROR
360 CREATE TABLE t1 (c VARCHAR) ENGINE=InnoDB;
361 --error ER_NOT_SUPPORTED_YET
362 ALTER SEQUENCE IF EXISTS t MINVALUE=1;
363
364 DROP TABLE t;
365
366 --echo
367 --echo MDEV-32631:
368 --echo
369
370 CREATE OR REPLACE TABLE t1(c INT ) ENGINE=ARIA;
371 SET SESSION WSREP_OSU_METHOD=RSU;
372 --error ER_NOT_SUPPORTED_YET
373 INSERT INTO t1 SELECT seq,concat(seq,1) FROM seq_1_to_100;
374 SET SESSION WSREP_OSU_METHOD=TOI;
375 DROP TABLE t1;
376
377 --echo
378 --echo End of 10.5 tests