Debian Codegraph @

mysql-test/suite/sql_sequence/replication.test

raw
1 #
2 # This test is originally sequence.test from ALISQL by Jianwei modified for
3 # MariaDB
4 #
5 # It tests basic sequence functionallity together with replication
6 #
7
8 --source include/have_binlog_format_row.inc
9 --source include/master-slave.inc
10 --source include/have_innodb.inc
11
12 --disable_ps2_protocol
13 connection master;
14 create database s_db;
15 use s_db;
16 grant all on s_db.* to normal_1@'%' identified by 'pass';
17 grant all on test.* to normal_2@'%' identified by 'pass';
18 grant all on s_db.* to normal_3@'%' identified by 'pass';
19 grant all on test.* to normal_4@'%' identified by 'pass';
20
21 --sync_slave_with_master
22
23 connect(m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT);
24 connect(m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT);
25
26 connect(s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT);
27 connect(s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT);
28
29 connection slave;
30 set global read_only=on;
31 use s_db;
32
33 --echo ###########################################
34 --echo master and slave sync sequence.
35 --echo ###########################################
36 connection master;
37
38 create sequence s1;
39 show create table s1;
40
41 --sync_slave_with_master
42
43 show create table s1;
44
45 connection master;
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 sequence
79 --echo ###########################################
80 connection master;
81
82 create table t_1(id int);
83 --error ER_NOT_SEQUENCE
84 show create sequence t_1;
85
86 drop table t_1;
87
88 --error ER_PARSE_ERROR
89 CREATE SEQUENCE `s2` (
90 `next_not_cached_value` bigint(21) NOT NULL,
91 `minimum_value` bigint(21) NOT NULL,
92 `maximum_value` bigint(21) NOT NULL,
93 `start_value` bigint(21) NOT NULL,
94 `increment` bigint(21) NOT NULL,
95 `cache_size` bigint(21) unsigned NOT NULL,
96 `cycle_option` tinyint(1) unsigned NOT NULL,
97 `cycle_count` bigint(21) NOT NULL
98 ) ENGINE=InnoDB sequence=1;
99
100 CREATE TABLE `s2` (
101 `next_not_cached_value` bigint(21) NOT NULL,
102 `minimum_value` bigint(21) NOT NULL,
103 `maximum_value` bigint(21) NOT NULL,
104 `start_value` bigint(21) NOT NULL,
105 `increment` bigint(21) NOT NULL,
106 `cache_size` bigint(21) unsigned NOT NULL,
107 `cycle_option` tinyint(1) unsigned NOT NULL,
108 `cycle_count` bigint(21) NOT NULL
109 ) ENGINE=InnoDB sequence=1;
110
111 insert into s2 values(1, 1, 10, 1, 2, 1, 1, 0);
112 commit;
113 --error ER_PARSE_ERROR
114 select * for s2;
115 select * from s2;
116 select NEXT VALUE for s2;
117 select NEXT VALUE for s2;
118 select NEXT VALUE for s2;
119 select NEXT VALUE for s2;
120 select NEXT VALUE for s2;
121 select NEXT VALUE for s2;
122 select NEXT VALUE for s2;
123 select * from s2;
124 commit;
125
126 connection master;
127 --sync_slave_with_master
128 select * from s2;
129
130 connection master;
131 drop sequence s2;
132
133 CREATE TABLE `s2` (
134 `next_not_cached_value` bigint(21) NOT NULL,
135 `minimum_value` bigint(21) NOT NULL,
136 `maximum_value` bigint(21) NOT NULL,
137 `start_value` bigint(21) NOT NULL,
138 `increment` bigint(21) NOT NULL,
139 `cache_size` bigint(21) unsigned NOT NULL,
140 `cycle_option` tinyint(1) unsigned NOT NULL,
141 `cycle_count` bigint(21) NOT NULL
142 ) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1;
143
144 show create sequence s2;
145 drop sequence s2;
146
147 --echo ###########################################
148 --echo select sequence syntax test
149 --echo ###########################################
150 connection master;
151 create sequence s2;
152 create table t2 (id int);
153
154 select * from s2;
155 select * from t2;
156 insert into t2 select next value for s2;
157 commit;
158
159 select NEXT VALUE for s2;
160 --error ER_NOT_SEQUENCE
161 select NEXT VALUE for t2;
162
163 select * from s2, t2;
164
165 --error ER_PARSE_ERROR
166 select * for s2;
167 --error ER_PARSE_ERROR
168 select * for s2, t2;
169
170 connection master;
171 drop sequence s2;
172 drop table t2;
173
174 --echo ###########################################
175 --echo support rename, not support truncate
176 --echo ###########################################
177 connection master;
178
179 create sequence s2;
180
181 alter table s2 rename to s2_1;
182 rename table s2_1 to s2_2;
183 show create sequence s2_2;
184 select * from s2_2;
185
186 --error ER_ILLEGAL_HA
187 truncate table s2_2;
188 rename table s2_2 to s2;
189 drop sequence s2;
190
191 --echo ###########################################
192 --echo all invalid sequence value
193 --echo ###########################################
194
195 connection master;
196 create sequence s2 start with 1
197 minvalue 1
198 maxvalue 100000
199 increment by 1
200 cache 10000
201 cycle;
202 drop sequence s2;
203 create sequence s2 start with 1
204 minvalue 1
205 maxvalue 100000
206 increment by 1
207 cache 10000
208 nocycle;
209 drop sequence s2;
210 create sequence s2 start with 1
211 minvalue 1
212 maxvalue 100000
213 increment by 1
214 nocache
215 nocycle;
216 drop sequence s2;
217
218 --error ER_SEQUENCE_INVALID_DATA
219 create sequence s2 start with 1
220 minvalue 5
221 maxvalue 100000
222 increment by 1
223 nocache
224 nocycle;
225
226 --error ER_SEQUENCE_INVALID_DATA
227 create sequence s2 start with 1
228 minvalue 5
229 maxvalue 5
230 increment by 1
231 nocache
232 nocycle;
233
234 --error ER_SEQUENCE_INVALID_DATA
235 create sequence s2 start with 1
236 minvalue 5
237 maxvalue 4
238 increment by 1
239 nocache
240 nocycle;
241
242 --error ER_SEQUENCE_INVALID_DATA
243 create sequence s2 start with 1
244 minvalue 5
245 maxvalue 4
246 increment by 0
247 nocache
248 nocycle;
249
250 --echo ###########################################
251 --echo global read lock prevent query sequence
252 --echo ###########################################
253 connection master;
254 create sequence s_db.s1;
255 flush table with read lock;
256 --error ER_CANT_UPDATE_WITH_READLOCK
257 select NEXT VALUE for s1;
258 unlock tables;
259 drop sequence s_db.s1;
260
261 --echo ###########################################
262 --echo query cache test
263 --echo ###########################################
264 connection master;
265 flush global status;
266 show global variables like 'query_cache_type';
267
268 show status like 'Qcache_hits';
269 show status like 'Qcache_inserts';
270
271 --echo ###########################################
272 --echo priv test
273 --echo ###########################################
274 connection m_normal_1;
275 create sequence s_db.s1;
276 select NEXT VALUE for s_db.s1;
277 create sequence s_db.s2;
278 drop sequence s_db.s2;
279
280
281 connection m_normal_2;
282 --error ER_TABLEACCESS_DENIED_ERROR
283 select NEXT VALUE for s_db.s1;
284 --error ER_TABLEACCESS_DENIED_ERROR
285 create sequence s_db.s2;
286
287 connection m_normal_1;
288 drop sequence s_db.s1;
289
290 --echo ###########################################
291 --echo run out sequence value
292 --echo ###########################################
293 connection m_normal_1;
294 create sequence s_t start with 1 cache 2 maxvalue 5;
295 create table t(id int);
296 insert into t values(1111);
297 insert into t select next value for s_t;
298 insert into t select next value for s_t;
299 insert into t select next value for s_t;
300 insert into t select next value for s_t;
301 insert into t select next value for s_t;
302 --error ER_SEQUENCE_RUN_OUT
303 insert into t select next value for s_t;
304 --error ER_SEQUENCE_RUN_OUT
305 insert into t select next value for s_t;
306 commit;
307 select * from t;
308
309 connection master;
310 --sync_slave_with_master
311
312 connection s_normal_3;
313 select * from t;
314
315 connection m_normal_1;
316 drop sequence s_t;
317 drop table t;
318
319 --echo ###########################################
320 --echo read_only prevent query sequence
321 --echo ###########################################
322 connection m_normal_1;
323 create sequence s_db.s1;
324 show global variables like 'read_only';
325 select * from s_db.s1;
326
327 connection master;
328 --sync_slave_with_master
329 connection s_normal_3;
330
331 show global variables like 'read_only';
332 --error ER_OPTION_PREVENTS_STATEMENT
333 select next value for s_db.s1;
334
335 connection m_normal_1;
336 drop sequence s_db.s1;
337
338 --echo ###########################################
339 --echo update based table
340 --echo ###########################################
341 connection m_normal_1;
342 create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle engine=innodb;
343
344 connection master;
345 --sync_slave_with_master
346
347 connection s_normal_3;
348 select * from s_t;
349
350
351 connection m_normal_1;
352 select next value for s_t;
353
354 connection master;
355 --sync_slave_with_master
356
357 connection s_normal_3;
358 select next_not_cached_value from s_t;
359
360 --echo ------------------------------------------
361 --echo master ALTER SEQUENCE
362 --echo ------------------------------------------
363 connection m_normal_1;
364 select next value for s_t;
365 alter sequence s_t restart= 11;
366
367 select * from s_t;
368
369 connection master;
370 --sync_slave_with_master
371
372 --echo ------------------------------------------
373 --echo show slave nextval;
374 --echo ------------------------------------------
375 connection s_normal_3;
376 select * from s_t;
377
378 connection m_normal_1;
379 select next value for s_t;
380
381 connection master;
382 --sync_slave_with_master
383
384 connection s_normal_3;
385 select * from s_t;
386
387
388 --echo ------------------------------------------
389 --echo update into invalid sequence
390 --echo ------------------------------------------
391 connection m_normal_1;
392 select next value for s_t;
393 select * from s_t;
394 --error ER_SEQUENCE_INVALID_DATA
395 alter sequence s_t minvalue=11 maxvalue=9;
396 select * from s_t;
397 --error ER_SEQUENCE_INVALID_DATA
398 alter sequence s_t restart= 12 start=10 minvalue=11 maxvalue=20;
399 select * from s_t;
400
401 --echo ------------------------------------------
402 --echo delete sequence row
403 --echo ------------------------------------------
404 connection m_normal_1;
405 --error ER_ILLEGAL_HA
406 delete from s_t;
407 commit;
408
409 select next value for s_t;
410
411 connection m_normal_1;
412 drop sequence s_t;
413
414 --echo ###########################################
415 --echo test transaction context (innodb)
416 --echo ###########################################
417
418 --echo ------------------------------------------
419 --echo transaction table and sequence
420 --echo normal transaction commit
421 --echo ------------------------------------------
422 connection m_normal_1;
423 create sequence s_1 cache 5 engine=innodb;
424
425 create table t_1(id int)engine=innodb;
426 begin;
427 insert into t_1 values(1111);
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 values(2222);
431 commit;
432
433 select * from t_1;
434 select * from s_1;
435
436 connection master;
437 --sync_slave_with_master
438
439 connection s_normal_3;
440 select * from t_1;
441
442 --echo ------------------------------------------
443 --echo normal transaction rollback
444 --echo ------------------------------------------
445 connection m_normal_1;
446 begin;
447 insert into t_1 values(3333);
448 insert into t_1 select next value for s_1;
449 insert into t_1 select next value for s_1;
450 insert into t_1 select next value for s_1;
451 insert into t_1 select next value for s_1;
452 insert into t_1 select next value for s_1;
453 insert into t_1 select next value for s_1;
454 insert into t_1 select next value for s_1;
455 insert into t_1 select next value for s_1;
456
457 select * from t_1;
458 rollback;
459
460 select * from t_1;
461 select * from s_1;
462 select next value for s_1;
463
464 connection master;
465 --sync_slave_with_master
466
467 connection s_normal_3;
468 select * from t_1;
469
470 connection m_normal_1;
471 drop sequence s_1;
472 drop table t_1;
473
474 --echo ###########################################
475 --echo test transaction context (myisam)
476 --echo ###########################################
477
478 --echo ------------------------------------------
479 --echo transaction table and sequence
480 --echo normal transaction commit
481 --echo ------------------------------------------
482 connection m_normal_1;
483 create sequence s_1 cache 5;
484
485 create table t_1(id int)engine=myisam;
486 begin;
487 insert into t_1 values(1111);
488 insert into t_1 select next value for s_1;
489 insert into t_1 select next value for s_1;
490 insert into t_1 values(2222);
491 commit;
492
493 select * from t_1;
494
495 connection master;
496 --sync_slave_with_master
497
498 connection s_normal_3;
499 select * from t_1;
500
501 --echo ------------------------------------------
502 --echo normal transaction rollback
503 --echo ------------------------------------------
504 connection m_normal_1;
505 begin;
506 insert into t_1 values(3333);
507 insert into t_1 select next value for s_1;
508 insert into t_1 select next value for s_1;
509 insert into t_1 select next value for s_1;
510 insert into t_1 select next value for s_1;
511 insert into t_1 select next value for s_1;
512 insert into t_1 select next value for s_1;
513 insert into t_1 select next value for s_1;
514 insert into t_1 select next value for s_1;
515
516 select * from t_1;
517 rollback;
518
519 select * from t_1;
520 select next value for s_1;
521
522 connection master;
523 --sync_slave_with_master
524
525 connection s_normal_3;
526 select * from t_1;
527
528 connection m_normal_1;
529 drop sequence s_1;
530 drop table t_1;
531
532 --echo ###########################################
533 --echo close binlog
534 --echo ###########################################
535 connection m_normal_1;
536 create sequence s1 cache 2;
537 select next value for s1;
538 select next value for s1;
539 select next value for s1;
540 select next value for s1;
541
542 commit;
543 select * from s1;
544
545 connection master;
546 --sync_slave_with_master
547
548 select * from s1;
549
550 --echo ------------------------------------------
551 --echo close session binlog.
552 --echo ------------------------------------------
553 connection master;
554 set session sql_log_bin=off;
555 select next value for s1;
556 select next value for s1;
557 select next value for s1;
558 select next value for s1;
559
560 set session sql_log_bin=on;
561 select * from s1;
562
563 connection master;
564 --sync_slave_with_master
565
566 select * from s1;
567
568 connection master;
569 select next value for s1;
570 select * from s1;
571 --sync_slave_with_master
572
573 select * from s1;
574 connection master;
575
576 drop sequence s1;
577
578 --echo ###########################################
579 --echo statement binlog
580 --echo ###########################################
581 --echo ------------------------------------------
582 --echo set binlog_format=statement
583 --echo ------------------------------------------
584 connection master;
585 set session binlog_format=statement;
586 select @@session.binlog_format;
587 create sequence s1 cache 2;
588 --error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
589 select next value for s1;
590
591 set session binlog_format=row;
592 select next value for s1;
593
594 connection master;
595 --sync_slave_with_master
596
597 select * from s1;
598
599 connection m_normal_1;
600 drop sequence s1;
601
602 --echo ------------------------------------------
603 --echo set binlog_format=mixed
604 --echo ------------------------------------------
605 connection master;
606 set session binlog_format=mixed;
607 select @@session.binlog_format;
608 create sequence s1 cache 2;
609 select next value for s1;
610
611 set session binlog_format=row;
612 select next value for s1;
613 select * from s1;
614
615 connection master;
616 --sync_slave_with_master
617
618 select * from s1;
619
620 connection m_normal_1;
621 drop sequence s1;
622 connection master;
623 --sync_slave_with_master
624
625 --echo ###########################################
626 --echo test savepoint
627 --echo ###########################################
628
629 connection master;
630 set session binlog_format=row;
631
632 create sequence s1 cache 2;
633 create table t1(id int)engine=innodb;
634
635 begin;
636 insert into t1 values(1111);
637 savepoint sp1;
638 insert into t1 select next value for s1;
639 insert into t1 select next value for s1;
640 insert into t1 select next value for s1;
641
642 insert into t1 values(2222);
643
644 select * from t1;
645 rollback to sp1;
646 select * from t1;
647 select next value for s1;
648
649 commit;
650
651 drop sequence s1;
652 drop table t1;
653
654 connection master;
655 --sync_slave_with_master
656
657 --echo ###########################################
658 --echo create as
659 --echo ###########################################
660 connection m_normal_1;
661
662 create sequence s1 cache 2;
663 create table t as select * from s1;
664 select * from t;
665 drop table t;
666 create table t as select next value for s1;
667 select * from t;
668 drop table t;
669
670 drop sequence s1;
671
672 connection master;
673 --sync_slave_with_master
674
675 --echo ###########################################
676 --echo test proc
677 --echo ###########################################
678 connection m_normal_1;
679 create table t(id int)engine=innodb;
680
681 delimiter //;
682
683 create procedure p1()
684 begin
685 create sequence s1 cache 2;
686 end//
687
688 create procedure p2()
689 begin
690 insert into t select next value for s1;
691 commit;
692 end//
693
694 delimiter ;//
695
696 call p1();
697 call p2();
698 call p2();
699 call p2();
700 call p2();
701
702 select * from t;
703
704 connection master;
705 --sync_slave_with_master
706
707 select * from t;
708
709 connection m_normal_1;
710 drop table t;
711 drop sequence s1;
712 drop procedure p1;
713 drop procedure p2;
714
715 --echo ###########################################
716 --echo test trigger
717 --echo ###########################################
718 connection m_normal_1;
719 create sequence s1 cache 2;
720 create table t1(id int)engine=innodb;
721 create table t2(id int)engine=innodb;
722
723 delimiter //;
724 CREATE TRIGGER tri_1
725 before INSERT ON t2 FOR EACH ROW
726 BEGIN
727 INSERT INTO t1 select next value for s1;
728 END//
729 delimiter ;//
730
731 begin;
732 insert into t2 values(1111);
733 insert into t2 values(1111);
734 insert into t2 values(1111);
735 insert into t2 values(1111);
736
737 select * from t2;
738 select * from t1;
739 rollback;
740 select * from t2;
741 select * from t1;
742
743 select next value for s1;
744
745
746 drop trigger tri_1;
747 drop table t1;
748 drop table t2;
749 drop sequence s1;
750
751 --echo ###########################################
752 --echo test function
753 --echo ###########################################
754 connection m_normal_1;
755 create sequence s1 cache 2;
756 create table t1(id int)engine=innodb;
757
758 delimiter //;
759 CREATE function f1() returns int
760 BEGIN
761 INSERT INTO t1 select next value for s1;
762 return (1);
763 END//
764 delimiter ;//
765
766 begin;
767 select f1();
768 select f1();
769 select f1();
770 select f1();
771
772 select * from t1;
773 rollback;
774 select * from t1;
775
776 select next value for s1;
777
778 drop function f1;
779 drop table t1;
780 drop sequence s1;
781
782 --echo ###########################################
783 --echo test value boundary
784 --echo ###########################################
785 connection m_normal_1;
786
787 --echo ------------------------------------------
788 --echo cycle_count increment by cycle_count
789 --echo ------------------------------------------
790 create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle;
791 select next value for s1;
792 select next value for s1;
793 select next value for s1;
794 select next value for s1;
795 select next value for s1;
796 drop sequence s1;
797
798 create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle;
799 select next value for s1;
800 select next value for s1;
801 select next value for s1;
802 --error ER_SEQUENCE_RUN_OUT
803 select next value for s1;
804 select * from s1;
805 drop sequence s1;
806
807 create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle;
808 select next value for s1;
809 select next_not_cached_value,cycle_count from s1;
810 select next value for s1;
811 select next_not_cached_value,cycle_count from s1;
812 select next value for s1;
813 select next_not_cached_value,cycle_count from s1;
814 select next value for s1;
815 select next_not_cached_value,cycle_count from s1;
816 select next value for s1;
817 select next_not_cached_value,cycle_count from s1;
818 drop sequence s1;
819
820 create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle;
821 select next value for s1;
822 --error ER_SEQUENCE_RUN_OUT
823 select next value for s1;
824 drop sequence s1;
825
826 --echo ------------------------------------------
827 --echo beyond ulonglong maxvalue
828 --echo ------------------------------------------
829 create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 1 cycle;
830 select next value for s1, cycle_count from s1;
831 select next value for s1, cycle_count from s1;
832 select next value for s1, cycle_count from s1;
833 select next value for s1, cycle_count from s1;
834 select next value for s1, cycle_count from s1;
835 select next value for s1, cycle_count from s1;
836 select next value for s1, cycle_count from s1;
837 select next value for s1, cycle_count from s1;
838 select next value for s1, cycle_count from s1;
839 select next value for s1, cycle_count from s1;
840 drop sequence s1;
841
842 create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 10 cycle;
843 select next value for s1, cycle_count from s1;
844 select next value for s1, cycle_count from s1;
845 select next value for s1, cycle_count from s1;
846 select next value for s1, cycle_count from s1;
847 select next value for s1, cycle_count from s1;
848 select next value for s1, cycle_count from s1;
849 select next value for s1, cycle_count from s1;
850 select next value for s1, cycle_count from s1;
851 select next value for s1, cycle_count from s1;
852 select next value for s1, cycle_count from s1;
853 drop sequence s1;
854
855 --echo ###########################################
856 --echo test default()
857 --echo ###########################################
858 connection master;
859
860 CREATE SEQUENCE s1 nocache engine=myisam;
861 CREATE table t1 (a int default next value for s1, b int);
862 insert into t1 (b) values (1),(2);
863 select default(a) from t1;
864 select * from t1;
865 select * from s1;
866 --sync_slave_with_master
867 connection s_normal_3;
868 select * from t1;
869 select * from s1;
870 connection master;
871 drop table t1,s1;
872 --enable_ps2_protocol
873
874 #
875 # Cleanup
876 #
877
878 connection master;
879 drop database s_db;
880 drop user normal_1@'%';
881 drop user normal_2@'%';
882 drop user normal_3@'%';
883 drop user normal_4@'%';
884
885 --source include/rpl_end.inc