Debian Codegraph @

mysql-test/suite/sql_sequence/gtid.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 create database s_db;
5 grant all on s_db.* to normal_1@'%' identified by 'pass';
6 grant all on test.* to normal_2@'%' identified by 'pass';
7 grant all on s_db.* to normal_3@'%' identified by 'pass';
8 grant all on test.* to normal_4@'%' identified by 'pass';
9 grant select on test.* to normal_5@'%' identified by 'pass';
10 flush status;
11 connection slave;
12 connect m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT;
13 connect m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT;
14 connect m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT;
15 connect s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT;
16 connect s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT;
17 connection slave;
18 set global read_only=on;
19 ###########################################
20 master and slave sync sequence.
21 ###########################################
22 connection master;
23 use s_db;
24 create sequence s1;
25 show create table s1;
26 Table Create Table
27 s1 CREATE TABLE `s1` (
28 `next_not_cached_value` bigint(21) NOT NULL,
29 `minimum_value` bigint(21) NOT NULL,
30 `maximum_value` bigint(21) NOT NULL,
31 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
32 `increment` bigint(21) NOT NULL COMMENT 'increment value',
33 `cache_size` bigint(21) unsigned NOT NULL,
34 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
35 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
36 ) ENGINE=MyISAM SEQUENCE=1
37 connection slave;
38 use s_db;
39 show create table s1;
40 Table Create Table
41 s1 CREATE TABLE `s1` (
42 `next_not_cached_value` bigint(21) NOT NULL,
43 `minimum_value` bigint(21) NOT NULL,
44 `maximum_value` bigint(21) NOT NULL,
45 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
46 `increment` bigint(21) NOT NULL COMMENT 'increment value',
47 `cache_size` bigint(21) unsigned NOT NULL,
48 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
49 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
50 ) ENGINE=MyISAM SEQUENCE=1
51 connection master;
52 use s_db;
53 drop sequence s1;
54 ###########################################
55 not support create table engine=sequence.
56 ###########################################
57 connection master;
58 create table t(id int)engine=sequence;
59 ERROR 42000: Unknown storage engine 'sequence'
60 create table t(id int)engine=innodb;
61 alter table t engine=sequence;
62 ERROR 42000: Unknown storage engine 'sequence'
63 drop table t;
64 ###########################################
65 not support alter sequence table.
66 ###########################################
67 connection master;
68 create sequence s2;
69 alter table s2 add id int;
70 ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns)
71 alter table s2 add index ind_x(start_value);
72 ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys)
73 drop sequence s2;
74 ###########################################
75 Support create temporary sequence.
76 ###########################################
77 connection master;
78 create temporary sequence s2;
79 drop temporary sequence s2;
80 ###########################################
81 all invalid sequence value
82 ###########################################
83 connection master;
84 use s_db;
85 create sequence s2 start with 1
86 minvalue 1
87 maxvalue 100000
88 increment by 1
89 cache 10000
90 cycle;
91 drop sequence s2;
92 create sequence s2 start with 1
93 minvalue 1
94 maxvalue 100000
95 increment by 1
96 cache 10000
97 nocycle;
98 drop sequence s2;
99 create sequence s2 start with 1
100 minvalue 1
101 maxvalue 100000
102 increment by 1
103 nocache
104 nocycle;
105 drop sequence s2;
106 create sequence s2 start with 1
107 minvalue 5
108 maxvalue 100000
109 increment by 1
110 nocache
111 nocycle;
112 ERROR HY000: Sequence 's_db.s2' has out of range value for options
113 create sequence s2 start with 1
114 minvalue 5
115 maxvalue 5
116 increment by 1
117 nocache
118 nocycle;
119 ERROR HY000: Sequence 's_db.s2' has out of range value for options
120 create sequence s2 start with 1
121 minvalue 5
122 maxvalue 4
123 increment by 1
124 nocache
125 nocycle;
126 ERROR HY000: Sequence 's_db.s2' has out of range value for options
127 create sequence s2 start with 1
128 minvalue 5
129 maxvalue 4
130 increment by 0
131 nocache
132 nocycle;
133 ERROR HY000: Sequence 's_db.s2' has out of range value for options
134 ###########################################
135 global read lock prevent query sequence
136 ###########################################
137 connection master;
138 use s_db;
139 create sequence s_db.s1;
140 flush table with read lock;
141 select next value for s1;
142 ERROR HY000: Can't execute the query because you have a conflicting read lock
143 unlock tables;
144 drop sequence s_db.s1;
145 ###########################################
146 query cache test
147 ###########################################
148 connection master;
149 use s_db;
150 show global variables like 'query_cache_type';
151 Variable_name Value
152 query_cache_type ON
153 show status like 'Qcache_hits';
154 Variable_name Value
155 Qcache_hits 0
156 show status like 'Qcache_inserts';
157 Variable_name Value
158 Qcache_inserts 0
159 ###########################################
160 priv test
161 ###########################################
162 connection m_normal_1;
163 create sequence s_db.s1;
164 select * from s_db.s1;
165 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
166 1 1 9223372036854775806 1 1 1000 0 0
167 select next value for s_db.s1;
168 next value for s_db.s1
169 1
170 select * from s_db.s1;
171 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
172 1001 1 9223372036854775806 1 1 1000 0 0
173 create sequence s_db.s2;
174 drop sequence s_db.s2;
175 connection m_normal_2;
176 select next value for s_db.s1;
177 ERROR 42000: SELECT, INSERT command denied to user 'normal_2'@'localhost' for table `s_db`.`s1`
178 create sequence s_db.s2;
179 ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table `s_db`.`s2`
180 connection m_normal_1;
181 drop sequence s_db.s1;
182 ###########################################
183 run out sequence value
184 ###########################################
185 connection m_normal_1;
186 use s_db;
187 create sequence s_t start with 1 cache 2 maxvalue 5;
188 create table t(id int);
189 insert into t values(1111);
190 insert into t select next value for s_t;
191 insert into t select next value for s_t;
192 insert into t select next value for s_t;
193 insert into t select next value for s_t;
194 insert into t select next value for s_t;
195 insert into t select next value for s_t;
196 ERROR HY000: Sequence 's_db.s_t' has run out
197 insert into t select next value for s_t;
198 ERROR HY000: Sequence 's_db.s_t' has run out
199 commit;
200 select * from t;
201 id
202 1111
203 1
204 2
205 3
206 4
207 5
208 connection master;
209 connection slave;
210 connection s_normal_3;
211 use s_db;
212 select * from t;
213 id
214 1111
215 1
216 2
217 3
218 4
219 5
220 connection m_normal_1;
221 use s_db;
222 drop sequence s_t;
223 drop table t;
224 ###########################################
225 read_only prevent query sequence
226 ###########################################
227 connection m_normal_1;
228 create sequence s_db.s1;
229 show global variables like 'read_only';
230 Variable_name Value
231 read_only OFF
232 select next value for s_db.s1;
233 next value for s_db.s1
234 1
235 connection master;
236 connection slave;
237 connection s_normal_3;
238 show global variables like 'read_only';
239 Variable_name Value
240 read_only ON
241 select next value for s_db.s1;
242 ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
243 connection m_normal_1;
244 drop sequence s_db.s1;
245 ###########################################
246 update based table
247 ###########################################
248 connection m_normal_1;
249 use s_db;
250 create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle;
251 connection master;
252 connection slave;
253 connection s_normal_3;
254 use s_db;
255 select * from s_t;
256 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
257 1 1 20 1 1 5 1 0
258 connection m_normal_1;
259 select next value for s_t;
260 next value for s_t
261 1
262 select * from s_t;
263 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
264 6 1 20 1 1 5 1 0
265 connection master;
266 connection slave;
267 connection s_normal_3;
268 select * from s_t;
269 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
270 6 1 20 1 1 5 1 0
271 ------------------------------------------
272 master update nextval;
273 ------------------------------------------
274 connection m_normal_1;
275 select next value for s_t;
276 next value for s_t
277 2
278 update s_t set next_not_cached_value= 11;
279 ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
280 alter sequence s_t restart=11;
281 commit;
282 select * from s_t;
283 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
284 11 1 20 1 1 5 1 0
285 connection master;
286 connection slave;
287 ------------------------------------------
288 show slave nextval;
289 ------------------------------------------
290 connection s_normal_3;
291 select * from s_t;
292 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
293 11 1 20 1 1 5 1 0
294 connection m_normal_1;
295 select next value for s_t;
296 next value for s_t
297 11
298 select * from s_t;
299 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
300 16 1 20 1 1 5 1 0
301 connection master;
302 connection slave;
303 connection s_normal_3;
304 select * from s_t;
305 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
306 16 1 20 1 1 5 1 0
307 ------------------------------------------
308 update into invalid sequence
309 ------------------------------------------
310 connection m_normal_1;
311 select * from s_t;
312 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
313 16 1 20 1 1 5 1 0
314 update s_t set next_not_cached_value= 11,start_value=10, minimum_value=11;
315 ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
316 ALTER SEQUENCE s_t restart with 11 start=10 minvalue=11;
317 ERROR HY000: Sequence 's_db.s_t' has out of range value for options
318 commit;
319 create table t_1(id int);
320 insert into t_1 value(1111);
321 select next value for s_t;
322 next value for s_t
323 12
324 insert into t_1 select next value for s_t;
325 commit;
326 select * from t_1;
327 id
328 1111
329 13
330 ------------------------------------------
331 delete sequence row
332 ------------------------------------------
333 connection m_normal_1;
334 delete from s_t;
335 ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
336 commit;
337 select next value for s_t;
338 next value for s_t
339 14
340 connection m_normal_1;
341 drop sequence s_t;
342 drop table t_1;
343 ###########################################
344 test transaction context (innodb)
345 ###########################################
346 ------------------------------------------
347 transaction table and sequence
348 normal transaction commit
349 ------------------------------------------
350 connection m_normal_1;
351 use s_db;
352 create sequence s_1 cache 5;
353 create table t_1(id int)engine=innodb;
354 begin;
355 insert into t_1 values(1111);
356 insert into t_1 select next value for s_1;
357 insert into t_1 select next value for s_1;
358 insert into t_1 values(2222);
359 commit;
360 select * from t_1;
361 id
362 1111
363 1
364 2
365 2222
366 connection master;
367 connection slave;
368 connection s_normal_3;
369 use s_db;
370 select * from t_1;
371 id
372 1111
373 1
374 2
375 2222
376 ------------------------------------------
377 normal transaction rollback
378 ------------------------------------------
379 connection m_normal_1;
380 begin;
381 insert into t_1 values(3333);
382 insert into t_1 select next value for s_1;
383 insert into t_1 select next value for s_1;
384 insert into t_1 select next value for s_1;
385 insert into t_1 select next value for s_1;
386 insert into t_1 select next value for s_1;
387 insert into t_1 select next value for s_1;
388 insert into t_1 select next value for s_1;
389 insert into t_1 select next value for s_1;
390 select * from t_1;
391 id
392 1111
393 1
394 2
395 2222
396 3333
397 3
398 4
399 5
400 6
401 7
402 8
403 9
404 10
405 rollback;
406 select * from t_1;
407 id
408 1111
409 1
410 2
411 2222
412 select next value for s_1;
413 next value for s_1
414 11
415 connection master;
416 connection slave;
417 connection s_normal_3;
418 use s_db;
419 select * from t_1;
420 id
421 1111
422 1
423 2
424 2222
425 connection m_normal_1;
426 use s_db;
427 drop sequence s_1;
428 drop table t_1;
429 ###########################################
430 test transaction context (myisam)
431 ###########################################
432 ------------------------------------------
433 transaction table and sequence
434 normal transaction commit
435 ------------------------------------------
436 connection m_normal_1;
437 use s_db;
438 create sequence s_1 cache 5;
439 create table t_1(id int)engine=myisam;
440 begin;
441 insert into t_1 values(1111);
442 insert into t_1 select next value for s_1;
443 insert into t_1 select next value for s_1;
444 insert into t_1 values(2222);
445 commit;
446 select * from t_1;
447 id
448 1111
449 1
450 2
451 2222
452 connection master;
453 connection slave;
454 connection s_normal_3;
455 use s_db;
456 select * from t_1;
457 id
458 1111
459 1
460 2
461 2222
462 ------------------------------------------
463 normal transaction rollback
464 ------------------------------------------
465 connection m_normal_1;
466 begin;
467 insert into t_1 values(3333);
468 insert into t_1 select next value for s_1;
469 insert into t_1 select next value for s_1;
470 insert into t_1 select next value for s_1;
471 insert into t_1 select next value for s_1;
472 insert into t_1 select next value for s_1;
473 insert into t_1 select next value for s_1;
474 insert into t_1 select next value for s_1;
475 insert into t_1 select next value for s_1;
476 select * from t_1;
477 id
478 1111
479 1
480 2
481 2222
482 3333
483 3
484 4
485 5
486 6
487 7
488 8
489 9
490 10
491 rollback;
492 Warnings:
493 Warning 1196 Some non-transactional changed tables couldn't be rolled back
494 select * from t_1;
495 id
496 1111
497 1
498 2
499 2222
500 3333
501 3
502 4
503 5
504 6
505 7
506 8
507 9
508 10
509 select next value for s_1;
510 next value for s_1
511 11
512 connection master;
513 connection slave;
514 connection s_normal_3;
515 use s_db;
516 select * from t_1;
517 id
518 1111
519 1
520 2
521 2222
522 3333
523 3
524 4
525 5
526 6
527 7
528 8
529 9
530 10
531 connection m_normal_1;
532 use s_db;
533 drop sequence s_1;
534 drop table t_1;
535 ###########################################
536 close binlog
537 ###########################################
538 connection m_normal_1;
539 use s_db;
540 create sequence s1 cache 2;
541 select next value for s1;
542 next value for s1
543 1
544 select next value for s1;
545 next value for s1
546 2
547 select next value for s1;
548 next value for s1
549 3
550 select next value for s1;
551 next value for s1
552 4
553 commit;
554 select * from s1;
555 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
556 5 1 9223372036854775806 1 1 2 0 0
557 connection master;
558 connection slave;
559 connection slave;
560 use s_db;
561 select * from s1;
562 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
563 5 1 9223372036854775806 1 1 2 0 0
564 ------------------------------------------
565 close session binlog.
566 ------------------------------------------
567 connection master;
568 set session sql_log_bin=off;
569 select next value for s1;
570 next value for s1
571 5
572 select next value for s1;
573 next value for s1
574 6
575 select next value for s1;
576 next value for s1
577 7
578 select next value for s1;
579 next value for s1
580 8
581 set session sql_log_bin=on;
582 select * from s1;
583 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
584 9 1 9223372036854775806 1 1 2 0 0
585 connection master;
586 connection slave;
587 connection slave;
588 use s_db;
589 select * from s1;
590 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
591 5 1 9223372036854775806 1 1 2 0 0
592 connection m_normal_1;
593 use s_db;
594 drop sequence s1;
595 ###########################################
596 statement binlog
597 ###########################################
598 ------------------------------------------
599 set binlog_format=statement
600 ------------------------------------------
601 connection master;
602 set session binlog_format=statement;
603 select @@session.binlog_format;
604 @@session.binlog_format
605 STATEMENT
606 create sequence s1 cache 2;
607 select next value for s1;
608 ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
609 set session binlog_format=row;
610 select next value for s1;
611 next value for s1
612 1
613 select * from s1;
614 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
615 3 1 9223372036854775806 1 1 2 0 0
616 connection master;
617 connection slave;
618 use s_db;
619 select * from s1;
620 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
621 3 1 9223372036854775806 1 1 2 0 0
622 connection m_normal_1;
623 use s_db;
624 drop sequence s1;
625 ###########################################
626 test savepoint
627 ###########################################
628 connection master;
629 set session binlog_format=row;
630 create sequence s1 cache 2;
631 create table t1(id int)engine=innodb;
632 begin;
633 insert into t1 values(1111);
634 savepoint sp1;
635 insert into t1 select next value for s1;
636 insert into t1 select next value for s1;
637 insert into t1 select next value for s1;
638 insert into t1 values(2222);
639 select * from t1;
640 id
641 1111
642 1
643 2
644 3
645 2222
646 rollback to sp1;
647 select * from t1;
648 id
649 1111
650 select next value for s1;
651 next value for s1
652 4
653 commit;
654 drop sequence s1;
655 drop table t1;
656 ###########################################
657 test proc
658 ###########################################
659 connection m_normal_1;
660 use s_db;
661 create table t(id int)engine=innodb;
662 create procedure p1()
663 begin
664 create sequence s1 cache 2;
665 end//
666 create procedure p2()
667 begin
668 insert into t select next value for s1;
669 commit;
670 end//
671 call p1();
672 call p2();
673 call p2();
674 call p2();
675 call p2();
676 select * from t;
677 id
678 1
679 2
680 3
681 4
682 connection master;
683 connection slave;
684 use s_db;
685 select * from t;
686 id
687 1
688 2
689 3
690 4
691 connection m_normal_1;
692 drop table t;
693 drop sequence s1;
694 drop procedure p1;
695 drop procedure p2;
696 ###########################################
697 test trigger
698 ###########################################
699 connection m_normal_1;
700 use s_db;
701 create sequence s1 cache 2;
702 create table t1(id int)engine=innodb;
703 create table t2(id int)engine=innodb;
704 CREATE TRIGGER tri_1
705 before INSERT ON t2 FOR EACH ROW
706 BEGIN
707 INSERT INTO t1 select next value for s1;
708 END//
709 begin;
710 insert into t2 values(1111);
711 insert into t2 values(1111);
712 insert into t2 values(1111);
713 insert into t2 values(1111);
714 select * from t2;
715 id
716 1111
717 1111
718 1111
719 1111
720 select * from t1;
721 id
722 1
723 2
724 3
725 4
726 rollback;
727 select * from t2;
728 id
729 select * from t1;
730 id
731 select next value for s1;
732 next value for s1
733 5
734 drop trigger tri_1;
735 drop table t1;
736 drop table t2;
737 drop sequence s1;
738 ###########################################
739 test function
740 ###########################################
741 connection m_normal_1;
742 use s_db;
743 create sequence s1 cache 2;
744 create table t1(id int)engine=innodb;
745 CREATE function f1() returns int
746 BEGIN
747 INSERT INTO t1 select next value for s1;
748 return (1);
749 END//
750 begin;
751 select f1();
752 f1()
753 1
754 select f1();
755 f1()
756 1
757 select f1();
758 f1()
759 1
760 select f1();
761 f1()
762 1
763 select * from t1;
764 id
765 1
766 2
767 3
768 4
769 rollback;
770 select * from t1;
771 id
772 select next value for s1;
773 next value for s1
774 5
775 drop function f1;
776 drop table t1;
777 drop sequence s1;
778 ##############
779 Test GRANT
780 ##############
781 connection m_normal_2;
782 create table t1 (a int);
783 create sequence s1;
784 select next value for s1;
785 next value for s1
786 1
787 insert into t1 values (1);
788 connection m_normal_3;
789 select * from t1;
790 a
791 1
792 select * from s1;
793 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
794 1001 1 9223372036854775806 1 1 1000 0 0
795 select previous value for s1;
796 previous value for s1
797 NULL
798 insert into t1 values (2);
799 ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`t1`
800 select next value for s1;
801 ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`s1`
802 do setval(s1,1000,0);
803 ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`s1`
804 connection master;
805 grant insert on test.* to normal_5@'%' identified by 'pass';
806 disconnect m_normal_3;
807 connect m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT;
808 insert into t1 values (2);
809 select t1.*, (next value for s1) from t1;
810 a (next value for s1)
811 1 2
812 2 3
813 do setval(s1,10000,0);
814 select * from s1;
815 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
816 10000 1 9223372036854775806 1 1 1000 0 0
817 connection m_normal_2;
818 drop table t1;
819 drop sequence s1;
820 #
821 # Cleanup
822 #
823 connection master;
824 use s_db;
825 drop database s_db;
826 drop user normal_1@'%';
827 drop user normal_2@'%';
828 drop user normal_3@'%';
829 drop user normal_4@'%';
830 drop user normal_5@'%';
831 include/rpl_end.inc