Debian Codegraph @

mysql-test/suite/sql_sequence/gtid.test

raw
1 #
2 # This test is based on tests from ALISQL test suite
3 #
4
5 --source include/have_binlog_format_row.inc
6 --source include/master-slave.inc
7 --source include/have_innodb.inc
8
9 --disable_ps2_protocol
10 connection master;
11 create database s_db;
12 grant all on s_db.* to normal_1@'%' identified by 'pass';
13 grant all on test.* to normal_2@'%' identified by 'pass';
14 grant all on s_db.* to normal_3@'%' identified by 'pass';
15 grant all on test.* to normal_4@'%' identified by 'pass';
16 grant select on test.* to normal_5@'%' identified by 'pass';
17 flush status;
18
19 --sync_slave_with_master
20
21 connect(m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT);
22 connect(m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT);
23 connect(m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT);
24
25 connect(s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT);
26 connect(s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT);
27
28 connection slave;
29 set global read_only=on;
30
31 --echo ###########################################
32 --echo master and slave sync sequence.
33 --echo ###########################################
34 connection master;
35 use s_db;
36
37 create sequence s1;
38 show create table s1;
39
40 --sync_slave_with_master
41 use s_db;
42 show create table s1;
43
44 connection master;
45 use s_db;
46
47 drop sequence s1;
48
49 --echo ###########################################
50 --echo not support create table engine=sequence.
51 --echo ###########################################
52 connection master;
53
54 --error ER_UNKNOWN_STORAGE_ENGINE
55 create table t(id int)engine=sequence;
56
57 create table t(id int)engine=innodb;
58
59 --error ER_UNKNOWN_STORAGE_ENGINE
60 alter table t engine=sequence;
61
62 drop table t;
63 --echo ###########################################
64 --echo not support alter sequence table.
65 --echo ###########################################
66 connection master;
67
68 create sequence s2;
69
70 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
71 alter table s2 add id int;
72
73 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
74 alter table s2 add index ind_x(start_value);
75 drop sequence s2;
76
77 --echo ###########################################
78 --echo Support create temporary sequence.
79 --echo ###########################################
80 connection master;
81
82 create temporary sequence s2;
83 drop temporary sequence s2;
84
85 --echo ###########################################
86 --echo all invalid sequence value
87 --echo ###########################################
88
89 connection master;
90 use s_db;
91 create sequence s2 start with 1
92 minvalue 1
93 maxvalue 100000
94 increment by 1
95 cache 10000
96 cycle;
97 drop sequence s2;
98 create sequence s2 start with 1
99 minvalue 1
100 maxvalue 100000
101 increment by 1
102 cache 10000
103 nocycle;
104 drop sequence s2;
105 create sequence s2 start with 1
106 minvalue 1
107 maxvalue 100000
108 increment by 1
109 nocache
110 nocycle;
111 drop sequence s2;
112
113 --error ER_SEQUENCE_INVALID_DATA
114 create sequence s2 start with 1
115 minvalue 5
116 maxvalue 100000
117 increment by 1
118 nocache
119 nocycle;
120
121 --error ER_SEQUENCE_INVALID_DATA
122 create sequence s2 start with 1
123 minvalue 5
124 maxvalue 5
125 increment by 1
126 nocache
127 nocycle;
128
129 --error ER_SEQUENCE_INVALID_DATA
130 create sequence s2 start with 1
131 minvalue 5
132 maxvalue 4
133 increment by 1
134 nocache
135 nocycle;
136
137 --error ER_SEQUENCE_INVALID_DATA
138 create sequence s2 start with 1
139 minvalue 5
140 maxvalue 4
141 increment by 0
142 nocache
143 nocycle;
144
145 --echo ###########################################
146 --echo global read lock prevent query sequence
147 --echo ###########################################
148 connection master;
149 use s_db;
150 create sequence s_db.s1;
151 flush table with read lock;
152 --error ER_CANT_UPDATE_WITH_READLOCK
153 select next value for s1;
154
155 unlock tables;
156
157 drop sequence s_db.s1;
158
159 --echo ###########################################
160 --echo query cache test
161 --echo ###########################################
162 connection master;
163 use s_db;
164 show global variables like 'query_cache_type';
165
166 show status like 'Qcache_hits';
167 show status like 'Qcache_inserts';
168
169 --echo ###########################################
170 --echo priv test
171 --echo ###########################################
172 connection m_normal_1;
173 create sequence s_db.s1;
174 select * from s_db.s1;
175 select next value for s_db.s1;
176 select * from s_db.s1;
177 create sequence s_db.s2;
178 drop sequence s_db.s2;
179
180
181 connection m_normal_2;
182 --error ER_TABLEACCESS_DENIED_ERROR
183 select next value for s_db.s1;
184 --error ER_TABLEACCESS_DENIED_ERROR
185 create sequence s_db.s2;
186
187 connection m_normal_1;
188 drop sequence s_db.s1;
189
190 --echo ###########################################
191 --echo run out sequence value
192 --echo ###########################################
193 connection m_normal_1;
194 use s_db;
195 create sequence s_t start with 1 cache 2 maxvalue 5;
196 create table t(id int);
197 insert into t values(1111);
198 insert into t select next value for s_t;
199 insert into t select next value for s_t;
200 insert into t select next value for s_t;
201 insert into t select next value for s_t;
202 insert into t select next value for s_t;
203 --error ER_SEQUENCE_RUN_OUT
204 insert into t select next value for s_t;
205 --error ER_SEQUENCE_RUN_OUT
206 insert into t select next value for s_t;
207 commit;
208 select * from t;
209
210 connection master;
211 --sync_slave_with_master
212
213 connection s_normal_3;
214 use s_db;
215 select * from t;
216
217 connection m_normal_1;
218 use s_db;
219 drop sequence s_t;
220 drop table t;
221
222 --echo ###########################################
223 --echo read_only prevent query sequence
224 --echo ###########################################
225 connection m_normal_1;
226 create sequence s_db.s1;
227 show global variables like 'read_only';
228 select next value for s_db.s1;
229
230 connection master;
231 sync_slave_with_master;
232
233 connection s_normal_3;
234 show global variables like 'read_only';
235 --error ER_OPTION_PREVENTS_STATEMENT
236 select next value for s_db.s1;
237
238 connection m_normal_1;
239 drop sequence s_db.s1;
240
241 --echo ###########################################
242 --echo update based table
243 --echo ###########################################
244 connection m_normal_1;
245 use s_db;
246 create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle;
247
248 connection master;
249 --sync_slave_with_master
250
251 connection s_normal_3;
252 use s_db;
253 select * from s_t;
254
255 connection m_normal_1;
256 select next value for s_t;
257 select * from s_t;
258
259 connection master;
260 --sync_slave_with_master
261
262 connection s_normal_3;
263 select * from s_t;
264
265 --echo ------------------------------------------
266 --echo master update nextval;
267 --echo ------------------------------------------
268 connection m_normal_1;
269 select next value for s_t;
270 --error ER_ILLEGAL_HA
271 update s_t set next_not_cached_value= 11;
272 alter sequence s_t restart=11;
273 commit;
274
275 select * from s_t;
276
277 connection master;
278 --sync_slave_with_master
279
280 --echo ------------------------------------------
281 --echo show slave nextval;
282 --echo ------------------------------------------
283 connection s_normal_3;
284 select * from s_t;
285
286 connection m_normal_1;
287 select next value for s_t;
288 select * from s_t;
289
290 connection master;
291 --sync_slave_with_master
292
293 connection s_normal_3;
294 select * from s_t;
295
296
297 --echo ------------------------------------------
298 --echo update into invalid sequence
299 --echo ------------------------------------------
300 connection m_normal_1;
301 select * from s_t;
302 --error ER_ILLEGAL_HA
303 update s_t set next_not_cached_value= 11,start_value=10, minimum_value=11;
304 --error ER_SEQUENCE_INVALID_DATA
305 ALTER SEQUENCE s_t restart with 11 start=10 minvalue=11;
306 commit;
307
308 create table t_1(id int);
309 insert into t_1 value(1111);
310 select next value for s_t;
311 insert into t_1 select next value for s_t;
312 commit;
313
314 select * from t_1;
315
316 --echo ------------------------------------------
317 --echo delete sequence row
318 --echo ------------------------------------------
319 connection m_normal_1;
320 --error ER_ILLEGAL_HA
321 delete from s_t;
322 commit;
323
324 select next value for s_t;
325
326 connection m_normal_1;
327 drop sequence s_t;
328 drop table t_1;
329
330 --echo ###########################################
331 --echo test transaction context (innodb)
332 --echo ###########################################
333
334 --echo ------------------------------------------
335 --echo transaction table and sequence
336 --echo normal transaction commit
337 --echo ------------------------------------------
338 connection m_normal_1;
339 use s_db;
340 create sequence s_1 cache 5;
341
342 create table t_1(id int)engine=innodb;
343 begin;
344 insert into t_1 values(1111);
345 insert into t_1 select next value for s_1;
346 insert into t_1 select next value for s_1;
347 insert into t_1 values(2222);
348 commit;
349
350 select * from t_1;
351
352 connection master;
353 --sync_slave_with_master
354
355 connection s_normal_3;
356 use s_db;
357 select * from t_1;
358
359 --echo ------------------------------------------
360 --echo normal transaction rollback
361 --echo ------------------------------------------
362 connection m_normal_1;
363 begin;
364 insert into t_1 values(3333);
365 insert into t_1 select next value for s_1;
366 insert into t_1 select next value for s_1;
367 insert into t_1 select next value for s_1;
368 insert into t_1 select next value for s_1;
369 insert into t_1 select next value for s_1;
370 insert into t_1 select next value for s_1;
371 insert into t_1 select next value for s_1;
372 insert into t_1 select next value for s_1;
373
374 select * from t_1;
375 rollback;
376
377 select * from t_1;
378 select next value for s_1;
379
380 connection master;
381 --sync_slave_with_master
382
383 connection s_normal_3;
384 use s_db;
385 select * from t_1;
386
387 connection m_normal_1;
388 use s_db;
389 drop sequence s_1;
390 drop table t_1;
391
392 --echo ###########################################
393 --echo test transaction context (myisam)
394 --echo ###########################################
395
396 --echo ------------------------------------------
397 --echo transaction table and sequence
398 --echo normal transaction commit
399 --echo ------------------------------------------
400 connection m_normal_1;
401 use s_db;
402 create sequence s_1 cache 5;
403
404 create table t_1(id int)engine=myisam;
405 begin;
406 insert into t_1 values(1111);
407 insert into t_1 select next value for s_1;
408 insert into t_1 select next value for s_1;
409 insert into t_1 values(2222);
410 commit;
411
412 select * from t_1;
413
414 connection master;
415 --sync_slave_with_master
416
417 connection s_normal_3;
418 use s_db;
419 select * from t_1;
420
421 --echo ------------------------------------------
422 --echo normal transaction rollback
423 --echo ------------------------------------------
424 connection m_normal_1;
425 begin;
426 insert into t_1 values(3333);
427 insert into t_1 select next value for s_1;
428 insert into t_1 select next value for s_1;
429 insert into t_1 select next value for s_1;
430 insert into t_1 select next value for s_1;
431 insert into t_1 select next value for s_1;
432 insert into t_1 select next value for s_1;
433 insert into t_1 select next value for s_1;
434 insert into t_1 select next value for s_1;
435
436 select * from t_1;
437 rollback;
438
439 select * from t_1;
440 select next value for s_1;
441
442 connection master;
443 --sync_slave_with_master
444
445 connection s_normal_3;
446 use s_db;
447 select * from t_1;
448
449 connection m_normal_1;
450 use s_db;
451 drop sequence s_1;
452 drop table t_1;
453
454 --echo ###########################################
455 --echo close binlog
456 --echo ###########################################
457 connection m_normal_1;
458 use s_db;
459 create sequence s1 cache 2;
460 select next value for s1;
461 select next value for s1;
462 select next value for s1;
463 select next value for s1;
464
465 commit;
466 select * from s1;
467
468 connection master;
469 --sync_slave_with_master
470
471 connection slave;
472 use s_db;
473 select * from s1;
474
475 --echo ------------------------------------------
476 --echo close session binlog.
477 --echo ------------------------------------------
478 connection master;
479 set session sql_log_bin=off;
480 select next value for s1;
481 select next value for s1;
482 select next value for s1;
483 select next value for s1;
484
485 set session sql_log_bin=on;
486 select * from s1;
487
488 connection master;
489 --sync_slave_with_master
490
491 connection slave;
492 use s_db;
493 select * from s1;
494
495 connection m_normal_1;
496 use s_db;
497 drop sequence s1;
498
499 --echo ###########################################
500 --echo statement binlog
501 --echo ###########################################
502 --echo ------------------------------------------
503 --echo set binlog_format=statement
504 --echo ------------------------------------------
505 connection master;
506 set session binlog_format=statement;
507 select @@session.binlog_format;
508 create sequence s1 cache 2;
509 --error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
510 select next value for s1;
511
512 set session binlog_format=row;
513 select next value for s1;
514 select * from s1;
515
516 connection master;
517 --sync_slave_with_master
518
519 use s_db;
520 select * from s1;
521
522 connection m_normal_1;
523 use s_db;
524 drop sequence s1;
525
526 --echo ###########################################
527 --echo test savepoint
528 --echo ###########################################
529 connection master;
530 set session binlog_format=row;
531
532 create sequence s1 cache 2;
533 create table t1(id int)engine=innodb;
534
535 begin;
536 insert into t1 values(1111);
537 savepoint sp1;
538 insert into t1 select next value for s1;
539 insert into t1 select next value for s1;
540 insert into t1 select next value for s1;
541
542 insert into t1 values(2222);
543
544 select * from t1;
545 rollback to sp1;
546 select * from t1;
547 select next value for s1;
548
549 commit;
550
551 drop sequence s1;
552 drop table t1;
553
554 --echo ###########################################
555 --echo test proc
556 --echo ###########################################
557 connection m_normal_1;
558 use s_db;
559 create table t(id int)engine=innodb;
560
561 delimiter //;
562
563 create procedure p1()
564 begin
565 create sequence s1 cache 2;
566 end//
567
568 create procedure p2()
569 begin
570 insert into t select next value for s1;
571 commit;
572 end//
573
574 delimiter ;//
575
576 call p1();
577 call p2();
578 call p2();
579 call p2();
580 call p2();
581
582 select * from t;
583
584 connection master;
585 --sync_slave_with_master
586
587 use s_db;
588 select * from t;
589
590 connection m_normal_1;
591 drop table t;
592 drop sequence s1;
593 drop procedure p1;
594 drop procedure p2;
595
596 --echo ###########################################
597 --echo test trigger
598 --echo ###########################################
599 connection m_normal_1;
600 use s_db;
601 create sequence s1 cache 2;
602 create table t1(id int)engine=innodb;
603 create table t2(id int)engine=innodb;
604
605 delimiter //;
606 CREATE TRIGGER tri_1
607 before INSERT ON t2 FOR EACH ROW
608 BEGIN
609 INSERT INTO t1 select next value for s1;
610 END//
611 delimiter ;//
612
613 begin;
614 insert into t2 values(1111);
615 insert into t2 values(1111);
616 insert into t2 values(1111);
617 insert into t2 values(1111);
618
619 select * from t2;
620 select * from t1;
621 rollback;
622 select * from t2;
623 select * from t1;
624
625 select next value for s1;
626
627
628 drop trigger tri_1;
629 drop table t1;
630 drop table t2;
631 drop sequence s1;
632
633 --echo ###########################################
634 --echo test function
635 --echo ###########################################
636 connection m_normal_1;
637 use s_db;
638 create sequence s1 cache 2;
639 create table t1(id int)engine=innodb;
640
641 delimiter //;
642 CREATE function f1() returns int
643 BEGIN
644 INSERT INTO t1 select next value for s1;
645 return (1);
646 END//
647 delimiter ;//
648
649 begin;
650 select f1();
651 select f1();
652 select f1();
653 select f1();
654
655 select * from t1;
656 rollback;
657 select * from t1;
658
659 select next value for s1;
660
661 drop function f1;
662 drop table t1;
663 drop sequence s1;
664
665 --echo ##############
666 --echo Test GRANT
667 --echo ##############
668
669 connection m_normal_2;
670 create table t1 (a int);
671 create sequence s1;
672 select next value for s1;
673 insert into t1 values (1);
674 connection m_normal_3;
675 select * from t1;
676 select * from s1;
677 select previous value for s1;
678 --error ER_TABLEACCESS_DENIED_ERROR
679 insert into t1 values (2);
680 --error ER_TABLEACCESS_DENIED_ERROR
681 select next value for s1;
682 --error ER_TABLEACCESS_DENIED_ERROR
683 do setval(s1,1000,0);
684 connection master;
685 grant insert on test.* to normal_5@'%' identified by 'pass';
686 disconnect m_normal_3;
687 connect(m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT);
688 insert into t1 values (2);
689 select t1.*, (next value for s1) from t1;
690 do setval(s1,10000,0);
691 select * from s1;
692 connection m_normal_2;
693 drop table t1;
694 drop sequence s1;
695
696 --echo #
697 --echo # Cleanup
698 --echo #
699
700 connection master;
701 use s_db;
702 drop database s_db;
703 drop user normal_1@'%';
704 drop user normal_2@'%';
705 drop user normal_3@'%';
706 drop user normal_4@'%';
707 drop user normal_5@'%';
708 --enable_ps2_protocol
709
710 --source include/rpl_end.inc