Debian Codegraph @

mysql-test/suite/sql_sequence/replication.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 create database s_db;
5 use s_db;
6 grant all on s_db.* to normal_1@'%' identified by 'pass';
7 grant all on test.* to normal_2@'%' identified by 'pass';
8 grant all on s_db.* to normal_3@'%' identified by 'pass';
9 grant all on test.* to normal_4@'%' identified by 'pass';
10 connection slave;
11 connect m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT;
12 connect m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT;
13 connect s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT;
14 connect s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT;
15 connection slave;
16 set global read_only=on;
17 use s_db;
18 ###########################################
19 master and slave sync sequence.
20 ###########################################
21 connection master;
22 create sequence s1;
23 show create table s1;
24 Table Create Table
25 s1 CREATE TABLE `s1` (
26 `next_not_cached_value` bigint(21) NOT NULL,
27 `minimum_value` bigint(21) NOT NULL,
28 `maximum_value` bigint(21) NOT NULL,
29 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
30 `increment` bigint(21) NOT NULL COMMENT 'increment value',
31 `cache_size` bigint(21) unsigned NOT NULL,
32 `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',
33 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
34 ) ENGINE=MyISAM SEQUENCE=1
35 connection slave;
36 show create table s1;
37 Table Create Table
38 s1 CREATE TABLE `s1` (
39 `next_not_cached_value` bigint(21) NOT NULL,
40 `minimum_value` bigint(21) NOT NULL,
41 `maximum_value` bigint(21) NOT NULL,
42 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
43 `increment` bigint(21) NOT NULL COMMENT 'increment value',
44 `cache_size` bigint(21) unsigned NOT NULL,
45 `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',
46 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
47 ) ENGINE=MyISAM SEQUENCE=1
48 connection master;
49 drop sequence s1;
50 ###########################################
51 not support create table engine=sequence.
52 ###########################################
53 connection master;
54 create table t(id int)engine=sequence;
55 ERROR 42000: Unknown storage engine 'sequence'
56 create table t(id int)engine=innodb;
57 alter table t engine=sequence;
58 ERROR 42000: Unknown storage engine 'sequence'
59 drop table t;
60 ###########################################
61 not support alter sequence table.
62 ###########################################
63 connection master;
64 create sequence s2;
65 alter table s2 add id int;
66 ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns)
67 alter table s2 add index ind_x(start_value);
68 ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys)
69 drop sequence s2;
70 ###########################################
71 support create sequence
72 ###########################################
73 connection master;
74 create table t_1(id int);
75 show create sequence t_1;
76 ERROR 42S02: 's_db.t_1' is not a SEQUENCE
77 drop table t_1;
78 CREATE SEQUENCE `s2` (
79 `next_not_cached_value` bigint(21) NOT NULL,
80 `minimum_value` bigint(21) NOT NULL,
81 `maximum_value` bigint(21) NOT NULL,
82 `start_value` bigint(21) NOT NULL,
83 `increment` bigint(21) NOT NULL,
84 `cache_size` bigint(21) unsigned NOT NULL,
85 `cycle_option` tinyint(1) unsigned NOT NULL,
86 `cycle_count` bigint(21) NOT NULL
87 ) ENGINE=InnoDB sequence=1;
88 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(
89 `next_not_cached_value` bigint(21) NOT NULL,
90 `minimum_value` bigint(21) NOT...' at line 1
91 CREATE TABLE `s2` (
92 `next_not_cached_value` bigint(21) NOT NULL,
93 `minimum_value` bigint(21) NOT NULL,
94 `maximum_value` bigint(21) NOT NULL,
95 `start_value` bigint(21) NOT NULL,
96 `increment` bigint(21) NOT NULL,
97 `cache_size` bigint(21) unsigned NOT NULL,
98 `cycle_option` tinyint(1) unsigned NOT NULL,
99 `cycle_count` bigint(21) NOT NULL
100 ) ENGINE=InnoDB sequence=1;
101 insert into s2 values(1, 1, 10, 1, 2, 1, 1, 0);
102 commit;
103 select * for s2;
104 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's2' at line 1
105 select * from s2;
106 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
107 1 1 10 1 2 1 1 0
108 select NEXT VALUE for s2;
109 NEXT VALUE for s2
110 1
111 select NEXT VALUE for s2;
112 NEXT VALUE for s2
113 3
114 select NEXT VALUE for s2;
115 NEXT VALUE for s2
116 5
117 select NEXT VALUE for s2;
118 NEXT VALUE for s2
119 7
120 select NEXT VALUE for s2;
121 NEXT VALUE for s2
122 9
123 select NEXT VALUE for s2;
124 NEXT VALUE for s2
125 1
126 select NEXT VALUE for s2;
127 NEXT VALUE for s2
128 3
129 select * from s2;
130 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
131 5 1 10 1 2 1 1 1
132 commit;
133 connection master;
134 connection slave;
135 select * from s2;
136 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
137 5 1 10 1 2 1 1 1
138 connection master;
139 drop sequence s2;
140 CREATE TABLE `s2` (
141 `next_not_cached_value` bigint(21) NOT NULL,
142 `minimum_value` bigint(21) NOT NULL,
143 `maximum_value` bigint(21) NOT NULL,
144 `start_value` bigint(21) NOT NULL,
145 `increment` bigint(21) NOT NULL,
146 `cache_size` bigint(21) unsigned NOT NULL,
147 `cycle_option` tinyint(1) unsigned NOT NULL,
148 `cycle_count` bigint(21) NOT NULL
149 ) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1;
150 show create sequence s2;
151 Table Create Table
152 s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
153 drop sequence s2;
154 ###########################################
155 select sequence syntax test
156 ###########################################
157 connection master;
158 create sequence s2;
159 create table t2 (id int);
160 select * from s2;
161 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
162 1 1 9223372036854775806 1 1 1000 0 0
163 select * from t2;
164 id
165 insert into t2 select next value for s2;
166 commit;
167 select NEXT VALUE for s2;
168 NEXT VALUE for s2
169 2
170 select NEXT VALUE for t2;
171 ERROR 42S02: 's_db.t2' is not a SEQUENCE
172 select * from s2, t2;
173 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count id
174 1001 1 9223372036854775806 1 1 1000 0 0 1
175 select * for s2;
176 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's2' at line 1
177 select * for s2, t2;
178 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's2, t2' at line 1
179 connection master;
180 drop sequence s2;
181 drop table t2;
182 ###########################################
183 support rename, not support truncate
184 ###########################################
185 connection master;
186 create sequence s2;
187 alter table s2 rename to s2_1;
188 rename table s2_1 to s2_2;
189 show create sequence s2_2;
190 Table Create Table
191 s2_2 CREATE SEQUENCE `s2_2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
192 select * from s2_2;
193 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
194 1 1 9223372036854775806 1 1 1000 0 0
195 truncate table s2_2;
196 ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s2_2` doesn't have this option
197 rename table s2_2 to s2;
198 drop sequence s2;
199 ###########################################
200 all invalid sequence value
201 ###########################################
202 connection master;
203 create sequence s2 start with 1
204 minvalue 1
205 maxvalue 100000
206 increment by 1
207 cache 10000
208 cycle;
209 drop sequence s2;
210 create sequence s2 start with 1
211 minvalue 1
212 maxvalue 100000
213 increment by 1
214 cache 10000
215 nocycle;
216 drop sequence s2;
217 create sequence s2 start with 1
218 minvalue 1
219 maxvalue 100000
220 increment by 1
221 nocache
222 nocycle;
223 drop sequence s2;
224 create sequence s2 start with 1
225 minvalue 5
226 maxvalue 100000
227 increment by 1
228 nocache
229 nocycle;
230 ERROR HY000: Sequence 's_db.s2' has out of range value for options
231 create sequence s2 start with 1
232 minvalue 5
233 maxvalue 5
234 increment by 1
235 nocache
236 nocycle;
237 ERROR HY000: Sequence 's_db.s2' has out of range value for options
238 create sequence s2 start with 1
239 minvalue 5
240 maxvalue 4
241 increment by 1
242 nocache
243 nocycle;
244 ERROR HY000: Sequence 's_db.s2' has out of range value for options
245 create sequence s2 start with 1
246 minvalue 5
247 maxvalue 4
248 increment by 0
249 nocache
250 nocycle;
251 ERROR HY000: Sequence 's_db.s2' has out of range value for options
252 ###########################################
253 global read lock prevent query sequence
254 ###########################################
255 connection master;
256 create sequence s_db.s1;
257 flush table with read lock;
258 select NEXT VALUE for s1;
259 ERROR HY000: Can't execute the query because you have a conflicting read lock
260 unlock tables;
261 drop sequence s_db.s1;
262 ###########################################
263 query cache test
264 ###########################################
265 connection master;
266 flush global status;
267 show global variables like 'query_cache_type';
268 Variable_name Value
269 query_cache_type ON
270 show status like 'Qcache_hits';
271 Variable_name Value
272 Qcache_hits 0
273 show status like 'Qcache_inserts';
274 Variable_name Value
275 Qcache_inserts 0
276 ###########################################
277 priv test
278 ###########################################
279 connection m_normal_1;
280 create sequence s_db.s1;
281 select NEXT VALUE for s_db.s1;
282 NEXT VALUE for s_db.s1
283 1
284 create sequence s_db.s2;
285 drop sequence s_db.s2;
286 connection m_normal_2;
287 select NEXT VALUE for s_db.s1;
288 ERROR 42000: SELECT, INSERT command denied to user 'normal_2'@'localhost' for table `s_db`.`s1`
289 create sequence s_db.s2;
290 ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table `s_db`.`s2`
291 connection m_normal_1;
292 drop sequence s_db.s1;
293 ###########################################
294 run out sequence value
295 ###########################################
296 connection m_normal_1;
297 create sequence s_t start with 1 cache 2 maxvalue 5;
298 create table t(id int);
299 insert into t values(1111);
300 insert into t select next value for s_t;
301 insert into t select next value for s_t;
302 insert into t select next value for s_t;
303 insert into t select next value for s_t;
304 insert into t select next value for s_t;
305 insert into t select next value for s_t;
306 ERROR HY000: Sequence 's_db.s_t' has run out
307 insert into t select next value for s_t;
308 ERROR HY000: Sequence 's_db.s_t' has run out
309 commit;
310 select * from t;
311 id
312 1111
313 1
314 2
315 3
316 4
317 5
318 connection master;
319 connection slave;
320 connection s_normal_3;
321 select * from t;
322 id
323 1111
324 1
325 2
326 3
327 4
328 5
329 connection m_normal_1;
330 drop sequence s_t;
331 drop table t;
332 ###########################################
333 read_only prevent query sequence
334 ###########################################
335 connection m_normal_1;
336 create sequence s_db.s1;
337 show global variables like 'read_only';
338 Variable_name Value
339 read_only OFF
340 select * from s_db.s1;
341 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
342 1 1 9223372036854775806 1 1 1000 0 0
343 connection master;
344 connection slave;
345 connection s_normal_3;
346 show global variables like 'read_only';
347 Variable_name Value
348 read_only ON
349 select next value for s_db.s1;
350 ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
351 connection m_normal_1;
352 drop sequence s_db.s1;
353 ###########################################
354 update based table
355 ###########################################
356 connection m_normal_1;
357 create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle engine=innodb;
358 connection master;
359 connection slave;
360 connection s_normal_3;
361 select * from s_t;
362 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
363 1 1 20 1 1 5 1 0
364 connection m_normal_1;
365 select next value for s_t;
366 next value for s_t
367 1
368 connection master;
369 connection slave;
370 connection s_normal_3;
371 select next_not_cached_value from s_t;
372 next_not_cached_value
373 6
374 ------------------------------------------
375 master ALTER SEQUENCE
376 ------------------------------------------
377 connection m_normal_1;
378 select next value for s_t;
379 next value for s_t
380 2
381 alter sequence s_t restart= 11;
382 select * from s_t;
383 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
384 11 1 20 1 1 5 1 0
385 connection master;
386 connection slave;
387 ------------------------------------------
388 show slave nextval;
389 ------------------------------------------
390 connection s_normal_3;
391 select * from s_t;
392 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
393 11 1 20 1 1 5 1 0
394 connection m_normal_1;
395 select next value for s_t;
396 next value for s_t
397 11
398 connection master;
399 connection slave;
400 connection s_normal_3;
401 select * from s_t;
402 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
403 16 1 20 1 1 5 1 0
404 ------------------------------------------
405 update into invalid sequence
406 ------------------------------------------
407 connection m_normal_1;
408 select next value for s_t;
409 next value for s_t
410 12
411 select * from s_t;
412 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
413 16 1 20 1 1 5 1 0
414 alter sequence s_t minvalue=11 maxvalue=9;
415 ERROR HY000: Sequence 's_db.s_t' has out of range value for options
416 select * from s_t;
417 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
418 16 1 20 1 1 5 1 0
419 alter sequence s_t restart= 12 start=10 minvalue=11 maxvalue=20;
420 ERROR HY000: Sequence 's_db.s_t' has out of range value for options
421 select * from s_t;
422 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
423 16 1 20 1 1 5 1 0
424 ------------------------------------------
425 delete sequence row
426 ------------------------------------------
427 connection m_normal_1;
428 delete from s_t;
429 ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
430 commit;
431 select next value for s_t;
432 next value for s_t
433 13
434 connection m_normal_1;
435 drop sequence s_t;
436 ###########################################
437 test transaction context (innodb)
438 ###########################################
439 ------------------------------------------
440 transaction table and sequence
441 normal transaction commit
442 ------------------------------------------
443 connection m_normal_1;
444 create sequence s_1 cache 5 engine=innodb;
445 create table t_1(id int)engine=innodb;
446 begin;
447 insert into t_1 values(1111);
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 values(2222);
451 commit;
452 select * from t_1;
453 id
454 1111
455 1
456 2
457 2222
458 select * from s_1;
459 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
460 6 1 9223372036854775806 1 1 5 0 0
461 connection master;
462 connection slave;
463 connection s_normal_3;
464 select * from t_1;
465 id
466 1111
467 1
468 2
469 2222
470 ------------------------------------------
471 normal transaction rollback
472 ------------------------------------------
473 connection m_normal_1;
474 begin;
475 insert into t_1 values(3333);
476 insert into t_1 select next value for s_1;
477 insert into t_1 select next value for s_1;
478 insert into t_1 select next value for s_1;
479 insert into t_1 select next value for s_1;
480 insert into t_1 select next value for s_1;
481 insert into t_1 select next value for s_1;
482 insert into t_1 select next value for s_1;
483 insert into t_1 select next value for s_1;
484 select * from t_1;
485 id
486 1111
487 1
488 2
489 2222
490 3333
491 3
492 4
493 5
494 6
495 7
496 8
497 9
498 10
499 rollback;
500 select * from t_1;
501 id
502 1111
503 1
504 2
505 2222
506 select * from s_1;
507 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
508 11 1 9223372036854775806 1 1 5 0 0
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 select * from t_1;
516 id
517 1111
518 1
519 2
520 2222
521 connection m_normal_1;
522 drop sequence s_1;
523 drop table t_1;
524 ###########################################
525 test transaction context (myisam)
526 ###########################################
527 ------------------------------------------
528 transaction table and sequence
529 normal transaction commit
530 ------------------------------------------
531 connection m_normal_1;
532 create sequence s_1 cache 5;
533 create table t_1(id int)engine=myisam;
534 begin;
535 insert into t_1 values(1111);
536 insert into t_1 select next value for s_1;
537 insert into t_1 select next value for s_1;
538 insert into t_1 values(2222);
539 commit;
540 select * from t_1;
541 id
542 1111
543 1
544 2
545 2222
546 connection master;
547 connection slave;
548 connection s_normal_3;
549 select * from t_1;
550 id
551 1111
552 1
553 2
554 2222
555 ------------------------------------------
556 normal transaction rollback
557 ------------------------------------------
558 connection m_normal_1;
559 begin;
560 insert into t_1 values(3333);
561 insert into t_1 select next value for s_1;
562 insert into t_1 select next value for s_1;
563 insert into t_1 select next value for s_1;
564 insert into t_1 select next value for s_1;
565 insert into t_1 select next value for s_1;
566 insert into t_1 select next value for s_1;
567 insert into t_1 select next value for s_1;
568 insert into t_1 select next value for s_1;
569 select * from t_1;
570 id
571 1111
572 1
573 2
574 2222
575 3333
576 3
577 4
578 5
579 6
580 7
581 8
582 9
583 10
584 rollback;
585 Warnings:
586 Warning 1196 Some non-transactional changed tables couldn't be rolled back
587 select * from t_1;
588 id
589 1111
590 1
591 2
592 2222
593 3333
594 3
595 4
596 5
597 6
598 7
599 8
600 9
601 10
602 select next value for s_1;
603 next value for s_1
604 11
605 connection master;
606 connection slave;
607 connection s_normal_3;
608 select * from t_1;
609 id
610 1111
611 1
612 2
613 2222
614 3333
615 3
616 4
617 5
618 6
619 7
620 8
621 9
622 10
623 connection m_normal_1;
624 drop sequence s_1;
625 drop table t_1;
626 ###########################################
627 close binlog
628 ###########################################
629 connection m_normal_1;
630 create sequence s1 cache 2;
631 select next value for s1;
632 next value for s1
633 1
634 select next value for s1;
635 next value for s1
636 2
637 select next value for s1;
638 next value for s1
639 3
640 select next value for s1;
641 next value for s1
642 4
643 commit;
644 select * from s1;
645 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
646 5 1 9223372036854775806 1 1 2 0 0
647 connection master;
648 connection slave;
649 select * from s1;
650 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
651 5 1 9223372036854775806 1 1 2 0 0
652 ------------------------------------------
653 close session binlog.
654 ------------------------------------------
655 connection master;
656 set session sql_log_bin=off;
657 select next value for s1;
658 next value for s1
659 5
660 select next value for s1;
661 next value for s1
662 6
663 select next value for s1;
664 next value for s1
665 7
666 select next value for s1;
667 next value for s1
668 8
669 set session sql_log_bin=on;
670 select * from s1;
671 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
672 9 1 9223372036854775806 1 1 2 0 0
673 connection master;
674 connection slave;
675 select * from s1;
676 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
677 5 1 9223372036854775806 1 1 2 0 0
678 connection master;
679 select next value for s1;
680 next value for s1
681 9
682 select * from s1;
683 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
684 11 1 9223372036854775806 1 1 2 0 0
685 connection slave;
686 select * from s1;
687 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
688 11 1 9223372036854775806 1 1 2 0 0
689 connection master;
690 drop sequence s1;
691 ###########################################
692 statement binlog
693 ###########################################
694 ------------------------------------------
695 set binlog_format=statement
696 ------------------------------------------
697 connection master;
698 set session binlog_format=statement;
699 select @@session.binlog_format;
700 @@session.binlog_format
701 STATEMENT
702 create sequence s1 cache 2;
703 select next value for s1;
704 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.
705 set session binlog_format=row;
706 select next value for s1;
707 next value for s1
708 1
709 connection master;
710 connection slave;
711 select * from s1;
712 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
713 3 1 9223372036854775806 1 1 2 0 0
714 connection m_normal_1;
715 drop sequence s1;
716 ------------------------------------------
717 set binlog_format=mixed
718 ------------------------------------------
719 connection master;
720 set session binlog_format=mixed;
721 select @@session.binlog_format;
722 @@session.binlog_format
723 MIXED
724 create sequence s1 cache 2;
725 select next value for s1;
726 next value for s1
727 1
728 set session binlog_format=row;
729 select next value for s1;
730 next value for s1
731 2
732 select * from s1;
733 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
734 3 1 9223372036854775806 1 1 2 0 0
735 connection master;
736 connection slave;
737 select * from s1;
738 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
739 3 1 9223372036854775806 1 1 2 0 0
740 connection m_normal_1;
741 drop sequence s1;
742 connection master;
743 connection slave;
744 ###########################################
745 test savepoint
746 ###########################################
747 connection master;
748 set session binlog_format=row;
749 create sequence s1 cache 2;
750 create table t1(id int)engine=innodb;
751 begin;
752 insert into t1 values(1111);
753 savepoint sp1;
754 insert into t1 select next value for s1;
755 insert into t1 select next value for s1;
756 insert into t1 select next value for s1;
757 insert into t1 values(2222);
758 select * from t1;
759 id
760 1111
761 1
762 2
763 3
764 2222
765 rollback to sp1;
766 select * from t1;
767 id
768 1111
769 select next value for s1;
770 next value for s1
771 4
772 commit;
773 drop sequence s1;
774 drop table t1;
775 connection master;
776 connection slave;
777 ###########################################
778 create as
779 ###########################################
780 connection m_normal_1;
781 create sequence s1 cache 2;
782 create table t as select * from s1;
783 select * from t;
784 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
785 1 1 9223372036854775806 1 1 2 0 0
786 drop table t;
787 create table t as select next value for s1;
788 select * from t;
789 next value for s1
790 1
791 drop table t;
792 drop sequence s1;
793 connection master;
794 connection slave;
795 ###########################################
796 test proc
797 ###########################################
798 connection m_normal_1;
799 create table t(id int)engine=innodb;
800 create procedure p1()
801 begin
802 create sequence s1 cache 2;
803 end//
804 create procedure p2()
805 begin
806 insert into t select next value for s1;
807 commit;
808 end//
809 call p1();
810 call p2();
811 call p2();
812 call p2();
813 call p2();
814 select * from t;
815 id
816 1
817 2
818 3
819 4
820 connection master;
821 connection slave;
822 select * from t;
823 id
824 1
825 2
826 3
827 4
828 connection m_normal_1;
829 drop table t;
830 drop sequence s1;
831 drop procedure p1;
832 drop procedure p2;
833 ###########################################
834 test trigger
835 ###########################################
836 connection m_normal_1;
837 create sequence s1 cache 2;
838 create table t1(id int)engine=innodb;
839 create table t2(id int)engine=innodb;
840 CREATE TRIGGER tri_1
841 before INSERT ON t2 FOR EACH ROW
842 BEGIN
843 INSERT INTO t1 select next value for s1;
844 END//
845 begin;
846 insert into t2 values(1111);
847 insert into t2 values(1111);
848 insert into t2 values(1111);
849 insert into t2 values(1111);
850 select * from t2;
851 id
852 1111
853 1111
854 1111
855 1111
856 select * from t1;
857 id
858 1
859 2
860 3
861 4
862 rollback;
863 select * from t2;
864 id
865 select * from t1;
866 id
867 select next value for s1;
868 next value for s1
869 5
870 drop trigger tri_1;
871 drop table t1;
872 drop table t2;
873 drop sequence s1;
874 ###########################################
875 test function
876 ###########################################
877 connection m_normal_1;
878 create sequence s1 cache 2;
879 create table t1(id int)engine=innodb;
880 CREATE function f1() returns int
881 BEGIN
882 INSERT INTO t1 select next value for s1;
883 return (1);
884 END//
885 begin;
886 select f1();
887 f1()
888 1
889 select f1();
890 f1()
891 1
892 select f1();
893 f1()
894 1
895 select f1();
896 f1()
897 1
898 select * from t1;
899 id
900 1
901 2
902 3
903 4
904 rollback;
905 select * from t1;
906 id
907 select next value for s1;
908 next value for s1
909 5
910 drop function f1;
911 drop table t1;
912 drop sequence s1;
913 ###########################################
914 test value boundary
915 ###########################################
916 connection m_normal_1;
917 ------------------------------------------
918 cycle_count increment by cycle_count
919 ------------------------------------------
920 create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle;
921 select next value for s1;
922 next value for s1
923 5
924 select next value for s1;
925 next value for s1
926 6
927 select next value for s1;
928 next value for s1
929 7
930 select next value for s1;
931 next value for s1
932 2
933 select next value for s1;
934 next value for s1
935 3
936 drop sequence s1;
937 create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle;
938 select next value for s1;
939 next value for s1
940 5
941 select next value for s1;
942 next value for s1
943 6
944 select next value for s1;
945 next value for s1
946 7
947 select next value for s1;
948 ERROR HY000: Sequence 's_db.s1' has run out
949 select * from s1;
950 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
951 8 2 7 5 1 10 0 0
952 drop sequence s1;
953 create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle;
954 select next value for s1;
955 next value for s1
956 2
957 select next_not_cached_value,cycle_count from s1;
958 next_not_cached_value cycle_count
959 4 0
960 select next value for s1;
961 next value for s1
962 1
963 select next_not_cached_value,cycle_count from s1;
964 next_not_cached_value cycle_count
965 4 1
966 select next value for s1;
967 next value for s1
968 1
969 select next_not_cached_value,cycle_count from s1;
970 next_not_cached_value cycle_count
971 4 2
972 select next value for s1;
973 next value for s1
974 1
975 select next_not_cached_value,cycle_count from s1;
976 next_not_cached_value cycle_count
977 4 3
978 select next value for s1;
979 next value for s1
980 1
981 select next_not_cached_value,cycle_count from s1;
982 next_not_cached_value cycle_count
983 4 4
984 drop sequence s1;
985 create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle;
986 select next value for s1;
987 next value for s1
988 2
989 select next value for s1;
990 ERROR HY000: Sequence 's_db.s1' has run out
991 drop sequence s1;
992 ------------------------------------------
993 beyond ulonglong maxvalue
994 ------------------------------------------
995 create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 1 cycle;
996 select next value for s1, cycle_count from s1;
997 next value for s1 cycle_count
998 9223372036854775805 0
999 select next value for s1, cycle_count from s1;
1000 next value for s1 cycle_count
1001 9223372036854775806 0
1002 select next value for s1, cycle_count from s1;
1003 next value for s1 cycle_count
1004 9223372036854775804 0
1005 select next value for s1, cycle_count from s1;
1006 next value for s1 cycle_count
1007 9223372036854775805 1
1008 select next value for s1, cycle_count from s1;
1009 next value for s1 cycle_count
1010 9223372036854775806 1
1011 select next value for s1, cycle_count from s1;
1012 next value for s1 cycle_count
1013 9223372036854775804 1
1014 select next value for s1, cycle_count from s1;
1015 next value for s1 cycle_count
1016 9223372036854775805 2
1017 select next value for s1, cycle_count from s1;
1018 next value for s1 cycle_count
1019 9223372036854775806 2
1020 select next value for s1, cycle_count from s1;
1021 next value for s1 cycle_count
1022 9223372036854775804 2
1023 select next value for s1, cycle_count from s1;
1024 next value for s1 cycle_count
1025 9223372036854775805 3
1026 drop sequence s1;
1027 create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 10 cycle;
1028 select next value for s1, cycle_count from s1;
1029 next value for s1 cycle_count
1030 9223372036854775805 0
1031 select next value for s1, cycle_count from s1;
1032 next value for s1 cycle_count
1033 9223372036854775806 0
1034 select next value for s1, cycle_count from s1;
1035 next value for s1 cycle_count
1036 9223372036854775804 0
1037 select next value for s1, cycle_count from s1;
1038 next value for s1 cycle_count
1039 9223372036854775805 1
1040 select next value for s1, cycle_count from s1;
1041 next value for s1 cycle_count
1042 9223372036854775806 1
1043 select next value for s1, cycle_count from s1;
1044 next value for s1 cycle_count
1045 9223372036854775804 1
1046 select next value for s1, cycle_count from s1;
1047 next value for s1 cycle_count
1048 9223372036854775805 2
1049 select next value for s1, cycle_count from s1;
1050 next value for s1 cycle_count
1051 9223372036854775806 2
1052 select next value for s1, cycle_count from s1;
1053 next value for s1 cycle_count
1054 9223372036854775804 2
1055 select next value for s1, cycle_count from s1;
1056 next value for s1 cycle_count
1057 9223372036854775805 3
1058 drop sequence s1;
1059 ###########################################
1060 test default()
1061 ###########################################
1062 connection master;
1063 CREATE SEQUENCE s1 nocache engine=myisam;
1064 CREATE table t1 (a int default next value for s1, b int);
1065 insert into t1 (b) values (1),(2);
1066 select default(a) from t1;
1067 default(a)
1068 3
1069 4
1070 select * from t1;
1071 a b
1072 1 1
1073 2 2
1074 select * from s1;
1075 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1076 5 1 9223372036854775806 1 1 0 0 0
1077 connection slave;
1078 connection s_normal_3;
1079 select * from t1;
1080 a b
1081 1 1
1082 2 2
1083 select * from s1;
1084 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1085 5 1 9223372036854775806 1 1 0 0 0
1086 connection master;
1087 drop table t1,s1;
1088 connection master;
1089 drop database s_db;
1090 drop user normal_1@'%';
1091 drop user normal_2@'%';
1092 drop user normal_3@'%';
1093 drop user normal_4@'%';
1094 include/rpl_end.inc