diff options
Diffstat (limited to '')
60 files changed, 9398 insertions, 0 deletions
diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result new file mode 100644 index 00000000..6d29876a --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.result @@ -0,0 +1,264 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Test alter sequence +# +CREATE SEQUENCE t1 nocache engine=myisam; +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 0 0 0 +select next value for t1; +next value for t1 +1 +alter sequence t1 start=50; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2 1 9223372036854775806 50 1 0 0 0 +select next value for t1; +next value for t1 +2 +alter sequence t1 minvalue=-100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3 -100 9223372036854775806 50 1 0 0 0 +alter sequence t1 minvalue=100 start=100; +ERROR HY000: Sequence 'test.t1' has out of range value for options +alter sequence t1 minvalue=100 start=100 restart=100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +100 100 9223372036854775806 100 1 0 0 0 +alter sequence t1 maxvalue=500; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +100 100 500 100 1 0 0 0 +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 nocache; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +alter sequence t1 cache=100; +flush tables; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM +alter sequence t1 nocache; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +flush tables; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 0 0 0 +select next value for t1; +next value for t1 +1 +select next value for t1; +next value for t1 +2 +select next value for t1; +next value for t1 +3 +select next_not_cached_value, cycle_count from t1; +next_not_cached_value cycle_count +4 0 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100 engine=myisam; +alter sequence t1 no maxvalue; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +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 +alter sequence t1 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +alter sequence t1 nocycle; +alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +15 10 20 15 1 1000 1 0 +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +15 +16 +17 +18 +19 +20 +10 +11 +12 +13 +alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +17 +18 +19 +20 +10 +11 +12 +13 +14 +15 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50 minvalue=-100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 -100 100 50 -2 1000 0 0 +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +1 +-1 +-3 +-5 +-7 +-9 +-11 +-13 +-15 +-17 +drop sequence t1; +# +# InnoDB (some things work different with InnoDB) + +CREATE SEQUENCE t1 cache 10 engine=innodb; +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 10 0 0 +select next value for t1; +next value for t1 +1 +alter sequence t1 start=100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 100 1 10 0 0 +select next value for t1; +next value for t1 +11 +drop sequence t1; +# +# ALTER TABLE +# +CREATE SEQUENCE t1 engine=innodb; +select next value for t1; +next value for t1 +1 +alter table t1 rename t2; +select next value for t2; +next value for t2 +1001 +rename table t2 to t1; +select next value for t1; +next value for t1 +2001 +alter table t1 comment="foo"; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' +alter table t1 engine=myisam; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' +alter table t1 engine=innodb; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3001 1 9223372036854775806 1 1 1000 0 0 +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 minvalue=100; +ERROR HY000: Sequence 'test.t1' has out of range value for options +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 minvalue=25 maxvalue=20; +ERROR HY000: Sequence 'test.t1' has out of range value for options +drop sequence t1; +create table t1 (a int); +alter sequence t1 minvalue=100; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; +alter sequence if exists t1 minvalue=100; +ERROR 42S02: Unknown SEQUENCE: 't1' +alter sequence t1 minvalue=100; +ERROR 42S02: Unknown SEQUENCE: 't1' +create sequence t1; +alter sequence t1; +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 '' at line 1 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50; +select next value for t1; +next value for t1 +1 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +0 1 100 50 -2 1000 0 0 +alter sequence t1 restart; +select next value for t1; +next value for t1 +50 +alter sequence t1 restart with 90; +select next value for t1; +next value for t1 +90 +drop sequence t1; +CREATE SEQUENCE t1 engine=innodb; +ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value); +ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints) +DROP SEQUENCE t1; +CREATE SEQUENCE s; +ALTER TABLE s ORDER BY cache_size; +ERROR HY000: Sequence 'test.s' table structure is invalid (ORDER BY) +SELECT NEXTVAL(s); +NEXTVAL(s) +1 +DROP SEQUENCE s; +# +# MDEV-31607 ER_DUP_KEY in mysql.table_stats upon REANME on sequence +# +CREATE SEQUENCE s1 ENGINE=InnoDB; +CREATE SEQUENCE s2 ENGINE=InnoDB; +SHOW CREATE SEQUENCE s1; +Table Create Table +s1 CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s2; +Table Create Table +s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB +DROP SEQUENCE s2; +RENAME TABLE s1 TO s2; +DROP SEQUENCE s2; diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test new file mode 100644 index 00000000..dab0e665 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.test @@ -0,0 +1,178 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test alter sequence +--echo # + +--disable_ps2_protocol +CREATE SEQUENCE t1 nocache engine=myisam; +select * from t1; +select next value for t1; +alter sequence t1 start=50; +show create sequence t1; +select * from t1; +select next value for t1; + +alter sequence t1 minvalue=-100; +show create sequence t1; +select * from t1; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=100 start=100; +alter sequence t1 minvalue=100 start=100 restart=100; +show create sequence t1; +select * from t1; + +alter sequence t1 maxvalue=500; +show create sequence t1; +select * from t1; +drop sequence t1; + +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 nocache; +show create sequence t1; +alter sequence t1 cache=100; +flush tables; +show create sequence t1; +alter sequence t1 nocache; +show create sequence t1; +flush tables; +show create sequence t1; +select * from t1; +select next value for t1; +select next value for t1; +select next value for t1; +select next_not_cached_value, cycle_count from t1; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100 engine=myisam; +alter sequence t1 no maxvalue; +show create sequence t1; +select * from t1; +alter sequence t1 cycle; +show create sequence t1; +alter sequence t1 nocycle; +alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; +show create sequence t1; +select * from t1; +select NEXT VALUE for t1 from seq_1_to_10; +alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; +select NEXT VALUE for t1 from seq_1_to_10; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50 minvalue=-100; +show create sequence t1; +select * from t1; +select NEXT VALUE for t1 from seq_1_to_10; +drop sequence t1; + +--echo # +--echo # InnoDB (some things work different with InnoDB) +--echo + +CREATE SEQUENCE t1 cache 10 engine=innodb; +select * from t1; +select next value for t1; +alter sequence t1 start=100; +show create sequence t1; +select * from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # ALTER TABLE +--echo # + +CREATE SEQUENCE t1 engine=innodb; +select next value for t1; +alter table t1 rename t2; +select next value for t2; +rename table t2 to t1; +select next value for t1; +alter table t1 comment="foo"; +show create sequence t1; +alter table t1 engine=myisam; +show create sequence t1; +alter table t1 engine=innodb; +show create sequence t1; +select * from t1; +drop sequence t1; + +# +# Some error testing +# + +CREATE SEQUENCE t1 engine=myisam; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=100; +drop sequence t1; + +CREATE SEQUENCE t1 engine=myisam; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=25 maxvalue=20; +drop sequence t1; + +create table t1 (a int); +--error ER_NOT_SEQUENCE +alter sequence t1 minvalue=100; +drop table t1; + +--error ER_UNKNOWN_SEQUENCES +alter sequence if exists t1 minvalue=100; +--error ER_UNKNOWN_SEQUENCES +alter sequence t1 minvalue=100; + +create sequence t1; +--error ER_PARSE_ERROR +alter sequence t1; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +select * from t1; +alter sequence t1 restart; +select next value for t1; +alter sequence t1 restart with 90; +select next value for t1; +drop sequence t1; + +# +# MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S || (0xFUL & mode) == LOCK_X' +# failed in lock_rec_lock +# + +CREATE SEQUENCE t1 engine=innodb; +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value); +DROP SEQUENCE t1; + +# +# MDEV-19320 Sequence gets corrupted and produces ER_KEY_NOT_FOUND (Can't +# find record) after ALTER .. ORDER BY +# + +CREATE SEQUENCE s; +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +ALTER TABLE s ORDER BY cache_size; +SELECT NEXTVAL(s); +DROP SEQUENCE s; + +--echo # +--echo # MDEV-31607 ER_DUP_KEY in mysql.table_stats upon REANME on sequence +--echo # + +CREATE SEQUENCE s1 ENGINE=InnoDB; +CREATE SEQUENCE s2 ENGINE=InnoDB; +SHOW CREATE SEQUENCE s1; +SHOW CREATE SEQUENCE s2; +DROP SEQUENCE s2; +RENAME TABLE s1 TO s2; +DROP SEQUENCE s2; + +--enable_ps2_protocol diff --git a/mysql-test/suite/sql_sequence/alter_notembedded.result b/mysql-test/suite/sql_sequence/alter_notembedded.result new file mode 100644 index 00000000..f3e1f5f1 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter_notembedded.result @@ -0,0 +1,38 @@ +# +# GRANT +# +create database s_db; +create sequence s_db.s1; +grant select on s_db.s1 to normal_1@'%' identified by 'pass'; +connect m_normal_1, localhost, normal_1, pass, s_db; +select * from 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 nextval(s1); +ERROR 42000: INSERT command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` +show create sequence s1; +Table Create Table +s1 CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s1 restart 50; +ERROR 42000: ALTER command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` +connection default; +grant insert on s_db.s1 to normal_1@'%'; +connection m_normal_1; +select nextval(s1); +nextval(s1) +1 +alter sequence s1 restart 50; +ERROR 42000: ALTER command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` +connection default; +grant alter on s_db.s1 to normal_1@'%'; +connection m_normal_1; +alter sequence s1 restart 50; +select nextval(s1); +nextval(s1) +50 +drop sequence s1; +ERROR 42000: DROP command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` +connection default; +disconnect m_normal_1; +drop database s_db; +drop user normal_1@'%'; diff --git a/mysql-test/suite/sql_sequence/alter_notembedded.test b/mysql-test/suite/sql_sequence/alter_notembedded.test new file mode 100644 index 00000000..5bd54a7c --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter_notembedded.test @@ -0,0 +1,39 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc +--source include/not_embedded.inc + +--echo # +--echo # GRANT +--echo # + +create database s_db; +create sequence s_db.s1; +grant select on s_db.s1 to normal_1@'%' identified by 'pass'; + +--disable_ps2_protocol +connect(m_normal_1, localhost, normal_1, pass, s_db); +select * from s1; +--error ER_TABLEACCESS_DENIED_ERROR +select nextval(s1); +show create sequence s1; +--error ER_TABLEACCESS_DENIED_ERROR +alter sequence s1 restart 50; +connection default; +grant insert on s_db.s1 to normal_1@'%'; +connection m_normal_1; +select nextval(s1); +--error ER_TABLEACCESS_DENIED_ERROR +alter sequence s1 restart 50; +connection default; +grant alter on s_db.s1 to normal_1@'%'; +connection m_normal_1; +alter sequence s1 restart 50; +select nextval(s1); +--error ER_TABLEACCESS_DENIED_ERROR +drop sequence s1; +--enable_ps2_protocol + +connection default; +disconnect m_normal_1; +drop database s_db; +drop user normal_1@'%'; diff --git a/mysql-test/suite/sql_sequence/aria.result b/mysql-test/suite/sql_sequence/aria.result new file mode 100644 index 00000000..cfc7d946 --- /dev/null +++ b/mysql-test/suite/sql_sequence/aria.result @@ -0,0 +1,66 @@ +set @@default_storage_engine="aria"; +CREATE SEQUENCE t1 cache=10; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=Aria +select NEXT VALUE for t1,seq from seq_1_to_20; +NEXT VALUE for t1 seq +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +16 16 +17 17 +18 18 +19 19 +20 20 +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +21 1 9223372036854775806 1 1 10 0 0 +drop sequence t1; +create sequence s1; +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1 +flush tables; +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1001 +flush tables; +repair table s1; +Table Op Msg_type Msg_text +test.s1 repair status OK +select next value for s1; +next value for s1 +2001 +drop sequence s1; +CREATE SEQUENCE t1; +alter sequence t1 minvalue=100; +ERROR HY000: Sequence 'test.t1' has out of range value for options +alter sequence t1 minvalue=100 start=100 restart=100; +rename table t1 to t2; +select next value for t2; +next value for t2 +100 +alter table t2 rename to t1; +select next value for t1; +next value for t1 +1100 +drop table t1; diff --git a/mysql-test/suite/sql_sequence/aria.test b/mysql-test/suite/sql_sequence/aria.test new file mode 100644 index 00000000..daca8aff --- /dev/null +++ b/mysql-test/suite/sql_sequence/aria.test @@ -0,0 +1,45 @@ +--source include/have_sequence.inc +--source include/have_aria.inc + +# +# Simple test of the aria engine +# As most test is above the engine, we only have to test base functionality +# + +set @@default_storage_engine="aria"; + +--disable_ps2_protocol +CREATE SEQUENCE t1 cache=10; +show create sequence t1; +select NEXT VALUE for t1,seq from seq_1_to_20; +select * from t1; +drop sequence t1; + +# +# Create and check +# + +create sequence s1; +check table s1; +select next value for s1; +flush tables; +check table s1; +select next value for s1; +flush tables; +repair table s1; +select next value for s1; +drop sequence s1; + +# +# ALTER and RENAME + +CREATE SEQUENCE t1; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=100; +alter sequence t1 minvalue=100 start=100 restart=100; +rename table t1 to t2; +select next value for t2; +alter table t2 rename to t1; +select next value for t1; +drop table t1; +--enable_ps2_protocol diff --git a/mysql-test/suite/sql_sequence/auto_increment.result b/mysql-test/suite/sql_sequence/auto_increment.result new file mode 100644 index 00000000..bf0a04bb --- /dev/null +++ b/mysql-test/suite/sql_sequence/auto_increment.result @@ -0,0 +1,30 @@ +set global auto_increment_increment= 2, auto_increment_offset= 2; +create sequence s start with -3 minvalue= -1000 increment 0; +select nextval(s); +nextval(s) +-2 +select nextval(s); +nextval(s) +0 +flush tables; +select nextval(s); +nextval(s) +1998 +drop sequence s; +set global auto_increment_increment= 2, auto_increment_offset= 1; +create sequence s start with -3 minvalue= -1000 increment 0; +select nextval(s); +nextval(s) +-3 +select nextval(s); +nextval(s) +-1 +select nextval(s); +nextval(s) +1 +flush tables; +select nextval(s); +nextval(s) +1997 +drop sequence s; +set global auto_increment_increment= default, auto_increment_offset= default; diff --git a/mysql-test/suite/sql_sequence/auto_increment.test b/mysql-test/suite/sql_sequence/auto_increment.test new file mode 100644 index 00000000..11f772b6 --- /dev/null +++ b/mysql-test/suite/sql_sequence/auto_increment.test @@ -0,0 +1,32 @@ +--source include/have_sequence.inc + +# +# tests with auto_increment_increment and auto_increment_offset +# + +--disable_ps2_protocol +set global auto_increment_increment= 2, auto_increment_offset= 2; + +create sequence s start with -3 minvalue= -1000 increment 0; + +select nextval(s); +select nextval(s); +flush tables; +select nextval(s); +drop sequence s; + +set global auto_increment_increment= 2, auto_increment_offset= 1; + +create sequence s start with -3 minvalue= -1000 increment 0; + +select nextval(s); +select nextval(s); +select nextval(s); +flush tables; +select nextval(s); +drop sequence s; +--enable_ps2_protocol + +# Clean up + +set global auto_increment_increment= default, auto_increment_offset= default; diff --git a/mysql-test/suite/sql_sequence/binlog.result b/mysql-test/suite/sql_sequence/binlog.result new file mode 100644 index 00000000..843cf74c --- /dev/null +++ b/mysql-test/suite/sql_sequence/binlog.result @@ -0,0 +1,58 @@ +create or replace sequence s1 cache 3; +select next value for s1, minimum_value from s1 where maximum_value> 1; +next value for s1 minimum_value +1 1 +select next value for s1, minimum_value from s1 where maximum_value> 2; +next value for s1 minimum_value +2 1 +select next value for s1, minimum_value from s1 where maximum_value> 3; +next value for s1 minimum_value +3 1 +select next value for s1, minimum_value from s1 where maximum_value> 4; +next value for s1 minimum_value +4 1 +alter sequence s1 maxvalue 1000; +optimize table s1; +Table Op Msg_type Msg_text +test.s1 optimize note The storage engine for the table doesn't support optimize +analyze table s1; +Table Op Msg_type Msg_text +test.s1 analyze status Engine-independent statistics collected +test.s1 analyze note The storage engine for the table doesn't support analyze +repair table s1; +Table Op Msg_type Msg_text +test.s1 repair status OK +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +rename table s1 to tmp_s; +rename table tmp_s to s1; +drop sequence s1; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace sequence s1 cache 3 +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # select next value for s1, minimum_value from s1 where maximum_value> 1 +master-bin.000001 # Table_map # # table_id: # (test.s1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # select next value for s1, minimum_value from s1 where maximum_value> 4 +master-bin.000001 # Table_map # # table_id: # (test.s1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; alter sequence s1 maxvalue 1000 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; optimize table s1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; analyze table s1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; repair table s1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; rename table s1 to tmp_s +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; rename table tmp_s to s1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP SEQUENCE `s1` /* generated by server */ diff --git a/mysql-test/suite/sql_sequence/binlog.test b/mysql-test/suite/sql_sequence/binlog.test new file mode 100644 index 00000000..70d28038 --- /dev/null +++ b/mysql-test/suite/sql_sequence/binlog.test @@ -0,0 +1,40 @@ +--source include/have_sequence.inc +--source include/have_binlog_format_mixed_or_row.inc +--source include/binlog_start_pos.inc + +# +# Testing binary logging of sequences +# + +--disable_query_log +reset master; # get rid of previous tests binlog +--enable_query_log + +create or replace sequence s1 cache 3; +--disable_ps2_protocol +select next value for s1, minimum_value from s1 where maximum_value> 1; +select next value for s1, minimum_value from s1 where maximum_value> 2; +select next value for s1, minimum_value from s1 where maximum_value> 3; +select next value for s1, minimum_value from s1 where maximum_value> 4; +--enable_ps2_protocol + +# +# Alter sequence +# +alter sequence s1 maxvalue 1000; + +# MDEV-24617 OPTIMIZE on a sequence causes unexpected +# ER_BINLOG_UNSAFE_STATEMENT The test below verifies no unsafe +# warnings anymore for any relavant commands that like OPTIMIZE can +# not produce ROW format events therefore the unsafe warning either. +optimize table s1; +analyze table s1; +repair table s1; +check table s1; +rename table s1 to tmp_s; +rename table tmp_s to s1; + +drop sequence s1; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/sql_sequence/concurrent_create.result b/mysql-test/suite/sql_sequence/concurrent_create.result new file mode 100644 index 00000000..2473abef --- /dev/null +++ b/mysql-test/suite/sql_sequence/concurrent_create.result @@ -0,0 +1,46 @@ +CREATE SEQUENCE s1 ENGINE=InnoDB; +CREATE SEQUENCE s2 ENGINE=InnoDB; +connect con1,localhost,root,,test; +CREATE TABLE s3 LIKE s2;; +connection default; +CREATE SEQUENCE s4 ENGINE=InnoDB; +SELECT * from s1 WHERE start_value IN (SELECT start_value FROM s2); +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 +connection con1; +disconnect con1; +connection default; +DROP SEQUENCE s1, s2, s3, s4; +CREATE SEQUENCE s1 ENGINE=InnoDB; +PREPARE stmt FROM "CREATE TABLE s2 LIKE s1"; +execute stmt; +drop table s2; +execute stmt; +drop table s2; +execute stmt; +select * from s2; +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 +DROP SEQUENCE s1, s2; +CREATE SEQUENCE s1 ENGINE=InnoDB; +connect con1,localhost,root,,test; +CREATE TABLE s2 LIKE s1;; +connection default; +FLUSH TABLES; +connection con1; +disconnect con1; +connection default; +DROP TABLE s1,s2; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connect con1,localhost,root,,test; +CREATE SEQUENCE s1 ENGINE=InnoDB; +FLUSH TABLES; +disconnect con1; +connection default; +SELECT NEXTVAL(s1); +NEXTVAL(s1) +1 +COMMIT; +DROP TABLE t1; +DROP SEQUENCE s1; diff --git a/mysql-test/suite/sql_sequence/concurrent_create.test b/mysql-test/suite/sql_sequence/concurrent_create.test new file mode 100644 index 00000000..e9a61e63 --- /dev/null +++ b/mysql-test/suite/sql_sequence/concurrent_create.test @@ -0,0 +1,79 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# MDEV-15149 Assorted assertion failures upon concurrent creating / querying +# sequences (same test case) +# + +CREATE SEQUENCE s1 ENGINE=InnoDB; +CREATE SEQUENCE s2 ENGINE=InnoDB; + +--connect (con1,localhost,root,,test) +--send CREATE TABLE s3 LIKE s2; + +--connection default +CREATE SEQUENCE s4 ENGINE=InnoDB; +SELECT * from s1 WHERE start_value IN (SELECT start_value FROM s2); + +--connection con1 +--reap + +# Cleanup +--disconnect con1 +--connection default +DROP SEQUENCE s1, s2, s3, s4; + +# +# Check prepared statements +# + +CREATE SEQUENCE s1 ENGINE=InnoDB; +PREPARE stmt FROM "CREATE TABLE s2 LIKE s1"; +execute stmt; +drop table s2; +execute stmt; +drop table s2; +execute stmt; +select * from s2; +DROP SEQUENCE s1, s2; + +# +# MDEV-15117 Server crashes in in open_and_process_table or ASAN +# heap-use-after-free in is_temporary_table upon creating/flushing sequences +# + +CREATE SEQUENCE s1 ENGINE=InnoDB; +--connect (con1,localhost,root,,test) +--send CREATE TABLE s2 LIKE s1; +--connection default +FLUSH TABLES; + +# Cleanup +--connection con1 +--reap +--disconnect con1 +--connection default + +DROP TABLE s1,s2; + +# +# MDEV-24545 Sequence created by one connection remains invisible to another +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connect (con1,localhost,root,,test) +CREATE SEQUENCE s1 ENGINE=InnoDB; +FLUSH TABLES; +--disconnect con1 + +--connection default +--disable_ps2_protocol +SELECT NEXTVAL(s1); +--enable_ps2_protocol +COMMIT; + +# Cleanup +DROP TABLE t1; +DROP SEQUENCE s1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result new file mode 100644 index 00000000..9d601a9e --- /dev/null +++ b/mysql-test/suite/sql_sequence/create.result @@ -0,0 +1,726 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace sequence t1 engine=myisam; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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 +select * from t1; +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 +create or replace sequence t1 engine=innodb; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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=InnoDB SEQUENCE=1 +select * from t1; +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 +create or replace sequence t1 engine=maria; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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=Aria SEQUENCE=1 +select * from t1; +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 +create or replace sequence t1 engine=archive; +ERROR HY000: Table storage engine 'ARCHIVE' does not support the create option 'SEQUENCE' +show create table t1; +ERROR 42S02: Table 'test.t1' doesn't exist +create or replace sequence t1 start with 10; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +10 1 9223372036854775806 10 1 1000 0 0 +create or replace sequence t1 minvalue=11; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 11 9223372036854775806 11 1 1000 0 0 +create or replace sequence t1 maxvalue=13 increment by -1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +13 -9223372036854775807 13 13 -1 1000 0 0 +create or replace sequence t1 increment by -1 cache 100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +-1 -9223372036854775807 -1 -1 -1 100 0 0 +create or replace sequence t1 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 1000 1 0 +create or replace sequence t1 nocycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +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 +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 cycle minvalue= 14; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +14 14 9223372036854775806 14 1 1000 1 0 +create or replace sequence t1 cycle increment by -1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 cycle ENGINE=MyISAM +drop sequence t1; +create sequence if not exists t1; +create sequence if not exists t1 start with 10; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +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 +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +10 10 11 10 1 0 1 0 +create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +10 -10 11 10 10 10 1 0 +create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE; +create or replace sequence t1 start with 10 maxvalue 10; +create or replace sequence t1 start with 10 minvalue 10; +create or replace sequence t1 start with 10 minvalue 10 maxvalue 11 cycle; +create or replace sequence t1 start with 10 maxvalue=9223372036854775806; +create or replace sequence t1 start with 10 minvalue=-9223372036854775807; +drop sequence if exists t1; +create sequence t1 increment by 0; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 0 1000 0 0 +drop sequence t1; +create table t1 (a int); +show create sequence t1; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop sequence t1; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop sequence if exists t1; +Warnings: +Note 4090 'test.t1' is not a SEQUENCE +create sequence t1 start with 10 maxvalue=9; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create sequence t1 minvalue= 100 maxvalue=10; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create sequence t1 start with 9 minvalue=10; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue=13, increment by -1; +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 ' increment by -1' at line 1 +create or replace sequence t1 start with= 10 maxvalue=13; +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 '= 10 maxvalue=13' at line 1 +create or replace sequence t1 maxvalue=13, increment= -1; +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 ' increment= -1' at line 1 +create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; +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 'NO MINVALUE' at line 1 +create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; +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 'NO MINVALUE' at line 1 +create sequence t1 start with 10 maxvalue=9223372036854775807; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create sequence t1 start with 10 minvalue=-9223372036854775808; +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 '9223372036854775808' at line 1 +create sequence t1 RESTART WITH 10; +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 'RESTART' at line 1 +create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; +drop sequence t1; +create sequence t1; +show fields from t1; +Field Type Null Key Default Extra +next_not_cached_value bigint(21) NO NULL +minimum_value bigint(21) NO NULL +maximum_value bigint(21) NO NULL +start_value bigint(21) NO NULL +increment bigint(21) NO NULL +cache_size bigint(21) unsigned NO NULL +cycle_option tinyint(1) unsigned NO NULL +cycle_count bigint(21) NO NULL +flush tables; +show fields from t1; +Field Type Null Key Default Extra +next_not_cached_value bigint(21) NO NULL +minimum_value bigint(21) NO NULL +maximum_value bigint(21) NO NULL +start_value bigint(21) NO NULL +increment bigint(21) NO NULL +cache_size bigint(21) unsigned NO NULL +cycle_option tinyint(1) unsigned NO NULL +cycle_count bigint(21) NO NULL +create or replace sequence t1 engine=aria; +show fields from t1; +Field Type Null Key Default Extra +next_not_cached_value bigint(21) NO NULL +minimum_value bigint(21) NO NULL +maximum_value bigint(21) NO NULL +start_value bigint(21) NO NULL +increment bigint(21) NO NULL +cache_size bigint(21) unsigned NO NULL +cycle_option tinyint(1) unsigned NO NULL +cycle_count bigint(21) NO NULL +show fields from t1; +Field Type Null Key Default Extra +next_not_cached_value bigint(21) NO NULL +minimum_value bigint(21) NO NULL +maximum_value bigint(21) NO NULL +start_value bigint(21) NO NULL +increment bigint(21) NO NULL +cache_size bigint(21) unsigned NO NULL +cycle_option tinyint(1) unsigned NO NULL +cycle_count bigint(21) NO NULL +flush tables; +create or replace sequence t1 comment= "test 1"; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 1' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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 COMMENT='test 1' +create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 2' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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 MIN_ROWS=1 MAX_ROWS=2 SEQUENCE=1 COMMENT='test 2' +create or replace sequence t1 start=1 increment= 2; +create or replace sequence t1 start 1 increment 2; +create or replace sequence t1 cache +1; +drop sequence t1; +CREATE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) sequence=1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) ENGINE=MyISAM SEQUENCE=1 +drop sequence t1; +CREATE OR REPLACE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count_not_exists` bigint(21) NOT NULL +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle_count_not_exists) +CREATE OR REPLACE TABLE t1 ( +`next_not_cached_value` int(21) NOT NULL, +`minimum_value` bigint(21) NOT NULL, +`maximum_value` bigint(21) NOT NULL, +`start_value` bigint(21) NOT NULL, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (next_not_cached_value) +CREATE OR REPLACE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle` bigint(21) unsigned NOT NULL, /* error */ +`cycle_count` bigint(21) NOT NULL +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle) +CREATE OR REPLACE TABLE t1 ( +`next_not_cached_value` bigint(21), /* error */ +`minimum_value` bigint(21) NOT NULL, +`maximum_value` bigint(21) NOT NULL, +`start_value` bigint(21) NOT NULL, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (next_not_cached_value) +CREATE OR REPLACE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL, +extra_field bigint(21) +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (Wrong number of columns) +CREATE OR REPLACE TABLE t1 ( +`minimum_value` bigint(21) NOT NULL, +`next_not_cached_value` bigint(21) NOT NULL, +`maximum_value` bigint(21) NOT NULL, +`start_value` bigint(21) NOT NULL, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (minimum_value) +CREATE OR REPLACE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL, +key key1 (next_not_cached_value) +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys) +CREATE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL, +CHECK (start_value < minimum_value) +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints) +CREATE TABLE t1 ( +`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 CHECK (start_value < minimum_value), +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (start_value) +CREATE TABLE t1 ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) generated always as (1) virtual +) sequence=1; +ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle_count) +drop sequence if exists t1; +Warnings: +Note 4091 Unknown SEQUENCE: 'test.t1' +create sequence t1; +create sequence t2; +create table t3 (a int) engine=myisam; +select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog="test"; +table_catalog table_schema table_name table_type +CREATE SEQUENCE s1; +drop sequence s1; +drop sequence if exists t1,t2,t3,t4; +Warnings: +Note 4090 'test.t3' is not a SEQUENCE +Note 4091 Unknown SEQUENCE: 'test.t4' +drop table if exists t1,t2,t3; +Warnings: +Note 1051 Unknown table 'test.t1,test.t2' +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop table t1,t2,s1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop table if exists t1,t2,s1,s2; +Warnings: +Note 1051 Unknown table 'test.s2' +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop sequence t1,t2,s1,s2; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table if exists t1,t2; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop sequence if exists t1,t2,s1,s2; +Warnings: +Note 4090 'test.t1' is not a SEQUENCE +Note 4090 'test.t2' is not a SEQUENCE +Note 4091 Unknown SEQUENCE: 'test.s2' +drop table if exists t1,t2; +CREATE TEMPORARY SEQUENCE s1; +DROP SEQUENCE s1; +DROP TEMPORARY SEQUENCE s1; +ERROR 42S02: Unknown SEQUENCE: 'test.s1' +CREATE TEMPORARY SEQUENCE s1; +CREATE SEQUENCE s2; +CREATE TEMPORARY TABLE t1 (a int); +CREATE TABLE t2 (a int); +DROP TEMPORARY SEQUENCE t1,t2,s1,s2; +ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.t2,test.s2' +DROP TEMPORARY SEQUENCE s1; +ERROR 42S02: Unknown SEQUENCE: 'test.s1' +DROP TEMPORARY TABLE t1; +DROP TABLE t1,t2,s1,s2; +ERROR 42S02: Unknown table 'test.t1,test.s1' +create view v1 as (select 1); +CREATE SEQUENCE s1; +DROP SEQUENCE s1,v1; +ERROR 42S02: 'test.v1' is a view +drop view v1; +CREATE TEMPORARY SEQUENCE t1; +select next value for t1; +next value for t1 +1 +drop temporary table t1; +select previous value for t1; +ERROR 42S02: Unknown SEQUENCE: 't1' +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10; +select next value for t1; +next value for t1 +1 +select previous value for t1; +previous value for t1 +1 +CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10; +select previous value for t1; +previous value for t1 +NULL +select next value for t1; +next value for t1 +100 +select previous value for t1; +previous value for t1 +100 +drop temporary sequence t1; +select previous value for t1; +previous value for t1 +1 +drop sequence t1; +CREATE TEMPORARY SEQUENCE t1 engine=innodb; +select next value for t1; +next value for t1 +1 +drop temporary table t1; +select previous value for t1; +ERROR 42S02: Unknown SEQUENCE: 't1' +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10 engine=innodb; +select next value for t1; +next value for t1 +1 +select previous value for t1; +previous value for t1 +1 +CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +select previous value for t1; +previous value for t1 +NULL +select next value for t1; +next value for t1 +100 +select previous value for t1; +previous value for t1 +100 +drop temporary sequence t1; +select previous value for t1; +previous value for t1 +1 +drop sequence t1; +create table t1 (a int) engine=sql_sequence; +ERROR 42000: Unknown storage engine 'sql_sequence' +# +# MDEV-13711 assertion on CREATE LIKE fix +# +create sequence s; +create table t like s; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `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 +show create sequence t; +Table Create Table +t CREATE SEQUENCE `t` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +drop tables t, s; +# +# MDEV-13714 SEQUENCE option fix +# +create or replace table s ( +`next_value` bigint(21) not null, +`min_value` bigint(21) not null, +`max_value` bigint(21) not null, +`start` bigint(21) not null, +`increment` bigint(21) not null, +`cache` bigint(21) not null, +`cycle` tinyint(1) unsigned not null, +`round` bigint(21) not null) +sequence=0; +create or replace table s2 ( +`next_value` bigint(21) not null, +`min_value` bigint(21) not null, +`max_value` bigint(21) not null, +`start` bigint(21) not null, +`increment` bigint(21) not null, +`cache` bigint(21) not null, +`cycle` tinyint(1) unsigned not null, +`round` bigint(21) not null) +sequence=default; +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `next_value` bigint(21) NOT NULL, + `min_value` bigint(21) NOT NULL, + `max_value` bigint(21) NOT NULL, + `start` bigint(21) NOT NULL, + `increment` bigint(21) NOT NULL, + `cache` bigint(21) NOT NULL, + `cycle` tinyint(1) unsigned NOT NULL, + `round` bigint(21) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +show create table s2; +Table Create Table +s2 CREATE TABLE `s2` ( + `next_value` bigint(21) NOT NULL, + `min_value` bigint(21) NOT NULL, + `max_value` bigint(21) NOT NULL, + `start` bigint(21) NOT NULL, + `increment` bigint(21) NOT NULL, + `cache` bigint(21) NOT NULL, + `cycle` tinyint(1) unsigned NOT NULL, + `round` bigint(21) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +show create sequence s; +ERROR 42S02: 'test.s' is not a SEQUENCE +show create sequence s2; +ERROR 42S02: 'test.s2' is not a SEQUENCE +drop table s,s2; +# +# MDEV-13721 Assertion is_lock_owner() failed in mysql_rm_table_no_locks +# +create or replace sequence s; +create temporary table s (i int); +drop sequence s; +show create table s; +Table Create Table +s CREATE TEMPORARY TABLE `s` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table s; +create or replace sequence s; +create temporary sequence s; +show create table s; +Table Create Table +s CREATE TEMPORARY TABLE `s` ( + `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 +drop sequence s; +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +drop table s; +create or replace sequence s; +create temporary sequence s; +drop temporary sequence s; +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +drop table s; +create temporary sequence s; +drop temporary table s; +create temporary table s (i int); +drop temporary sequence s; +ERROR 42S02: Unknown SEQUENCE: 'test.s' +drop table s; +# +# MDEV-15115 Assertion failure in CREATE SEQUENCE...ROW_FORMAT=REDUNDANT +# +CREATE SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/seq1'; +TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE +# test/seq1 12288 11 # Redundant 0 Single +DROP SEQUENCE seq1; +CREATE TEMPORARY SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +DROP TEMPORARY SEQUENCE seq1; +# +# MDEV-17503 CREATE SEQUENCE failed with innodb_force_primary_key =1 +# +set global innodb_force_primary_key =1; +CREATE SEQUENCE s1 START WITH 100 INCREMENT BY 10 ENGINE=innodb; +set global innodb_force_primary_key=default; +ALTER TABLE s1 ADD PRIMARY KEY (next_not_cached_value); +ERROR HY000: Sequence 'test.s1' table structure is invalid (Sequence tables cannot have any keys) +DROP SEQUENCE s1; +# +# Beginning of 10.4 Test +# +# MDEV-13005: Fixing bugs in SEQUENCE, part 3 +# +# Task 1: +SET @x = PREVIOUS VALUE FOR x; +ERROR 42S02: Unknown SEQUENCE: 'x' +# Task 2: +CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678; +ERROR HY000: Sequence 'test.x' has out of range value for options +# Task 3: +CREATE SEQUENCE seq1 START WITH 1 cache -1; +ERROR HY000: Sequence 'test.seq1' has out of range value for options +# Task 4: +CREATE TEMPORARY TABLE s1 (s1 INT); +DROP TEMPORARY SEQUENCE s1; +ERROR 42S02: Unknown SEQUENCE: 'test.s1' +DROP TEMPORARY TABLE s1; +# Task 5: +CREATE TEMPORARY TABLE s1 (s1 INT); +CREATE TEMPORARY SEQUENCE s1 (s1 INT); +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 '(s1 INT)' at line 1 +DROP TEMPORARY TABLE s1; +# Task 6: +CREATE SEQUENCE seq1 START WITH 2; +CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; +ERROR HY000: Trigger's 'seq1' is a view, temporary table or sequence +DROP SEQUENCE seq1; +# +# MDEV-29771: Server crashes in check_sequence_fields upon +# CREATE TABLE .. SEQUENCE=1 AS SELECT .. +# +create table s sequence=1 as select 1; +ERROR HY000: Sequence 'test.s' table structure is invalid (Wrong number of columns) +# +# End of 10.4 test +# diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test new file mode 100644 index 00000000..4cece26d --- /dev/null +++ b/mysql-test/suite/sql_sequence/create.test @@ -0,0 +1,567 @@ +# +# Test create options with sequences +# +--source include/have_innodb.inc +--source include/have_archive.inc + +drop table if exists t1; + +# +# Check some sample engines +# + +create or replace sequence t1 engine=myisam; +show create sequence t1; +show create table t1; +select * from t1; +create or replace sequence t1 engine=innodb; +show create sequence t1; +show create table t1; +select * from t1; +create or replace sequence t1 engine=maria; +show create sequence t1; +show create table t1; +select * from t1; +--error ER_ILLEGAL_HA_CREATE_OPTION +create or replace sequence t1 engine=archive; +# +# The following error should be fixed. We shouldn't delete old table on errors +# +--error ER_NO_SUCH_TABLE +show create table t1; + + +# Check start values +create or replace sequence t1 start with 10; +show create sequence t1; +select * from t1; +create or replace sequence t1 minvalue=11; +show create sequence t1; +select * from t1; +create or replace sequence t1 maxvalue=13 increment by -1; +show create sequence t1; +select * from t1; + +create or replace sequence t1 increment by -1 cache 100; +show create sequence t1; +select * from t1; +create or replace sequence t1 cycle; +show create sequence t1; +select * from t1; +create or replace sequence t1 nocycle; +show create sequence t1; +select * from t1; +show create sequence t1; +create or replace sequence t1 cycle minvalue= 14; +show create sequence t1; +select * from t1; +create or replace sequence t1 cycle increment by -1; +show create sequence t1; + +drop sequence t1; +create sequence if not exists t1; +create sequence if not exists t1 start with 10; +select * from t1; +show create sequence t1; + +create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle; +show create sequence t1; +select * from t1; +create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10; +show create sequence t1; +select * from t1; + +# NO MINVALUE, NO MAXVALUE +create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE; + +# Some edge cases +create or replace sequence t1 start with 10 maxvalue 10; +create or replace sequence t1 start with 10 minvalue 10; +create or replace sequence t1 start with 10 minvalue 10 maxvalue 11 cycle; +create or replace sequence t1 start with 10 maxvalue=9223372036854775806; +create or replace sequence t1 start with 10 minvalue=-9223372036854775807; +drop sequence if exists t1; + +create sequence t1 increment by 0; +show create sequence t1; +select * from t1; +drop sequence t1; + +# +# Wrong usage and arguments to create sequence +# + +create table t1 (a int); +--error ER_NOT_SEQUENCE +show create sequence t1; +--error ER_NOT_SEQUENCE2 +drop sequence t1; +drop sequence if exists t1; + +--error ER_SEQUENCE_INVALID_DATA +create sequence t1 start with 10 maxvalue=9; +--error ER_SEQUENCE_INVALID_DATA +create sequence t1 minvalue= 100 maxvalue=10; +--error ER_SEQUENCE_INVALID_DATA +create sequence t1 start with 9 minvalue=10; +--error ER_PARSE_ERROR +create or replace sequence t1 maxvalue=13, increment by -1; +--error ER_PARSE_ERROR +create or replace sequence t1 start with= 10 maxvalue=13; +--error ER_PARSE_ERROR +create or replace sequence t1 maxvalue=13, increment= -1; +--error ER_PARSE_ERROR +create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; +--error ER_PARSE_ERROR +create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; +--error ER_SEQUENCE_INVALID_DATA +create sequence t1 start with 10 maxvalue=9223372036854775807; +--error ER_PARSE_ERROR +create sequence t1 start with 10 minvalue=-9223372036854775808; +--error ER_PARSE_ERROR +create sequence t1 RESTART WITH 10; + +# This should probably give an error +create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; +drop sequence t1; + +# +# Test with LIST COLUMNS as first command +# +create sequence t1; +show fields from t1; +flush tables; +show fields from t1; +create or replace sequence t1 engine=aria; +show fields from t1; +show fields from t1; +flush tables; + +# +# Test with other create options +# + +create or replace sequence t1 comment= "test 1"; +show create sequence t1; +show create table t1; +create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2; +show create sequence t1; +show create table t1; +create or replace sequence t1 start=1 increment= 2; +create or replace sequence t1 start 1 increment 2; +create or replace sequence t1 cache +1; +drop sequence t1; + +# +# test with create table +# + +CREATE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) sequence=1; + +show create sequence t1; +show create table t1; +drop sequence t1; + +# Wrong column name + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count_not_exists` bigint(21) NOT NULL +) sequence=1; + +# Wrong type + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `next_not_cached_value` int(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) sequence=1; + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle` bigint(21) unsigned NOT NULL, /* error */ + `cycle_count` bigint(21) NOT NULL +) sequence=1; + + +# Missing NOT NULL + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `next_not_cached_value` bigint(21), /* error */ + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) sequence=1; + +# Extra field + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL, + extra_field bigint(21) +) sequence=1; + +# Wrong field order + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `minimum_value` bigint(21) NOT NULL, + `next_not_cached_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) sequence=1; + +# key + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE OR REPLACE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL, + key key1 (next_not_cached_value) +) sequence=1; + +# Check constraint + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL, + CHECK (start_value < minimum_value) +) sequence=1; + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE TABLE t1 ( + `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 CHECK (start_value < minimum_value), + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) sequence=1; + + +# Virtual field + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +CREATE TABLE t1 ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) generated always as (1) virtual +) sequence=1; + +drop sequence if exists t1; + +# +# DROP SEQUENCE +# + +create sequence t1; +create sequence t2; +create table t3 (a int) engine=myisam; +select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog="test"; + +CREATE SEQUENCE s1; +drop sequence s1; + +drop sequence if exists t1,t2,t3,t4; +drop table if exists t1,t2,t3; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop table t1,t2,s1; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop table if exists t1,t2,s1,s2; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +--error ER_NOT_SEQUENCE2 +drop sequence t1,t2,s1,s2; +drop table if exists t1,t2; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE SEQUENCE s1; +drop sequence if exists t1,t2,s1,s2; +drop table if exists t1,t2; + +CREATE TEMPORARY SEQUENCE s1; +DROP SEQUENCE s1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE s1; + +CREATE TEMPORARY SEQUENCE s1; +CREATE SEQUENCE s2; +CREATE TEMPORARY TABLE t1 (a int); +CREATE TABLE t2 (a int); +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1,t2,s1,s2; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE s1; +DROP TEMPORARY TABLE t1; +--error ER_BAD_TABLE_ERROR +DROP TABLE t1,t2,s1,s2; + +create view v1 as (select 1); +CREATE SEQUENCE s1; +--error ER_IT_IS_A_VIEW +DROP SEQUENCE s1,v1; +drop view v1; + +# +# CREATE TEMPORARY SEQUENCE +# + +--disable_ps2_protocol +CREATE TEMPORARY SEQUENCE t1; +select next value for t1; +drop temporary table t1; +--error ER_UNKNOWN_SEQUENCES +select previous value for t1; +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10; +select next value for t1; +select previous value for t1; +CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10; +select previous value for t1; +select next value for t1; +select previous value for t1; +drop temporary sequence t1; +select previous value for t1; +drop sequence t1; + +CREATE TEMPORARY SEQUENCE t1 engine=innodb; +select next value for t1; +drop temporary table t1; +--error ER_UNKNOWN_SEQUENCES +select previous value for t1; +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10 engine=innodb; +select next value for t1; +select previous value for t1; +CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +select previous value for t1; +select next value for t1; +select previous value for t1; +drop temporary sequence t1; +select previous value for t1; +drop sequence t1; +--enable_ps2_protocol + +# +# Check that we can't create anything with the sequence engine +# + +--error ER_UNKNOWN_STORAGE_ENGINE +create table t1 (a int) engine=sql_sequence; + +--echo # +--echo # MDEV-13711 assertion on CREATE LIKE fix +--echo # + +create sequence s; +create table t like s; +show create table t; +show create sequence t; +drop tables t, s; + +--echo # +--echo # MDEV-13714 SEQUENCE option fix +--echo # + +create or replace table s ( + `next_value` bigint(21) not null, + `min_value` bigint(21) not null, + `max_value` bigint(21) not null, + `start` bigint(21) not null, + `increment` bigint(21) not null, + `cache` bigint(21) not null, + `cycle` tinyint(1) unsigned not null, + `round` bigint(21) not null) +sequence=0; + +create or replace table s2 ( + `next_value` bigint(21) not null, + `min_value` bigint(21) not null, + `max_value` bigint(21) not null, + `start` bigint(21) not null, + `increment` bigint(21) not null, + `cache` bigint(21) not null, + `cycle` tinyint(1) unsigned not null, + `round` bigint(21) not null) +sequence=default; + +show create table s; +show create table s2; +--error ER_NOT_SEQUENCE +show create sequence s; +--error ER_NOT_SEQUENCE +show create sequence s2; +drop table s,s2; + +--echo # +--echo # MDEV-13721 Assertion is_lock_owner() failed in mysql_rm_table_no_locks +--echo # + +create or replace sequence s; +create temporary table s (i int); +drop sequence s; +show create table s; +drop table s; + +create or replace sequence s; +create temporary sequence s; +show create table s; +drop sequence s; +show create table s; +drop table s; + +create or replace sequence s; +create temporary sequence s; +drop temporary sequence s; +show create table s; +drop table s; + +create temporary sequence s; +drop temporary table s; +create temporary table s (i int); +--error ER_UNKNOWN_SEQUENCES +drop temporary sequence s; +drop table s; + +--echo # +--echo # MDEV-15115 Assertion failure in CREATE SEQUENCE...ROW_FORMAT=REDUNDANT +--echo # +CREATE SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT; + +--replace_column 1 # 5 # +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/seq1'; + +DROP SEQUENCE seq1; +CREATE TEMPORARY SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +DROP TEMPORARY SEQUENCE seq1; + +--echo # +--echo # MDEV-17503 CREATE SEQUENCE failed with innodb_force_primary_key =1 +--echo # + +set global innodb_force_primary_key =1; +CREATE SEQUENCE s1 START WITH 100 INCREMENT BY 10 ENGINE=innodb; +set global innodb_force_primary_key=default; +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +ALTER TABLE s1 ADD PRIMARY KEY (next_not_cached_value); +DROP SEQUENCE s1; + +--echo # +--echo # Beginning of 10.4 Test +--echo # +--echo # MDEV-13005: Fixing bugs in SEQUENCE, part 3 +--echo # + +--echo # Task 1: +--error ER_UNKNOWN_SEQUENCES +SET @x = PREVIOUS VALUE FOR x; + +--echo # Task 2: +--error ER_SEQUENCE_INVALID_DATA +CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678; + +--echo # Task 3: +--error ER_SEQUENCE_INVALID_DATA +CREATE SEQUENCE seq1 START WITH 1 cache -1; + +--echo # Task 4: +CREATE TEMPORARY TABLE s1 (s1 INT); +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE s1; +DROP TEMPORARY TABLE s1; + +--echo # Task 5: +CREATE TEMPORARY TABLE s1 (s1 INT); +--error ER_PARSE_ERROR +CREATE TEMPORARY SEQUENCE s1 (s1 INT); +DROP TEMPORARY TABLE s1; + +--echo # Task 6: +CREATE SEQUENCE seq1 START WITH 2; +--error ER_TRG_ON_VIEW_OR_TEMP_TABLE +CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; +DROP SEQUENCE seq1; + +--echo # +--echo # MDEV-29771: Server crashes in check_sequence_fields upon +--echo # CREATE TABLE .. SEQUENCE=1 AS SELECT .. +--echo # + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +create table s sequence=1 as select 1; + +--echo # +--echo # End of 10.4 test +--echo # diff --git a/mysql-test/suite/sql_sequence/debug_sync.opt b/mysql-test/suite/sql_sequence/debug_sync.opt new file mode 100644 index 00000000..7ba8cab8 --- /dev/null +++ b/mysql-test/suite/sql_sequence/debug_sync.opt @@ -0,0 +1 @@ +--loose-debug-sync-timeout=2 diff --git a/mysql-test/suite/sql_sequence/debug_sync.result b/mysql-test/suite/sql_sequence/debug_sync.result new file mode 100644 index 00000000..516b2343 --- /dev/null +++ b/mysql-test/suite/sql_sequence/debug_sync.result @@ -0,0 +1,7 @@ +connect con1,localhost,root,,; +CREATE TEMPORARY SEQUENCE f ENGINE=InnoDB; +disconnect con1; +connection default; +SELECT 'Still alive' AS `Heartbeat`; +Heartbeat +Still alive diff --git a/mysql-test/suite/sql_sequence/debug_sync.test b/mysql-test/suite/sql_sequence/debug_sync.test new file mode 100644 index 00000000..ecc75f79 --- /dev/null +++ b/mysql-test/suite/sql_sequence/debug_sync.test @@ -0,0 +1,15 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc + +# +# MDEV-13029 +# Assertion `ds_control' failed in debug_sync upon closing connection after +# creating temporary sequence + +--connect (con1,localhost,root,,) +CREATE TEMPORARY SEQUENCE f ENGINE=InnoDB; +--disconnect con1 +--connection default +--sleep 3 + +SELECT 'Still alive' AS `Heartbeat`; diff --git a/mysql-test/suite/sql_sequence/default.result b/mysql-test/suite/sql_sequence/default.result new file mode 100644 index 00000000..eecef1d3 --- /dev/null +++ b/mysql-test/suite/sql_sequence/default.result @@ -0,0 +1,297 @@ +drop table if exists t1,s1,s2; +Warnings: +Note 1051 Unknown table 'test.t1,test.s1,test.s2' +drop view if exists v1; +Warnings: +Note 4092 Unknown VIEW: 'test.v1' +# +# Test DEFAULT +# +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT nextval(`test`.`s1`), + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +insert into t1 SET b=1; +insert into t1 SET b=2; +insert into t1 (b) values (3),(4); +select * from t1; +a b +1 1 +2 2 +3 3 +4 4 +update t1 set b=5 where a=1; +delete from t1 where b=1; +select * from t1; +a b +1 5 +2 2 +3 3 +4 4 +# +# Executing DEFAULT function +# +INSERT into t1 values(default(a),10); +INSERT into t1 values(default(a),default(a)); +update t1 set a=default(a), b=12 where b=2; +select * from t1; +a b +1 5 +8 12 +3 3 +4 4 +5 10 +6 7 +select default(a), a, b from t1; +default(a) a b +9 1 5 +10 8 12 +11 3 3 +12 4 4 +13 5 10 +14 6 7 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +15 1 9223372036854775806 1 1 0 0 0 +select * from t1 where default(a) > 0; +a b +1 5 +8 12 +3 3 +4 4 +5 10 +6 7 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +16 1 9223372036854775806 1 1 0 0 0 +# +# View +# +create view v1 as select * from t1; +insert into v1 set b=20; +select * from v1; +a b +1 5 +8 12 +3 3 +4 4 +5 10 +6 7 +16 20 +drop view v1; +# +# Alter table +# +CREATE SEQUENCE s2 nocache engine=myisam; +alter table t1 add column c int default next value for s2, add column d int default previous value for s2; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT nextval(`test`.`s1`), + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT nextval(`test`.`s2`), + `d` int(11) DEFAULT lastval(`test`.`s2`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +select * from t1; +a b c d +1 5 1 1 +8 12 2 2 +3 3 3 3 +4 4 4 4 +5 10 5 5 +6 7 6 6 +16 20 7 7 +drop sequence s2; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT nextval(`test`.`s1`), + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT nextval(`test`.`s2`), + `d` int(11) DEFAULT lastval(`test`.`s2`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1; +drop sequence s1; +# +# LOCK TABLES +# +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +insert into t1 (b) values (3),(4); +LOCK TABLE t1 WRITE; +insert into t1 (b) values (5),(6); +ERROR HY000: Table 's1' was not locked with LOCK TABLES +UNLOCK TABLES; +LOCK TABLE t1 WRITE, s1 WRITE; +insert into t1 (b) values (5),(6); +select default(a) from t1; +default(a) +5 +6 +7 +8 +UNLOCK TABLES; +LOCK TABLE t1 READ; +insert into t1 (b) values (5),(6); +ERROR HY000: Table 's1' was not locked with LOCK TABLES +select default(a) from t1; +ERROR HY000: Table 's1' was not locked with LOCK TABLES +UNLOCK TABLES; +LOCK TABLE t1 READ, s1 read; +insert into t1 (b) values (5),(6); +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +select default(a) from t1; +ERROR HY000: Table 's1' was locked with a READ lock and can't be updated +UNLOCK TABLES; +drop table t1; +drop sequence s1; +# +# Testing prepared statements +# +CREATE or replace SEQUENCE s1 nocache engine=myisam; +CREATE or replace table t1 (a int default next value for s1, b int); +PREPARE stmt FROM "insert into t1 (b) values(?)"; +execute stmt using 1; +execute stmt using 2; +execute stmt using 3; +select * from t1; +a b +1 1 +2 2 +3 3 +drop table t1,s1; +deallocate prepare stmt; +# +# Wrong usage of default +# +CREATE table t1 (a int default next value for s1, b int); +ERROR 42S02: Table 'test.s1' doesn't exist +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +DROP SEQUENCE s1; +insert into t1 (b) values (5),(6); +ERROR 42S02: Table 'test.s1' doesn't exist +ALTER TABLE t1 add column c int; +ERROR 42S02: Table 'test.s1' doesn't exist +CREATE SEQUENCE s1 nocache engine=myisam; +ALTER TABLE t1 add column c int; +ALTER TABLE t1 add column d int default next value for s_not_exits; +ERROR 42S02: Table 'test.s_not_exits' doesn't exist +drop table t1; +drop sequence s1; +# +# MDEV 22785 Crash with prepared statements and NEXTVAL() +# +CREATE SEQUENCE s; +CREATE TABLE t1 (id int NOT NULL DEFAULT NEXTVAL(s), PRIMARY KEY (id)); +PREPARE stmt FROM " INSERT INTO t1 () values ()"; +INSERT INTO t1 () values (); +EXECUTE stmt; +DROP TABLE t1; +DROP SEQUENCE s; +# +# MDEV-29540 Incorrect sequence values in INSERT SELECT +# +CREATE SEQUENCE s1; +CREATE TABLE t1 ( +a BIGINT UNSIGNED NOT NULL PRIMARY KEY +DEFAULT (NEXT VALUE FOR s1), +b CHAR(1) NOT NULL +); +INSERT INTO t1 (b) VALUES ('a'); +INSERT INTO t1 (b) VALUES ('b'), ('c'); +INSERT INTO t1 (b) VALUES ('d'); +INSERT INTO t1 (b) SELECT c FROM ( +SELECT 'e' as c +UNION +SELECT 'f' + UNION +SELECT 'g' +) der; +SELECT a, b FROM t1; +a b +1 a +2 b +3 c +4 d +5 e +6 f +7 g +ALTER SEQUENCE s1 RESTART; +INSERT INTO t1 (b) SELECT c FROM ( +SELECT 'a' as c +UNION +SELECT 'b' + UNION +SELECT 'c' + UNION +SELECT 'd' + UNION +SELECT 'e' + UNION +SELECT 'f' + UNION +SELECT 'g' +) der; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +ALTER SEQUENCE s1 RESTART; +INSERT IGNORE INTO t1 (b) SELECT c FROM ( +SELECT 'a' as c +UNION +SELECT 'b' + UNION +SELECT 'c' + UNION +SELECT 'd' + UNION +SELECT 'e' + UNION +SELECT 'f' + UNION +SELECT 'g' +) der; +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +Warning 1062 Duplicate entry '2' for key 'PRIMARY' +Warning 1062 Duplicate entry '3' for key 'PRIMARY' +Warning 1062 Duplicate entry '4' for key 'PRIMARY' +Warning 1062 Duplicate entry '5' for key 'PRIMARY' +Warning 1062 Duplicate entry '6' for key 'PRIMARY' +Warning 1062 Duplicate entry '7' for key 'PRIMARY' +SELECT a, b FROM t1; +a b +1 a +2 b +3 c +4 d +5 e +6 f +7 g +INSERT IGNORE INTO t1 (b) SELECT c FROM ( +SELECT 'h' as c +UNION +SELECT 'i' + UNION +SELECT 'j' +) der; +SELECT a, b FROM t1; +a b +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 h +9 i +10 j +DROP TABLE t1; +DROP SEQUENCE s1; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/sql_sequence/default.test b/mysql-test/suite/sql_sequence/default.test new file mode 100644 index 00000000..f965089d --- /dev/null +++ b/mysql-test/suite/sql_sequence/default.test @@ -0,0 +1,221 @@ +# +# Testing sequence in DEFAULT clause +# +--source include/have_sequence.inc + +drop table if exists t1,s1,s2; +drop view if exists v1; + +--echo # +--echo # Test DEFAULT +--echo # + +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +show create table t1; +insert into t1 SET b=1; +insert into t1 SET b=2; +insert into t1 (b) values (3),(4); +select * from t1; +update t1 set b=5 where a=1; +delete from t1 where b=1; +select * from t1; + +--echo # +--echo # Executing DEFAULT function +--echo # + +INSERT into t1 values(default(a),10); +INSERT into t1 values(default(a),default(a)); +update t1 set a=default(a), b=12 where b=2; +select * from t1; +--disable_ps2_protocol +select default(a), a, b from t1; +select * from s1; +select * from t1 where default(a) > 0; +select * from s1; +--enable_ps2_protocol + +--echo # +--echo # View +--echo # + +create view v1 as select * from t1; +insert into v1 set b=20; +select * from v1; +drop view v1; + +--echo # +--echo # Alter table +--echo # + +CREATE SEQUENCE s2 nocache engine=myisam; +alter table t1 add column c int default next value for s2, add column d int default previous value for s2; +show create table t1; +select * from t1; +drop sequence s2; +show create table t1; +drop table t1; +drop sequence s1; + +--echo # +--echo # LOCK TABLES +--echo # + +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +insert into t1 (b) values (3),(4); +LOCK TABLE t1 WRITE; +--error ER_TABLE_NOT_LOCKED +insert into t1 (b) values (5),(6); +UNLOCK TABLES; + +LOCK TABLE t1 WRITE, s1 WRITE; +insert into t1 (b) values (5),(6); +--disable_ps2_protocol +select default(a) from t1; +--enable_ps2_protocol +UNLOCK TABLES; + +LOCK TABLE t1 READ; +--error ER_TABLE_NOT_LOCKED +insert into t1 (b) values (5),(6); +--error ER_TABLE_NOT_LOCKED +select default(a) from t1; +UNLOCK TABLES; + +LOCK TABLE t1 READ, s1 read; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +insert into t1 (b) values (5),(6); +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +select default(a) from t1; +UNLOCK TABLES; + +drop table t1; +drop sequence s1; + +--echo # +--echo # Testing prepared statements +--echo # + +CREATE or replace SEQUENCE s1 nocache engine=myisam; +CREATE or replace table t1 (a int default next value for s1, b int); +PREPARE stmt FROM "insert into t1 (b) values(?)"; +execute stmt using 1; +execute stmt using 2; +execute stmt using 3; +select * from t1; +drop table t1,s1; +deallocate prepare stmt; + +--echo # +--echo # Wrong usage of default +--echo # + +--error ER_NO_SUCH_TABLE +CREATE table t1 (a int default next value for s1, b int); +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +DROP SEQUENCE s1; +--error ER_NO_SUCH_TABLE +insert into t1 (b) values (5),(6); +--error ER_NO_SUCH_TABLE +ALTER TABLE t1 add column c int; +CREATE SEQUENCE s1 nocache engine=myisam; +ALTER TABLE t1 add column c int; +--error ER_NO_SUCH_TABLE +ALTER TABLE t1 add column d int default next value for s_not_exits; +drop table t1; +drop sequence s1; + +--echo # +--echo # MDEV 22785 Crash with prepared statements and NEXTVAL() +--echo # +CREATE SEQUENCE s; +CREATE TABLE t1 (id int NOT NULL DEFAULT NEXTVAL(s), PRIMARY KEY (id)); +PREPARE stmt FROM " INSERT INTO t1 () values ()"; +INSERT INTO t1 () values (); +EXECUTE stmt; +# Cleanup +DROP TABLE t1; +DROP SEQUENCE s; + +--echo # +--echo # MDEV-29540 Incorrect sequence values in INSERT SELECT +--echo # + +CREATE SEQUENCE s1; +CREATE TABLE t1 ( + a BIGINT UNSIGNED NOT NULL PRIMARY KEY + DEFAULT (NEXT VALUE FOR s1), + b CHAR(1) NOT NULL +); + +INSERT INTO t1 (b) VALUES ('a'); +INSERT INTO t1 (b) VALUES ('b'), ('c'); +INSERT INTO t1 (b) VALUES ('d'); +INSERT INTO t1 (b) SELECT c FROM ( + SELECT 'e' as c + UNION + SELECT 'f' + UNION + SELECT 'g' +) der; + +SELECT a, b FROM t1; + +ALTER SEQUENCE s1 RESTART; + +--error ER_DUP_ENTRY +INSERT INTO t1 (b) SELECT c FROM ( + SELECT 'a' as c + UNION + SELECT 'b' + UNION + SELECT 'c' + UNION + SELECT 'd' + UNION + SELECT 'e' + UNION + SELECT 'f' + UNION + SELECT 'g' +) der; + +ALTER SEQUENCE s1 RESTART; + +INSERT IGNORE INTO t1 (b) SELECT c FROM ( + SELECT 'a' as c + UNION + SELECT 'b' + UNION + SELECT 'c' + UNION + SELECT 'd' + UNION + SELECT 'e' + UNION + SELECT 'f' + UNION + SELECT 'g' +) der; + +SELECT a, b FROM t1; + +INSERT IGNORE INTO t1 (b) SELECT c FROM ( + SELECT 'h' as c + UNION + SELECT 'i' + UNION + SELECT 'j' +) der; + +SELECT a, b FROM t1; + +DROP TABLE t1; +DROP SEQUENCE s1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/sql_sequence/disabled.def b/mysql-test/suite/sql_sequence/disabled.def new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/mysql-test/suite/sql_sequence/disabled.def diff --git a/mysql-test/suite/sql_sequence/grant.result b/mysql-test/suite/sql_sequence/grant.result new file mode 100644 index 00000000..0a69d69f --- /dev/null +++ b/mysql-test/suite/sql_sequence/grant.result @@ -0,0 +1,60 @@ +SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_AUTO_CREATE_USER', ''); +create database mysqltest_1; +use mysqltest_1; +grant all on mysqltest_1.* to 'normal'@'%'; +grant select on mysqltest_1.* to 'read_only'@'%'; +grant select,insert on mysqltest_1.* to 'read_write'@'%'; +grant select,insert,alter on mysqltest_1.* to 'alter'@'%'; +grant alter on mysqltest_1.* to only_alter@'%'; +connect normal,localhost,normal,,mysqltest_1; +connect read_only,localhost,read_only,,mysqltest_1; +connect read_write,localhost,read_write,,mysqltest_1; +connect alter,localhost,alter,,mysqltest_1; +connect only_alter, localhost, only_alter,,mysqltest_1; +connection normal; +create sequence s1; +select next value for s1; +next value for s1 +1 +alter sequence s1 restart= 11; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 1 1 1000 0 0 +connection read_only; +select next value for s1; +ERROR 42000: INSERT command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1` +alter sequence s1 restart= 11; +ERROR 42000: ALTER command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1` +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 1 1 1000 0 0 +connection read_write; +select next value for s1; +next value for s1 +11 +alter sequence s1 restart= 11; +ERROR 42000: ALTER command denied to user 'read_write'@'localhost' for table `mysqltest_1`.`s1` +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1011 1 9223372036854775806 1 1 1000 0 0 +connection alter; +select next value for s1; +next value for s1 +12 +alter sequence s1 restart= 11; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 1 1 1000 0 0 +connection only_alter; +select next value for s1; +ERROR 42000: INSERT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1` +alter sequence s1 restart= 11; +select * from s1; +ERROR 42000: SELECT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1` +connection default; +drop database mysqltest_1; +drop user 'normal'@'%'; +drop user 'read_only'@'%'; +drop user 'read_write'@'%'; +drop user 'alter'@'%'; +drop user 'only_alter'@'%'; diff --git a/mysql-test/suite/sql_sequence/grant.test b/mysql-test/suite/sql_sequence/grant.test new file mode 100644 index 00000000..fb8a9f81 --- /dev/null +++ b/mysql-test/suite/sql_sequence/grant.test @@ -0,0 +1,69 @@ +# +# Test some grants with sequences +# Note that replication.test also does some grant testing +# + +# Grant tests not performed with embedded server +-- source include/not_embedded.inc + + +SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_AUTO_CREATE_USER', ''); +create database mysqltest_1; +use mysqltest_1; +grant all on mysqltest_1.* to 'normal'@'%'; +grant select on mysqltest_1.* to 'read_only'@'%'; +grant select,insert on mysqltest_1.* to 'read_write'@'%'; +grant select,insert,alter on mysqltest_1.* to 'alter'@'%'; +grant alter on mysqltest_1.* to only_alter@'%'; + +connect(normal,localhost,normal,,mysqltest_1); +connect(read_only,localhost,read_only,,mysqltest_1); +connect(read_write,localhost,read_write,,mysqltest_1); +connect(alter,localhost,alter,,mysqltest_1); +connect(only_alter, localhost, only_alter,,mysqltest_1); + +--disable_ps2_protocol +connection normal; +create sequence s1; +select next value for s1; +alter sequence s1 restart= 11; +select * from s1; + +connection read_only; +--error ER_TABLEACCESS_DENIED_ERROR +select next value for s1; +--error ER_TABLEACCESS_DENIED_ERROR +alter sequence s1 restart= 11; +select * from s1; + +connection read_write; +select next value for s1; +--error ER_TABLEACCESS_DENIED_ERROR +alter sequence s1 restart= 11; +select * from s1; + +connection alter; +select next value for s1; +alter sequence s1 restart= 11; +select * from s1; + +connection only_alter; +--error ER_TABLEACCESS_DENIED_ERROR +select next value for s1; +alter sequence s1 restart= 11; +--error ER_TABLEACCESS_DENIED_ERROR +select * from s1; +--enable_ps2_protocol + +# +# Cleanup +# + +connection default; +drop database mysqltest_1; +drop user 'normal'@'%'; +drop user 'read_only'@'%'; +drop user 'read_write'@'%'; +drop user 'alter'@'%'; +drop user 'only_alter'@'%'; + diff --git a/mysql-test/suite/sql_sequence/gtid-master.opt b/mysql-test/suite/sql_sequence/gtid-master.opt new file mode 100644 index 00000000..dd4fb8c5 --- /dev/null +++ b/mysql-test/suite/sql_sequence/gtid-master.opt @@ -0,0 +1,3 @@ +--binlog_format=row +--query_cache_type=1 +--log-slave-updates diff --git a/mysql-test/suite/sql_sequence/gtid-slave.opt b/mysql-test/suite/sql_sequence/gtid-slave.opt new file mode 100644 index 00000000..dc0ff186 --- /dev/null +++ b/mysql-test/suite/sql_sequence/gtid-slave.opt @@ -0,0 +1,4 @@ +--binlog_format=row +--query_cache_type=1 +--read_only=true +--log-slave-updates 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 diff --git a/mysql-test/suite/sql_sequence/gtid.test b/mysql-test/suite/sql_sequence/gtid.test new file mode 100644 index 00000000..37c1c8d2 --- /dev/null +++ b/mysql-test/suite/sql_sequence/gtid.test @@ -0,0 +1,710 @@ +# +# This test is based on tests from ALISQL test suite +# + +--source include/have_binlog_format_row.inc +--source include/master-slave.inc +--source include/have_innodb.inc + +--disable_ps2_protocol +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; + +--sync_slave_with_master + +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; + +--echo ########################################### +--echo master and slave sync sequence. +--echo ########################################### +connection master; +use s_db; + +create sequence s1; +show create table s1; + +--sync_slave_with_master +use s_db; +show create table s1; + +connection master; +use s_db; + +drop sequence s1; + +--echo ########################################### +--echo not support create table engine=sequence. +--echo ########################################### +connection master; + +--error ER_UNKNOWN_STORAGE_ENGINE +create table t(id int)engine=sequence; + +create table t(id int)engine=innodb; + +--error ER_UNKNOWN_STORAGE_ENGINE +alter table t engine=sequence; + +drop table t; +--echo ########################################### +--echo not support alter sequence table. +--echo ########################################### +connection master; + +create sequence s2; + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +alter table s2 add id int; + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +alter table s2 add index ind_x(start_value); +drop sequence s2; + +--echo ########################################### +--echo Support create temporary sequence. +--echo ########################################### +connection master; + +create temporary sequence s2; +drop temporary sequence s2; + +--echo ########################################### +--echo all invalid sequence value +--echo ########################################### + +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; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 100000 + increment by 1 + nocache + nocycle; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 5 + increment by 1 + nocache + nocycle; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 4 + increment by 1 + nocache + nocycle; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 4 + increment by 0 + nocache + nocycle; + +--echo ########################################### +--echo global read lock prevent query sequence +--echo ########################################### +connection master; +use s_db; +create sequence s_db.s1; +flush table with read lock; +--error ER_CANT_UPDATE_WITH_READLOCK +select next value for s1; + +unlock tables; + +drop sequence s_db.s1; + +--echo ########################################### +--echo query cache test +--echo ########################################### +connection master; +use s_db; +show global variables like 'query_cache_type'; + +show status like 'Qcache_hits'; +show status like 'Qcache_inserts'; + +--echo ########################################### +--echo priv test +--echo ########################################### +connection m_normal_1; +create sequence s_db.s1; +select * from s_db.s1; +select next value for s_db.s1; +select * from s_db.s1; +create sequence s_db.s2; +drop sequence s_db.s2; + + +connection m_normal_2; +--error ER_TABLEACCESS_DENIED_ERROR +select next value for s_db.s1; +--error ER_TABLEACCESS_DENIED_ERROR +create sequence s_db.s2; + +connection m_normal_1; +drop sequence s_db.s1; + +--echo ########################################### +--echo run out sequence value +--echo ########################################### +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; +--error ER_SEQUENCE_RUN_OUT +insert into t select next value for s_t; +--error ER_SEQUENCE_RUN_OUT +insert into t select next value for s_t; +commit; +select * from t; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +use s_db; +select * from t; + +connection m_normal_1; +use s_db; +drop sequence s_t; +drop table t; + +--echo ########################################### +--echo read_only prevent query sequence +--echo ########################################### +connection m_normal_1; +create sequence s_db.s1; +show global variables like 'read_only'; +select next value for s_db.s1; + +connection master; +sync_slave_with_master; + +connection s_normal_3; +show global variables like 'read_only'; +--error ER_OPTION_PREVENTS_STATEMENT +select next value for s_db.s1; + +connection m_normal_1; +drop sequence s_db.s1; + +--echo ########################################### +--echo update based table +--echo ########################################### +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; +--sync_slave_with_master + +connection s_normal_3; +use s_db; +select * from s_t; + +connection m_normal_1; +select next value for s_t; +select * from s_t; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from s_t; + +--echo ------------------------------------------ +--echo master update nextval; +--echo ------------------------------------------ +connection m_normal_1; +select next value for s_t; +--error ER_ILLEGAL_HA +update s_t set next_not_cached_value= 11; +alter sequence s_t restart=11; +commit; + +select * from s_t; + +connection master; +--sync_slave_with_master + +--echo ------------------------------------------ +--echo show slave nextval; +--echo ------------------------------------------ +connection s_normal_3; +select * from s_t; + +connection m_normal_1; +select next value for s_t; +select * from s_t; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from s_t; + + +--echo ------------------------------------------ +--echo update into invalid sequence +--echo ------------------------------------------ +connection m_normal_1; +select * from s_t; +--error ER_ILLEGAL_HA +update s_t set next_not_cached_value= 11,start_value=10, minimum_value=11; +--error ER_SEQUENCE_INVALID_DATA +ALTER SEQUENCE s_t restart with 11 start=10 minvalue=11; +commit; + +create table t_1(id int); +insert into t_1 value(1111); +select next value for s_t; +insert into t_1 select next value for s_t; +commit; + +select * from t_1; + +--echo ------------------------------------------ +--echo delete sequence row +--echo ------------------------------------------ +connection m_normal_1; +--error ER_ILLEGAL_HA +delete from s_t; +commit; + +select next value for s_t; + +connection m_normal_1; +drop sequence s_t; +drop table t_1; + +--echo ########################################### +--echo test transaction context (innodb) +--echo ########################################### + +--echo ------------------------------------------ +--echo transaction table and sequence +--echo normal transaction commit +--echo ------------------------------------------ +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; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +use s_db; +select * from t_1; + +--echo ------------------------------------------ +--echo normal transaction rollback +--echo ------------------------------------------ +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; +rollback; + +select * from t_1; +select next value for s_1; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +use s_db; +select * from t_1; + +connection m_normal_1; +use s_db; +drop sequence s_1; +drop table t_1; + +--echo ########################################### +--echo test transaction context (myisam) +--echo ########################################### + +--echo ------------------------------------------ +--echo transaction table and sequence +--echo normal transaction commit +--echo ------------------------------------------ +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; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +use s_db; +select * from t_1; + +--echo ------------------------------------------ +--echo normal transaction rollback +--echo ------------------------------------------ +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; +rollback; + +select * from t_1; +select next value for s_1; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +use s_db; +select * from t_1; + +connection m_normal_1; +use s_db; +drop sequence s_1; +drop table t_1; + +--echo ########################################### +--echo close binlog +--echo ########################################### +connection m_normal_1; +use s_db; +create sequence s1 cache 2; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; + +commit; +select * from s1; + +connection master; +--sync_slave_with_master + +connection slave; +use s_db; +select * from s1; + +--echo ------------------------------------------ +--echo close session binlog. +--echo ------------------------------------------ +connection master; +set session sql_log_bin=off; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; + +set session sql_log_bin=on; +select * from s1; + +connection master; +--sync_slave_with_master + +connection slave; +use s_db; +select * from s1; + +connection m_normal_1; +use s_db; +drop sequence s1; + +--echo ########################################### +--echo statement binlog +--echo ########################################### +--echo ------------------------------------------ +--echo set binlog_format=statement +--echo ------------------------------------------ +connection master; +set session binlog_format=statement; +select @@session.binlog_format; +create sequence s1 cache 2; +--error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE +select next value for s1; + +set session binlog_format=row; +select next value for s1; +select * from s1; + +connection master; +--sync_slave_with_master + +use s_db; +select * from s1; + +connection m_normal_1; +use s_db; +drop sequence s1; + +--echo ########################################### +--echo test savepoint +--echo ########################################### +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; +rollback to sp1; +select * from t1; +select next value for s1; + +commit; + +drop sequence s1; +drop table t1; + +--echo ########################################### +--echo test proc +--echo ########################################### +connection m_normal_1; +use s_db; +create table t(id int)engine=innodb; + +delimiter //; + +create procedure p1() +begin + create sequence s1 cache 2; +end// + +create procedure p2() +begin + insert into t select next value for s1; + commit; +end// + +delimiter ;// + +call p1(); +call p2(); +call p2(); +call p2(); +call p2(); + +select * from t; + +connection master; +--sync_slave_with_master + +use s_db; +select * from t; + +connection m_normal_1; +drop table t; +drop sequence s1; +drop procedure p1; +drop procedure p2; + +--echo ########################################### +--echo test trigger +--echo ########################################### +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; + +delimiter //; +CREATE TRIGGER tri_1 + before INSERT ON t2 FOR EACH ROW +BEGIN + INSERT INTO t1 select next value for s1; +END// +delimiter ;// + +begin; +insert into t2 values(1111); +insert into t2 values(1111); +insert into t2 values(1111); +insert into t2 values(1111); + +select * from t2; +select * from t1; +rollback; +select * from t2; +select * from t1; + +select next value for s1; + + +drop trigger tri_1; +drop table t1; +drop table t2; +drop sequence s1; + +--echo ########################################### +--echo test function +--echo ########################################### +connection m_normal_1; +use s_db; +create sequence s1 cache 2; +create table t1(id int)engine=innodb; + +delimiter //; +CREATE function f1() returns int +BEGIN + INSERT INTO t1 select next value for s1; + return (1); +END// +delimiter ;// + +begin; +select f1(); +select f1(); +select f1(); +select f1(); + +select * from t1; +rollback; +select * from t1; + +select next value for s1; + +drop function f1; +drop table t1; +drop sequence s1; + +--echo ############## +--echo Test GRANT +--echo ############## + +connection m_normal_2; +create table t1 (a int); +create sequence s1; +select next value for s1; +insert into t1 values (1); +connection m_normal_3; +select * from t1; +select * from s1; +select previous value for s1; +--error ER_TABLEACCESS_DENIED_ERROR +insert into t1 values (2); +--error ER_TABLEACCESS_DENIED_ERROR +select next value for s1; +--error ER_TABLEACCESS_DENIED_ERROR +do setval(s1,1000,0); +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; +do setval(s1,10000,0); +select * from s1; +connection m_normal_2; +drop table t1; +drop sequence s1; + +--echo # +--echo # Cleanup +--echo # + +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@'%'; +--enable_ps2_protocol + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/kill.result b/mysql-test/suite/sql_sequence/kill.result new file mode 100644 index 00000000..6d966254 --- /dev/null +++ b/mysql-test/suite/sql_sequence/kill.result @@ -0,0 +1,12 @@ +# +# MDEV-16929 Assertion ... in close_thread_tables upon killing connection +# running SHOW on sequence +# +CREATE SEQUENCE s ENGINE=InnoDB; +RENAME TABLE s TO s1; +connect con1,localhost,root,,test; +SHOW CREATE SEQUENCE s1; +connection default; +KILL thread_id; +connection default; +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/kill.test b/mysql-test/suite/sql_sequence/kill.test new file mode 100644 index 00000000..9caebc57 --- /dev/null +++ b/mysql-test/suite/sql_sequence/kill.test @@ -0,0 +1,20 @@ +--source include/have_innodb.inc + +--echo # +--echo # MDEV-16929 Assertion ... in close_thread_tables upon killing connection +--echo # running SHOW on sequence +--echo # + +CREATE SEQUENCE s ENGINE=InnoDB; +RENAME TABLE s TO s1; +--connect (con1,localhost,root,,test) +--let $conid= `SELECT CONNECTION_ID()` +--send + SHOW CREATE SEQUENCE s1; +--connection default +--replace_result $conid thread_id +--eval KILL $conid + +# Cleanup +--connection default +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/lock.result b/mysql-test/suite/sql_sequence/lock.result new file mode 100644 index 00000000..e92bdbff --- /dev/null +++ b/mysql-test/suite/sql_sequence/lock.result @@ -0,0 +1,39 @@ +drop table if exists s1, t1, t2; +CREATE SEQUENCE s1; +create table t1 (a int); +create table t2 (a int); +LOCK TABLE s1 WRITE, t1 write; +create or replace sequence s1; +select * from 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 * from t1; +a +select * from t2; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables; +select * from t1; +a +select * from t2; +a +drop tables s1, t1, t2; +CREATE SEQUENCE s1; +LOCK TABLE s1 READ; +SELECT NEXTVAL(s1); +ERROR HY000: Table 's1' was locked with a READ lock and can't be updated +SELECT NEXTVAL(s); +ERROR HY000: Table 's' was not locked with LOCK TABLES +DROP SEQUENCE s1; +ERROR HY000: Table 's1' was locked with a READ lock and can't be updated +unlock tables; +DROP SEQUENCE s1; +CREATE SEQUENCE seq1; +CREATE SEQUENCE seq2; +LOCK TABLE seq1 WRITE, seq2 WRITE; +INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1); +DROP SEQUENCE seq1, seq2; +CREATE OR REPLACE SEQUENCE s1 ENGINE=MyISAM; +LOCK TABLE s1 WRITE; +TRUNCATE TABLE s1; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s1` doesn't have this option +DROP SEQUENCE s1; diff --git a/mysql-test/suite/sql_sequence/lock.test b/mysql-test/suite/sql_sequence/lock.test new file mode 100644 index 00000000..1cb6aa6f --- /dev/null +++ b/mysql-test/suite/sql_sequence/lock.test @@ -0,0 +1,64 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +--disable_warnings +drop table if exists s1, t1, t2; +--enable_warnings + +# +# MDEV-14831 CREATE OR REPLACE SEQUENCE under LOCK TABLE corrupts the +# sequence, causes ER_KEY_NOT_FOUND +# +CREATE SEQUENCE s1; +create table t1 (a int); +create table t2 (a int); +LOCK TABLE s1 WRITE, t1 write; +create or replace sequence s1; +select * from s1; +select * from t1; +--error ER_TABLE_NOT_LOCKED +select * from t2; +unlock tables; +select * from t1; +select * from t2; +drop tables s1, t1, t2; + +# +# MDEV-15742 Assertion `table_share->tmp_table != NO_TMP_TABLE || +# m_lock_type == 1' failed in handler::ha_write_row +# + +CREATE SEQUENCE s1; +LOCK TABLE s1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +SELECT NEXTVAL(s1); +--error ER_TABLE_NOT_LOCKED +SELECT NEXTVAL(s); +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +DROP SEQUENCE s1; +unlock tables; +DROP SEQUENCE s1; + +# +# MDEV-15106 Unexpected ER_WRONG_INSERT_INTO_SEQUENCE upon INSERT with +# multiple locks on sequences +# + +CREATE SEQUENCE seq1; +CREATE SEQUENCE seq2; +LOCK TABLE seq1 WRITE, seq2 WRITE; +INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1); +DROP SEQUENCE seq1, seq2; + +# +# MDEV-15970 +# Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failure and/or +# ER_KEY_NOT_FOUND upon TRUNCATE sequence under lock +# + +CREATE OR REPLACE SEQUENCE s1 ENGINE=MyISAM; +LOCK TABLE s1 WRITE; +--error ER_ILLEGAL_HA +TRUNCATE TABLE s1; +# Cleanup +DROP SEQUENCE s1; diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result new file mode 100644 index 00000000..0199bb71 --- /dev/null +++ b/mysql-test/suite/sql_sequence/mysqldump.result @@ -0,0 +1,75 @@ +CREATE SEQUENCE a1 engine=aria; +CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; +insert into t1 values (1),(2); +CREATE SEQUENCE x1 engine=innodb; +# dump whole database +CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +SELECT SETVAL(`a1`, 1, 0); +CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +SELECT SETVAL(`x1`, 1, 0); +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024; +/*!40101 SET character_set_client = @saved_cs_client */; +INSERT INTO `t1` VALUES +(1), +(2); +# dump by tables order 1 +CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +SELECT SETVAL(`a1`, 1, 0); +CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +SELECT SETVAL(`x1`, 1, 0); +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024; +/*!40101 SET character_set_client = @saved_cs_client */; +INSERT INTO `t1` VALUES +(1), +(2); +# dump by tables order 2 +CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +SELECT SETVAL(`a1`, 1, 0); +CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +SELECT SETVAL(`x1`, 1, 0); +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024; +/*!40101 SET character_set_client = @saved_cs_client */; +INSERT INTO `t1` VALUES +(1), +(2); +# dump by tables only tables +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024; +/*!40101 SET character_set_client = @saved_cs_client */; +INSERT INTO `t1` VALUES +(1), +(2); +# dump by tables only sequences +CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +SELECT SETVAL(`a1`, 1, 0); +CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +SELECT SETVAL(`x1`, 1, 0); +# end of dumps +DROP TABLE a1,t1,x1; +set default_storage_engine=InnoDB; +create sequence t1; +LOCK TABLES t1 READ; +SELECT * FROM t1; +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 +unlock tables; +drop table t1; diff --git a/mysql-test/suite/sql_sequence/mysqldump.test b/mysql-test/suite/sql_sequence/mysqldump.test new file mode 100644 index 00000000..d2afb2fd --- /dev/null +++ b/mysql-test/suite/sql_sequence/mysqldump.test @@ -0,0 +1,37 @@ +# +# Testing mysqldump of sequences +# + +# Embedded server doesn't support external clients +--source include/not_embedded.inc +--source include/have_aria.inc +--source include/have_innodb.inc + +CREATE SEQUENCE a1 engine=aria; +CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; +insert into t1 values (1),(2); +CREATE SEQUENCE x1 engine=innodb; +--echo # dump whole database +--exec $MYSQL_DUMP --compact test +--echo # dump by tables order 1 +--exec $MYSQL_DUMP --compact --tables test t1 a1 x1 +--echo # dump by tables order 2 +--exec $MYSQL_DUMP --compact --tables test a1 t1 x1 +--echo # dump by tables only tables +--exec $MYSQL_DUMP --compact --tables test t1 +--echo # dump by tables only sequences +--exec $MYSQL_DUMP --compact --tables test a1 x1 +--echo # end of dumps + +DROP TABLE a1,t1,x1; + +# +# MDEV-12887 UT_LIST_GET_LEN(trx->lock.trx_locks) == 0 when mysqldump sequence +# + +set default_storage_engine=InnoDB; +create sequence t1; +LOCK TABLES t1 READ; +SELECT * FROM t1; +unlock tables; +drop table t1; diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result new file mode 100644 index 00000000..9d559210 --- /dev/null +++ b/mysql-test/suite/sql_sequence/next.result @@ -0,0 +1,550 @@ +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `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 +select next value for t1; +next value for t1 +1 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +3 0 +select next value for t1; +next value for t1 +2 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +3 0 +select next value for t1; +next value for t1 +3 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +5 0 +select next value for t1; +next value for t1 +4 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +5 0 +select next value for t1; +next value for t1 +5 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +7 0 +select next value for t1; +next value for t1 +6 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +7 0 +select next value for t1; +next value for t1 +7 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +9 0 +select next value for t1; +next value for t1 +8 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +9 0 +select next value for t1; +next value for t1 +9 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +11 0 +select next value for t1; +next value for t1 +10 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +11 0 +select next value for t1; +next value for t1 +1 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +3 1 +select NEXT VALUE for t1,seq from seq_1_to_20; +NEXT VALUE for t1 seq +2 1 +3 2 +4 3 +5 4 +6 5 +7 6 +8 7 +9 8 +10 9 +1 10 +2 11 +3 12 +4 13 +5 14 +6 15 +7 16 +8 17 +9 18 +10 19 +1 20 +drop sequence t1; +CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria; +select next value for t1; +next value for t1 +10 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +8 0 +select next value for t1; +next value for t1 +9 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +8 0 +select next value for t1; +next value for t1 +8 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +6 0 +select next value for t1; +next value for t1 +7 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +6 0 +select next value for t1; +next value for t1 +6 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +4 0 +select next value for t1; +next value for t1 +5 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +4 0 +select next value for t1; +next value for t1 +4 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +2 0 +select next value for t1; +next value for t1 +3 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +2 0 +select next value for t1; +next value for t1 +2 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +0 0 +select next value for t1; +next value for t1 +1 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +0 0 +select next value for t1; +next value for t1 +10 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +8 1 +select NEXT VALUE for t1,seq from seq_1_to_20; +NEXT VALUE for t1 seq +9 1 +8 2 +7 3 +6 4 +5 5 +4 6 +3 7 +2 8 +1 9 +10 10 +9 11 +8 12 +7 13 +6 14 +5 15 +4 16 +3 17 +2 18 +1 19 +10 20 +drop sequence t1; +CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle; +select next value for t1; +next value for t1 +8 +select next value for t1; +next value for t1 +9 +select next value for t1; +next value for t1 +10 +select previous value for t1; +previous value for t1 +10 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +select previous value for t1; +previous value for t1 +NULL +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +drop sequence t1; +create sequence s1 start with 1 cache 2 maxvalue 5; +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 +select next value for s1; +next value for s1 +5 +select next value for s1; +ERROR HY000: Sequence 'test.s1' has run out +drop sequence s1; +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10; +select next value for t1; +next value for t1 +1 +select * from t1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 100 1 1 10 0 0 +flush tables; +select next value for t1; +next value for t1 +11 +select nextval(t1); +nextval(t1) +12 +drop sequence t1; +CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +select previous value for t9; +previous value for t9 +NULL +select next value for t9; +next value for t9 +1 +select previous value for t9, lastval(t9); +previous value for t9 lastval(t9) +1 1 +select next value for t9; +next value for t9 +2 +select previous value for t9, lastval(t9); +previous value for t9 lastval(t9) +2 2 +select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20; +seq previous value for t9 NEXT VALUE for t9 previous value for t9 +1 2 3 3 +2 3 4 4 +3 4 5 5 +4 5 6 6 +5 6 7 7 +6 7 8 8 +7 8 9 9 +8 9 10 10 +9 10 1 1 +10 1 2 2 +11 2 3 3 +12 3 4 4 +13 4 5 5 +14 5 6 6 +15 6 7 7 +16 7 8 8 +17 8 9 9 +18 9 10 10 +19 10 1 1 +20 1 2 2 +select * from t9; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +6 1 10 1 1 5 1 2 +drop sequence t9; +CREATE SEQUENCE s1 cache=0; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 0 0 0 +select next value for s1; +next value for s1 +1 +select next_not_cached_value from s1; +next_not_cached_value +2 +select next value for s1; +next value for s1 +2 +select next_not_cached_value from s1; +next_not_cached_value +3 +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=1; +select next_not_cached_value from s1; +next_not_cached_value +1 +select next value for s1; +next value for s1 +1 +select next_not_cached_value from s1; +next_not_cached_value +2 +select next value for s1; +next value for s1 +2 +select next_not_cached_value from s1; +next_not_cached_value +3 +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=2; +select next_not_cached_value from s1; +next_not_cached_value +1 +select next value for s1; +next value for s1 +1 +select next_not_cached_value from s1; +next_not_cached_value +3 +select next value for s1; +next value for s1 +2 +select next_not_cached_value from s1; +next_not_cached_value +3 +DROP SEQUENCE s1; +CREATE SEQUENCE s1; +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 +alter sequence s1 increment -2; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1001 1 9223372036854775806 1 -2 1000 0 0 +select next value for s1; +next value for s1 +1001 +select next value for s1; +next value for s1 +999 +alter sequence s1 restart 6; +select next value for s1; +next value for s1 +6 +select next value for s1; +next value for s1 +4 +select next value for s1; +next value for s1 +2 +select next value for s1; +ERROR HY000: Sequence 'test.s1' has run out +DROP SEQUENCE s1; +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +select next value for t1; +next value for t1 +1 +select previous value for t1; +previous value for t1 +1 +flush tables; +select previous value for t1; +previous value for t1 +1 +drop sequence t1; +select previous value for t1; +ERROR 42S02: Unknown SEQUENCE: 't1' +CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +select previous value for t1; +previous value for t1 +NULL +select next value for t1; +next value for t1 +5 +select previous value for t1; +previous value for t1 +5 +drop sequence t1; +CREATE or replace SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 +INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb; +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=InnoDB SEQUENCE=1 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3984356 1 9999999999 3984356 1 20 1 0 +select NEXT VALUE FOR s1; +NEXT VALUE FOR s1 +3984356 +select NEXT VALUE FOR s1; +NEXT VALUE FOR s1 +3984357 +select NEXT VALUE FOR s1; +NEXT VALUE FOR s1 +3984358 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3984376 1 9999999999 3984356 1 20 1 0 +FLUSH TABLES; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3984376 1 9999999999 3984356 1 20 1 0 +select NEXT VALUE FOR s1; +NEXT VALUE FOR s1 +3984376 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3984396 1 9999999999 3984356 1 20 1 0 +drop sequence s1; +CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +explain select next value for t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +explain select next value for t1, minimum_value from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +drop table t1; +CREATE SEQUENCE s1; +CREATE TABLE t1 (a int); +insert into t1 values (next value for s1); +insert into t1 values (next value for s1); +select * from t1; +a +1 +2 +drop table t1,s1; +CREATE SEQUENCE s1; +CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam; +insert into t1 values (),(),(),(),(),(),(); +update t1 set b= next value for s1 where a <= 3; +select * from t1; +a b +1 1 +2 2 +3 3 +4 0 +5 0 +6 0 +7 0 +drop table t1,s1; +CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb'; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +3984356 1 9999999999 3984356 1 0 1 0 +select next value for s1; +next value for s1 +3984356 +explain extended select next value for s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select nextval(`test`.`s1`) AS `next value for s1` +explain extended select previous value for s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select lastval(`test`.`s1`) AS `previous value for s1` +drop sequence s1; +create table t1 (a int); +select next value for t1; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; +create sequence t1; +select next value for t1; +next value for t1 +1 +select next value for t1, minimum_value; +ERROR 42S22: Unknown column 'minimum_value' in 'field list' +drop sequence t1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +CREATE SEQUENCE s1; +SELECT +NEXT VALUE FOR s1, +PREVIOUS VALUE FOR s1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def NEXT VALUE FOR s1 8 20 1 Y 32896 0 63 +def PREVIOUS VALUE FOR s1 8 20 1 Y 32896 0 63 +NEXT VALUE FOR s1 PREVIOUS VALUE FOR s1 +1 1 +DROP SEQUENCE s1; +# +# MDEV-13720 ER_NOT_SEQUENCE for temporary table +# +create temporary table tmp (i int); +select next value for tmp; +ERROR 42S02: 'test.tmp' is not a SEQUENCE +drop table tmp; +# +# Test negative numbers +# +create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1; +select next value for s; +next value for s +1 +select next value for s; +next value for s +0 +flush tables; +select next value for s; +next value for s +-999 +drop sequence s; +# +# MDEV-23823 NEXT VALUE crash on locked view +# +CREATE VIEW v AS SELECT 1; +LOCK TABLE v READ; +SELECT NEXT VALUE FOR v; +ERROR 42S02: 'test.v' is not a SEQUENCE +# +# MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL +# +SELECT SETVAL (v,0); +ERROR 42S02: 'test.v' is not a SEQUENCE +UNLOCK TABLES; +DROP VIEW v; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test new file mode 100644 index 00000000..18b52961 --- /dev/null +++ b/mysql-test/suite/sql_sequence/next.test @@ -0,0 +1,301 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# Test sequence generation +# + +--disable_ps2_protocol +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle; +show create table t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; + +select NEXT VALUE for t1,seq from seq_1_to_20; + +drop sequence t1; + +CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; + +select NEXT VALUE for t1,seq from seq_1_to_20; + +drop sequence t1; + +CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle; +select next value for t1; +select next value for t1; +select next value for t1; +select previous value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +select previous value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +drop sequence t1; + +create sequence s1 start with 1 cache 2 maxvalue 5; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; +--error ER_SEQUENCE_RUN_OUT +select next value for s1; +drop sequence s1; + +# +# Test that flush tables jumps to next next_not_cached_value +# + +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10; +select next value for t1; +select * from t1; +flush tables; +select next value for t1; +select nextval(t1); +drop sequence t1; + +# +# Test currval/previous +# + +CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +select previous value for t9; +select next value for t9; +select previous value for t9, lastval(t9); +select next value for t9; +select previous value for t9, lastval(t9); +select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20; +select * from t9; +drop sequence t9; + +# +# CACHE = 0 should be same as CACHE = 1 +# +CREATE SEQUENCE s1 cache=0; +select * from s1; +select next value for s1; +select next_not_cached_value from s1; +select next value for s1; +select next_not_cached_value from s1; +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=1; +select next_not_cached_value from s1; +select next value for s1; +select next_not_cached_value from s1; +select next value for s1; +select next_not_cached_value from s1; +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=2; +select next_not_cached_value from s1; +select next value for s1; +select next_not_cached_value from s1; +select next value for s1; +select next_not_cached_value from s1; +DROP SEQUENCE s1; + +# +# Negative increment for sequence +# + +CREATE SEQUENCE s1; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; +alter sequence s1 increment -2; +select * from s1; +select next value for s1; +select next value for s1; +alter sequence s1 restart 6; +select next value for s1; +select next value for s1; +select next value for s1; +--error ER_SEQUENCE_RUN_OUT +select next value for s1; +DROP SEQUENCE s1; + +# +# Check what happens when one refers to a sequence that has been closed/deleted +# + +CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +select next value for t1; +select previous value for t1; +flush tables; +select previous value for t1; +drop sequence t1; +--error ER_UNKNOWN_SEQUENCES +select previous value for t1; +CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +select previous value for t1; +select next value for t1; +select previous value for t1; +drop sequence t1; + +# This failed in an early build + +CREATE or replace SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 +INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb; +show create table s1; +select * from s1; +select NEXT VALUE FOR s1; +select NEXT VALUE FOR s1; +select NEXT VALUE FOR s1; +select * from s1; +FLUSH TABLES; +select * from s1; +select NEXT VALUE FOR s1; +select * from s1; +drop sequence s1; + +# +# Explain +# + +CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle; +explain select next value for t1; +explain select next value for t1, minimum_value from t1; +drop table t1; + +# +# Using insert with NEXT VALUE +# + +CREATE SEQUENCE s1; +CREATE TABLE t1 (a int); +insert into t1 values (next value for s1); +insert into t1 values (next value for s1); +select * from t1; +drop table t1,s1; + +# +# Using update with NEXT VALUE +# + +CREATE SEQUENCE s1; +CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam; +insert into t1 values (),(),(),(),(),(),(); +update t1 set b= next value for s1 where a <= 3; +select * from t1; +drop table t1,s1; + +# +# NO CACHE and InnoDB +# + +CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb'; +select * from s1; +select next value for s1; +explain extended select next value for s1; +explain extended select previous value for s1; +drop sequence s1; + +# +# Some error testing +# + +create table t1 (a int); +--error ER_NOT_SEQUENCE +select next value for t1; +drop table t1; + +create sequence t1; +select next value for t1; +--error ER_BAD_FIELD_ERROR +select next value for t1, minimum_value; +drop sequence t1; + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +CREATE SEQUENCE s1; +SELECT + NEXT VALUE FOR s1, + PREVIOUS VALUE FOR s1; +DROP SEQUENCE s1; +--enable_ps_protocol +--disable_metadata + +--echo # +--echo # MDEV-13720 ER_NOT_SEQUENCE for temporary table +--echo # + +create temporary table tmp (i int); +--error ER_NOT_SEQUENCE +select next value for tmp; +drop table tmp; + +--echo # +--echo # Test negative numbers +--echo # + +create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1; +select next value for s; +select next value for s; +flush tables; +select next value for s; +drop sequence s; + +--echo # +--echo # MDEV-23823 NEXT VALUE crash on locked view +--echo # +CREATE VIEW v AS SELECT 1; +LOCK TABLE v READ; +--error ER_NOT_SEQUENCE +SELECT NEXT VALUE FOR v; + +--echo # +--echo # MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL +--echo # +--error ER_NOT_SEQUENCE +SELECT SETVAL (v,0); + +UNLOCK TABLES; +DROP VIEW v; +--disable_ps2_protocol diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result new file mode 100644 index 00000000..b950a7d8 --- /dev/null +++ b/mysql-test/suite/sql_sequence/other.result @@ -0,0 +1,391 @@ +# +# Create and check +# +create sequence s1 engine=innodb; +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1 +flush tables; +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1001 +flush tables; +repair table s1; +Table Op Msg_type Msg_text +test.s1 repair note The storage engine for the table doesn't support repair +select next value for s1; +next value for s1 +2001 +drop sequence s1; +create or replace sequence s1 engine=innodb; +select next value for s1; +next value for s1 +1 +repair table s1; +Table Op Msg_type Msg_text +test.s1 repair note The storage engine for the table doesn't support repair +check table s1; +Table Op Msg_type Msg_text +test.s1 check note The storage engine for the table doesn't support check +select next value for s1; +next value for s1 +1001 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2001 1 9223372036854775806 1 1 1000 0 0 +drop sequence s1; +# +# INSERT +# +create sequence s1; +create sequence s2; +insert into s1 (next_not_cached_value, minimum_value) values (100,1000); +ERROR HY000: Field 'maximum_value' doesn't have a default value +insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0); +ERROR HY000: Table 's1' is specified twice, both as a target for 'INSERT' and as a separate source for data +insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0); +ERROR HY000: Sequence 'test.s1' has out of range value for options +insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0); +ERROR HY000: Sequence 'test.s1' has out of range value for options +select * from 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 +insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0); +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1000 1 9223372036854775806 1 1 1000 0 0 +select next value for s1; +next value for s1 +1000 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2000 1 9223372036854775806 1 1 1000 0 0 +insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0); +ERROR HY000: Sequence 'test.s2' has out of range value for options +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2000 1 9223372036854775806 1 1 1000 0 0 +insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0); +select * from 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 * from s2; +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 +insert into s1 select * from s2; +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 +drop sequence s1,s2; +# +# UPDATE and DELETE +# +create sequence s1; +update s1 set next_not_cached_value=100; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s1` doesn't have this option +delete from s1 where next_not_cached_value > 0; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s1` doesn't have this option +drop sequence s1; +# +# SHOW TABLES +# +create sequence s1; +create table t1 (a int); +create view v1 as select * from s1; +show full tables; +Tables_in_test Table_type +s1 SEQUENCE +t1 BASE TABLE +v1 VIEW +SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test" ORDER BY TABLE_NAME; +TABLE_TYPE ENGINE +SEQUENCE MyISAM +BASE TABLE MyISAM +VIEW NULL +drop table t1,s1; +drop view v1; +# +# LOCK TABLES (as in mysqldump) +# +create sequence s1 engine=innodb; +LOCK TABLES s1 READ; +SELECT * from 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 +UNLOCK TABLES; +LOCK TABLES s1 WRITE; +insert into s1 values (1,1,9223372036854775806, 1, 1, 1000, 0, 0); +UNLOCK TABLES; +drop table s1; +# +# Many sequence calls with innodb +# +create sequence s1 cache=1000 engine=innodb; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +count(nextval(s1)) +2000 +commit; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2001 1 9223372036854775806 1 1 1000 0 0 +drop sequence s1; +create sequence s1 cache=1000 engine=innodb; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +count(nextval(s1)) +2000 +connect addconroot, localhost, root,,; +connection addconroot; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +count(nextval(s1)) +2000 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +4001 1 9223372036854775806 1 1 1000 0 0 +commit; +disconnect addconroot; +connection default; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +4001 1 9223372036854775806 1 1 1000 0 0 +commit; +drop sequence s1; +# +# Flush tables with read lock +# +create sequence s1; +select next value for s1; +next value for s1 +1 +flush tables with read lock; +create sequence s2; +ERROR HY000: Can't execute the query because you have a conflicting 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 s1; +# +# MDEV-14761 +# Assertion `!mysql_parse_status || thd->is_error() || +# thd->get_internal_handler()' failed in parse_sql +# +CREATE SEQUENCE s1; +ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE; +ERROR HY000: Option 'MAXVALUE' used twice in statement +DROP SEQUENCE s1; +# +# Don't allow SEQUENCE to be used with CHECK or virtual fields +# +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int check (next value for s1 > 0)); +ERROR HY000: Function or expression 'nextval()' cannot be used in the CHECK clause of `a` +CREATE table t1 (a int check (previous value for s1 > 0)); +ERROR HY000: Function or expression 'lastval()' cannot be used in the CHECK clause of `a` +CREATE table t1 (a int check (setval(s1,10))); +ERROR HY000: Function or expression 'setval()' cannot be used in the CHECK clause of `a` +CREATE TABLE t1 (a int, b int as (next value for s1 > 0)); +ERROR HY000: Function or expression 'nextval()' cannot be used in the GENERATED ALWAYS AS clause of `b` +drop sequence s1; +# +# MDEV-13024: Server crashes in my_store_ptr upon DELETE from +# sequence in multi-table format +# +CREATE SEQUENCE s; +CREATE table t1 (a int); +insert into t1 values (1),(2); +DELETE s FROM s; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s` doesn't have this option +delete t1,s from s,t1; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s` doesn't have this option +delete s,t1 from t1,s; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s` doesn't have this option +DROP SEQUENCE s; +DROP TABLE t1; +# +# MDEV-20074: Lost connection on update trigger +# +# INSERT & table +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO t2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +update t1 set p_first_name='Yunxi' where p_id=1; +drop sequence s1; +drop table t1,t2; +# INSERT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +update t1 set p_first_name='Yunxi' where p_id=1; +drop view v2; +drop table t1,t2; +drop sequence s1; +# INSERT SELECT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name; +END; +$$ +update t1 set p_first_name='Yunxi' where p_id=1; +drop view v2; +drop table t1,t2; +drop sequence s1; +# REPLACE & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +REPLACE INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +update t1 set p_first_name='Yunxi' where p_id=1; +drop view v2; +drop table t1,t2; +drop sequence s1; +# REPLACE SELECT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +REPLACE INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name; +END; +$$ +update t1 set p_first_name='Yunxi' where p_id=1; +drop view v2; +drop table t1,t2; +drop sequence s1; +# +# MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or +# Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE, +# table->db.str, table->table_name.str, MDL_SHARED)' failed +# in mysql_rm_table_no_locks +# +CREATE TABLE t1 (a INT); +CREATE TEMPORARY TABLE tmp (b INT); +LOCK TABLE t1 READ; +DROP SEQUENCE tmp; +ERROR 42S02: Unknown SEQUENCE: 'test.tmp' +DROP TEMPORARY SEQUENCE tmp; +ERROR 42S02: Unknown SEQUENCE: 'test.tmp' +DROP SEQUENCE t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +DROP TEMPORARY SEQUENCE t1; +ERROR 42S02: Unknown SEQUENCE: 'test.t1' +UNLOCK TABLES; +DROP SEQUENCE t1; +ERROR 42S02: 'test.t1' is not a SEQUENCE +DROP TEMPORARY SEQUENCE t1; +ERROR 42S02: Unknown SEQUENCE: 'test.t1' +DROP TABLE t1; +CREATE TABLE t (a INT); +CREATE SEQUENCE s; +LOCK TABLE t WRITE; +DROP SEQUENCE s; +ERROR HY000: Table 's' was not locked with LOCK TABLES +DROP TEMPORARY SEQUENCE s; +ERROR 42S02: Unknown SEQUENCE: 'test.s' +UNLOCK TABLES; +CREATE TEMPORARY SEQUENCE s; +LOCK TABLE t WRITE; +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +DROP SEQUENCE s; +create table s(a INT); +CREATE TEMPORARY TABLE s (f INT); +LOCK TABLE t WRITE; +DROP TEMPORARY TABLE s; +CREATE TEMPORARY TABLE s (f INT); +DROP TABLE s; +DROP TABLE s; +ERROR HY000: Table 's' was not locked with LOCK TABLES +UNLOCK TABLES; +DROP TABLE s; +CREATE VIEW v1 as SELECT * FROM t; +CREATE SEQUENCE s; +DROP SEQUENCE IF EXISTS v1; +Warnings: +Note 1965 'test.v1' is a view +DROP VIEW IF EXISTS s; +Warnings: +Warning 1347 'test.s' is not of type 'VIEW' +Note 4092 Unknown VIEW: 'test.s' +DROP VIEW v1; +DROP SEQUENCE s; +DROP TABLE t; +# +# End of 10.3 tests +# +# +# MDEV-32541 Assertion `!(thd->server_status & (1U | 8192U))' failed in MDL_context::release_transactional_locks +# +create sequence s1; +create sequence s2; +connect con1,localhost,root,,; +set session transaction read only; +start transaction; +connection default; +start transaction; +insert into s2 values (1, 1, 10000, 100, 1, 1000, 0, 0); +connection con1; +select lastval(s1); +lastval(s1) +NULL +select lastval(s2);; +connection default; +set lock_wait_timeout= 1; +insert into s1 values (1, 1, 10000, 100, 1, 1000, 0, 0); +connection con1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +disconnect con1; +connection default; +drop sequence s1; +drop sequence s2; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test new file mode 100644 index 00000000..9761e426 --- /dev/null +++ b/mysql-test/suite/sql_sequence/other.test @@ -0,0 +1,423 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# Test various combinations of operations on sequence +# + +--echo # +--echo # Create and check +--echo # + +--disable_ps2_protocol +create sequence s1 engine=innodb; +check table s1; +select next value for s1; +flush tables; +check table s1; +select next value for s1; +flush tables; +repair table s1; +select next value for s1; +drop sequence s1; + +create or replace sequence s1 engine=innodb; +select next value for s1; +repair table s1; +check table s1; +select next value for s1; +select * from s1; +drop sequence s1; +--enable_ps2_protocol + +--echo # +--echo # INSERT +--echo # + +create sequence s1; +create sequence s2; +--error ER_NO_DEFAULT_FOR_FIELD +insert into s1 (next_not_cached_value, minimum_value) values (100,1000); +--error ER_UPDATE_TABLE_USED +insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0); +--error ER_SEQUENCE_INVALID_DATA +insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0); +--error ER_SEQUENCE_INVALID_DATA +insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0); +select * from s1; +insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0); +select * from s1; +--disable_ps2_protocol +select next value for s1; +--enable_ps2_protocol +select * from s1; +--error ER_SEQUENCE_INVALID_DATA +insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0); + +select * from s1; +insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0); +select * from s1; +select * from s2; +insert into s1 select * from s2; +select * from s1; +drop sequence s1,s2; + +--echo # +--echo # UPDATE and DELETE +--echo # + +create sequence s1; +--error ER_ILLEGAL_HA +update s1 set next_not_cached_value=100; +--error ER_ILLEGAL_HA +delete from s1 where next_not_cached_value > 0; +drop sequence s1; + +--echo # +--echo # SHOW TABLES +--echo # + +create sequence s1; +create table t1 (a int); +create view v1 as select * from s1; +show full tables; +SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test" ORDER BY TABLE_NAME; +drop table t1,s1; +drop view v1; + +--echo # +--echo # LOCK TABLES (as in mysqldump) +--echo # + +create sequence s1 engine=innodb; +LOCK TABLES s1 READ; +SELECT * from s1; +UNLOCK TABLES; +LOCK TABLES s1 WRITE; +insert into s1 values (1,1,9223372036854775806, 1, 1, 1000, 0, 0); +UNLOCK TABLES; +drop table s1; + +--echo # +--echo # Many sequence calls with innodb +--echo # + +--disable_ps2_protocol +create sequence s1 cache=1000 engine=innodb; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +commit; +select * from s1; +drop sequence s1; + +create sequence s1 cache=1000 engine=innodb; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; + +connect (addconroot, localhost, root,,); +connection addconroot; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +select * from s1; +commit; +disconnect addconroot; +connection default; +select * from s1; +commit; +drop sequence s1; +--enable_ps2_protocol + +--echo # +--echo # Flush tables with read lock +--echo # + +--disable_ps2_protocol +create sequence s1; +select next value for s1; +flush tables with read lock; +--error 1223 +create sequence s2; +--error 1223 +select next value for s1; +unlock tables; +drop sequence s1; +--enable_ps2_protocol + +--echo # +--echo # MDEV-14761 +--echo # Assertion `!mysql_parse_status || thd->is_error() || +--echo # thd->get_internal_handler()' failed in parse_sql +--echo # + +CREATE SEQUENCE s1; +--error ER_DUP_ARGUMENT +ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE; +DROP SEQUENCE s1; + +--echo # +--echo # Don't allow SEQUENCE to be used with CHECK or virtual fields +--echo # + +CREATE SEQUENCE s1 nocache engine=myisam; +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE table t1 (a int check (next value for s1 > 0)); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE table t1 (a int check (previous value for s1 > 0)); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE table t1 (a int check (setval(s1,10))); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a int, b int as (next value for s1 > 0)); +drop sequence s1; + + +--echo # +--echo # MDEV-13024: Server crashes in my_store_ptr upon DELETE from +--echo # sequence in multi-table format +--echo # +CREATE SEQUENCE s; +CREATE table t1 (a int); +insert into t1 values (1),(2); +--error ER_ILLEGAL_HA +DELETE s FROM s; +--error ER_ILLEGAL_HA +delete t1,s from s,t1; +--error ER_ILLEGAL_HA +delete s,t1 from t1,s; +DROP SEQUENCE s; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20074: Lost connection on update trigger +--echo # + +--echo # INSERT & table +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO t2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop sequence s1; +drop table t1,t2; + + +--echo # INSERT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + + +--echo # INSERT SELECT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name; +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + + +--echo # REPLACE & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +REPLACE INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + + +--echo # REPLACE SELECT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +REPLACE INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name; +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + +--echo # +--echo # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or +--echo # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE, +--echo # table->db.str, table->table_name.str, MDL_SHARED)' failed +--echo # in mysql_rm_table_no_locks +--echo # + +CREATE TABLE t1 (a INT); +CREATE TEMPORARY TABLE tmp (b INT); +LOCK TABLE t1 READ; +--error ER_UNKNOWN_SEQUENCES +DROP SEQUENCE tmp; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE tmp; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +DROP SEQUENCE t1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1; +UNLOCK TABLES; +--error ER_NOT_SEQUENCE2 +DROP SEQUENCE t1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1; + +# Cleanup +DROP TABLE t1; + + +CREATE TABLE t (a INT); +CREATE SEQUENCE s; +LOCK TABLE t WRITE; +--error ER_TABLE_NOT_LOCKED +DROP SEQUENCE s; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +CREATE TEMPORARY SEQUENCE s; +LOCK TABLE t WRITE; +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +DROP SEQUENCE s; + +create table s(a INT); +CREATE TEMPORARY TABLE s (f INT); +LOCK TABLE t WRITE; +DROP TEMPORARY TABLE s; +CREATE TEMPORARY TABLE s (f INT); +DROP TABLE s; +--error ER_TABLE_NOT_LOCKED +DROP TABLE s; +UNLOCK TABLES; +DROP TABLE s; + +CREATE VIEW v1 as SELECT * FROM t; +CREATE SEQUENCE s; + +DROP SEQUENCE IF EXISTS v1; +DROP VIEW IF EXISTS s; + +DROP VIEW v1; +DROP SEQUENCE s; +DROP TABLE t; +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # +--echo # MDEV-32541 Assertion `!(thd->server_status & (1U | 8192U))' failed in MDL_context::release_transactional_locks +--echo # +create sequence s1; +create sequence s2; +--connect (con1,localhost,root,,) +set session transaction read only; +start transaction; + +--connection default +start transaction; +insert into s2 values (1, 1, 10000, 100, 1, 1000, 0, 0); + +--connection con1 +select lastval(s1); +--send select lastval(s2); + +--connection default +set lock_wait_timeout= 1; +insert into s1 values (1, 1, 10000, 100, 1, 1000, 0, 0); + +--connection con1 +--error ER_LOCK_DEADLOCK +--reap +--disconnect con1 +--connection default +drop sequence s1; +drop sequence s2; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/sql_sequence/partition.result b/mysql-test/suite/sql_sequence/partition.result new file mode 100644 index 00000000..223285ce --- /dev/null +++ b/mysql-test/suite/sql_sequence/partition.result @@ -0,0 +1,7 @@ +# +# MDEV-13715 ha_partition::engine_name() segfault fix +# +create sequence s; +alter table s partition by hash(start_value) partitions 2; +ERROR HY000: Table storage engine 'partition' does not support the create option 'SEQUENCE' +drop sequence s; diff --git a/mysql-test/suite/sql_sequence/partition.test b/mysql-test/suite/sql_sequence/partition.test new file mode 100644 index 00000000..d820b469 --- /dev/null +++ b/mysql-test/suite/sql_sequence/partition.test @@ -0,0 +1,11 @@ +--source include/have_partition.inc +--source include/have_sequence.inc + +--echo # +--echo # MDEV-13715 ha_partition::engine_name() segfault fix +--echo # + +create sequence s; +--error ER_ILLEGAL_HA_CREATE_OPTION +alter table s partition by hash(start_value) partitions 2; +drop sequence s; diff --git a/mysql-test/suite/sql_sequence/read_only.result b/mysql-test/suite/sql_sequence/read_only.result new file mode 100644 index 00000000..cd8d498b --- /dev/null +++ b/mysql-test/suite/sql_sequence/read_only.result @@ -0,0 +1,40 @@ +create sequence s1 cache 2 engine=innodb; +# restart: --innodb-read-only +connection default; +show global variables like 'innodb_read_only'; +Variable_name Value +innodb_read_only ON +use test; +set session binlog_format= row; +########################################### +read_only create error. +########################################### +show global variables like 'innodb_read_only'; +Variable_name Value +innodb_read_only ON +use test; +create sequence s2 cache 5 engine=innodb; +ERROR HY000: Can't create table `test`.`s2` (errno: 165 "Table is read only") +########################################### +read_only query error. +########################################### +select next value for s1; +ERROR HY000: Table 's1' is read only +select next value for s1; +ERROR HY000: Table 's1' is read only +select next value for s1; +ERROR HY000: Table 's1' is read only +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 2 0 0 +# restart +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 2 0 0 +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 +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/read_only.test b/mysql-test/suite/sql_sequence/read_only.test new file mode 100644 index 00000000..141a310c --- /dev/null +++ b/mysql-test/suite/sql_sequence/read_only.test @@ -0,0 +1,46 @@ +--source include/have_innodb.inc +--source include/not_embedded.inc + +# +# Test innodb read only +# + +create sequence s1 cache 2 engine=innodb; + +--let $restart_parameters= --innodb-read-only +--source include/restart_mysqld.inc + +connection default; +show global variables like 'innodb_read_only'; +use test; +set session binlog_format= row; + +--echo ########################################### +--echo read_only create error. +--echo ########################################### + +show global variables like 'innodb_read_only'; +use test; + +--error ER_CANT_CREATE_TABLE +create sequence s2 cache 5 engine=innodb; + +--echo ########################################### +--echo read_only query error. +--echo ########################################### +--error ER_OPEN_AS_READONLY +select next value for s1; +--error ER_OPEN_AS_READONLY +select next value for s1; +--error ER_OPEN_AS_READONLY +select next value for s1; + +select * from s1; +--let $restart_parameters= +--source include/restart_mysqld.inc +select * from s1; +--disable_ps2_protocol +select next value for s1; +--enable_ps2_protocol +select * from s1; +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/rebuild.result b/mysql-test/suite/sql_sequence/rebuild.result new file mode 100644 index 00000000..6348d177 --- /dev/null +++ b/mysql-test/suite/sql_sequence/rebuild.result @@ -0,0 +1,18 @@ +# +# MDEV-15977 Assertion `! thd->in_sub_stmt' failed in trans_commit_stmt +# +CREATE SEQUENCE s1 ENGINE=InnoDB; +ALTER TABLE s1 FORCE; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +CREATE TABLE t2 (b VARCHAR(64)) ENGINE=MyISAM; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test'; +INSERT INTO t1 VALUES (1); +select * from t1; +a +1 +select * from t2; +b +s1 +t1 +t2 +DROP TABLE t1, t2, s1; diff --git a/mysql-test/suite/sql_sequence/rebuild.test b/mysql-test/suite/sql_sequence/rebuild.test new file mode 100644 index 00000000..ae8a0667 --- /dev/null +++ b/mysql-test/suite/sql_sequence/rebuild.test @@ -0,0 +1,21 @@ +--source include/have_innodb.inc +--source include/have_perfschema.inc + +--echo # +--echo # MDEV-15977 Assertion `! thd->in_sub_stmt' failed in trans_commit_stmt +--echo # + +CREATE SEQUENCE s1 ENGINE=InnoDB; +ALTER TABLE s1 FORCE; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +CREATE TABLE t2 (b VARCHAR(64)) ENGINE=MyISAM; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test'; +INSERT INTO t1 VALUES (1); +--sorted_result +select * from t1; +--sorted_result +select * from t2; + +# Cleanup +DROP TABLE t1, t2, s1; + diff --git a/mysql-test/suite/sql_sequence/rename.result b/mysql-test/suite/sql_sequence/rename.result new file mode 100644 index 00000000..26f529b5 --- /dev/null +++ b/mysql-test/suite/sql_sequence/rename.result @@ -0,0 +1,6 @@ +CREATE SEQUENCE seq1; +RENAME TABLE seq1 TO seq2, seq3 TO seq4; +ERROR 42S02: Table 'test.seq3' doesn't exist +LOCK TABLE seq1 READ; +UNLOCK TABLES; +drop table seq1; diff --git a/mysql-test/suite/sql_sequence/rename.test b/mysql-test/suite/sql_sequence/rename.test new file mode 100644 index 00000000..232a1bda --- /dev/null +++ b/mysql-test/suite/sql_sequence/rename.test @@ -0,0 +1,6 @@ +CREATE SEQUENCE seq1; +--error ER_NO_SUCH_TABLE +RENAME TABLE seq1 TO seq2, seq3 TO seq4; +LOCK TABLE seq1 READ; +UNLOCK TABLES; +drop table seq1; diff --git a/mysql-test/suite/sql_sequence/replication-master.opt b/mysql-test/suite/sql_sequence/replication-master.opt new file mode 100644 index 00000000..bbea8eab --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication-master.opt @@ -0,0 +1 @@ +--binlog_format=row --query_cache_type=1 diff --git a/mysql-test/suite/sql_sequence/replication-slave.opt b/mysql-test/suite/sql_sequence/replication-slave.opt new file mode 100644 index 00000000..a4e068e4 --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication-slave.opt @@ -0,0 +1 @@ +--binlog_format=row --query_cache_type=1 --read_only=true diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result new file mode 100644 index 00000000..94b1c72b --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication.result @@ -0,0 +1,1094 @@ +include/master-slave.inc +[connection master] +connection master; +create database s_db; +use 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'; +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 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; +use s_db; +########################################### +master and slave sync sequence. +########################################### +connection master; +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; +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; +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 sequence +########################################### +connection master; +create table t_1(id int); +show create sequence t_1; +ERROR 42S02: 's_db.t_1' is not a SEQUENCE +drop table t_1; +CREATE SEQUENCE `s2` ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) ENGINE=InnoDB sequence=1; +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 '( +`next_not_cached_value` bigint(21) NOT NULL, +`minimum_value` bigint(21) NOT...' at line 1 +CREATE TABLE `s2` ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) ENGINE=InnoDB sequence=1; +insert into s2 values(1, 1, 10, 1, 2, 1, 1, 0); +commit; +select * for s2; +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 +select * from s2; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 10 1 2 1 1 0 +select NEXT VALUE for s2; +NEXT VALUE for s2 +1 +select NEXT VALUE for s2; +NEXT VALUE for s2 +3 +select NEXT VALUE for s2; +NEXT VALUE for s2 +5 +select NEXT VALUE for s2; +NEXT VALUE for s2 +7 +select NEXT VALUE for s2; +NEXT VALUE for s2 +9 +select NEXT VALUE for s2; +NEXT VALUE for s2 +1 +select NEXT VALUE for s2; +NEXT VALUE for s2 +3 +select * from s2; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 10 1 2 1 1 1 +commit; +connection master; +connection slave; +select * from s2; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 10 1 2 1 1 1 +connection master; +drop sequence s2; +CREATE TABLE `s2` ( +`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, +`increment` bigint(21) NOT NULL, +`cache_size` bigint(21) unsigned NOT NULL, +`cycle_option` tinyint(1) unsigned NOT NULL, +`cycle_count` bigint(21) NOT NULL +) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1; +show create sequence s2; +Table Create Table +s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +drop sequence s2; +########################################### +select sequence syntax test +########################################### +connection master; +create sequence s2; +create table t2 (id int); +select * from s2; +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 * from t2; +id +insert into t2 select next value for s2; +commit; +select NEXT VALUE for s2; +NEXT VALUE for s2 +2 +select NEXT VALUE for t2; +ERROR 42S02: 's_db.t2' is not a SEQUENCE +select * from s2, t2; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count id +1001 1 9223372036854775806 1 1 1000 0 0 1 +select * for s2; +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 +select * for s2, t2; +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 +connection master; +drop sequence s2; +drop table t2; +########################################### +support rename, not support truncate +########################################### +connection master; +create sequence s2; +alter table s2 rename to s2_1; +rename table s2_1 to s2_2; +show create sequence s2_2; +Table Create Table +s2_2 CREATE SEQUENCE `s2_2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from s2_2; +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 +truncate table s2_2; +ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s2_2` doesn't have this option +rename table s2_2 to s2; +drop sequence s2; +########################################### +all invalid sequence value +########################################### +connection master; +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; +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; +flush status; +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 NEXT VALUE for s_db.s1; +NEXT VALUE for s_db.s1 +1 +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; +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; +select * from t; +id +1111 +1 +2 +3 +4 +5 +connection m_normal_1; +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 * 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 +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; +create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle engine=innodb; +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 +1 1 20 1 1 5 1 0 +connection m_normal_1; +select next value for s_t; +next value for s_t +1 +connection master; +connection slave; +connection s_normal_3; +select next_not_cached_value from s_t; +next_not_cached_value +6 +------------------------------------------ +master ALTER SEQUENCE +------------------------------------------ +connection m_normal_1; +select next value for s_t; +next value for s_t +2 +alter sequence s_t restart= 11; +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 +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 next value for s_t; +next value for s_t +12 +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 +alter sequence s_t minvalue=11 maxvalue=9; +ERROR HY000: Sequence 's_db.s_t' has out of range value for options +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 +alter sequence s_t restart= 12 start=10 minvalue=11 maxvalue=20; +ERROR HY000: Sequence 's_db.s_t' has out of range value for options +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 +------------------------------------------ +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 +13 +connection m_normal_1; +drop sequence s_t; +########################################### +test transaction context (innodb) +########################################### +------------------------------------------ +transaction table and sequence +normal transaction commit +------------------------------------------ +connection m_normal_1; +create sequence s_1 cache 5 engine=innodb; +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 +select * from s_1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +6 1 9223372036854775806 1 1 5 0 0 +connection master; +connection slave; +connection s_normal_3; +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 * from s_1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 1 1 5 0 0 +select next value for s_1; +next value for s_1 +11 +connection master; +connection slave; +connection s_normal_3; +select * from t_1; +id +1111 +1 +2 +2222 +connection m_normal_1; +drop sequence s_1; +drop table t_1; +########################################### +test transaction context (myisam) +########################################### +------------------------------------------ +transaction table and sequence +normal transaction commit +------------------------------------------ +connection m_normal_1; +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; +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; +select * from t_1; +id +1111 +1 +2 +2222 +3333 +3 +4 +5 +6 +7 +8 +9 +10 +connection m_normal_1; +drop sequence s_1; +drop table t_1; +########################################### +close binlog +########################################### +connection m_normal_1; +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; +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; +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; +select next value for s1; +next value for s1 +9 +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 1 1 2 0 0 +connection slave; +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +11 1 9223372036854775806 1 1 2 0 0 +connection master; +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 +connection master; +connection slave; +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; +drop sequence s1; +------------------------------------------ +set binlog_format=mixed +------------------------------------------ +connection master; +set session binlog_format=mixed; +select @@session.binlog_format; +@@session.binlog_format +MIXED +create sequence s1 cache 2; +select next value for s1; +next value for s1 +1 +set session binlog_format=row; +select next value for s1; +next value for s1 +2 +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; +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; +drop sequence s1; +connection master; +connection slave; +########################################### +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; +connection master; +connection slave; +########################################### +create as +########################################### +connection m_normal_1; +create sequence s1 cache 2; +create table t as select * from s1; +select * from t; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 9223372036854775806 1 1 2 0 0 +drop table t; +create table t as select next value for s1; +select * from t; +next value for s1 +1 +drop table t; +drop sequence s1; +connection master; +connection slave; +########################################### +test proc +########################################### +connection m_normal_1; +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; +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; +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; +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 value boundary +########################################### +connection m_normal_1; +------------------------------------------ +cycle_count increment by cycle_count +------------------------------------------ +create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle; +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 +2 +select next value for s1; +next value for s1 +3 +drop sequence s1; +create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle; +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; +ERROR HY000: Sequence 's_db.s1' has run out +select * from s1; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +8 2 7 5 1 10 0 0 +drop sequence s1; +create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle; +select next value for s1; +next value for s1 +2 +select next_not_cached_value,cycle_count from s1; +next_not_cached_value cycle_count +4 0 +select next value for s1; +next value for s1 +1 +select next_not_cached_value,cycle_count from s1; +next_not_cached_value cycle_count +4 1 +select next value for s1; +next value for s1 +1 +select next_not_cached_value,cycle_count from s1; +next_not_cached_value cycle_count +4 2 +select next value for s1; +next value for s1 +1 +select next_not_cached_value,cycle_count from s1; +next_not_cached_value cycle_count +4 3 +select next value for s1; +next value for s1 +1 +select next_not_cached_value,cycle_count from s1; +next_not_cached_value cycle_count +4 4 +drop sequence s1; +create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle; +select next value for s1; +next value for s1 +2 +select next value for s1; +ERROR HY000: Sequence 's_db.s1' has run out +drop sequence s1; +------------------------------------------ +beyond ulonglong maxvalue +------------------------------------------ +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 1 cycle; +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 0 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775806 0 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775804 0 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 1 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775806 1 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775804 1 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 2 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775806 2 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775804 2 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 3 +drop sequence s1; +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 10 cycle; +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 0 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775806 0 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775804 0 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 1 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775806 1 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775804 1 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 2 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775806 2 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775804 2 +select next value for s1, cycle_count from s1; +next value for s1 cycle_count +9223372036854775805 3 +drop sequence s1; +########################################### +test default() +########################################### +connection master; +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +insert into t1 (b) values (1),(2); +select default(a) from t1; +default(a) +3 +4 +select * from t1; +a b +1 1 +2 2 +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 0 0 0 +connection slave; +connection s_normal_3; +select * from t1; +a b +1 1 +2 2 +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 0 0 0 +connection master; +drop table t1,s1; +connection master; +drop database s_db; +drop user normal_1@'%'; +drop user normal_2@'%'; +drop user normal_3@'%'; +drop user normal_4@'%'; +include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/replication.test b/mysql-test/suite/sql_sequence/replication.test new file mode 100644 index 00000000..827cda56 --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication.test @@ -0,0 +1,885 @@ +# +# This test is originally sequence.test from ALISQL by Jianwei modified for +# MariaDB +# +# It tests basic sequence functionallity together with replication +# + +--source include/have_binlog_format_row.inc +--source include/master-slave.inc +--source include/have_innodb.inc + +--disable_ps2_protocol +connection master; +create database s_db; +use 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'; + +--sync_slave_with_master + +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(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; +use s_db; + +--echo ########################################### +--echo master and slave sync sequence. +--echo ########################################### +connection master; + +create sequence s1; +show create table s1; + +--sync_slave_with_master + +show create table s1; + +connection master; + +drop sequence s1; + +--echo ########################################### +--echo not support create table engine=sequence. +--echo ########################################### +connection master; + +--error ER_UNKNOWN_STORAGE_ENGINE +create table t(id int)engine=sequence; + +create table t(id int)engine=innodb; + +--error ER_UNKNOWN_STORAGE_ENGINE +alter table t engine=sequence; + +drop table t; +--echo ########################################### +--echo not support alter sequence table. +--echo ########################################### +connection master; + +create sequence s2; + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +alter table s2 add id int; + +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +alter table s2 add index ind_x(start_value); +drop sequence s2; + +--echo ########################################### +--echo support create sequence +--echo ########################################### +connection master; + +create table t_1(id int); +--error ER_NOT_SEQUENCE +show create sequence t_1; + +drop table t_1; + +--error ER_PARSE_ERROR +CREATE SEQUENCE `s2` ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) ENGINE=InnoDB sequence=1; + +CREATE TABLE `s2` ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) ENGINE=InnoDB sequence=1; + +insert into s2 values(1, 1, 10, 1, 2, 1, 1, 0); +commit; +--error ER_PARSE_ERROR +select * for s2; +select * from s2; +select NEXT VALUE for s2; +select NEXT VALUE for s2; +select NEXT VALUE for s2; +select NEXT VALUE for s2; +select NEXT VALUE for s2; +select NEXT VALUE for s2; +select NEXT VALUE for s2; +select * from s2; +commit; + +connection master; +--sync_slave_with_master +select * from s2; + +connection master; +drop sequence s2; + +CREATE TABLE `s2` ( + `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, + `increment` bigint(21) NOT NULL, + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL, + `cycle_count` bigint(21) NOT NULL +) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1; + +show create sequence s2; +drop sequence s2; + +--echo ########################################### +--echo select sequence syntax test +--echo ########################################### +connection master; +create sequence s2; +create table t2 (id int); + +select * from s2; +select * from t2; +insert into t2 select next value for s2; +commit; + +select NEXT VALUE for s2; +--error ER_NOT_SEQUENCE +select NEXT VALUE for t2; + +select * from s2, t2; + +--error ER_PARSE_ERROR +select * for s2; +--error ER_PARSE_ERROR +select * for s2, t2; + +connection master; +drop sequence s2; +drop table t2; + +--echo ########################################### +--echo support rename, not support truncate +--echo ########################################### +connection master; + +create sequence s2; + +alter table s2 rename to s2_1; +rename table s2_1 to s2_2; +show create sequence s2_2; +select * from s2_2; + +--error ER_ILLEGAL_HA +truncate table s2_2; +rename table s2_2 to s2; +drop sequence s2; + +--echo ########################################### +--echo all invalid sequence value +--echo ########################################### + +connection master; +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; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 100000 + increment by 1 + nocache + nocycle; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 5 + increment by 1 + nocache + nocycle; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 4 + increment by 1 + nocache + nocycle; + +--error ER_SEQUENCE_INVALID_DATA +create sequence s2 start with 1 + minvalue 5 + maxvalue 4 + increment by 0 + nocache + nocycle; + +--echo ########################################### +--echo global read lock prevent query sequence +--echo ########################################### +connection master; +create sequence s_db.s1; +flush table with read lock; +--error ER_CANT_UPDATE_WITH_READLOCK +select NEXT VALUE for s1; +unlock tables; +drop sequence s_db.s1; + +--echo ########################################### +--echo query cache test +--echo ########################################### +connection master; +flush status; +show global variables like 'query_cache_type'; + +show status like 'Qcache_hits'; +show status like 'Qcache_inserts'; + +--echo ########################################### +--echo priv test +--echo ########################################### +connection m_normal_1; +create sequence s_db.s1; +select NEXT VALUE for s_db.s1; +create sequence s_db.s2; +drop sequence s_db.s2; + + +connection m_normal_2; +--error ER_TABLEACCESS_DENIED_ERROR +select NEXT VALUE for s_db.s1; +--error ER_TABLEACCESS_DENIED_ERROR +create sequence s_db.s2; + +connection m_normal_1; +drop sequence s_db.s1; + +--echo ########################################### +--echo run out sequence value +--echo ########################################### +connection m_normal_1; +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; +--error ER_SEQUENCE_RUN_OUT +insert into t select next value for s_t; +--error ER_SEQUENCE_RUN_OUT +insert into t select next value for s_t; +commit; +select * from t; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from t; + +connection m_normal_1; +drop sequence s_t; +drop table t; + +--echo ########################################### +--echo read_only prevent query sequence +--echo ########################################### +connection m_normal_1; +create sequence s_db.s1; +show global variables like 'read_only'; +select * from s_db.s1; + +connection master; +--sync_slave_with_master +connection s_normal_3; + +show global variables like 'read_only'; +--error ER_OPTION_PREVENTS_STATEMENT +select next value for s_db.s1; + +connection m_normal_1; +drop sequence s_db.s1; + +--echo ########################################### +--echo update based table +--echo ########################################### +connection m_normal_1; +create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle engine=innodb; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from s_t; + + +connection m_normal_1; +select next value for s_t; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select next_not_cached_value from s_t; + +--echo ------------------------------------------ +--echo master ALTER SEQUENCE +--echo ------------------------------------------ +connection m_normal_1; +select next value for s_t; +alter sequence s_t restart= 11; + +select * from s_t; + +connection master; +--sync_slave_with_master + +--echo ------------------------------------------ +--echo show slave nextval; +--echo ------------------------------------------ +connection s_normal_3; +select * from s_t; + +connection m_normal_1; +select next value for s_t; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from s_t; + + +--echo ------------------------------------------ +--echo update into invalid sequence +--echo ------------------------------------------ +connection m_normal_1; +select next value for s_t; +select * from s_t; +--error ER_SEQUENCE_INVALID_DATA +alter sequence s_t minvalue=11 maxvalue=9; +select * from s_t; +--error ER_SEQUENCE_INVALID_DATA +alter sequence s_t restart= 12 start=10 minvalue=11 maxvalue=20; +select * from s_t; + +--echo ------------------------------------------ +--echo delete sequence row +--echo ------------------------------------------ +connection m_normal_1; +--error ER_ILLEGAL_HA +delete from s_t; +commit; + +select next value for s_t; + +connection m_normal_1; +drop sequence s_t; + +--echo ########################################### +--echo test transaction context (innodb) +--echo ########################################### + +--echo ------------------------------------------ +--echo transaction table and sequence +--echo normal transaction commit +--echo ------------------------------------------ +connection m_normal_1; +create sequence s_1 cache 5 engine=innodb; + +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; +select * from s_1; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from t_1; + +--echo ------------------------------------------ +--echo normal transaction rollback +--echo ------------------------------------------ +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; +rollback; + +select * from t_1; +select * from s_1; +select next value for s_1; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from t_1; + +connection m_normal_1; +drop sequence s_1; +drop table t_1; + +--echo ########################################### +--echo test transaction context (myisam) +--echo ########################################### + +--echo ------------------------------------------ +--echo transaction table and sequence +--echo normal transaction commit +--echo ------------------------------------------ +connection m_normal_1; +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; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from t_1; + +--echo ------------------------------------------ +--echo normal transaction rollback +--echo ------------------------------------------ +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; +rollback; + +select * from t_1; +select next value for s_1; + +connection master; +--sync_slave_with_master + +connection s_normal_3; +select * from t_1; + +connection m_normal_1; +drop sequence s_1; +drop table t_1; + +--echo ########################################### +--echo close binlog +--echo ########################################### +connection m_normal_1; +create sequence s1 cache 2; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; + +commit; +select * from s1; + +connection master; +--sync_slave_with_master + +select * from s1; + +--echo ------------------------------------------ +--echo close session binlog. +--echo ------------------------------------------ +connection master; +set session sql_log_bin=off; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; + +set session sql_log_bin=on; +select * from s1; + +connection master; +--sync_slave_with_master + +select * from s1; + +connection master; +select next value for s1; +select * from s1; +--sync_slave_with_master + +select * from s1; +connection master; + +drop sequence s1; + +--echo ########################################### +--echo statement binlog +--echo ########################################### +--echo ------------------------------------------ +--echo set binlog_format=statement +--echo ------------------------------------------ +connection master; +set session binlog_format=statement; +select @@session.binlog_format; +create sequence s1 cache 2; +--error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE +select next value for s1; + +set session binlog_format=row; +select next value for s1; + +connection master; +--sync_slave_with_master + +select * from s1; + +connection m_normal_1; +drop sequence s1; + +--echo ------------------------------------------ +--echo set binlog_format=mixed +--echo ------------------------------------------ +connection master; +set session binlog_format=mixed; +select @@session.binlog_format; +create sequence s1 cache 2; +select next value for s1; + +set session binlog_format=row; +select next value for s1; +select * from s1; + +connection master; +--sync_slave_with_master + +select * from s1; + +connection m_normal_1; +drop sequence s1; +connection master; +--sync_slave_with_master + +--echo ########################################### +--echo test savepoint +--echo ########################################### + +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; +rollback to sp1; +select * from t1; +select next value for s1; + +commit; + +drop sequence s1; +drop table t1; + +connection master; +--sync_slave_with_master + +--echo ########################################### +--echo create as +--echo ########################################### +connection m_normal_1; + +create sequence s1 cache 2; +create table t as select * from s1; +select * from t; +drop table t; +create table t as select next value for s1; +select * from t; +drop table t; + +drop sequence s1; + +connection master; +--sync_slave_with_master + +--echo ########################################### +--echo test proc +--echo ########################################### +connection m_normal_1; +create table t(id int)engine=innodb; + +delimiter //; + +create procedure p1() +begin + create sequence s1 cache 2; +end// + +create procedure p2() +begin + insert into t select next value for s1; + commit; +end// + +delimiter ;// + +call p1(); +call p2(); +call p2(); +call p2(); +call p2(); + +select * from t; + +connection master; +--sync_slave_with_master + +select * from t; + +connection m_normal_1; +drop table t; +drop sequence s1; +drop procedure p1; +drop procedure p2; + +--echo ########################################### +--echo test trigger +--echo ########################################### +connection m_normal_1; +create sequence s1 cache 2; +create table t1(id int)engine=innodb; +create table t2(id int)engine=innodb; + +delimiter //; +CREATE TRIGGER tri_1 + before INSERT ON t2 FOR EACH ROW +BEGIN + INSERT INTO t1 select next value for s1; +END// +delimiter ;// + +begin; +insert into t2 values(1111); +insert into t2 values(1111); +insert into t2 values(1111); +insert into t2 values(1111); + +select * from t2; +select * from t1; +rollback; +select * from t2; +select * from t1; + +select next value for s1; + + +drop trigger tri_1; +drop table t1; +drop table t2; +drop sequence s1; + +--echo ########################################### +--echo test function +--echo ########################################### +connection m_normal_1; +create sequence s1 cache 2; +create table t1(id int)engine=innodb; + +delimiter //; +CREATE function f1() returns int +BEGIN + INSERT INTO t1 select next value for s1; + return (1); +END// +delimiter ;// + +begin; +select f1(); +select f1(); +select f1(); +select f1(); + +select * from t1; +rollback; +select * from t1; + +select next value for s1; + +drop function f1; +drop table t1; +drop sequence s1; + +--echo ########################################### +--echo test value boundary +--echo ########################################### +connection m_normal_1; + +--echo ------------------------------------------ +--echo cycle_count increment by cycle_count +--echo ------------------------------------------ +create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; +select next value for s1; +drop sequence s1; + +create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle; +select next value for s1; +select next value for s1; +select next value for s1; +--error ER_SEQUENCE_RUN_OUT +select next value for s1; +select * from s1; +drop sequence s1; + +create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle; +select next value for s1; +select next_not_cached_value,cycle_count from s1; +select next value for s1; +select next_not_cached_value,cycle_count from s1; +select next value for s1; +select next_not_cached_value,cycle_count from s1; +select next value for s1; +select next_not_cached_value,cycle_count from s1; +select next value for s1; +select next_not_cached_value,cycle_count from s1; +drop sequence s1; + +create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle; +select next value for s1; +--error ER_SEQUENCE_RUN_OUT +select next value for s1; +drop sequence s1; + +--echo ------------------------------------------ +--echo beyond ulonglong maxvalue +--echo ------------------------------------------ +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 1 cycle; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +drop sequence s1; + +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 10 cycle; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +select next value for s1, cycle_count from s1; +drop sequence s1; + +--echo ########################################### +--echo test default() +--echo ########################################### +connection master; + +CREATE SEQUENCE s1 nocache engine=myisam; +CREATE table t1 (a int default next value for s1, b int); +insert into t1 (b) values (1),(2); +select default(a) from t1; +select * from t1; +select * from s1; +--sync_slave_with_master +connection s_normal_3; +select * from t1; +select * from s1; +connection master; +drop table t1,s1; +--enable_ps2_protocol + +# +# Cleanup +# + +connection master; +drop database s_db; +drop user normal_1@'%'; +drop user normal_2@'%'; +drop user normal_3@'%'; +drop user normal_4@'%'; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/replication_drop.result b/mysql-test/suite/sql_sequence/replication_drop.result new file mode 100644 index 00000000..1cd70227 --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication_drop.result @@ -0,0 +1,5 @@ +CREATE SEQUENCE seq ENGINE=InnoDB; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +INSERT INTO seq VALUES (1,1,100,1,1,1,1,1); +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. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. +DROP SEQUENCE seq; diff --git a/mysql-test/suite/sql_sequence/replication_drop.test b/mysql-test/suite/sql_sequence/replication_drop.test new file mode 100644 index 00000000..ca050246 --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication_drop.test @@ -0,0 +1,17 @@ +# +# Test for MDEV-15812 +# Assertion `m_lock_type == 2' failed in +# handler::~handler on dropping a sequence after +# ER_BINLOG_STMT_MODE_AND_ROW_ENGINE +# + +--source include/have_innodb.inc +--source include/have_binlog_format_statement.inc + +CREATE SEQUENCE seq ENGINE=InnoDB; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +--error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE +INSERT INTO seq VALUES (1,1,100,1,1,1,1,1); + +# Cleanup +DROP SEQUENCE seq; diff --git a/mysql-test/suite/sql_sequence/replication_mixed.result b/mysql-test/suite/sql_sequence/replication_mixed.result new file mode 100644 index 00000000..f581d59f --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication_mixed.result @@ -0,0 +1,35 @@ +include/master-slave.inc +[connection master] +# +# MDEV-16234 +# CREATE TABLE .. SELECT LASTVAL is written to binlog as single +# statement, causes discrepancy between master and slave +# +CREATE SEQUENCE s1 ENGINE=InnoDB; +SELECT NEXTVAL(s1); +NEXTVAL(s1) +1 +CREATE TABLE t1 ENGINE=InnoDB SELECT LASTVAL(s1) AS a; +INSERT INTO t1 VALUES (NEXTVAL(s1)); +INSERT INTO t1 VALUES (LASTVAL(s1)); +SELECT * FROM t1; +a +1 +2 +2 +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 +connection slave; +SELECT * FROM t1; +a +1 +2 +2 +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 +connection master; +DROP TABLE t1; +DROP SEQUENCE s1; +include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/replication_mixed.test b/mysql-test/suite/sql_sequence/replication_mixed.test new file mode 100644 index 00000000..4796c848 --- /dev/null +++ b/mysql-test/suite/sql_sequence/replication_mixed.test @@ -0,0 +1,29 @@ +--source include/have_innodb.inc +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +--echo # +--echo # MDEV-16234 +--echo # CREATE TABLE .. SELECT LASTVAL is written to binlog as single +--echo # statement, causes discrepancy between master and slave +--echo # + +CREATE SEQUENCE s1 ENGINE=InnoDB; +--disable_ps2_protocol +SELECT NEXTVAL(s1); +--enable_ps2_protocol +CREATE TABLE t1 ENGINE=InnoDB SELECT LASTVAL(s1) AS a; +INSERT INTO t1 VALUES (NEXTVAL(s1)); +INSERT INTO t1 VALUES (LASTVAL(s1)); +SELECT * FROM t1; +SELECT * from s1; +--sync_slave_with_master +SELECT * FROM t1; +SELECT * from s1; + +# Cleanup +--connection master +DROP TABLE t1; +DROP SEQUENCE s1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result new file mode 100644 index 00000000..504d460f --- /dev/null +++ b/mysql-test/suite/sql_sequence/setval.result @@ -0,0 +1,271 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Test setval function +# +CREATE SEQUENCE t1 cache 10 engine=myisam; +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +1 0 +do setval(t1,10); +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +11 0 +select next value for t1; +next value for t1 +11 +do setval(t1,12,1); +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +21 0 +select next value for t1; +next value for t1 +13 +do setval(t1,15,0); +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +21 0 +select next value for t1; +next value for t1 +15 +select setval(t1,16,0); +setval(t1,16,0) +16 +select next value for t1; +next value for t1 +16 +do setval(t1,1000,0); +select next value for t1; +next value for t1 +1000 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +1010 0 +do setval(t1,2000,0); +select next value for t1; +next value for t1 +2000 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +2010 0 +select setval(t1,1000,0); +setval(t1,1000,0) +NULL +select next value for t1; +next value for t1 +2001 +select setval(t1,1000,TRUE); +setval(t1,1000,TRUE) +NULL +select next value for t1; +next value for t1 +2002 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +2010 0 +select setval(t1,2002,0); +setval(t1,2002,0) +NULL +select next value for t1; +next value for t1 +2003 +select setval(t1,2010,0); +setval(t1,2010,0) +2010 +select next value for t1; +next value for t1 +2010 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +2020 0 +drop sequence t1; +# +# Testing with cycle +# +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +1 0 +select setval(t1,100,0); +setval(t1,100,0) +100 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +100 0 +select next value for t1; +next value for t1 +100 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +101 0 +select setval(t1,100,0); +setval(t1,100,0) +NULL +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +101 0 +select next value for t1; +next value for t1 +1 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +11 1 +select next value for t1; +next value for t1 +2 +select setval(t1,100,0,1); +setval(t1,100,0,1) +100 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +100 1 +select next value for t1; +next value for t1 +100 +select setval(t1,100,1,2); +setval(t1,100,1,2) +100 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +101 2 +select next value for t1; +next value for t1 +1 +select setval(t1,100,0,3); +setval(t1,100,0,3) +100 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +100 3 +select next value for t1; +next value for t1 +100 +drop sequence t1; +# +# Testing extreme values +# +CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb; +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +1 0 +select setval(t1,200); +setval(t1,200) +200 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +101 0 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +1 0 +select setval(t1,200); +setval(t1,200) +200 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +101 0 +select next value for t1; +next value for t1 +1 +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10); +setval(t1,-10) +-10 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +-20 0 +select next value for t1; +next value for t1 +-20 +select setval(t1,-15); +setval(t1,-15) +NULL +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +-120 0 +select next value for t1; +next value for t1 +-30 +select setval(t1,-500,FALSE); +setval(t1,-500,FALSE) +-500 +select next value for t1; +next value for t1 +-500 +select next value for t1; +next value for t1 +-510 +select setval(t1,-525,0); +setval(t1,-525,0) +-525 +select next value for t1; +next value for t1 +-525 +select next value for t1; +next value for t1 +-535 +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10,0); +setval(t1,-10,0) +-10 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +-10 0 +select next value for t1; +next value for t1 +-10 +drop sequence t1; +# +# Other testing +# +CREATE SEQUENCE t1; +select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1); +setval(t1,10,0) setval(t1,15,1) setval(t1,5,1) +10 15 NULL +select next value for t1; +next value for t1 +16 +select next_not_cached_value,cycle_count from t1; +next_not_cached_value cycle_count +1016 0 +explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select setval(`test`.`t1`,100,1,0) AS `setval(t1,100)`,setval(`test`.`t1`,100,1,0) AS `setval(t1,100,TRUE)`,setval(`test`.`t1`,100,0,50) AS `setval(t1,100,FALSE,50)` +drop sequence t1; +create table t1 (a int); +select setval(t1,10); +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +CREATE SEQUENCE s1; +SELECT SETVAL(s1,10); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SETVAL(s1,10) 8 20 2 Y 32896 0 63 +SETVAL(s1,10) +10 +DROP SEQUENCE s1; +# +# MDEV-15732: Assertion `next_free_value % real_increment == offset && +# next_free_value >= reserved_until' failed in +# sequence_definition::adjust_values upon SETVAL for sequence with +# INCREMENT 0 +# +CREATE SEQUENCE s INCREMENT 0; +SELECT NEXTVAL(s); +NEXTVAL(s) +1 +SELECT SETVAL(s, 10); +SETVAL(s, 10) +10 +DROP SEQUENCE s; +# End of 10.3 tests diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test new file mode 100644 index 00000000..712e7a77 --- /dev/null +++ b/mysql-test/suite/sql_sequence/setval.test @@ -0,0 +1,157 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test setval function +--echo # + +--disable_ps2_protocol +CREATE SEQUENCE t1 cache 10 engine=myisam; +select next_not_cached_value,cycle_count from t1; +do setval(t1,10); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +do setval(t1,12,1); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +do setval(t1,15,0); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select setval(t1,16,0); +select next value for t1; +do setval(t1,1000,0); +select next value for t1; +select next_not_cached_value,cycle_count from t1; +do setval(t1,2000,0); +select next value for t1; +select next_not_cached_value,cycle_count from t1; +# Set smaller value +select setval(t1,1000,0); +select next value for t1; +select setval(t1,1000,TRUE); +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select setval(t1,2002,0); +select next value for t1; +select setval(t1,2010,0); +select next value for t1; +select next_not_cached_value,cycle_count from t1; +drop sequence t1; + +--echo # +--echo # Testing with cycle +--echo # + +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_not_cached_value,cycle_count from t1; +select setval(t1,100,0); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select setval(t1,100,0); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select setval(t1,100,0,1); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select setval(t1,100,1,2); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select setval(t1,100,0,3); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # Testing extreme values +--echo # + +CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb; +select next_not_cached_value,cycle_count from t1; +select setval(t1,200); +select next_not_cached_value,cycle_count from t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_not_cached_value,cycle_count from t1; +select setval(t1,200); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select setval(t1,-15); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +select setval(t1,-500,FALSE); +select next value for t1; +select next value for t1; +select setval(t1,-525,0); +select next value for t1; +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10,0); +select next_not_cached_value,cycle_count from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # Other testing +--echo # + +CREATE SEQUENCE t1; +select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1); +select next value for t1; +select next_not_cached_value,cycle_count from t1; +explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50); +drop sequence t1; + +# +# Some error testing +# + +create table t1 (a int); +--error ER_NOT_SEQUENCE +select setval(t1,10); +drop table t1; + + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +CREATE SEQUENCE s1; +SELECT SETVAL(s1,10); +DROP SEQUENCE s1; +--enable_ps_protocol +--disable_metadata + +--echo # +--echo # MDEV-15732: Assertion `next_free_value % real_increment == offset && +--echo # next_free_value >= reserved_until' failed in +--echo # sequence_definition::adjust_values upon SETVAL for sequence with +--echo # INCREMENT 0 +--echo # + +CREATE SEQUENCE s INCREMENT 0; +SELECT NEXTVAL(s); +SELECT SETVAL(s, 10); + +# Cleanup +DROP SEQUENCE s; +--enable_ps2_protocol + +--echo # End of 10.3 tests diff --git a/mysql-test/suite/sql_sequence/slave_nextval.result b/mysql-test/suite/sql_sequence/slave_nextval.result new file mode 100644 index 00000000..bfbc472e --- /dev/null +++ b/mysql-test/suite/sql_sequence/slave_nextval.result @@ -0,0 +1,108 @@ +include/master-slave.inc +[connection master] +CREATE SEQUENCE s; +INSERT INTO s VALUES (1,1,4,1,1,1,0,0); +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 4 increment by 1 cache 1 nocycle ENGINE=MyISAM +SELECT NEXTVAL(s); +NEXTVAL(s) +1 +connection slave; +SELECT NEXTVAL(s); +NEXTVAL(s) +2 +SELECT NEXTVAL(s); +NEXTVAL(s) +3 +connection master; +SELECT NEXTVAL(s); +NEXTVAL(s) +2 +SELECT NEXTVAL(s); +NEXTVAL(s) +3 +SELECT NEXTVAL(s); +NEXTVAL(s) +4 +select * from s; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 4 1 1 1 0 0 +connection slave; +select * from s; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +5 1 4 1 1 1 0 0 +connection master; +DROP SEQUENCE s; +CREATE SEQUENCE s; +INSERT INTO s VALUES (1,1,3,1,1,1,1,0); +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 3 increment by 1 cache 1 cycle ENGINE=MyISAM +SELECT NEXTVAL(s); +NEXTVAL(s) +1 +connection slave; +SELECT NEXTVAL(s); +NEXTVAL(s) +2 +SELECT NEXTVAL(s); +NEXTVAL(s) +3 +connection master; +SELECT NEXTVAL(s); +NEXTVAL(s) +2 +SELECT NEXTVAL(s); +NEXTVAL(s) +3 +SELECT NEXTVAL(s); +NEXTVAL(s) +1 +select * from s; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2 1 3 1 1 1 1 1 +connection slave; +select * from s; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +2 1 3 1 1 1 1 1 +connection master; +DROP SEQUENCE s; +CREATE SEQUENCE s; +INSERT INTO s VALUES (1,1,3,1,1,1,1,0); +SELECT NEXTVAL(s); +NEXTVAL(s) +1 +CREATE PROCEDURE pr(n INT) +BEGIN +DECLARE i INT DEFAULT 0; +WHILE i < n +DO +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); +SET i= i+1; +END WHILE; +END $ +connect con1,localhost,root,,; +CALL pr(100); +connect con2,localhost,root,,; +CALL pr(100); +connect con3,localhost,root,,; +CALL pr(100); +connect con4,localhost,root,,; +CALL pr(100); +connect con5,localhost,root,,; +CALL pr(100); +connect con6,localhost,root,,; +CALL pr(100); +connect con7,localhost,root,,; +CALL pr(100); +connect con8,localhost,root,,; +CALL pr(100); +connection master; +connection slave; +connection master; +DROP SEQUENCE s; +DROP PROCEDURE pr; +include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/slave_nextval.test b/mysql-test/suite/sql_sequence/slave_nextval.test new file mode 100644 index 00000000..41aca150 --- /dev/null +++ b/mysql-test/suite/sql_sequence/slave_nextval.test @@ -0,0 +1,134 @@ +--source include/master-slave.inc +--source include/have_binlog_format_row.inc + +# +# MDEV-14092 NEXTVAL() fails on slave +# + +--disable_ps2_protocol +CREATE SEQUENCE s; +INSERT INTO s VALUES (1,1,4,1,1,1,0,0); +show create sequence s; +SELECT NEXTVAL(s); + +--sync_slave_with_master +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); + +--connection master +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); + +select * from s; + +--sync_slave_with_master + +select * from s; +--connection master +DROP SEQUENCE s; + +# +# Same as above, but with cycles +# + +CREATE SEQUENCE s; +INSERT INTO s VALUES (1,1,3,1,1,1,1,0); +show create sequence s; +SELECT NEXTVAL(s); + +--sync_slave_with_master +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); + +--connection master +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); + +select * from s; + +--sync_slave_with_master + +select * from s; + +--connection master +DROP SEQUENCE s; + +# Here is a bit more complicated concurrent scenario that +# causes the same effect without any updates on the slave. You might +# need to replace 100 with a bigger value if it doesn't happen on your +# machine right away. + +CREATE SEQUENCE s; +INSERT INTO s VALUES (1,1,3,1,1,1,1,0); +SELECT NEXTVAL(s); + +--delimiter $ +CREATE PROCEDURE pr(n INT) +BEGIN +DECLARE i INT DEFAULT 0; +WHILE i < n +DO +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); +SELECT NEXTVAL(s); +SET i= i+1; +END WHILE; +END $ +--delimiter ; + +--connect (con1,localhost,root,,) +--send CALL pr(100) +--connect (con2,localhost,root,,) +--send CALL pr(100) +--connect (con3,localhost,root,,) +--send CALL pr(100) +--connect (con4,localhost,root,,) +--send CALL pr(100) +--connect (con5,localhost,root,,) +--send CALL pr(100) +--connect (con6,localhost,root,,) +--send CALL pr(100) +--connect (con7,localhost,root,,) +--send CALL pr(100) +--connect (con8,localhost,root,,) +--send CALL pr(100) + + +--disable_query_log +--disable_result_log + +--connection con1 +--reap +--connection con2 +--reap +--connection con3 +--reap +--connection con4 +--reap +--connection con5 +--reap +--connection con6 +--reap +--connection con7 +--reap +--connection con8 +--reap + +--enable_query_log +--enable_result_log + +--connection master + +--sync_slave_with_master + +--connection master +DROP SEQUENCE s; +DROP PROCEDURE pr; +--enable_ps2_protocol + +# +# Cleanup +# +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/temporary.result b/mysql-test/suite/sql_sequence/temporary.result new file mode 100644 index 00000000..b5c70fd3 --- /dev/null +++ b/mysql-test/suite/sql_sequence/temporary.result @@ -0,0 +1,43 @@ +# +# Create +# +create temporary sequence s1 engine=innodb; +alter table s1 engine myisam; +select nextval(s1); +nextval(s1) +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 +drop temporary sequence s1; +# +# MDEV-14762 Server crashes in MDL_ticket::has_stronger_or_equal_type +# upon inserting into temporary sequence +# +CREATE TEMPORARY SEQUENCE s1 ENGINE=InnoDB; +INSERT INTO s1 VALUES (1, 1, 1000, 1, 1, 1, 1, 0); +DROP TEMPORARY SEQUENCE s1; +# +# MDEV-13007 ALTER .. ENGINE on temporary sequence may go wrong +# +create temporary sequence s1 engine=aria; +alter table s1 engine myisam; +select nextval(s1); +nextval(s1) +1 +drop temporary sequence s1; +create temporary sequence s1 engine=innodb; +alter table s1 engine myisam; +select nextval(s1); +nextval(s1) +1 +drop temporary sequence s1; +create temporary sequence s1; +alter table s1 engine innodb; +select nextval(s1); +nextval(s1) +1 +select nextval(s1); +nextval(s1) +2 +drop temporary sequence s1; diff --git a/mysql-test/suite/sql_sequence/temporary.test b/mysql-test/suite/sql_sequence/temporary.test new file mode 100644 index 00000000..e57dc693 --- /dev/null +++ b/mysql-test/suite/sql_sequence/temporary.test @@ -0,0 +1,47 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# Test temporary sequences +# + +--echo # +--echo # Create +--echo # + +create temporary sequence s1 engine=innodb; +alter table s1 engine myisam; +--disable_ps2_protocol +select nextval(s1); +--enable_ps2_protocol +select * from s1; +drop temporary sequence s1; + +--echo # +--echo # MDEV-14762 Server crashes in MDL_ticket::has_stronger_or_equal_type +--echo # upon inserting into temporary sequence +--echo # + +CREATE TEMPORARY SEQUENCE s1 ENGINE=InnoDB; +INSERT INTO s1 VALUES (1, 1, 1000, 1, 1, 1, 1, 0); +DROP TEMPORARY SEQUENCE s1; + +--echo # +--echo # MDEV-13007 ALTER .. ENGINE on temporary sequence may go wrong +--echo # + +--disable_ps2_protocol +create temporary sequence s1 engine=aria; +alter table s1 engine myisam; +select nextval(s1); +drop temporary sequence s1; +create temporary sequence s1 engine=innodb; +alter table s1 engine myisam; +select nextval(s1); +drop temporary sequence s1; +create temporary sequence s1; +alter table s1 engine innodb; +select nextval(s1); +select nextval(s1); +drop temporary sequence s1; +--enable_ps2_protocol diff --git a/mysql-test/suite/sql_sequence/view.result b/mysql-test/suite/sql_sequence/view.result new file mode 100644 index 00000000..0f39a637 --- /dev/null +++ b/mysql-test/suite/sql_sequence/view.result @@ -0,0 +1,36 @@ +create sequence s1; +create view v1 as select * from s1; +create view v2 as select next value for s1; +select * from v1; +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 * from v2; +next value for s1 +1 +select * from v2; +next value for s1 +2 +select next value for v1; +ERROR 42S02: 'test.v1' is not a SEQUENCE +drop sequence s1; +drop view v1,v2; +# +# MDEV 13020 Server crashes in Item_func_nextval::val_int upon +# selecting NEXT or PREVIOUS VALUE for a view +# +CREATE OR REPLACE VIEW v1 AS SELECT 1 AS f; +SELECT NEXT VALUE FOR v1; +ERROR 42S02: 'test.v1' is not a SEQUENCE +SELECT PREVIOUS VALUE FOR v1; +ERROR 42S02: 'test.v1' is not a SEQUENCE +drop view v1; +# +# MDEV 17978 Server crashes in mysqld_show_create_get_fields +# upon SHOW CREATE SEQUENCE on a broken view +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +DROP TABLE t1; +SHOW CREATE SEQUENCE v1; +ERROR 42S02: 'test.v1' is not a SEQUENCE +DROP VIEW v1; diff --git a/mysql-test/suite/sql_sequence/view.test b/mysql-test/suite/sql_sequence/view.test new file mode 100644 index 00000000..affac002 --- /dev/null +++ b/mysql-test/suite/sql_sequence/view.test @@ -0,0 +1,42 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# Test sequences with views +# + +create sequence s1; +create view v1 as select * from s1; +create view v2 as select next value for s1; +--disable_ps2_protocol +select * from v1; +select * from v2; +select * from v2; +--enable_ps2_protocol +--error ER_NOT_SEQUENCE +select next value for v1; +drop sequence s1; +drop view v1,v2; + +--echo # +--echo # MDEV 13020 Server crashes in Item_func_nextval::val_int upon +--echo # selecting NEXT or PREVIOUS VALUE for a view +--echo # + +CREATE OR REPLACE VIEW v1 AS SELECT 1 AS f; +--error ER_NOT_SEQUENCE +SELECT NEXT VALUE FOR v1; +--error ER_NOT_SEQUENCE +SELECT PREVIOUS VALUE FOR v1; +drop view v1; + +--echo # +--echo # MDEV 17978 Server crashes in mysqld_show_create_get_fields +--echo # upon SHOW CREATE SEQUENCE on a broken view +--echo # +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +DROP TABLE t1; +--error ER_NOT_SEQUENCE +SHOW CREATE SEQUENCE v1; +DROP VIEW v1; |