diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/gtid.result')
| -rw-r--r-- | mysql-test/suite/sql_sequence/gtid.result | 831 |
1 files changed, 831 insertions, 0 deletions
diff --git a/mysql-test/suite/sql_sequence/gtid.result b/mysql-test/suite/sql_sequence/gtid.result new file mode 100644 index 00000000..f23ebc56 --- /dev/null +++ b/mysql-test/suite/sql_sequence/gtid.result @@ -0,0 +1,831 @@ +include/master-slave.inc +[connection master] +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'; +grant select on test.* to normal_5@'%' identified by 'pass'; +flush status; +connection slave; +connect m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT; +connect m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT; +connect m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT; +connect s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT; +connect s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT; +connection slave; +set global read_only=on; +########################################### +master and slave sync sequence. +########################################### +connection master; +use s_db; +create sequence s1; +show create table s1; +Table Create Table +s1 CREATE TABLE `s1` ( + `next_not_cached_value` bigint(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `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', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +connection slave; +use s_db; +show create table s1; +Table Create Table +s1 CREATE TABLE `s1` ( + `next_not_cached_value` bigint(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `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', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +connection master; +use s_db; +drop sequence s1; +########################################### +not support create table engine=sequence. +########################################### +connection master; +create table t(id int)engine=sequence; +ERROR 42000: Unknown storage engine 'sequence' +create table t(id int)engine=innodb; +alter table t engine=sequence; +ERROR 42000: Unknown storage engine 'sequence' +drop table t; +########################################### +not support alter sequence table. +########################################### +connection master; +create sequence s2; +alter table s2 add id int; +ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns) +alter table s2 add index ind_x(start_value); +ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys) +drop sequence s2; +########################################### +Support create temporary sequence. +########################################### +connection master; +create temporary sequence s2; +drop temporary sequence s2; +########################################### +all invalid sequence value +########################################### +connection master; +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' has out of range value for options +create sequence s2 start with 1 +minvalue 5 +maxvalue 5 +increment by 1 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' has out of range value for options +create sequence s2 start with 1 +minvalue 5 +maxvalue 4 +increment by 1 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' has out of range value for options +create sequence s2 start with 1 +minvalue 5 +maxvalue 4 +increment by 0 +nocache +nocycle; +ERROR HY000: Sequence 's_db.s2' has out of range value for options +########################################### +global read lock prevent query sequence +########################################### +connection master; +use s_db; +create sequence s_db.s1; +flush table with read lock; +select next value for s1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop sequence s_db.s1; +########################################### +query cache test +########################################### +connection master; +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 0 +########################################### +priv test +########################################### +connection m_normal_1; +create sequence s_db.s1; +select * from s_db.s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 1000 0 0 +select next value for s_db.s1; +next value for s_db.s1 +1 +select * from s_db.s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1001 1 9223372036854775806 1 1 1000 0 0 +create sequence s_db.s2; +drop sequence s_db.s2; +connection m_normal_2; +select next value for s_db.s1; +ERROR 42000: INSERT command denied to user 'normal_2'@'localhost' for table `s_db`.`s1` +create sequence s_db.s2; +ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table `s_db`.`s2` +connection m_normal_1; +drop sequence s_db.s1; +########################################### +run out sequence value +########################################### +connection m_normal_1; +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 next value for s_t; +insert into t select next value for s_t; +insert into t select next value for s_t; +insert into t select next value for s_t; +insert into t select next value for s_t; +insert into t select next value for s_t; +ERROR HY000: Sequence 's_db.s_t' has run out +insert into t select next value for s_t; +ERROR HY000: Sequence 's_db.s_t' has run out +commit; +select * from t; +id +1111 +1 +2 +3 +4 +5 +connection master; +connection slave; +connection s_normal_3; +use s_db; +select * from t; +id +1111 +1 +2 +3 +4 +5 +connection m_normal_1; +use s_db; +drop sequence s_t; +drop table t; +########################################### +read_only prevent query sequence +########################################### +connection m_normal_1; +create sequence s_db.s1; +show global variables like 'read_only'; +Variable_name Value +read_only OFF +select next value for s_db.s1; +next value for s_db.s1 +1 +connection master; +connection slave; +connection s_normal_3; +show global variables like 'read_only'; +Variable_name Value +read_only ON +select next value for s_db.s1; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +connection m_normal_1; +drop sequence s_db.s1; +########################################### +update based table +########################################### +connection m_normal_1; +use s_db; +create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle; +connection master; +connection slave; +connection s_normal_3; +use s_db; +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 20 1 1 5 1 0 +connection m_normal_1; +select next value for s_t; +next value for s_t +1 +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +6 1 20 1 1 5 1 0 +connection master; +connection slave; +connection s_normal_3; +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +6 1 20 1 1 5 1 0 +------------------------------------------ +master update nextval; +------------------------------------------ +connection m_normal_1; +select next value for s_t; +next value for s_t +2 +update s_t set next_not_cached_value= 11; +ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option +alter sequence s_t restart=11; +commit; +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 20 1 1 5 1 0 +connection master; +connection slave; +------------------------------------------ +show slave nextval; +------------------------------------------ +connection s_normal_3; +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 20 1 1 5 1 0 +connection m_normal_1; +select next value for s_t; +next value for s_t +11 +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +16 1 20 1 1 5 1 0 +connection master; +connection slave; +connection s_normal_3; +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +16 1 20 1 1 5 1 0 +------------------------------------------ +update into invalid sequence +------------------------------------------ +connection m_normal_1; +select * from s_t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +16 1 20 1 1 5 1 0 +update s_t set next_not_cached_value= 11,start_value=10, minimum_value=11; +ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option +ALTER SEQUENCE s_t restart with 11 start=10 minvalue=11; +ERROR HY000: Sequence 's_db.s_t' has out of range value for options +commit; +create table t_1(id int); +insert into t_1 value(1111); +select next value for s_t; +next value for s_t +12 +insert into t_1 select next value for s_t; +commit; +select * from t_1; +id +1111 +13 +------------------------------------------ +delete sequence row +------------------------------------------ +connection m_normal_1; +delete from s_t; +ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option +commit; +select next value for s_t; +next value for s_t +14 +connection m_normal_1; +drop sequence s_t; +drop table t_1; +########################################### +test transaction context (innodb) +########################################### +------------------------------------------ +transaction table and sequence +normal transaction commit +------------------------------------------ +connection m_normal_1; +use s_db; +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 next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 values(2222); +commit; +select * from t_1; +id +1111 +1 +2 +2222 +connection master; +connection slave; +connection s_normal_3; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +------------------------------------------ +normal transaction rollback +------------------------------------------ +connection m_normal_1; +begin; +insert into t_1 values(3333); +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value 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 next value for s_1; +next value for s_1 +11 +connection master; +connection slave; +connection s_normal_3; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +connection m_normal_1; +use s_db; +drop sequence s_1; +drop table t_1; +########################################### +test transaction context (myisam) +########################################### +------------------------------------------ +transaction table and sequence +normal transaction commit +------------------------------------------ +connection m_normal_1; +use s_db; +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 next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 values(2222); +commit; +select * from t_1; +id +1111 +1 +2 +2222 +connection master; +connection slave; +connection s_normal_3; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +------------------------------------------ +normal transaction rollback +------------------------------------------ +connection m_normal_1; +begin; +insert into t_1 values(3333); +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value for s_1; +insert into t_1 select next value 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 next value for s_1; +next value for s_1 +11 +connection master; +connection slave; +connection s_normal_3; +use s_db; +select * from t_1; +id +1111 +1 +2 +2222 +3333 +3 +4 +5 +6 +7 +8 +9 +10 +connection m_normal_1; +use s_db; +drop sequence s_1; +drop table t_1; +########################################### +close binlog +########################################### +connection m_normal_1; +use s_db; +create sequence s1 cache 2; +select next value for s1; +next value for s1 +1 +select next value for s1; +next value for s1 +2 +select next value for s1; +next value for s1 +3 +select next value for s1; +next value for s1 +4 +commit; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; +connection slave; +use s_db; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 9223372036854775806 1 1 2 0 0 +------------------------------------------ +close session binlog. +------------------------------------------ +connection master; +set session sql_log_bin=off; +select next value for s1; +next value for s1 +5 +select next value for s1; +next value for s1 +6 +select next value for s1; +next value for s1 +7 +select next value for s1; +next value for s1 +8 +set session sql_log_bin=on; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +9 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; +connection slave; +use s_db; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 9223372036854775806 1 1 2 0 0 +connection m_normal_1; +use s_db; +drop sequence s1; +########################################### +statement binlog +########################################### +------------------------------------------ +set binlog_format=statement +------------------------------------------ +connection master; +set session binlog_format=statement; +select @@session.binlog_format; +@@session.binlog_format +STATEMENT +create sequence s1 cache 2; +select next value for s1; +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. +set session binlog_format=row; +select next value for s1; +next value for s1 +1 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; +use s_db; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3 1 9223372036854775806 1 1 2 0 0 +connection m_normal_1; +use s_db; +drop sequence s1; +########################################### +test savepoint +########################################### +connection master; +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 next value for s1; +insert into t1 select next value for s1; +insert into t1 select next value 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 next value for s1; +next value for s1 +4 +commit; +drop sequence s1; +drop table t1; +########################################### +test proc +########################################### +connection m_normal_1; +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 next value for s1; +commit; +end// +call p1(); +call p2(); +call p2(); +call p2(); +call p2(); +select * from t; +id +1 +2 +3 +4 +connection master; +connection slave; +use s_db; +select * from t; +id +1 +2 +3 +4 +connection m_normal_1; +drop table t; +drop sequence s1; +drop procedure p1; +drop procedure p2; +########################################### +test trigger +########################################### +connection m_normal_1; +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 next value 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 next value for s1; +next value for s1 +5 +drop trigger tri_1; +drop table t1; +drop table t2; +drop sequence s1; +########################################### +test function +########################################### +connection m_normal_1; +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 next value 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 next value for s1; +next value for s1 +5 +drop function f1; +drop table t1; +drop sequence s1; +############## +Test GRANT +############## +connection m_normal_2; +create table t1 (a int); +create sequence s1; +select next value for s1; +next value for s1 +1 +insert into t1 values (1); +connection m_normal_3; +select * from t1; +a +1 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1001 1 9223372036854775806 1 1 1000 0 0 +select previous value for s1; +previous value for s1 +NULL +insert into t1 values (2); +ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`t1` +select next value for s1; +ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`s1` +do setval(s1,1000,0); +ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`s1` +connection master; +grant insert on test.* to normal_5@'%' identified by 'pass'; +disconnect m_normal_3; +connect m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT; +insert into t1 values (2); +select t1.*, (next value for s1) from t1; +a (next value for s1) +1 2 +2 3 +do setval(s1,10000,0); +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +10000 1 9223372036854775806 1 1 1000 0 0 +connection m_normal_2; +drop table t1; +drop sequence s1; +# +# Cleanup +# +connection master; +use s_db; +drop database s_db; +drop user normal_1@'%'; +drop user normal_2@'%'; +drop user normal_3@'%'; +drop user normal_4@'%'; +drop user normal_5@'%'; +include/rpl_end.inc |