diff options
31 files changed, 1272 insertions, 813 deletions
diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result index 43afe2377c3..bb57a704c11 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -29,19 +29,21 @@ select * from t1; next_value min_value max_value start increment cache cycle round 3 -100 9223372036854775806 50 1 0 0 0 alter sequence t1 minvalue=100 start=100; +ERROR HY000: Sequence 'test.t1' values are conflicting +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_value min_value max_value start increment cache cycle round -3 100 9223372036854775806 100 1 0 0 0 +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_value min_value max_value start increment cache cycle round -3 100 500 100 1 0 0 0 +100 100 500 100 1 0 0 0 drop sequence t1; CREATE SEQUENCE t1 engine=myisam; alter sequence t1 nocache; diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test index 7454f5900f7..584bb6f19aa 100644 --- a/mysql-test/suite/sql_sequence/alter.test +++ b/mysql-test/suite/sql_sequence/alter.test @@ -18,7 +18,9 @@ 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; diff --git a/mysql-test/suite/sql_sequence/aria.result b/mysql-test/suite/sql_sequence/aria.result new file mode 100644 index 00000000000..8bf45563a6f --- /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_value min_value max_value start increment cache cycle round +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' values are conflicting +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 00000000000..8e8a50ef412 --- /dev/null +++ b/mysql-test/suite/sql_sequence/aria.test @@ -0,0 +1,43 @@ +--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"; + +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; diff --git a/mysql-test/suite/sql_sequence/binlog.result b/mysql-test/suite/sql_sequence/binlog.result index 12009b76ac4..b4f772a204b 100644 --- a/mysql-test/suite/sql_sequence/binlog.result +++ b/mysql-test/suite/sql_sequence/binlog.result @@ -11,6 +11,7 @@ next value for s1 min_value select next value for s1, min_value from s1 where max_value> 4; next value for s1 min_value 4 1 +alter sequence s1 maxvalue 1000; drop sequence s1; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info @@ -27,4 +28,6 @@ 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`; DROP TABLE `s1` /* generated by server */ +master-bin.000001 # Query # # use `test`; alter sequence s1 maxvalue 1000 +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 index 432edabf583..56d835f05ca 100644 --- a/mysql-test/suite/sql_sequence/binlog.test +++ b/mysql-test/suite/sql_sequence/binlog.test @@ -10,12 +10,17 @@ reset master; # get rid of previous tests binlog --enable_query_log - create or replace sequence s1 cache 3; select next value for s1, min_value from s1 where max_value> 1; select next value for s1, min_value from s1 where max_value> 2; select next value for s1, min_value from s1 where max_value> 3; select next value for s1, min_value from s1 where max_value> 4; + +# +# Alter sequence +# +alter sequence s1 maxvalue 1000; + drop sequence s1; --let $binlog_file = LAST diff --git a/mysql-test/suite/sql_sequence/disabled.def b/mysql-test/suite/sql_sequence/disabled.def index 507617dd75d..e69de29bb2d 100644 --- a/mysql-test/suite/sql_sequence/disabled.def +++ b/mysql-test/suite/sql_sequence/disabled.def @@ -1,2 +0,0 @@ -gtid : Disabled until Monty has time to check the result -replication : Disabled until Monty has time to check the result diff --git a/mysql-test/suite/sql_sequence/gtid.result b/mysql-test/suite/sql_sequence/gtid.result index f9a2d8ab60a..366f201d338 100644 --- a/mysql-test/suite/sql_sequence/gtid.result +++ b/mysql-test/suite/sql_sequence/gtid.result @@ -1,74 +1,83 @@ include/master-slave.inc -Warnings: -Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. -Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [connection master] +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'; +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; ########################################### master and slave sync sequence. ########################################### +connection master; use s_db; create sequence s1; show create table s1; Table Create Table -s1 CREATE SEQUENCE `s1` ( - `currval` bigint(21) NOT NULL COMMENT 'current value', - `nextval` bigint(21) NOT NULL COMMENT 'next value', - `minvalue` bigint(21) NOT NULL COMMENT 'min value', - `maxvalue` bigint(21) NOT NULL COMMENT 'max value', +s1 CREATE TABLE `s1` ( + `next_value` bigint(21) NOT NULL COMMENT 'next not cached value', + `min_value` bigint(21) NOT NULL COMMENT 'min value', + `max_value` bigint(21) NOT NULL COMMENT 'max value', `start` bigint(21) NOT NULL COMMENT 'start value', `increment` bigint(21) NOT NULL COMMENT 'increment value', `cache` bigint(21) NOT NULL COMMENT 'cache size', - `cycle` bigint(21) NOT NULL COMMENT 'cycle state', - `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1 + `cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done' +) ENGINE=MyISAM SEQUENCE=1 +connection slave; use s_db; show create table s1; Table Create Table -s1 CREATE SEQUENCE `s1` ( - `currval` bigint(21) NOT NULL COMMENT 'current value', - `nextval` bigint(21) NOT NULL COMMENT 'next value', - `minvalue` bigint(21) NOT NULL COMMENT 'min value', - `maxvalue` bigint(21) NOT NULL COMMENT 'max value', +s1 CREATE TABLE `s1` ( + `next_value` bigint(21) NOT NULL COMMENT 'next not cached value', + `min_value` bigint(21) NOT NULL COMMENT 'min value', + `max_value` bigint(21) NOT NULL COMMENT 'max value', `start` bigint(21) NOT NULL COMMENT 'start value', `increment` bigint(21) NOT NULL COMMENT 'increment value', `cache` bigint(21) NOT NULL COMMENT 'cache size', - `cycle` bigint(21) NOT NULL COMMENT 'cycle state', - `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1 + `cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'How many cycles has 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 HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +ERROR 42000: Unknown storage engine 'sequence' create table t(id int)engine=innodb; alter table t engine=sequence; -ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +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: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns) alter table s2 add index ind_x(start); -ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys) drop sequence s2; ########################################### -not support create temproary sequence. +Support create temporary sequence. ########################################### +connection master; create temporary sequence s2; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sequence s2' at line 1 +drop temporary sequence s2; ########################################### all invalid sequence value ########################################### +connection master; use s_db; create sequence s2 start with 1 minvalue 1 @@ -97,73 +106,43 @@ maxvalue 100000 increment by 1 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting create sequence s2 start with 1 minvalue 5 maxvalue 5 increment by 1 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting create sequence s2 start with 1 minvalue 5 maxvalue 4 increment by 1 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting create sequence s2 start with 1 minvalue 5 maxvalue 4 increment by 0 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting ########################################### global read lock prevent query sequence ########################################### +connection master; use s_db; create sequence s_db.s1; flush table with read lock; -select * for s1; +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; ########################################### -session setting -########################################### -use s_db; -create sequence s1; -set session sequence_read_skip_cache=true; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -0 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -0 -set session sequence_read_skip_cache=false; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -2 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 3 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -4 -drop sequence s1; -########################################### query cache test ########################################### +connection master; use s_db; show global variables like 'query_cache_type'; Variable_name Value @@ -177,33 +156,43 @@ Qcache_inserts 0 ########################################### priv test ########################################### +connection m_normal_1; create sequence s_db.s1; -select * for s_db.s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 +select * from s_db.s1; +next_value min_value max_value start increment cache cycle round +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_value min_value max_value start increment cache cycle round +1001 1 9223372036854775806 1 1 1000 0 0 create sequence s_db.s2; drop sequence s_db.s2; -select * for s_db.s1; +connection m_normal_2; +select next value for s_db.s1; ERROR 42000: SELECT command denied to user 'normal_2'@'localhost' for table 's1' create sequence s_db.s2; ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table 's2' +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 nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' has been run out. -insert into t select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' has been run out. +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 @@ -213,6 +202,9 @@ id 3 4 5 +connection master; +connection slave; +connection s_normal_3; use s_db; select * from t; id @@ -222,89 +214,125 @@ id 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 * for s_db.s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 +select next value for s_db.s1; +next value for s_db.s1 +1 +connection s_normal_3; show global variables like 'read_only'; Variable_name Value read_only ON -select * for s_db.s1; -ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +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; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 20 1 1 5 1 0 -select nextval for s_t; -nextval +next_value min_value max_value start increment cache cycle round +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 nextval from s_t; -nextval -7 +select * from s_t; +next_value min_value max_value start increment cache cycle round +6 1 20 1 1 5 1 0 +connection master; +connection slave; +connection s_normal_3; +select * from s_t; +next_value min_value max_value start increment cache cycle round +6 1 20 1 1 5 1 0 ------------------------------------------ master update nextval; ------------------------------------------ -select nextval for s_t; -nextval +connection m_normal_1; +select next value for s_t; +next value for s_t 2 -update s_t set nextval= 11; +update s_t set next_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; -currval nextval minvalue maxvalue start increment cache cycle round -0 11 1 20 1 1 5 1 0 +next_value min_value max_value start increment cache cycle round +11 1 20 1 1 5 1 0 +connection master; +connection slave; ------------------------------------------ show slave nextval; ------------------------------------------ +connection s_normal_3; +select * from s_t; +next_value min_value max_value start increment cache cycle round +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; -currval nextval minvalue maxvalue start increment cache cycle round -0 11 1 20 1 1 5 1 0 -set session sequence_read_skip_cache=off; -select * for s_t; -currval nextval minvalue maxvalue start increment cache cycle round -0 11 1 20 1 1 5 1 0 +next_value min_value max_value start increment cache cycle round +16 1 20 1 1 5 1 0 +connection master; +connection slave; +connection s_normal_3; select * from s_t; -currval nextval minvalue maxvalue start increment cache cycle round -0 17 1 20 1 1 5 1 0 +next_value min_value max_value start increment cache cycle round +16 1 20 1 1 5 1 0 ------------------------------------------ update into invalid sequence ------------------------------------------ -select nextval for s_t; -nextval -12 -update s_t set nextval= 11,start=10, minvalue=11; +connection m_normal_1; +select * from s_t; +next_value min_value max_value start increment cache cycle round +16 1 20 1 1 5 1 0 +update s_t set next_value= 11,start=10, min_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' values are conflicting commit; create table t_1(id int); insert into t_1 value(1111); -select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' structure or number is invalid. -insert into t_1 select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' structure or number is invalid. +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 nextval for s_t; -nextval +select next value for s_t; +next value for s_t +14 +connection m_normal_1; drop sequence s_t; drop table t_1; ########################################### @@ -314,14 +342,14 @@ test transaction context (innodb) transaction table and sequence normal transaction commit ------------------------------------------ +connection m_normal_1; use s_db; -set session sequence_read_skip_cache=off; create sequence s_1 cache 5; create table t_1(id int)engine=innodb; begin; insert into t_1 values(1111); -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; +insert into t_1 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; @@ -330,7 +358,9 @@ id 1 2 2222 -set session sequence_read_skip_cache=off; +connection master; +connection slave; +connection s_normal_3; use s_db; select * from t_1; id @@ -341,16 +371,17 @@ id ------------------------------------------ normal transaction rollback ------------------------------------------ +connection m_normal_1; begin; insert into t_1 values(3333); -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; +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 @@ -373,10 +404,12 @@ id 1 2 2222 -select nextval for s_1; -nextval +select next value for s_1; +next value for s_1 11 -set session sequence_read_skip_cache=off; +connection master; +connection slave; +connection s_normal_3; use s_db; select * from t_1; id @@ -384,6 +417,7 @@ id 1 2 2222 +connection m_normal_1; use s_db; drop sequence s_1; drop table t_1; @@ -394,14 +428,14 @@ test transaction context (myisam) transaction table and sequence normal transaction commit ------------------------------------------ +connection m_normal_1; use s_db; -set session sequence_read_skip_cache=off; create sequence s_1 cache 5; create table t_1(id int)engine=myisam; begin; insert into t_1 values(1111); -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; +insert into t_1 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; @@ -410,7 +444,9 @@ id 1 2 2222 -set session sequence_read_skip_cache=off; +connection master; +connection slave; +connection s_normal_3; use s_db; select * from t_1; id @@ -421,16 +457,17 @@ id ------------------------------------------ normal transaction rollback ------------------------------------------ +connection m_normal_1; begin; insert into t_1 values(3333); -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; +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 @@ -464,10 +501,12 @@ id 8 9 10 -select nextval for s_1; -nextval +select next value for s_1; +next value for s_1 11 -set session sequence_read_skip_cache=off; +connection master; +connection slave; +connection s_normal_3; use s_db; select * from t_1; id @@ -484,58 +523,68 @@ id 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 nextval for s1; -nextval +select next value for s1; +next value for s1 1 -select nextval for s1; -nextval +select next value for s1; +next value for s1 2 -select nextval for s1; -nextval +select next value for s1; +next value for s1 3 -select nextval for s1; -nextval +select next value for s1; +next value for s1 4 commit; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 9223372036854775807 1 1 2 0 0 +next_value min_value max_value start increment cache cycle round +5 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; +connection slave; use s_db; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 9223372036854775807 1 1 2 0 0 +next_value min_value max_value start increment cache cycle round +5 1 9223372036854775806 1 1 2 0 0 ------------------------------------------ close session binlog. ------------------------------------------ +connection master; set session sql_log_bin=off; -select nextval for s1; -nextval +select next value for s1; +next value for s1 5 -select nextval for s1; -nextval +select next value for s1; +next value for s1 6 -select nextval for s1; -nextval +select next value for s1; +next value for s1 7 -select nextval for s1; -nextval +select next value for s1; +next value for s1 8 set session sql_log_bin=on; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 10 1 9223372036854775807 1 1 2 0 0 +next_value min_value max_value start increment cache cycle round +9 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; +connection slave; use s_db; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 9223372036854775807 1 1 2 0 0 +next_value min_value max_value start increment cache cycle round +5 1 9223372036854775806 1 1 2 0 0 +connection m_normal_1; use s_db; drop sequence s1; ########################################### @@ -544,38 +593,44 @@ statement binlog ------------------------------------------ set binlog_format=statement ------------------------------------------ -set session sequence_read_skip_cache=off; +connection master; set session binlog_format=statement; show session variables like '%binlog_format%'; Variable_name Value binlog_format STATEMENT +wsrep_forced_binlog_format NONE create sequence s1 cache 2; -select nextval for s1; -ERROR HY000: Sequence requires binlog_format= row +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 * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 2 0 0 +select next value for s1; +next value for s1 +1 +select * from s1; +next_value min_value max_value start increment cache cycle round +3 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; use s_db; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 4 1 9223372036854775807 1 1 2 0 0 -set session sequence_read_skip_cache=off; +next_value min_value max_value start increment cache cycle round +3 1 9223372036854775806 1 1 2 0 0 +connection m_normal_1; use s_db; drop sequence s1; ########################################### test savepoint ########################################### -set session sequence_read_skip_cache=off; +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 nextval for s1; -insert into t1 select nextval for s1; -insert into t1 select nextval for s1; +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 @@ -588,8 +643,8 @@ rollback to sp1; select * from t1; id 1111 -select nextval for s1; -nextval +select next value for s1; +next value for s1 4 commit; drop sequence s1; @@ -597,7 +652,7 @@ drop table t1; ########################################### test proc ########################################### -set session sequence_read_skip_cache=off; +connection m_normal_1; use s_db; create table t(id int)engine=innodb; create procedure p1() @@ -606,7 +661,7 @@ create sequence s1 cache 2; end// create procedure p2() begin -insert into t select nextval for s1; +insert into t select next value for s1; commit; end// call p1(); @@ -620,6 +675,8 @@ id 2 3 4 +connection master; +connection slave; use s_db; select * from t; id @@ -627,6 +684,7 @@ id 2 3 4 +connection m_normal_1; drop table t; drop sequence s1; drop procedure p1; @@ -634,7 +692,7 @@ drop procedure p2; ########################################### test trigger ########################################### -set session sequence_read_skip_cache=off; +connection m_normal_1; use s_db; create sequence s1 cache 2; create table t1(id int)engine=innodb; @@ -642,7 +700,7 @@ create table t2(id int)engine=innodb; CREATE TRIGGER tri_1 before INSERT ON t2 FOR EACH ROW BEGIN -INSERT INTO t1 select nextval for s1; +INSERT INTO t1 select next value for s1; END// begin; insert into t2 values(1111); @@ -666,8 +724,8 @@ select * from t2; id select * from t1; id -select nextval for s1; -nextval +select next value for s1; +next value for s1 5 drop trigger tri_1; drop table t1; @@ -676,13 +734,13 @@ drop sequence s1; ########################################### test function ########################################### -set session sequence_read_skip_cache=off; +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 nextval for s1; +INSERT INTO t1 select next value for s1; return (1); END// begin; @@ -707,12 +765,13 @@ id rollback; select * from t1; id -select nextval for s1; -nextval +select next value for s1; +next value for s1 5 drop function f1; drop table t1; drop sequence s1; +connection master; use s_db; drop database s_db; drop user normal_1@'%'; diff --git a/mysql-test/suite/sql_sequence/gtid.test b/mysql-test/suite/sql_sequence/gtid.test index 30717a4e53c..50492cbb329 100644 --- a/mysql-test/suite/sql_sequence/gtid.test +++ b/mysql-test/suite/sql_sequence/gtid.test @@ -1,3 +1,7 @@ +# +# 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 @@ -30,7 +34,6 @@ create sequence s1; show create table s1; --sync_slave_with_master -connection slave; use s_db; show create table s1; @@ -164,7 +167,9 @@ show status like 'Qcache_inserts'; --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; @@ -240,9 +245,9 @@ 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 @@ -255,7 +260,9 @@ select * from s_t; --echo ------------------------------------------ connection m_normal_1; select next value for s_t; +--error ER_ILLEGAL_HA update s_t set next_value= 11; +alter sequence s_t restart=11; commit; select * from s_t; @@ -285,8 +292,10 @@ select * from s_t; --echo ------------------------------------------ connection m_normal_1; select * from s_t; ---error ER_SEQUENCE_INVALID_DATA +--error ER_ILLEGAL_HA update s_t set next_value= 11,start=10, min_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); @@ -495,11 +504,11 @@ select next value for s1; set session binlog_format=row; select next value for s1; +select * from s1; connection master; --sync_slave_with_master -connection slave; use s_db; select * from s1; @@ -568,7 +577,6 @@ select * from t; connection master; --sync_slave_with_master -connection slave; use s_db; select * from t; @@ -655,6 +663,4 @@ drop user normal_2@'%'; drop user normal_3@'%'; drop user normal_4@'%'; - ---sync_slave_with_master --source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index 1e0b7e28a10..b7d3a8ff26d 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -287,6 +287,93 @@ select * from t9; next_value min_value max_value start increment cache cycle round 6 1 10 1 1 5 1 2 drop sequence t9; +CREATE SEQUENCE s1 cache=0; +select * from s1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 0 0 0 +select next value for s1; +next value for s1 +1 +select next_value from s1; +next_value +2 +select next value for s1; +next value for s1 +2 +select next_value from s1; +next_value +3 +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=1; +select next_value from s1; +next_value +1 +select next value for s1; +next value for s1 +1 +select next_value from s1; +next_value +2 +select next value for s1; +next value for s1 +2 +select next_value from s1; +next_value +3 +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=2; +select next_value from s1; +next_value +1 +select next value for s1; +next value for s1 +1 +select next_value from s1; +next_value +3 +select next value for s1; +next value for s1 +2 +select next_value from s1; +next_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_value min_value max_value start increment cache cycle round +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 diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index 8ba9ba1277c..cf67b7a2752 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -111,6 +111,52 @@ 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_value from s1; +select next value for s1; +select next_value from s1; +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=1; +select next_value from s1; +select next value for s1; +select next_value from s1; +select next value for s1; +select next_value from s1; +DROP SEQUENCE s1; +CREATE SEQUENCE s1 cache=2; +select next_value from s1; +select next value for s1; +select next_value from s1; +select next value for s1; +select next_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 # diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result index b9510d46de7..1d86fa80e4f 100644 --- a/mysql-test/suite/sql_sequence/other.result +++ b/mysql-test/suite/sql_sequence/other.result @@ -40,3 +40,45 @@ select * from s1; next_value min_value max_value start increment cache cycle round 2001 1 9223372036854775806 1 1 1000 0 0 drop sequence s1; +# +# ÌNSERT +# +create sequence s1; +create sequence s2; +insert into s1 (next_value, min_value) values (100,1000); +ERROR HY000: Field 'max_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 (next value for s2, 1,9223372036854775806,1,1,1000,0,0); +ERROR HY000: Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a squence object (like with mysqldump). If you want to change the SEQUENCE, use ALTER SEQUENCE instead. +insert into s1 select * from s2; +ERROR HY000: Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a squence object (like with mysqldump). If you want to change the SEQUENCE, use ALTER SEQUENCE instead. +insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0); +ERROR HY000: Sequence 'test.s1' values are conflicting +insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0); +ERROR HY000: Sequence 'test.s1' values are conflicting +select * from s1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 1000 0 0 +insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0); +select * from s1; +next_value min_value max_value start increment cache cycle round +1000 1 9223372036854775806 1 1 1000 0 0 +select next value for s1; +next value for s1 +1000 +select * from s1; +next_value min_value max_value start increment cache cycle round +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' values are conflicting +drop sequence s1,s2; +# +# UPDATE and DELETE +# +create sequence s1; +update s1 set next_value=100; +ERROR HY000: Storage engine SEQUENCE of the table `test`.`s1` doesn't have this option +delete from s1 where next_value > 0; +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/other.test b/mysql-test/suite/sql_sequence/other.test index c9ed326004a..6860a91e217 100644 --- a/mysql-test/suite/sql_sequence/other.test +++ b/mysql-test/suite/sql_sequence/other.test @@ -27,3 +27,41 @@ check table s1; select next value for s1; select * from s1; drop sequence s1; + +--echo # +--echo # ÌNSERT +--echo # + +create sequence s1; +create sequence s2; +--error ER_NO_DEFAULT_FOR_FIELD +insert into s1 (next_value, min_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_WRONG_INSERT_INTO_SEQUENCE +insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0); +--error ER_WRONG_INSERT_INTO_SEQUENCE +insert into s1 select * from s2; +--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; +select next value for s1; +select * from s1; +--error ER_SEQUENCE_INVALID_DATA +insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0); +drop sequence s1,s2; + +--echo # +--echo # UPDATE and DELETE +--echo # + +create sequence s1; +--error ER_ILLEGAL_HA +update s1 set next_value=100; +--error ER_ILLEGAL_HA +delete from s1 where next_value > 0; +drop sequence s1; diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result index eed4c130a9b..de084410c95 100644 --- a/mysql-test/suite/sql_sequence/replication.result +++ b/mysql-test/suite/sql_sequence/replication.result @@ -1,69 +1,76 @@ include/master-slave.inc -Warnings: -Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. -Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [connection master] +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. ########################################### -use s_db; +connection master; create sequence s1; show create table s1; Table Create Table -s1 CREATE SEQUENCE `s1` ( - `currval` bigint(21) NOT NULL COMMENT 'current value', - `nextval` bigint(21) NOT NULL COMMENT 'next value', - `minvalue` bigint(21) NOT NULL COMMENT 'min value', - `maxvalue` bigint(21) NOT NULL COMMENT 'max value', +s1 CREATE TABLE `s1` ( + `next_value` bigint(21) NOT NULL COMMENT 'next not cached value', + `min_value` bigint(21) NOT NULL COMMENT 'min value', + `max_value` bigint(21) NOT NULL COMMENT 'max value', `start` bigint(21) NOT NULL COMMENT 'start value', `increment` bigint(21) NOT NULL COMMENT 'increment value', `cache` bigint(21) NOT NULL COMMENT 'cache size', - `cycle` bigint(21) NOT NULL COMMENT 'cycle state', - `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -use s_db; + `cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done' +) ENGINE=MyISAM SEQUENCE=1 +connection slave; show create table s1; Table Create Table -s1 CREATE SEQUENCE `s1` ( - `currval` bigint(21) NOT NULL COMMENT 'current value', - `nextval` bigint(21) NOT NULL COMMENT 'next value', - `minvalue` bigint(21) NOT NULL COMMENT 'min value', - `maxvalue` bigint(21) NOT NULL COMMENT 'max value', +s1 CREATE TABLE `s1` ( + `next_value` bigint(21) NOT NULL COMMENT 'next not cached value', + `min_value` bigint(21) NOT NULL COMMENT 'min value', + `max_value` bigint(21) NOT NULL COMMENT 'max value', `start` bigint(21) NOT NULL COMMENT 'start value', `increment` bigint(21) NOT NULL COMMENT 'increment value', `cache` bigint(21) NOT NULL COMMENT 'cache size', - `cycle` bigint(21) NOT NULL COMMENT 'cycle state', - `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -use s_db; + `cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state', + `round` bigint(21) NOT NULL COMMENT 'How many cycles has 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 HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +ERROR 42000: Unknown storage engine 'sequence' create table t(id int)engine=innodb; alter table t engine=sequence; -ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +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: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns) alter table s2 add index ind_x(start); -ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' +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 HY000: 's_db.t_1' is not SEQUENCE @@ -78,145 +85,122 @@ CREATE SEQUENCE `s2` ( `cache` bigint(21) NOT NULL COMMENT 'cache size', `cycle` bigint(21) NOT NULL COMMENT 'cycle state', `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -insert into s2 values(0, 0, 1, 10, 1, 2, 1, 1, 0); +) 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 '( +`currval` bigint(21) NOT NULL COMMENT 'current value', +`nextval` bigint(21) NO' at line 1 +CREATE TABLE `s2` ( +`next_value` bigint(21) NOT NULL COMMENT 'next value', +`min_value` bigint(21) NOT NULL COMMENT 'min value', +`max_value` bigint(21) NOT NULL COMMENT 'max value', +`start` bigint(21) NOT NULL COMMENT 'start value', +`increment` bigint(21) NOT NULL COMMENT 'increment value', +`cache` bigint(21) NOT NULL COMMENT 'cache size', +`cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state', +`round` bigint(21) NOT NULL COMMENT 'already how many round' +) ENGINE=InnoDB sequence=1; +insert into s2 values(1, 1, 10, 1, 2, 1, 1, 0); commit; select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 10 1 2 1 1 0 -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 3 1 10 1 2 1 1 0 -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 5 1 10 1 2 1 1 0 -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 10 1 2 1 1 0 -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 9 1 10 1 2 1 1 0 -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 10 1 2 1 1 1 -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 3 1 10 1 2 1 1 1 +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; -currval nextval minvalue maxvalue start increment cache cycle round -0 5 1 10 1 2 1 1 1 +next_value min_value max_value start increment cache cycle round +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_value min_value max_value start increment cache cycle round +5 1 10 1 2 1 1 1 +commit; +connection master; +connection slave; select * from s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 5 1 10 1 2 1 1 1 +next_value min_value max_value start increment cache cycle round +5 1 10 1 2 1 1 1 +connection master; drop sequence s2; -CREATE SEQUENCE `s2` ( -`currval` bigint(21) NULL COMMENT 'current value', -`nextval` bigint(21) NOT NULL COMMENT 'next value', -`minvalue` bigint(21) NOT NULL COMMENT 'min value', -`maxvalue` bigint(21) NOT NULL COMMENT 'max value', -`start` bigint(21) NOT NULL COMMENT 'start value', -`increment` bigint(21) NOT NULL COMMENT 'increment value', -`cache` bigint(21) NOT NULL COMMENT 'cache size', -`cycle` bigint(21) NOT NULL COMMENT 'cycle state', -`round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. -CREATE SEQUENCE `s2` ( -`rrval` bigint(21) NULL COMMENT 'current value', -`nextval` bigint(21) NOT NULL COMMENT 'next value', -`minvalue` bigint(21) NOT NULL COMMENT 'min value', -`maxvalue` bigint(21) NOT NULL COMMENT 'max value', +CREATE TABLE `s2` ( +`next_value` bigint(21) NOT NULL COMMENT 'next value', +`min_value` bigint(21) NOT NULL COMMENT 'min value', +`max_value` bigint(21) NOT NULL COMMENT 'max value', `start` bigint(21) NOT NULL COMMENT 'start value', `increment` bigint(21) NOT NULL COMMENT 'increment value', `cache` bigint(21) NOT NULL COMMENT 'cache size', -`cycle` bigint(21) NOT NULL COMMENT 'cycle state', -`round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. -CREATE SEQUENCE `s2` ( -`currval` bigint(21) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'current value', -`nextval` bigint(21) NOT NULL COMMENT 'next value', -`minvalue` bigint(21) NOT NULL COMMENT 'min value', -`maxvalue` bigint(21) NOT NULL COMMENT 'max value', -`start` bigint(21) NOT NULL COMMENT 'start value', -`increment` bigint(21) NOT NULL COMMENT 'increment value', -`cache` bigint(21) NOT NULL COMMENT 'cache size', -`cycle` bigint(21) NOT NULL COMMENT 'cycle state', -`round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. -CREATE SEQUENCE `s2` ( -`currval` bigint(21) NOT NULL COMMENT 'current value', -`nextval` bigint(21) NOT NULL COMMENT 'next value', -`minvalue` bigint(21) NOT NULL COMMENT 'min value', -`maxvalue` bigint(21) NOT NULL COMMENT 'max value', -`start` bigint(21) NOT NULL COMMENT 'start value', -`increment` bigint(21) NOT NULL COMMENT 'increment value', -`cache` bigint(21) NOT NULL COMMENT 'cache size', -`cycle` bigint(21) NOT NULL COMMENT 'cycle state', +`cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state', `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=myisam DEFAULT CHARSET=latin1; +) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1; show create sequence s2; Table Create Table -s2 CREATE SEQUENCE `s2` ( - `currval` bigint(21) NOT NULL COMMENT 'current value', - `nextval` bigint(21) NOT NULL COMMENT 'next value', - `minvalue` bigint(21) NOT NULL COMMENT 'min value', - `maxvalue` bigint(21) NOT NULL COMMENT 'max value', - `start` bigint(21) NOT NULL COMMENT 'start value', - `increment` bigint(21) NOT NULL COMMENT 'increment value', - `cache` bigint(21) NOT NULL COMMENT 'cache size', - `cycle` bigint(21) NOT NULL COMMENT 'cycle state', - `round` bigint(21) NOT NULL COMMENT 'already how many round' -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +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; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 9223372036854775807 1 1 10000 0 0 +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 1000 0 0 select * from t2; id -insert into t2 select nextval for s2; +insert into t2 select next value for s2; commit; -select * for s2; -currval nextval minvalue maxvalue start increment cache cycle round -0 2 1 9223372036854775807 1 1 10000 0 0 -select * for t2; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use +select 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; -currval nextval minvalue maxvalue start increment cache cycle round id -0 10002 1 9223372036854775807 1 1 10000 0 0 1 +next_value min_value max_value start increment cache cycle round 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 MySQL server version for the right syntax to use near ' t2' at line 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 '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; -ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE' -rename table s2 to s2_1; -select * for s2_1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 -truncate table s2_1; -ERROR HY000: Table storage engine for 's2_1' doesn't have this option -rename table s2_1 to s2; +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_value min_value max_value start increment cache cycle round +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; ########################################### -not support create temproary sequence. -########################################### -create temporary sequence s2; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sequence s2' at line 1 -########################################### all invalid sequence value ########################################### -use s_db; +connection master; create sequence s2 start with 1 minvalue 1 maxvalue 100000 @@ -244,74 +228,43 @@ maxvalue 100000 increment by 1 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting create sequence s2 start with 1 minvalue 5 maxvalue 5 increment by 1 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting create sequence s2 start with 1 minvalue 5 maxvalue 4 increment by 1 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting create sequence s2 start with 1 minvalue 5 maxvalue 4 increment by 0 nocache nocycle; -ERROR HY000: Sequence 's_db.s2' structure or number is invalid. +ERROR HY000: Sequence 's_db.s2' values are conflicting ########################################### global read lock prevent query sequence ########################################### -use s_db; +connection master; create sequence s_db.s1; flush table with read lock; -select * for s1; +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; ########################################### -session setting -########################################### -use s_db; -create sequence s1; -set session sequence_read_skip_cache=true; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -0 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -0 -set session sequence_read_skip_cache=false; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -2 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 3 1 9223372036854775807 1 1 10000 0 0 -select nextval for s1; -nextval -4 -drop sequence s1; -########################################### query cache test ########################################### -use s_db; +connection master; +flush status; show global variables like 'query_cache_type'; Variable_name Value query_cache_type ON @@ -320,37 +273,40 @@ Variable_name Value Qcache_hits 0 show status like 'Qcache_inserts'; Variable_name Value -Qcache_inserts 1 +Qcache_inserts 0 ########################################### priv test ########################################### +connection m_normal_1; create sequence s_db.s1; -select * for s_db.s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 +select NEXT VALUE for s_db.s1; +NEXT VALUE for s_db.s1 +1 create sequence s_db.s2; drop sequence s_db.s2; -select * for s_db.s1; +connection m_normal_2; +select NEXT VALUE for s_db.s1; ERROR 42000: SELECT command denied to user 'normal_2'@'localhost' for table 's1' create sequence s_db.s2; ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table 's2' +connection m_normal_1; drop sequence s_db.s1; ########################################### run out sequence value ########################################### -use s_db; +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 nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -insert into t select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' has been run out. -insert into t select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' has been run out. +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 @@ -360,7 +316,9 @@ id 3 4 5 -use s_db; +connection master; +connection slave; +connection s_normal_3; select * from t; id 1111 @@ -369,91 +327,111 @@ id 3 4 5 -use s_db; +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 * for s_db.s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 10000 0 0 +select * from s_db.s1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 1000 0 0 +connection s_normal_3; show global variables like 'read_only'; Variable_name Value read_only ON -select * for s_db.s1; -ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +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 ########################################### -use s_db; -create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle; -use s_db; +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; -currval nextval minvalue maxvalue start increment cache cycle round -0 0 1 20 1 1 5 1 0 -select nextval for s_t; -nextval +next_value min_value max_value start increment cache cycle round +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 nextval from s_t; -nextval -7 +connection master; +connection slave; +connection s_normal_3; +select next_value from s_t; +next_value +6 ------------------------------------------ -master update nextval; +master ALTER SEQUENCE ------------------------------------------ -select nextval for s_t; -nextval +connection m_normal_1; +select next value for s_t; +next value for s_t 2 -update s_t set nextval= 11; -commit; +alter sequence s_t restart= 11; select * from s_t; -currval nextval minvalue maxvalue start increment cache cycle round -0 11 1 20 1 1 5 1 0 +next_value min_value max_value start increment cache cycle round +11 1 20 1 1 5 1 0 +connection master; +connection slave; ------------------------------------------ show slave nextval; ------------------------------------------ +connection s_normal_3; select * from s_t; -currval nextval minvalue maxvalue start increment cache cycle round -0 11 1 20 1 1 5 1 0 -set session sequence_read_skip_cache=off; -select * for s_t; -currval nextval minvalue maxvalue start increment cache cycle round -0 11 1 20 1 1 5 1 0 +next_value min_value max_value start increment cache cycle round +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; -currval nextval minvalue maxvalue start increment cache cycle round -0 17 1 20 1 1 5 1 0 +next_value min_value max_value start increment cache cycle round +16 1 20 1 1 5 1 0 ------------------------------------------ update into invalid sequence ------------------------------------------ -select nextval for s_t; -nextval +connection m_normal_1; +select next value for s_t; +next value for s_t 12 -update s_t set nextval= 11,start=10, minvalue=11; -commit; -create table t_1(id int); -insert into t_1 value(1111); -select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' structure or number is invalid. -insert into t_1 select nextval for s_t; -ERROR HY000: Sequence 's_db.s_t' structure or number is invalid. -commit; -select * from t_1; -id -1111 +select * from s_t; +next_value min_value max_value start increment cache cycle round +16 1 20 1 1 5 1 0 +alter sequence s_t minvalue=11 maxvalue=9; +ERROR HY000: Sequence 's_db.s_t' values are conflicting +select * from s_t; +next_value min_value max_value start increment cache cycle round +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' values are conflicting +select * from s_t; +next_value min_value max_value start increment cache cycle round +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 nextval for s_t; -nextval +select next value for s_t; +next value for s_t +13 +connection m_normal_1; drop sequence s_t; -drop table t_1; ########################################### test transaction context (innodb) ########################################### @@ -461,14 +439,13 @@ test transaction context (innodb) transaction table and sequence normal transaction commit ------------------------------------------ -use s_db; -set session sequence_read_skip_cache=off; -create sequence s_1 cache 5; +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 nextval for s_1; -insert into t_1 select nextval 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 values(2222); commit; select * from t_1; @@ -477,8 +454,12 @@ id 1 2 2222 -set session sequence_read_skip_cache=off; -use s_db; +select * from s_1; +next_value min_value max_value start increment cache cycle round +6 1 9223372036854775806 1 1 5 0 0 +connection master; +connection slave; +connection s_normal_3; select * from t_1; id 1111 @@ -488,16 +469,17 @@ id ------------------------------------------ normal transaction rollback ------------------------------------------ +connection m_normal_1; begin; insert into t_1 values(3333); -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; +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 @@ -520,18 +502,22 @@ id 1 2 2222 -select nextval for s_1; -nextval +select * from s_1; +next_value min_value max_value start increment cache cycle round +11 1 9223372036854775806 1 1 5 0 0 +select next value for s_1; +next value for s_1 11 -set session sequence_read_skip_cache=off; -use s_db; +connection master; +connection slave; +connection s_normal_3; select * from t_1; id 1111 1 2 2222 -use s_db; +connection m_normal_1; drop sequence s_1; drop table t_1; ########################################### @@ -541,14 +527,13 @@ test transaction context (myisam) transaction table and sequence normal transaction commit ------------------------------------------ -use s_db; -set session sequence_read_skip_cache=off; +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 nextval for s_1; -insert into t_1 select nextval 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 values(2222); commit; select * from t_1; @@ -557,8 +542,9 @@ id 1 2 2222 -set session sequence_read_skip_cache=off; -use s_db; +connection master; +connection slave; +connection s_normal_3; select * from t_1; id 1111 @@ -568,16 +554,17 @@ id ------------------------------------------ normal transaction rollback ------------------------------------------ +connection m_normal_1; begin; insert into t_1 values(3333); -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; -insert into t_1 select nextval for s_1; +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 @@ -611,11 +598,12 @@ id 8 9 10 -select nextval for s_1; -nextval +select next value for s_1; +next value for s_1 11 -set session sequence_read_skip_cache=off; -use s_db; +connection master; +connection slave; +connection s_normal_3; select * from t_1; id 1111 @@ -631,59 +619,73 @@ id 8 9 10 -use s_db; +connection m_normal_1; drop sequence s_1; drop table t_1; ########################################### close binlog ########################################### -use s_db; +connection m_normal_1; create sequence s1 cache 2; -select nextval for s1; -nextval +select next value for s1; +next value for s1 1 -select nextval for s1; -nextval +select next value for s1; +next value for s1 2 -select nextval for s1; -nextval +select next value for s1; +next value for s1 3 -select nextval for s1; -nextval +select next value for s1; +next value for s1 4 commit; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 9223372036854775807 1 1 2 0 0 -use s_db; +next_value min_value max_value start increment cache cycle round +5 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 9223372036854775807 1 1 2 0 0 +next_value min_value max_value start increment cache cycle round +5 1 9223372036854775806 1 1 2 0 0 ------------------------------------------ close session binlog. ------------------------------------------ +connection master; set session sql_log_bin=off; -select nextval for s1; -nextval +select next value for s1; +next value for s1 5 -select nextval for s1; -nextval +select next value for s1; +next value for s1 6 -select nextval for s1; -nextval +select next value for s1; +next value for s1 7 -select nextval for s1; -nextval +select next value for s1; +next value for s1 8 set session sql_log_bin=on; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 10 1 9223372036854775807 1 1 2 0 0 -use s_db; +next_value min_value max_value start increment cache cycle round +9 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 1 9223372036854775807 1 1 2 0 0 -use s_db; +next_value min_value max_value start increment cache cycle round +5 1 9223372036854775806 1 1 2 0 0 +connection master; +select next value for s1; +next value for s1 +9 +select * from s1; +next_value min_value max_value start increment cache cycle round +11 1 9223372036854775806 1 1 2 0 0 +connection slave; +select * from s1; +next_value min_value max_value start increment cache cycle round +11 1 9223372036854775806 1 1 2 0 0 +connection master; drop sequence s1; ########################################### statement binlog @@ -691,60 +693,68 @@ statement binlog ------------------------------------------ set binlog_format=statement ------------------------------------------ -set session sequence_read_skip_cache=off; +connection master; set session binlog_format=statement; show session variables like '%binlog_format%'; Variable_name Value binlog_format STATEMENT +wsrep_forced_binlog_format NONE create sequence s1 cache 2; -select nextval for s1; -ERROR HY000: Sequence requires binlog_format= row +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 * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 2 0 0 -use s_db; +select next value for s1; +next value for s1 +1 +connection master; +connection slave; select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 4 1 9223372036854775807 1 1 2 0 0 -set session sequence_read_skip_cache=off; -use s_db; +next_value min_value max_value start increment cache cycle round +3 1 9223372036854775806 1 1 2 0 0 +connection m_normal_1; drop sequence s1; ------------------------------------------ set binlog_format=mixed ------------------------------------------ -set session sequence_read_skip_cache=off; +connection master; set session binlog_format=mixed; show session variables like '%binlog_format%'; Variable_name Value binlog_format MIXED +wsrep_forced_binlog_format NONE create sequence s1 cache 2; -select nextval for s1; -ERROR HY000: Sequence requires binlog_format= row +select next value for s1; +next value for s1 +1 set session binlog_format=row; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 2 0 0 -use s_db; +select next value for s1; +next value for s1 +2 select * from s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 4 1 9223372036854775807 1 1 2 0 0 -set session sequence_read_skip_cache=off; -use s_db; +next_value min_value max_value start increment cache cycle round +3 1 9223372036854775806 1 1 2 0 0 +connection master; +connection slave; +select * from s1; +next_value min_value max_value start increment cache cycle round +3 1 9223372036854775806 1 1 2 0 0 +connection m_normal_1; drop sequence s1; +connection master; +connection slave; ########################################### test savepoint ########################################### -set session sequence_read_skip_cache=off; +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 nextval for s1; -insert into t1 select nextval for s1; -insert into t1 select nextval for s1; +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 @@ -757,28 +767,36 @@ rollback to sp1; select * from t1; id 1111 -select nextval for s1; -nextval +select next value for s1; +next value for s1 4 commit; drop sequence s1; drop table t1; +connection master; +connection slave; ########################################### create as ########################################### -set session sequence_read_skip_cache=off; +connection m_normal_1; create sequence s1 cache 2; -create table t as select * for s1; +create table t as select * from s1; select * from t; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 9223372036854775807 1 1 2 0 0 -drop sequence s1; +next_value min_value max_value start increment cache cycle round +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 ########################################### -set session sequence_read_skip_cache=off; -use s_db; +connection m_normal_1; create table t(id int)engine=innodb; create procedure p1() begin @@ -786,7 +804,7 @@ create sequence s1 cache 2; end// create procedure p2() begin -insert into t select nextval for s1; +insert into t select next value for s1; commit; end// call p1(); @@ -800,13 +818,15 @@ id 2 3 4 -use s_db; +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; @@ -814,15 +834,14 @@ drop procedure p2; ########################################### test trigger ########################################### -set session sequence_read_skip_cache=off; -use s_db; +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 nextval for s1; +INSERT INTO t1 select next value for s1; END// begin; insert into t2 values(1111); @@ -846,8 +865,8 @@ select * from t2; id select * from t1; id -select nextval for s1; -nextval +select next value for s1; +next value for s1 5 drop trigger tri_1; drop table t1; @@ -856,13 +875,12 @@ drop sequence s1; ########################################### test function ########################################### -set session sequence_read_skip_cache=off; -use s_db; +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 nextval for s1; +INSERT INTO t1 select next value for s1; return (1); END// begin; @@ -887,8 +905,8 @@ id rollback; select * from t1; id -select nextval for s1; -nextval +select next value for s1; +next value for s1 5 drop function f1; drop table t1; @@ -896,132 +914,150 @@ drop sequence s1; ########################################### test value boundary ########################################### -use s_db; +connection m_normal_1; ------------------------------------------ round increment by round ------------------------------------------ create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 5 2 7 5 1 1 1 0 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 6 2 7 5 1 1 1 0 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 7 2 7 5 1 1 1 0 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 2 2 7 5 1 1 1 1 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 3 2 7 5 1 1 1 1 +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 nextval, round for s1; -nextval round -5 0 -select nextval, round for s1; -nextval round -6 0 -select nextval, round for s1; -nextval round -7 0 -select nextval, round for s1; -ERROR HY000: Sequence 's_db.s1' has been run out. +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_value min_value max_value start increment cache cycle round +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 * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 2 1 3 2 3 0 1 0 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 3 2 3 0 1 1 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 3 2 3 0 1 2 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 3 2 3 0 1 3 -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 1 1 3 2 3 0 1 4 +select next value for s1; +next value for s1 +2 +select next_value,round from s1; +next_value round +4 0 +select next value for s1; +next value for s1 +1 +select next_value,round from s1; +next_value round +4 1 +select next value for s1; +next value for s1 +1 +select next_value,round from s1; +next_value round +4 2 +select next value for s1; +next value for s1 +1 +select next_value,round from s1; +next_value round +4 3 +select next value for s1; +next value for s1 +1 +select next_value,round from s1; +next_value round +4 4 drop sequence s1; create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle; -select * for s1; -currval nextval minvalue maxvalue start increment cache cycle round -0 2 1 3 2 3 2 0 0 -select * for s1; -ERROR HY000: Sequence 's_db.s1' has been run out. +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 9223372036854775807 cache 1 cycle; -select nextval, round for s1; -nextval round +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 1 cycle; +select next value for s1, round from s1; +next value for s1 round 9223372036854775805 0 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round 9223372036854775806 0 -select nextval, round for s1; -nextval round -9223372036854775807 0 -select nextval, round for s1; -nextval round -9223372036854775804 1 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round +9223372036854775804 0 +select next value for s1, round from s1; +next value for s1 round 9223372036854775805 1 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round 9223372036854775806 1 -select nextval, round for s1; -nextval round -9223372036854775807 1 -select nextval, round for s1; -nextval round -9223372036854775804 2 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round +9223372036854775804 1 +select next value for s1, round from s1; +next value for s1 round 9223372036854775805 2 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round 9223372036854775806 2 +select next value for s1, round from s1; +next value for s1 round +9223372036854775804 2 +select next value for s1, round from s1; +next value for s1 round +9223372036854775805 3 drop sequence s1; -create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775807 cache 10 cycle; -select nextval, round for s1; -nextval round +create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806 cache 10 cycle; +select next value for s1, round from s1; +next value for s1 round 9223372036854775805 0 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round 9223372036854775806 0 -select nextval, round for s1; -nextval round -9223372036854775807 0 -select nextval, round for s1; -nextval round -9223372036854775804 1 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round +9223372036854775804 0 +select next value for s1, round from s1; +next value for s1 round 9223372036854775805 1 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round 9223372036854775806 1 -select nextval, round for s1; -nextval round -9223372036854775807 1 -select nextval, round for s1; -nextval round -9223372036854775804 2 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round +9223372036854775804 1 +select next value for s1, round from s1; +next value for s1 round 9223372036854775805 2 -select nextval, round for s1; -nextval round +select next value for s1, round from s1; +next value for s1 round 9223372036854775806 2 +select next value for s1, round from s1; +next value for s1 round +9223372036854775804 2 +select next value for s1, round from s1; +next value for s1 round +9223372036854775805 3 drop sequence s1; -use s_db; +connection master; drop database s_db; drop user normal_1@'%'; drop user normal_2@'%'; diff --git a/mysql-test/suite/sql_sequence/replication.test b/mysql-test/suite/sql_sequence/replication.test index bf7b6bbdbf7..f49ca5021f1 100644 --- a/mysql-test/suite/sql_sequence/replication.test +++ b/mysql-test/suite/sql_sequence/replication.test @@ -1,14 +1,17 @@ +# +# 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 -# -# This test is originally sequence.test from Jianwei modified for MariaDB -# To test basic sequence functionallity together with replication -# - 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'; @@ -24,23 +27,21 @@ 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; -use s_db; create sequence s1; show create table s1; --sync_slave_with_master -connection slave; -use s_db; + show create table s1; connection master; -use s_db; drop sequence s1; @@ -107,10 +108,15 @@ CREATE TABLE `s2` ( `round` bigint(21) NOT NULL COMMENT 'already how many round' ) ENGINE=InnoDB sequence=1; -insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0); +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; @@ -121,10 +127,6 @@ connection master; --sync_slave_with_master select * from s2; -connection slave; -select * from s2; - - connection master; drop sequence s2; @@ -191,7 +193,6 @@ drop sequence s2; --echo ########################################### connection master; -use s_db; create sequence s2 start with 1 minvalue 1 maxvalue 100000 @@ -250,7 +251,6 @@ create sequence s2 start with 1 --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 @@ -262,7 +262,6 @@ drop sequence s_db.s1; --echo query cache test --echo ########################################### connection master; -use s_db; flush status; show global variables like 'query_cache_type'; @@ -292,7 +291,6 @@ drop sequence s_db.s1; --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); @@ -312,11 +310,9 @@ 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; @@ -340,14 +336,12 @@ drop sequence s_db.s1; --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 engine=innodb; connection master; --sync_slave_with_master connection s_normal_3; -use s_db; select * from s_t; @@ -361,15 +355,13 @@ connection s_normal_3; select next_value from s_t; --echo ------------------------------------------ ---echo master update nextval; +--echo master ALTER SEQUENCE --echo ------------------------------------------ connection m_normal_1; select next value for s_t; -update s_t set next_value= 11; -commit; +alter sequence s_t restart= 11; select * from s_t; -SELECT NEXT VALUE for s_t; connection master; --sync_slave_with_master @@ -397,10 +389,10 @@ connection m_normal_1; select next value for s_t; select * from s_t; --error ER_SEQUENCE_INVALID_DATA -update s_t set min_value=11, max_value=9; +alter sequence s_t minvalue=11 maxvalue=9; select * from s_t; --error ER_SEQUENCE_INVALID_DATA -update s_t set next_value= 12, start=10, min_value=11, max_value=20; +alter sequence s_t restart= 12 start=10 minvalue=11 maxvalue=20; select * from s_t; --echo ------------------------------------------ @@ -425,7 +417,6 @@ drop sequence s_t; --echo normal transaction commit --echo ------------------------------------------ connection m_normal_1; -use s_db; create sequence s_1 cache 5 engine=innodb; create table t_1(id int)engine=innodb; @@ -443,7 +434,6 @@ connection master; --sync_slave_with_master connection s_normal_3; -use s_db; select * from t_1; --echo ------------------------------------------ @@ -472,11 +462,9 @@ 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; @@ -489,7 +477,6 @@ drop table t_1; --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; @@ -506,7 +493,6 @@ connection master; --sync_slave_with_master connection s_normal_3; -use s_db; select * from t_1; --echo ------------------------------------------ @@ -534,11 +520,9 @@ 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; @@ -546,7 +530,6 @@ drop table t_1; --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; @@ -559,8 +542,6 @@ select * from s1; connection master; --sync_slave_with_master -connection slave; -use s_db; select * from s1; --echo ------------------------------------------ @@ -579,12 +560,16 @@ select * from s1; connection master; --sync_slave_with_master -connection slave; -use s_db; select * from s1; -connection m_normal_1; -use s_db; +connection master; +select next value for s1; +select * from s1; +--sync_slave_with_master + +select * from s1; +connection master; + drop sequence s1; --echo ########################################### @@ -606,12 +591,9 @@ select next value for 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 ------------------------------------------ @@ -625,22 +607,23 @@ select next value for s1; set session binlog_format=row; select next value for s1; +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; +connection master; +--sync_slave_with_master + --echo ########################################### --echo test savepoint --echo ########################################### + connection master; ---sync_slave_with_master set session binlog_format=row; create sequence s1 cache 2; @@ -674,11 +657,14 @@ connection master; 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; -drop table t; connection master; --sync_slave_with_master @@ -687,7 +673,6 @@ connection master; --echo test proc --echo ########################################### connection m_normal_1; -use s_db; create table t(id int)engine=innodb; delimiter //; @@ -716,8 +701,6 @@ select * from t; connection master; --sync_slave_with_master -connection slave; -use s_db; select * from t; connection m_normal_1; @@ -730,7 +713,6 @@ drop procedure p2; --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; @@ -767,7 +749,6 @@ drop sequence s1; --echo test function --echo ########################################### connection m_normal_1; -use s_db; create sequence s1 cache 2; create table t1(id int)engine=innodb; @@ -799,7 +780,6 @@ drop sequence s1; --echo test value boundary --echo ########################################### connection m_normal_1; -use s_db; --echo ------------------------------------------ --echo round increment by round @@ -823,15 +803,15 @@ drop sequence s1; create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle; select next value for s1; -select * from s1; +select next_value,round from s1; select next value for s1; -select * from s1; +select next_value,round from s1; select next value for s1; -select * from s1; +select next_value,round from s1; select next value for s1; -select * from s1; +select next_value,round from s1; select next value for s1; -select * from s1; +select next_value,round from s1; drop sequence s1; create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle; @@ -870,12 +850,10 @@ select next value for s1, round from s1; drop sequence s1; 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@'%'; ---sync_slave_with_master --source include/rpl_end.inc diff --git a/mysys/thr_lock.c b/mysys/thr_lock.c index c168957097f..11ac1189577 100644 --- a/mysys/thr_lock.c +++ b/mysys/thr_lock.c @@ -357,7 +357,8 @@ static void check_locks(THR_LOCK *lock, const char *where, data && count < MAX_LOCKS; data=data->next) { - if (data->type != TL_WRITE_CONCURRENT_INSERT) + if (data->type != TL_WRITE_CONCURRENT_INSERT && + data->type != TL_WRITE_ALLOW_WRITE) { fprintf(stderr, "Warning at '%s': Found TL_WRITE_CONCURRENT_INSERT lock mixed with other write lock: %d\n", diff --git a/sql/datadict.cc b/sql/datadict.cc index b5a6a6794ce..edc9fe5681b 100644 --- a/sql/datadict.cc +++ b/sql/datadict.cc @@ -88,6 +88,12 @@ Table_type dd_frm_type(THD *thd, char *path, LEX_CSTRING *engine_name, engine_name->length= 0; dbt= header[3]; + if (((header[39] >> 4) & 3) == HA_CHOICE_YES) + { + DBUG_PRINT("info", ("Sequence found")); + *is_sequence= 1; + } + /* cannot use ha_resolve_by_legacy_type without a THD */ if (thd && dbt < DB_TYPE_FIRST_DYNAMIC) { @@ -99,9 +105,6 @@ Table_type dd_frm_type(THD *thd, char *path, LEX_CSTRING *engine_name, } } - if (((header[39] >> 4) & 3) == HA_CHOICE_YES) - *is_sequence= 1; - /* read the true engine name */ { MY_STAT state; diff --git a/sql/ha_sequence.cc b/sql/ha_sequence.cc index a918da92be2..279e7801b2d 100644 --- a/sql/ha_sequence.cc +++ b/sql/ha_sequence.cc @@ -187,6 +187,7 @@ int ha_sequence::create(const char *name, TABLE *form, int ha_sequence::write_row(uchar *buf) { int error; + sequence_definition tmp_seq; DBUG_ENTER("ha_sequence::write_row"); DBUG_ASSERT(table->record[0] == buf); @@ -199,16 +200,31 @@ int ha_sequence::write_row(uchar *buf) if (unlikely(sequence->initialized != SEQUENCE::SEQ_READY_TO_USE)) DBUG_RETURN(HA_ERR_WRONG_COMMAND); - /* - User tries to write a row - - Check that the new row is an accurate object - - Update the first row in the table - */ + if (!sequence_locked) // If not from next_value() + { + /* + User tries to write a full row directly to the sequence table with + INSERT or LOAD DATA. + + - Get an exclusive lock for the table. This is needed to ensure that + we excute all full inserts (same as ALTER SEQUENCE) in same order + on master and slaves + - Check that we are only using one table. + This is to avoid deadlock problems when upgrading lock to exlusive. + - Check that the new row is an accurate SEQUENCE object + */ - sequence_definition tmp_seq; - tmp_seq.read_fields(table); - if (tmp_seq.check_and_adjust()) - DBUG_RETURN(HA_ERR_SEQUENCE_INVALID_DATA); + THD *thd= table->in_use; + if (thd->lock->table_count != 1) + DBUG_RETURN(ER_WRONG_INSERT_INTO_SEQUENCE); + if (thd->mdl_context.upgrade_shared_lock(table->mdl_ticket, MDL_EXCLUSIVE, + thd->variables.lock_wait_timeout)) + DBUG_RETURN(ER_LOCK_WAIT_TIMEOUT); + + tmp_seq.read_fields(table); + if (tmp_seq.check_and_adjust(0)) + DBUG_RETURN(HA_ERR_SEQUENCE_INVALID_DATA); + } /* Lock sequence to ensure that no one can come in between @@ -235,39 +251,6 @@ int ha_sequence::write_row(uchar *buf) } -int ha_sequence::update_row(const uchar *old_data, const uchar *new_data) -{ - int error; - sequence_definition tmp_seq; - DBUG_ENTER("ha_sequence::update_row"); - DBUG_ASSERT(new_data == table->record[0]); - - row_already_logged= 0; - - tmp_seq.read_fields(table); - if (tmp_seq.check_and_adjust()) - DBUG_RETURN(HA_ERR_SEQUENCE_INVALID_DATA); - - /* - Lock sequence to ensure that no one can come in between - while sequence, table and binary log is updated. - */ - sequence->lock(); - if (!(error= file->update_row(old_data, new_data))) - { - sequence->copy(&tmp_seq); - rows_changed++; - /* We have to do the logging while we hold the sequence mutex */ - error= binlog_log_row(table, old_data, new_data, - Update_rows_log_event::binlog_row_logging_function); - row_already_logged= 1; - } - sequence->all_values_used= 0; - sequence->unlock(); - DBUG_RETURN(error); -} - - /* Inherit the sequence base table flags. */ @@ -346,6 +329,9 @@ void ha_sequence::print_error(int error, myf errflag) case HA_ERR_WRONG_COMMAND: my_error(ER_ILLEGAL_HA, MYF(0), "SEQUENCE", sequence_db, sequence_name); DBUG_VOID_RETURN; + case ER_WRONG_INSERT_INTO_SEQUENCE: + my_error(error, MYF(0)); + DBUG_VOID_RETURN; } file->print_error(error, errflag); DBUG_VOID_RETURN; diff --git a/sql/ha_sequence.h b/sql/ha_sequence.h index f753d038114..c5a98c2a47b 100644 --- a/sql/ha_sequence.h +++ b/sql/ha_sequence.h @@ -69,9 +69,10 @@ public: HA_CREATE_INFO *create_info); handler *clone(const char *name, MEM_ROOT *mem_root); int write_row(uchar *buf); - int update_row(const uchar *old_data, const uchar *new_data); Table_flags table_flags() const; - /* One can't delete from sequence engine */ + /* One can't update or delete from sequence engine */ + int update_row(const uchar *old_data, const uchar *new_data) + { return HA_ERR_WRONG_COMMAND; } int delete_row(const uchar *buf) { return HA_ERR_WRONG_COMMAND; } /* One can't delete from sequence engine */ diff --git a/sql/log_event.cc b/sql/log_event.cc index 383972d1a54..3c062975041 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -12525,7 +12525,9 @@ Rows_log_event::write_row(rpl_group_info *rgi, TODO: Add safety measures against infinite looping. */ - while ((error= table->file->ha_write_row(table->record[0]))) + if (table->s->sequence) + error= update_sequence(); + else while ((error= table->file->ha_write_row(table->record[0]))) { if (error == HA_ERR_LOCK_DEADLOCK || error == HA_ERR_LOCK_WAIT_TIMEOUT || @@ -12692,6 +12694,33 @@ Rows_log_event::write_row(rpl_group_info *rgi, DBUG_RETURN(error); } + +int Rows_log_event::update_sequence() +{ + TABLE *table= m_table; // pointer to event's table + + if (!bitmap_is_set(table->rpl_write_set, MIN_VALUE_FIELD_NO)) + { + /* This event come from a setval function executed on the master. + Update the sequence next_number and round, like we do with setval() + */ + my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, + table->read_set); + longlong nextval= table->field[NEXT_FIELD_NO]->val_int(); + longlong round= table->field[ROUND_FIELD_NO]->val_int(); + dbug_tmp_restore_column_map(table->read_set, old_map); + + return table->s->sequence->set_value(table, nextval, round, 0); + } + + /* + Update all fields in table and update the active sequence, like with + ALTER SEQUENCE + */ + return table->file->ha_write_row(table->record[0]); +} + + #endif int diff --git a/sql/log_event.h b/sql/log_event.h index e79c88c28f1..3497ffab26d 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -4606,6 +4606,7 @@ protected: int find_key(); // Find a best key to use in find_row() int find_row(rpl_group_info *); int write_row(rpl_group_info *, const bool); + int update_sequence(); // Unpack the current row into m_table->record[0], but with // a different columns bitmap. diff --git a/sql/mdl.cc b/sql/mdl.cc index f1a505f3d84..f751a8e298e 100644 --- a/sql/mdl.cc +++ b/sql/mdl.cc @@ -2320,12 +2320,6 @@ MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket, if (mdl_ticket->has_stronger_or_equal_type(new_type)) DBUG_RETURN(FALSE); - /* Only allow upgrades from SHARED_UPGRADABLE/NO_WRITE/NO_READ_WRITE/READ */ - DBUG_ASSERT(mdl_ticket->m_type == MDL_SHARED_UPGRADABLE || - mdl_ticket->m_type == MDL_SHARED_NO_WRITE || - mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE || - mdl_ticket->m_type == MDL_SHARED_READ); - mdl_xlock_request.init(&mdl_ticket->m_lock->key, new_type, MDL_TRANSACTION); diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 97a6421ec5c..f19d953fc52 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7486,3 +7486,5 @@ ER_UNKNOWN_SEQUENCES 42S02 eng "Unknown SEQUENCE: '%-.300s'" ER_UNKNOWN_VIEW 42S02 eng "Unknown VIEW: '%-.300s'" +ER_WRONG_INSERT_INTO_SEQUENCE + eng "Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a squence object (like with mysqldump). If you want to change the SEQUENCE, use ALTER SEQUENCE instead." diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 5b8aa9ce2aa..0468d9cc9aa 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -5922,8 +5922,7 @@ int THD::decide_logging_format(TABLE_LIST *tables) table->table->file->ht) multi_write_engine= TRUE; if (table->table->s->non_determinstic_insert && - lex->sql_command != SQLCOM_CREATE_SEQUENCE && - lex->sql_command != SQLCOM_CREATE_TABLE) + !(sql_command_flags[lex->sql_command] & CF_SCHEMA_CHANGE)) has_write_tables_with_unsafe_statements= true; trans= table->table->file->has_transactions(); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index edf4f77f70c..f4e232f8476 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -557,7 +557,7 @@ void init_update_queries(void) CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS | CF_INSERTS_DATA; sql_command_flags[SQLCOM_ALTER_SEQUENCE]= CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND | - CF_AUTO_COMMIT_TRANS; + CF_AUTO_COMMIT_TRANS | CF_SCHEMA_CHANGE; sql_command_flags[SQLCOM_TRUNCATE]= CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS; sql_command_flags[SQLCOM_DROP_TABLE]= CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS | CF_SCHEMA_CHANGE; @@ -1456,7 +1456,7 @@ static my_bool deny_updates_if_read_only_option(THD *thd, if (lex->sql_command == SQLCOM_UPDATE_MULTI) DBUG_RETURN(FALSE); - /* Check if we created and dropped temporary tables */ + /* Check if we created or dropped temporary tables */ if ((sql_command_flags[lex->sql_command] & CF_SCHEMA_CHANGE) && lex->tmp_table()) DBUG_RETURN(FALSE); diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc index a6e7b073251..1bf360a4f41 100644 --- a/sql/sql_sequence.cc +++ b/sql/sql_sequence.cc @@ -20,6 +20,7 @@ #include "sql_sequence.h" #include "ha_sequence.h" #include "sql_base.h" +#include "sql_table.h" // write_bin_log #include "transaction.h" #include "lock.h" #include "sql_acl.h" @@ -70,50 +71,60 @@ static Field_definition sequence_structure[]= Check whether sequence values are valid. Sets default values for fields that are not used, according to Oracle spec. - Note that reserved_until is not checked as it's ok that it's outside of - the range (to indicate that sequence us used up). - RETURN VALUES false valid true invalid */ -bool sequence_definition::check_and_adjust() +bool sequence_definition::check_and_adjust(bool set_reserved_until) { longlong max_increment; DBUG_ENTER("sequence_definition::check"); + if (!(real_increment= increment)) + real_increment= global_system_variables.auto_increment_increment; + /* If min_value is not set, set it to LONGLONG_MIN or 1, depending on increment */ if (!(used_fields & seq_field_used_min_value)) - min_value= increment < 0 ? LONGLONG_MIN+1 : 1; + min_value= real_increment < 0 ? LONGLONG_MIN+1 : 1; /* If min_value is not set, set it to LONGLONG_MAX or -1, depending on - increment + real_increment */ if (!(used_fields & seq_field_used_max_value)) - max_value= increment < 0 ? -1 : LONGLONG_MAX-1; + max_value= real_increment < 0 ? -1 : LONGLONG_MAX-1; if (!(used_fields & seq_field_used_start)) { - /* Use min_value or max_value for start depending on increment */ - start= increment < 0 ? max_value : min_value; + /* Use min_value or max_value for start depending on real_increment */ + start= real_increment < 0 ? max_value : min_value; } - /* To ensure that cache * increment will never overflow */ - max_increment= increment ? labs(increment) : MAX_AUTO_INCREMENT_VALUE; + if (set_reserved_until) + reserved_until= start; + + adjust_values(reserved_until); + + /* To ensure that cache * real_increment will never overflow */ + max_increment= (real_increment ? + labs(real_increment) : + MAX_AUTO_INCREMENT_VALUE); if (max_value >= start && max_value > min_value && start >= min_value && max_value != LONGLONG_MAX && min_value != LONGLONG_MIN && - cache < (LONGLONG_MAX - max_increment) / max_increment) + cache < (LONGLONG_MAX - max_increment) / max_increment && + ((real_increment > 0 && reserved_until >= min_value) || + (real_increment < 0 && reserved_until <= max_value))) DBUG_RETURN(FALSE); - DBUG_RETURN(TRUE); + + DBUG_RETURN(TRUE); // Error } @@ -448,7 +459,7 @@ int SEQUENCE::read_stored_values() Adjust values after reading a the stored state */ -void SEQUENCE::adjust_values(longlong next_value) +void sequence_definition::adjust_values(longlong next_value) { next_free_value= next_value; if (!(real_increment= increment)) @@ -531,17 +542,26 @@ int sequence_definition::write_initial_sequence(TABLE *table) Store current sequence values into the sequence table */ -int sequence_definition::write(TABLE *table) +int sequence_definition::write(TABLE *table, bool all_fields) { int error; MY_BITMAP *save_rpl_write_set, *save_write_set; - /* Log a full insert (ok as table is small) */ save_rpl_write_set= table->rpl_write_set; + if (likely(!all_fields)) + { + /* Only write next_value and round to binary log */ + table->rpl_write_set= &table->def_rpl_write_set; + bitmap_clear_all(table->rpl_write_set); + bitmap_set_bit(table->rpl_write_set, NEXT_FIELD_NO); + bitmap_set_bit(table->rpl_write_set, ROUND_FIELD_NO); + } + else + table->rpl_write_set= &table->s->all_set; /* Update table */ save_write_set= table->write_set; - table->rpl_write_set= table->write_set= &table->s->all_set; + table->write_set= &table->s->all_set; store_fields(table); /* Tell ha_sequence::write_row that we already hold the mutex */ ((ha_sequence*) table->file)->sequence_locked= 1; @@ -612,7 +632,7 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) The cache value is checked on insert so the following can't overflow */ - add_to= cache ? real_increment * cache : 1; + add_to= cache ? real_increment * cache : real_increment; out_of_values= 0; if (real_increment > 0) @@ -651,7 +671,7 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) DBUG_RETURN(next_value(table, 1, error)); } - if ((*error= write(table))) + if ((*error= write(table, 0))) { reserved_until= org_reserved_until; next_free_value= res_value; @@ -751,7 +771,7 @@ bool SEQUENCE::set_value(TABLE *table, longlong next_val, ulonglong next_round, needs_to_be_stored) { reserved_until= next_free_value; - if (write(table)) + if (write(table, 0)) { reserved_until= org_reserved_until; next_free_value= org_next_free_value; @@ -842,7 +862,7 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) /* Let check_and_adjust think all fields are used */ new_seq->used_fields= ~0; - if (new_seq->check_and_adjust()) + if (new_seq->check_and_adjust(0)) { my_error(ER_SEQUENCE_INVALID_DATA, MYF(0), first_table->db, @@ -851,17 +871,22 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) goto end; } - if (!(error= new_seq->write(table))) + if (!(error= new_seq->write(table, 1))) { /* Store the sequence values in table share */ table->s->sequence->copy(new_seq); } - trans_commit_stmt(thd); - trans_commit_implicit(thd); + else + table->file->print_error(error, MYF(0)); + if (trans_commit_stmt(thd)) + error= 1; + if (trans_commit_implicit(thd)) + error= 1; + if (!error) + error= write_bin_log(thd, 1, thd->query(), thd->query_length()); if (!error) my_ok(thd); end: - close_thread_tables(thd); DBUG_RETURN(error); } diff --git a/sql/sql_sequence.h b/sql/sql_sequence.h index ffe5ded4cff..b9d6326fcd1 100644 --- a/sql/sql_sequence.h +++ b/sql/sql_sequence.h @@ -26,6 +26,11 @@ #define seq_field_used_restart 64 #define seq_field_used_restart_value 128 +/* Field position in sequence table for some fields we refer to directly */ +#define NEXT_FIELD_NO 0 +#define MIN_VALUE_FIELD_NO 1 +#define ROUND_FIELD_NO 7 + /** sequence_definition is used when defining a sequence as part of create */ @@ -48,17 +53,26 @@ public: uint used_fields; // Which fields where used in CREATE longlong restart; // alter sequence restart value - bool check_and_adjust(); + bool check_and_adjust(bool set_reserved_until); void store_fields(TABLE *table); void read_fields(TABLE *table); int write_initial_sequence(TABLE *table); - int write(TABLE *table); + int write(TABLE *table, bool all_fields); + /* This must be called after sequence data has been updated */ + void adjust_values(longlong next_value); inline void print_dbug() { DBUG_PRINT("sequence", ("reserved: %lld start: %lld increment: %lld min_value: %lld max_value: %lld cache: %lld round: %lld", reserved_until, start, increment, min_value, max_value, cache, round)); } +protected: + /* + The following values are the values from sequence_definition + merged with global auto_increment_offset and auto_increment_increment + */ + longlong real_increment; + longlong next_free_value; }; /** @@ -87,8 +101,6 @@ public: { mysql_mutex_unlock(&mutex); } - /* This must be called after sequence data has been updated */ - void adjust_values(longlong next_value); void copy(sequence_definition *seq) { sequence_definition::operator= (*seq); @@ -124,12 +136,6 @@ public: private: TABLE *table; mysql_mutex_t mutex; - longlong next_free_value; - /* - The following values are the values from sequence_definition - merged with global auto_increment_offset and auto_increment_increment - */ - longlong real_increment; }; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index a84cc5bbbf9..5c4ea4734f3 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2243,7 +2243,9 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists, uint32 comment_len; built_query.set_charset(thd->charset()); - built_query.append("DROP TABLE "); + built_query.append("DROP "); + built_query.append(object_to_drop); + built_query.append(' '); if (if_exists) built_query.append("IF EXISTS "); @@ -5556,9 +5558,8 @@ int mysql_discard_or_import_tablespace(THD *thd, error= trans_commit_stmt(thd); if (trans_commit_implicit(thd)) error=1; - if (error) - goto err; - error= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); + if (!error) + error= write_bin_log(thd, FALSE, thd->query(), thd->query_length()); err: thd->tablespace_op=FALSE; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index c401a1d8b3a..8ebf7e0e405 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2508,7 +2508,7 @@ create: { LEX *lex= thd->lex; - if (lex->create_info.seq_create_info->check_and_adjust()) + if (lex->create_info.seq_create_info->check_and_adjust(1)) { my_error(ER_SEQUENCE_INVALID_DATA, MYF(0), lex->select_lex.table_list.first->db, diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index e4cdd81ce80..1ad75c01676 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1949,7 +1949,7 @@ create: { LEX *lex= thd->lex; - if (lex->create_info.seq_create_info->check_and_adjust()) + if (lex->create_info.seq_create_info->check_and_adjust(1)) { my_error(ER_SEQUENCE_INVALID_DATA, MYF(0), lex->select_lex.table_list.first->db, diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 548c4a2112e..b21d631be6a 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -2304,7 +2304,7 @@ bool ha_maria::is_crashed() const #define CHECK_UNTIL_WE_FULLY_IMPLEMENTED_VERSIONING(msg) \ do { \ - if (file->lock.type == TL_WRITE_CONCURRENT_INSERT) \ + if (file->lock.type == TL_WRITE_CONCURRENT_INSERT && !table->s->sequence) \ { \ my_error(ER_CHECK_NOT_IMPLEMENTED, MYF(0), msg); \ return 1; \ |