diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/replication.result')
| -rw-r--r-- | mysql-test/suite/sql_sequence/replication.result | 1030 |
1 files changed, 1030 insertions, 0 deletions
diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result new file mode 100644 index 00000000000..eed4c130a9b --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication.result @@ -0,0 +1,1030 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +create database s_db; +grant all on s_db.* to normal_1@'%' identified by 'pass'; +grant all on test.* to normal_2@'%' identified by 'pass'; +grant all on s_db.* to normal_3@'%' identified by 'pass'; +grant all on test.* to normal_4@'%' identified by 'pass'; +set global read_only=on; +########################################### +master and slave sync sequence. +########################################### +use s_db; +create sequence s1; +show create table s1; +Table Create Table +s1 CREATE SEQUENCE `s1` ( + `currval` bigint(21) NOT NULL COMMENT 'current value', + `nextval` bigint(21) NOT NULL COMMENT 'next value', + `minvalue` bigint(21) NOT NULL COMMENT 'min value', + `maxvalue` bigint(21) NOT NULL COMMENT 'max value', + `start` bigint(21) NOT NULL COMMENT 'start value', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache` bigint(21) NOT NULL COMMENT 'cache size', + `cycle` bigint(21) NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +use s_db; +show create table s1; +Table Create Table +s1 CREATE SEQUENCE `s1` ( + `currval` bigint(21) NOT NULL COMMENT 'current value', + `nextval` bigint(21) NOT NULL COMMENT 'next value', + `minvalue` bigint(21) NOT NULL COMMENT 'min value', + `maxvalue` bigint(21) NOT NULL COMMENT 'max value', + `start` bigint(21) NOT NULL COMMENT 'start value', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache` bigint(21) NOT NULL COMMENT 'cache size', + `cycle` bigint(21) NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +use s_db; +drop sequence s1; +########################################### +not support create table engine=sequence. +########################################### +create table t(id int)engine=sequence; +ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +create table t(id int)engine=innodb; +alter table t engine=sequence; +ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +drop table t; +########################################### +not support alter sequence table. +########################################### +create sequence s2; +alter table s2 add id int; +ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +alter table s2 add index ind_x(start); +ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +drop sequence s2; +########################################### +support create sequence +########################################### +create table t_1(id int); +show create sequence t_1; +ERROR HY000: 's_db.t_1' is not SEQUENCE +drop table t_1; +CREATE SEQUENCE `s2` ( +`currval` bigint(21) NOT NULL COMMENT 'current value', +`nextval` bigint(21) NOT NULL COMMENT 'next value', +`minvalue` bigint(21) NOT NULL COMMENT 'min value', +`maxvalue` bigint(21) NOT NULL COMMENT 'max value', +`start` bigint(21) NOT NULL COMMENT 'start value', +`increment` bigint(21) NOT NULL COMMENT 'increment value', +`cache` bigint(21) NOT NULL COMMENT 'cache size', +`cycle` bigint(21) NOT NULL COMMENT 'cycle state', +`round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into s2 values(0, 0, 1, 10, 1, 2, 1, 1, 0); +commit; +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 10 1 2 1 1 0 +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 3 1 10 1 2 1 1 0 +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 5 1 10 1 2 1 1 0 +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 7 1 10 1 2 1 1 0 +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 9 1 10 1 2 1 1 0 +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 10 1 2 1 1 1 +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 3 1 10 1 2 1 1 1 +select * from s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 5 1 10 1 2 1 1 1 +select * from s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 5 1 10 1 2 1 1 1 +drop sequence s2; +CREATE SEQUENCE `s2` ( +`currval` bigint(21) NULL COMMENT 'current value', +`nextval` bigint(21) NOT NULL COMMENT 'next value', +`minvalue` bigint(21) NOT NULL COMMENT 'min value', +`maxvalue` bigint(21) NOT NULL COMMENT 'max value', +`start` bigint(21) NOT NULL COMMENT 'start value', +`increment` bigint(21) NOT NULL COMMENT 'increment value', +`cache` bigint(21) NOT NULL COMMENT 'cache size', +`cycle` bigint(21) NOT NULL COMMENT 'cycle state', +`round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +CREATE SEQUENCE `s2` ( +`rrval` bigint(21) NULL COMMENT 'current value', +`nextval` bigint(21) NOT NULL COMMENT 'next value', +`minvalue` bigint(21) NOT NULL COMMENT 'min value', +`maxvalue` bigint(21) NOT NULL COMMENT 'max value', +`start` bigint(21) NOT NULL COMMENT 'start value', +`increment` bigint(21) NOT NULL COMMENT 'increment value', +`cache` bigint(21) NOT NULL COMMENT 'cache size', +`cycle` bigint(21) NOT NULL COMMENT 'cycle state', +`round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +CREATE SEQUENCE `s2` ( +`currval` bigint(21) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'current value', +`nextval` bigint(21) NOT NULL COMMENT 'next value', +`minvalue` bigint(21) NOT NULL COMMENT 'min value', +`maxvalue` bigint(21) NOT NULL COMMENT 'max value', +`start` bigint(21) NOT NULL COMMENT 'start value', +`increment` bigint(21) NOT NULL COMMENT 'increment value', +`cache` bigint(21) NOT NULL COMMENT 'cache size', +`cycle` bigint(21) NOT NULL COMMENT 'cycle state', +`round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +CREATE SEQUENCE `s2` ( +`currval` bigint(21) NOT NULL COMMENT 'current value', +`nextval` bigint(21) NOT NULL COMMENT 'next value', +`minvalue` bigint(21) NOT NULL COMMENT 'min value', +`maxvalue` bigint(21) NOT NULL COMMENT 'max value', +`start` bigint(21) NOT NULL COMMENT 'start value', +`increment` bigint(21) NOT NULL COMMENT 'increment value', +`cache` bigint(21) NOT NULL COMMENT 'cache size', +`cycle` bigint(21) NOT NULL COMMENT 'cycle state', +`round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=myisam DEFAULT CHARSET=latin1; +show create sequence s2; +Table Create Table +s2 CREATE SEQUENCE `s2` ( + `currval` bigint(21) NOT NULL COMMENT 'current value', + `nextval` bigint(21) NOT NULL COMMENT 'next value', + `minvalue` bigint(21) NOT NULL COMMENT 'min value', + `maxvalue` bigint(21) NOT NULL COMMENT 'max value', + `start` bigint(21) NOT NULL COMMENT 'start value', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache` bigint(21) NOT NULL COMMENT 'cache size', + `cycle` bigint(21) NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop sequence s2; +########################################### +select sequence syntax test +########################################### +create sequence s2; +create table t2 (id int); +select * from s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 0 1 9223372036854775807 1 1 10000 0 0 +select * from t2; +id +insert into t2 select nextval for s2; +commit; +select * for s2; +currval nextval minvalue maxvalue start increment cache cycle round +0 2 1 9223372036854775807 1 1 10000 0 0 +select * for t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use +select * from s2, t2; +currval nextval minvalue maxvalue start increment cache cycle round id +0 10002 1 9223372036854775807 1 1 10000 0 0 1 +select * for s2, t2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' t2' at line 1 +drop sequence s2; +drop table t2; +########################################### +support rename, not support truncate +########################################### +create sequence s2; +alter table s2 rename to s2_1; +ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +rename table s2 to s2_1; +select * for s2_1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 10000 0 0 +truncate table s2_1; +ERROR HY000: Table storage engine for 's2_1' doesn't have this option +rename table s2_1 to s2; +drop sequence s2; +########################################### +not support create temproary sequence. +########################################### +create temporary sequence s2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sequence s2' at line 1 +########################################### +all invalid sequence value +########################################### +use s_db; +create sequence s2 start with 1 +minvalue 1 +maxvalue 100000 +increment by 1 +cache 10000 +cycle; +drop sequence s2; +create sequence s2 start with 1 +minvalue 1 +maxvalue 100000 +increment by 1 +cache 10000 +nocycle; +drop sequence s2; +create sequence s2 start with 1 +minvalue 1 +maxvalue 100000 +increment by 1 +nocache +nocycle; +drop sequence s2; +create sequence s2 start with 1 +minvalue 5 +maxvalue 100000 +increment by 1 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +create sequence s2 start with 1 +minvalue 5 +maxvalue 5 +increment by 1 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +create sequence s2 start with 1 +minvalue 5 +maxvalue 4 +increment by 1 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +create sequence s2 start with 1 +minvalue 5 +maxvalue 4 +increment by 0 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +########################################### +global read lock prevent query sequence +########################################### +use s_db; +create sequence s_db.s1; +flush table with read lock; +select * for s1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop sequence s_db.s1; +########################################### +session setting +########################################### +use s_db; +create sequence s1; +set session sequence_read_skip_cache=true; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 0 1 9223372036854775807 1 1 10000 0 0 +select nextval for s1; +nextval +0 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 0 1 9223372036854775807 1 1 10000 0 0 +select nextval for s1; +nextval +0 +set session sequence_read_skip_cache=false; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 10000 0 0 +select nextval for s1; +nextval +2 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 3 1 9223372036854775807 1 1 10000 0 0 +select nextval for s1; +nextval +4 +drop sequence s1; +########################################### +query cache test +########################################### +use s_db; +show global variables like 'query_cache_type'; +Variable_name Value +query_cache_type ON +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +show status like 'Qcache_inserts'; +Variable_name Value +Qcache_inserts 1 +########################################### +priv test +########################################### +create sequence s_db.s1; +select * for s_db.s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 10000 0 0 +create sequence s_db.s2; +drop sequence s_db.s2; +select * for s_db.s1; +ERROR 42000: SELECT command denied to user 'normal_2'@'localhost' for table 's1' +create sequence s_db.s2; +ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table 's2' +drop sequence s_db.s1; +########################################### +run out sequence value +########################################### +use s_db; +create sequence s_t start with 1 cache 2 maxvalue 5; +create table t(id int); +insert into t values(1111); +insert into t select nextval for s_t; +insert into t select nextval for s_t; +insert into t select nextval for s_t; +insert into t select nextval for s_t; +insert into t select nextval for s_t; +insert into t select nextval for s_t; +ERROR HY000: Sequence 's_db.s_t' has been run out. +insert into t select nextval for s_t; +ERROR HY000: Sequence 's_db.s_t' has been run out. +commit; +select * from t; +id +1111 +1 +2 +3 +4 +5 +use s_db; +select * from t; +id +1111 +1 +2 +3 +4 +5 +use s_db; +drop sequence s_t; +drop table t; +########################################### +read_only prevent query sequence +########################################### +create sequence s_db.s1; +show global variables like 'read_only'; +Variable_name Value +read_only OFF +select * for s_db.s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 10000 0 0 +show global variables like 'read_only'; +Variable_name Value +read_only ON +select * for s_db.s1; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +drop sequence s_db.s1; +########################################### +update based table +########################################### +use s_db; +create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle; +use s_db; +select * from s_t; +currval nextval minvalue maxvalue start increment cache cycle round +0 0 1 20 1 1 5 1 0 +select nextval for s_t; +nextval +1 +select nextval from s_t; +nextval +7 +------------------------------------------ +master update nextval; +------------------------------------------ +select nextval for s_t; +nextval +2 +update s_t set nextval= 11; +commit; +select * from s_t; +currval nextval minvalue maxvalue start increment cache cycle round +0 11 1 20 1 1 5 1 0 +------------------------------------------ +show slave nextval; +------------------------------------------ +select * from s_t; +currval nextval minvalue maxvalue start increment cache cycle round +0 11 1 20 1 1 5 1 0 +set session sequence_read_skip_cache=off; +select * for s_t; +currval nextval minvalue maxvalue start increment cache cycle round +0 11 1 20 1 1 5 1 0 +select * from s_t; +currval nextval minvalue maxvalue start increment cache cycle round +0 17 1 20 1 1 5 1 0 +------------------------------------------ +update into invalid sequence +------------------------------------------ +select nextval for s_t; +nextval +12 +update s_t set nextval= 11,start=10, minvalue=11; +commit; +create table t_1(id int); +insert into t_1 value(1111); +select nextval for s_t; +ERROR HY000: Sequence 's_db.s_t' structure or number is invalid. +insert into t_1 select nextval for s_t; +ERROR HY000: Sequence 's_db.s_t' structure or number is invalid. +commit; +select * from t_1; +id +1111 +------------------------------------------ +delete sequence row +------------------------------------------ +delete from s_t; +commit; +select nextval for s_t; +nextval +drop sequence s_t; +drop table t_1; +########################################### +test transaction context (innodb) +########################################### +------------------------------------------ +transaction table and sequence +normal transaction commit +------------------------------------------ +use s_db; +set session sequence_read_skip_cache=off; +create sequence s_1 cache 5; +create table t_1(id int)engine=innodb; +begin; +insert into t_1 values(1111); +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 values(2222); +commit; +select * from t_1; +id +1111 +1 +2 +2222 +set session sequence_read_skip_cache=off; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +------------------------------------------ +normal transaction rollback +------------------------------------------ +begin; +insert into t_1 values(3333); +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +select * from t_1; +id +1111 +1 +2 +2222 +3333 +3 +4 +5 +6 +7 +8 +9 +10 +rollback; +select * from t_1; +id +1111 +1 +2 +2222 +select nextval for s_1; +nextval +11 +set session sequence_read_skip_cache=off; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +use s_db; +drop sequence s_1; +drop table t_1; +########################################### +test transaction context (myisam) +########################################### +------------------------------------------ +transaction table and sequence +normal transaction commit +------------------------------------------ +use s_db; +set session sequence_read_skip_cache=off; +create sequence s_1 cache 5; +create table t_1(id int)engine=myisam; +begin; +insert into t_1 values(1111); +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 values(2222); +commit; +select * from t_1; +id +1111 +1 +2 +2222 +set session sequence_read_skip_cache=off; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +------------------------------------------ +normal transaction rollback +------------------------------------------ +begin; +insert into t_1 values(3333); +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +insert into t_1 select nextval for s_1; +select * from t_1; +id +1111 +1 +2 +2222 +3333 +3 +4 +5 +6 +7 +8 +9 +10 +rollback; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select * from t_1; +id +1111 +1 +2 +2222 +3333 +3 +4 +5 +6 +7 +8 +9 +10 +select nextval for s_1; +nextval +11 +set session sequence_read_skip_cache=off; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +3333 +3 +4 +5 +6 +7 +8 +9 +10 +use s_db; +drop sequence s_1; +drop table t_1; +########################################### +close binlog +########################################### +use s_db; +create sequence s1 cache 2; +select nextval for s1; +nextval +1 +select nextval for s1; +nextval +2 +select nextval for s1; +nextval +3 +select nextval for s1; +nextval +4 +commit; +select * from s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 7 1 9223372036854775807 1 1 2 0 0 +use s_db; +select * from s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 7 1 9223372036854775807 1 1 2 0 0 +------------------------------------------ +close session binlog. +------------------------------------------ +set session sql_log_bin=off; +select nextval for s1; +nextval +5 +select nextval for s1; +nextval +6 +select nextval for s1; +nextval +7 +select nextval for s1; +nextval +8 +set session sql_log_bin=on; +select * from s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 10 1 9223372036854775807 1 1 2 0 0 +use s_db; +select * from s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 7 1 9223372036854775807 1 1 2 0 0 +use s_db; +drop sequence s1; +########################################### +statement binlog +########################################### +------------------------------------------ +set binlog_format=statement +------------------------------------------ +set session sequence_read_skip_cache=off; +set session binlog_format=statement; +show session variables like '%binlog_format%'; +Variable_name Value +binlog_format STATEMENT +create sequence s1 cache 2; +select nextval for s1; +ERROR HY000: Sequence requires binlog_format= row +set session binlog_format=row; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 2 0 0 +use s_db; +select * from s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 4 1 9223372036854775807 1 1 2 0 0 +set session sequence_read_skip_cache=off; +use s_db; +drop sequence s1; +------------------------------------------ +set binlog_format=mixed +------------------------------------------ +set session sequence_read_skip_cache=off; +set session binlog_format=mixed; +show session variables like '%binlog_format%'; +Variable_name Value +binlog_format MIXED +create sequence s1 cache 2; +select nextval for s1; +ERROR HY000: Sequence requires binlog_format= row +set session binlog_format=row; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 2 0 0 +use s_db; +select * from s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 4 1 9223372036854775807 1 1 2 0 0 +set session sequence_read_skip_cache=off; +use s_db; +drop sequence s1; +########################################### +test savepoint +########################################### +set session sequence_read_skip_cache=off; +set session binlog_format=row; +create sequence s1 cache 2; +create table t1(id int)engine=innodb; +begin; +insert into t1 values(1111); +savepoint sp1; +insert into t1 select nextval for s1; +insert into t1 select nextval for s1; +insert into t1 select nextval for s1; +insert into t1 values(2222); +select * from t1; +id +1111 +1 +2 +3 +2222 +rollback to sp1; +select * from t1; +id +1111 +select nextval for s1; +nextval +4 +commit; +drop sequence s1; +drop table t1; +########################################### +create as +########################################### +set session sequence_read_skip_cache=off; +create sequence s1 cache 2; +create table t as select * for s1; +select * from t; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 9223372036854775807 1 1 2 0 0 +drop sequence s1; +drop table t; +########################################### +test proc +########################################### +set session sequence_read_skip_cache=off; +use s_db; +create table t(id int)engine=innodb; +create procedure p1() +begin +create sequence s1 cache 2; +end// +create procedure p2() +begin +insert into t select nextval for s1; +commit; +end// +call p1(); +call p2(); +call p2(); +call p2(); +call p2(); +select * from t; +id +1 +2 +3 +4 +use s_db; +select * from t; +id +1 +2 +3 +4 +drop table t; +drop sequence s1; +drop procedure p1; +drop procedure p2; +########################################### +test trigger +########################################### +set session sequence_read_skip_cache=off; +use s_db; +create sequence s1 cache 2; +create table t1(id int)engine=innodb; +create table t2(id int)engine=innodb; +CREATE TRIGGER tri_1 +before INSERT ON t2 FOR EACH ROW +BEGIN +INSERT INTO t1 select nextval for s1; +END// +begin; +insert into t2 values(1111); +insert into t2 values(1111); +insert into t2 values(1111); +insert into t2 values(1111); +select * from t2; +id +1111 +1111 +1111 +1111 +select * from t1; +id +1 +2 +3 +4 +rollback; +select * from t2; +id +select * from t1; +id +select nextval for s1; +nextval +5 +drop trigger tri_1; +drop table t1; +drop table t2; +drop sequence s1; +########################################### +test function +########################################### +set session sequence_read_skip_cache=off; +use s_db; +create sequence s1 cache 2; +create table t1(id int)engine=innodb; +CREATE function f1() returns int +BEGIN +INSERT INTO t1 select nextval for s1; +return (1); +END// +begin; +select f1(); +f1() +1 +select f1(); +f1() +1 +select f1(); +f1() +1 +select f1(); +f1() +1 +select * from t1; +id +1 +2 +3 +4 +rollback; +select * from t1; +id +select nextval for s1; +nextval +5 +drop function f1; +drop table t1; +drop sequence s1; +########################################### +test value boundary +########################################### +use s_db; +------------------------------------------ +round increment by round +------------------------------------------ +create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 5 2 7 5 1 1 1 0 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 6 2 7 5 1 1 1 0 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 7 2 7 5 1 1 1 0 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 2 2 7 5 1 1 1 1 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 3 2 7 5 1 1 1 1 +drop sequence s1; +create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle; +select nextval, round for s1; +nextval round +5 0 +select nextval, round for s1; +nextval round +6 0 +select nextval, round for s1; +nextval round +7 0 +select nextval, round for s1; +ERROR HY000: Sequence 's_db.s1' has been run out. +drop sequence s1; +create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 2 1 3 2 3 0 1 0 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 3 2 3 0 1 1 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 3 2 3 0 1 2 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 3 2 3 0 1 3 +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 1 1 3 2 3 0 1 4 +drop sequence s1; +create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle; +select * for s1; +currval nextval minvalue maxvalue start increment cache cycle round +0 2 1 3 2 3 2 0 0 +select * for s1; +ERROR HY000: Sequence 's_db.s1' has been run out. +drop sequence s1; +------------------------------------------ +beyond ulonglong maxvalue +------------------------------------------ +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775807 cache 1 cycle; +select nextval, round for s1; +nextval round +9223372036854775805 0 +select nextval, round for s1; +nextval round +9223372036854775806 0 +select nextval, round for s1; +nextval round +9223372036854775807 0 +select nextval, round for s1; +nextval round +9223372036854775804 1 +select nextval, round for s1; +nextval round +9223372036854775805 1 +select nextval, round for s1; +nextval round +9223372036854775806 1 +select nextval, round for s1; +nextval round +9223372036854775807 1 +select nextval, round for s1; +nextval round +9223372036854775804 2 +select nextval, round for s1; +nextval round +9223372036854775805 2 +select nextval, round for s1; +nextval round +9223372036854775806 2 +drop sequence s1; +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775807 cache 10 cycle; +select nextval, round for s1; +nextval round +9223372036854775805 0 +select nextval, round for s1; +nextval round +9223372036854775806 0 +select nextval, round for s1; +nextval round +9223372036854775807 0 +select nextval, round for s1; +nextval round +9223372036854775804 1 +select nextval, round for s1; +nextval round +9223372036854775805 1 +select nextval, round for s1; +nextval round +9223372036854775806 1 +select nextval, round for s1; +nextval round +9223372036854775807 1 +select nextval, round for s1; +nextval round +9223372036854775804 2 +select nextval, round for s1; +nextval round +9223372036854775805 2 +select nextval, round for s1; +nextval round +9223372036854775806 2 +drop sequence s1; +use s_db; +drop database s_db; +drop user normal_1@'%'; +drop user normal_2@'%'; +drop user normal_3@'%'; +drop user normal_4@'%'; +include/rpl_end.inc |