diff options
31 files changed, 1399 insertions, 85 deletions
diff --git a/include/my_base.h b/include/my_base.h index ea8fd623b28..57b16ad985b 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -202,7 +202,9 @@ enum ha_extra_function { HA_EXTRA_DETACH_CHILDREN, HA_EXTRA_DETACH_CHILD, /* Inform handler we will force a close as part of flush */ - HA_EXTRA_PREPARE_FOR_FORCED_CLOSE + HA_EXTRA_PREPARE_FOR_FORCED_CLOSE, + /* Inform handler that we will do an alter table */ + HA_EXTRA_PREPARE_FOR_ALTER_TABLE, }; /* Compatible option, to be deleted in 6.0 */ diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 778bfb13bd3..09c8bf9813b 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1396,7 +1396,7 @@ performance-schema-max-rwlock-instances -1 performance-schema-max-socket-classes 10 performance-schema-max-socket-instances -1 performance-schema-max-stage-classes 150 -performance-schema-max-statement-classes 189 +performance-schema-max-statement-classes 190 performance-schema-max-table-handles -1 performance-schema-max-table-instances -1 performance-schema-max-thread-classes 50 diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result new file mode 100644 index 00000000000..43afe2377c3 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.result @@ -0,0 +1,238 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Test alter sequence +# +CREATE SEQUENCE t1 nocache engine=myisam; +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 0 0 0 +select next value for t1; +next value for t1 +1 +alter sequence t1 start=50; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +2 1 9223372036854775806 50 1 0 0 0 +select next value for t1; +next value for t1 +2 +alter sequence t1 minvalue=-100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_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; +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 +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 +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 nocache; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +alter sequence t1 cache=100; +flush tables; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM +alter sequence t1 nocache; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +flush tables; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 0 0 0 +select next value for t1; +next value for t1 +1 +select next value for t1; +next value for t1 +2 +select next value for t1; +next value for t1 +3 +select next_value, round from t1; +next_value round +4 0 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100 engine=myisam; +alter sequence t1 no maxvalue; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 1000 0 0 +alter sequence t1 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +alter sequence t1 nocycle; +alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +15 10 20 15 1 1000 1 0 +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +15 +16 +17 +18 +19 +20 +10 +11 +12 +13 +alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +17 +18 +19 +20 +10 +11 +12 +13 +14 +15 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50 minvalue=-100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM +select * from t1; +next_value min_value max_value start increment cache cycle round +1 -100 100 50 -2 1000 0 0 +select NEXT VALUE for t1 from seq_1_to_10; +NEXT VALUE for t1 +1 +-1 +-3 +-5 +-7 +-9 +-11 +-13 +-15 +-17 +drop sequence t1; +# +# InnoDB (some things work different with InnoDB) + +CREATE SEQUENCE t1 cache 10 engine=innodb; +select * from t1; +next_value min_value max_value start increment cache cycle round +1 1 9223372036854775806 1 1 10 0 0 +select next value for t1; +next value for t1 +1 +alter sequence t1 start=100; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB +select * from t1; +next_value min_value max_value start increment cache cycle round +11 1 9223372036854775806 100 1 10 0 0 +select next value for t1; +next value for t1 +11 +drop sequence t1; +# +# ALTER TABLE +# +CREATE SEQUENCE t1 engine=innodb; +select next value for t1; +next value for t1 +1 +alter table t1 rename t2; +select next value for t2; +next value for t2 +1001 +rename table t2 to t1; +select next value for t1; +next value for t1 +2001 +alter table t1 comment="foo"; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' +alter table t1 engine=myisam; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' +alter table t1 engine=innodb; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' +select * from t1; +next_value min_value max_value start increment cache cycle round +3001 1 9223372036854775806 1 1 1000 0 0 +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 minvalue=100; +ERROR HY000: Sequence 'test.t1' values are conflicting +drop sequence t1; +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 minvalue=25 maxvalue=20; +ERROR HY000: Sequence 'test.t1' values are conflicting +drop sequence t1; +create table t1 (a int); +alter sequence t1 minvalue=100; +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; +alter sequence if exists t1 minvalue=100; +Warnings: +Note 4067 Unknown SEQUENCE: 'test.t1' +alter sequence t1 minvalue=100; +ERROR 42S02: Table 'test.t1' doesn't exist +create sequence t1; +alter sequence t1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +drop sequence t1; +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50; +select next value for t1; +next value for t1 +1 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +select * from t1; +next_value min_value max_value start increment cache cycle round +0 1 100 50 -2 1000 0 0 +alter sequence t1 restart; +select next value for t1; +next value for t1 +50 +alter sequence t1 restart with 90; +select next value for t1; +next value for t1 +90 +drop sequence t1; diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test new file mode 100644 index 00000000000..7454f5900f7 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.test @@ -0,0 +1,139 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test alter sequence +--echo # + +CREATE SEQUENCE t1 nocache engine=myisam; +select * from t1; +select next value for t1; +alter sequence t1 start=50; +show create sequence t1; +select * from t1; +select next value for t1; + +alter sequence t1 minvalue=-100; +show create sequence t1; +select * from t1; +alter sequence t1 minvalue=100 start=100; +show create sequence t1; +select * from t1; + +alter sequence t1 maxvalue=500; +show create sequence t1; +select * from t1; +drop sequence t1; + +CREATE SEQUENCE t1 engine=myisam; +alter sequence t1 nocache; +show create sequence t1; +alter sequence t1 cache=100; +flush tables; +show create sequence t1; +alter sequence t1 nocache; +show create sequence t1; +flush tables; +show create sequence t1; +select * from t1; +select next value for t1; +select next value for t1; +select next value for t1; +select next_value, round from t1; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100 engine=myisam; +alter sequence t1 no maxvalue; +show create sequence t1; +select * from t1; +alter sequence t1 cycle; +show create sequence t1; +alter sequence t1 nocycle; +alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; +show create sequence t1; +select * from t1; +select NEXT VALUE for t1 from seq_1_to_10; +alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle; +select NEXT VALUE for t1 from seq_1_to_10; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50 minvalue=-100; +show create sequence t1; +select * from t1; +select NEXT VALUE for t1 from seq_1_to_10; +drop sequence t1; + +--echo # +--echo # InnoDB (some things work different with InnoDB) +--echo + +CREATE SEQUENCE t1 cache 10 engine=innodb; +select * from t1; +select next value for t1; +alter sequence t1 start=100; +show create sequence t1; +select * from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # ALTER TABLE +--echo # + +CREATE SEQUENCE t1 engine=innodb; +select next value for t1; +alter table t1 rename t2; +select next value for t2; +rename table t2 to t1; +select next value for t1; +alter table t1 comment="foo"; +show create sequence t1; +alter table t1 engine=myisam; +show create sequence t1; +alter table t1 engine=innodb; +show create sequence t1; +select * from t1; +drop sequence t1; + +# +# Some error testing +# + +CREATE SEQUENCE t1 engine=myisam; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=100; +drop sequence t1; + +CREATE SEQUENCE t1 engine=myisam; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=25 maxvalue=20; +drop sequence t1; + +create table t1 (a int); +--error ER_NOT_SEQUENCE +alter sequence t1 minvalue=100; +drop table t1; + +alter sequence if exists t1 minvalue=100; +--error ER_NO_SUCH_TABLE +alter sequence t1 minvalue=100; + +create sequence t1; +--error ER_PARSE_ERROR +alter sequence t1; +drop sequence t1; + +CREATE SEQUENCE t1 maxvalue=100; +alter sequence t1 increment=-2 start with 50; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +select * from t1; +alter sequence t1 restart; +select next value for t1; +alter sequence t1 restart with 90; +select next value for t1; +drop sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 59dfe62acee..4962752c7d9 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -186,6 +186,8 @@ create sequence t1 start with 10 maxvalue=9223372036854775807; ERROR HY000: Sequence 'test.t1' values are conflicting create sequence t1 start with 10 minvalue=-9223372036854775808; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '9223372036854775808' at line 1 +create sequence t1 RESTART WITH 10; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RESTART' at line 1 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; drop sequence t1; create sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test index 6cb6dedd91b..cf094c2cedd 100644 --- a/mysql-test/suite/sql_sequence/create.test +++ b/mysql-test/suite/sql_sequence/create.test @@ -118,6 +118,8 @@ create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; create sequence t1 start with 10 maxvalue=9223372036854775807; --error ER_PARSE_ERROR create sequence t1 start with 10 minvalue=-9223372036854775808; +--error ER_PARSE_ERROR +create sequence t1 RESTART WITH 10; # This should probably give an error create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index fc28152a2b7..84f91bc0bdd 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -390,6 +390,16 @@ next_value min_value max_value start increment cache cycle round select next value for s1; next value for s1 3984356 +explain extended select next value for s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select nextval(`test`.`s1`) AS `next value for s1` +explain extended select previous value for s1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select lastval(`test`.`s1`) AS `previous value for s1` drop sequence s1; create table t1 (a int); select next value for t1; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index 426ee5709a1..472feafb2c6 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -182,9 +182,10 @@ drop table t1,s1; CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb'; select * from s1; select next value for s1; +explain extended select next value for s1; +explain extended select previous value for s1; drop sequence s1; - # # Some error testing # diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result new file mode 100644 index 00000000000..2fe46ff90c2 --- /dev/null +++ b/mysql-test/suite/sql_sequence/setval.result @@ -0,0 +1,246 @@ +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Test setval function +# +CREATE SEQUENCE t1 cache 10 engine=myisam; +select next_value,round from t1; +next_value round +1 0 +do setval(t1,10); +select next_value,round from t1; +next_value round +11 0 +select next value for t1; +next value for t1 +11 +do setval(t1,12,1); +select next_value,round from t1; +next_value round +21 0 +select next value for t1; +next value for t1 +13 +do setval(t1,15,0); +select next_value,round from t1; +next_value round +21 0 +select next value for t1; +next value for t1 +15 +select setval(t1,16,0); +setval(t1,16,0) +16 +select next value for t1; +next value for t1 +16 +do setval(t1,1000,0); +select next value for t1; +next value for t1 +1000 +select next_value,round from t1; +next_value round +1010 0 +do setval(t1,2000,0); +select next value for t1; +next value for t1 +2000 +select next_value,round from t1; +next_value round +2010 0 +select setval(t1,1000,0); +setval(t1,1000,0) +NULL +select next value for t1; +next value for t1 +2001 +select setval(t1,1000,TRUE); +setval(t1,1000,TRUE) +NULL +select next value for t1; +next value for t1 +2002 +select next_value,round from t1; +next_value round +2010 0 +select setval(t1,2002,0); +setval(t1,2002,0) +NULL +select next value for t1; +next value for t1 +2003 +select setval(t1,2010,0); +setval(t1,2010,0) +2010 +select next value for t1; +next value for t1 +2010 +select next_value,round from t1; +next_value round +2020 0 +drop sequence t1; +# +# Testing with cycle +# +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +next_value round +1 0 +select setval(t1,100,0); +setval(t1,100,0) +100 +select next_value,round from t1; +next_value round +100 0 +select next value for t1; +next value for t1 +100 +select next_value,round from t1; +next_value round +101 0 +select setval(t1,100,0); +setval(t1,100,0) +NULL +select next_value,round from t1; +next_value round +101 0 +select next value for t1; +next value for t1 +1 +select next_value,round from t1; +next_value round +11 1 +select next value for t1; +next value for t1 +2 +select setval(t1,100,0,1); +setval(t1,100,0,1) +100 +select next_value,round from t1; +next_value round +100 1 +select next value for t1; +next value for t1 +100 +select setval(t1,100,1,2); +setval(t1,100,1,2) +100 +select next_value,round from t1; +next_value round +101 2 +select next value for t1; +next value for t1 +1 +select setval(t1,100,0,3); +setval(t1,100,0,3) +100 +select next_value,round from t1; +next_value round +100 3 +select next value for t1; +next value for t1 +100 +drop sequence t1; +# +# Testing extreme values +# +CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb; +select next_value,round from t1; +next_value round +1 0 +select setval(t1,200); +setval(t1,200) +200 +select next_value,round from t1; +next_value round +101 0 +select next value for t1; +ERROR HY000: Sequence 'test.t1' has run out +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +next_value round +1 0 +select setval(t1,200); +setval(t1,200) +200 +select next_value,round from t1; +next_value round +101 0 +select next value for t1; +next value for t1 +1 +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10); +setval(t1,-10) +-10 +select next_value,round from t1; +next_value round +-20 0 +select next value for t1; +next value for t1 +-20 +select setval(t1,-15); +setval(t1,-15) +NULL +select next_value,round from t1; +next_value round +-120 0 +select next value for t1; +next value for t1 +-30 +select setval(t1,-500,FALSE); +setval(t1,-500,FALSE) +-500 +select next value for t1; +next value for t1 +-500 +select next value for t1; +next value for t1 +-510 +select setval(t1,-525,0); +setval(t1,-525,0) +-525 +select next value for t1; +next value for t1 +-525 +select next value for t1; +next value for t1 +-535 +drop sequence t1; +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10,0); +setval(t1,-10,0) +-10 +select next_value,round from t1; +next_value round +-10 0 +select next value for t1; +next value for t1 +-10 +drop sequence t1; +# +# Other testing +# +CREATE SEQUENCE t1; +select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1); +setval(t1,10,0) setval(t1,15,1) setval(t1,5,1) +10 15 NULL +select next value for t1; +next value for t1 +16 +select next_value,round from t1; +next_value round +1016 0 +explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select setval(`test`.`t1`,100,1,0) AS `setval(t1,100)`,setval(`test`.`t1`,100,1,0) AS `setval(t1,100,TRUE)`,setval(`test`.`t1`,100,0,50) AS `setval(t1,100,FALSE,50)` +drop sequence t1; +create table t1 (a int); +select setval(t1,10); +ERROR 42S02: 'test.t1' is not a SEQUENCE +drop table t1; diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test new file mode 100644 index 00000000000..fe0c0669494 --- /dev/null +++ b/mysql-test/suite/sql_sequence/setval.test @@ -0,0 +1,126 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test setval function +--echo # + +CREATE SEQUENCE t1 cache 10 engine=myisam; +select next_value,round from t1; +do setval(t1,10); +select next_value,round from t1; +select next value for t1; +do setval(t1,12,1); +select next_value,round from t1; +select next value for t1; +do setval(t1,15,0); +select next_value,round from t1; +select next value for t1; +select setval(t1,16,0); +select next value for t1; +do setval(t1,1000,0); +select next value for t1; +select next_value,round from t1; +do setval(t1,2000,0); +select next value for t1; +select next_value,round from t1; +# Set smaller value +select setval(t1,1000,0); +select next value for t1; +select setval(t1,1000,TRUE); +select next value for t1; +select next_value,round from t1; +select setval(t1,2002,0); +select next value for t1; +select setval(t1,2010,0); +select next value for t1; +select next_value,round from t1; +drop sequence t1; + +--echo # +--echo # Testing with cycle +--echo # + +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +select setval(t1,100,0); +select next_value,round from t1; +select next value for t1; +select next_value,round from t1; +select setval(t1,100,0); +select next_value,round from t1; +select next value for t1; +select next_value,round from t1; +select next value for t1; +select setval(t1,100,0,1); +select next_value,round from t1; +select next value for t1; +select setval(t1,100,1,2); +select next_value,round from t1; +select next value for t1; +select setval(t1,100,0,3); +select next_value,round from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # Testing extreme values +--echo # + +CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb; +select next_value,round from t1; +select setval(t1,200); +select next_value,round from t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb; +select next_value,round from t1; +select setval(t1,200); +select next_value,round from t1; +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10); +select next_value,round from t1; +select next value for t1; +select setval(t1,-15); +select next_value,round from t1; +select next value for t1; +select setval(t1,-500,FALSE); +select next value for t1; +select next value for t1; +select setval(t1,-525,0); +select next value for t1; +select next value for t1; +drop sequence t1; + +CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10; +select setval(t1,-10,0); +select next_value,round from t1; +select next value for t1; +drop sequence t1; + +--echo # +--echo # Other testing +--echo # + +CREATE SEQUENCE t1; +select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1); +select next value for t1; +select next_value,round from t1; +explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50); +drop sequence t1; + +# +# Some error testing +# + +create table t1 (a int); +--error ER_NOT_SEQUENCE +select setval(t1,10); +drop table t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index fcc93fbac82..0c8647fc946 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -3063,9 +3063,9 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES SESSION_VALUE NULL -GLOBAL_VALUE 189 +GLOBAL_VALUE 190 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 189 +DEFAULT_VALUE 190 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum number of statement instruments. diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 6ce17090efd..f293c40ccdd 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -7149,6 +7149,7 @@ int ha_partition::extra(enum ha_extra_function operation) case HA_EXTRA_QUICK: case HA_EXTRA_PREPARE_FOR_DROP: case HA_EXTRA_FLUSH_CACHE: + case HA_EXTRA_PREPARE_FOR_ALTER_TABLE: { DBUG_RETURN(loop_extra(operation)); } diff --git a/sql/ha_sequence.cc b/sql/ha_sequence.cc index 5ff5a4eb213..a918da92be2 100644 --- a/sql/ha_sequence.cc +++ b/sql/ha_sequence.cc @@ -201,7 +201,7 @@ int ha_sequence::write_row(uchar *buf) /* User tries to write a row - - Check that row is an accurate object + - Check that the new row is an accurate object - Update the first row in the table */ @@ -290,6 +290,25 @@ int ha_sequence::info(uint flag) DBUG_RETURN(false); } + +int ha_sequence::extra(enum ha_extra_function operation) +{ + if (operation == HA_EXTRA_PREPARE_FOR_ALTER_TABLE) + { + /* In case of ALTER TABLE allow ::write_row() to copy rows */ + sequence->initialized= SEQUENCE::SEQ_IN_PREPARE; + } + return file->extra(operation); +} + +bool ha_sequence::check_if_incompatible_data(HA_CREATE_INFO *create_info, + uint table_changes) +{ + /* Table definition is locked for SEQUENCE tables */ + return(COMPATIBLE_DATA_YES); +} + + int ha_sequence::external_lock(THD *thd, int lock_type) { int error= file->external_lock(thd, lock_type); diff --git a/sql/ha_sequence.h b/sql/ha_sequence.h index 3aacd62c5cb..f753d038114 100644 --- a/sql/ha_sequence.h +++ b/sql/ha_sequence.h @@ -84,6 +84,10 @@ public: int info(uint); LEX_CSTRING *engine_name() { return hton_name(file->ht); } int external_lock(THD *thd, int lock_type); + int extra(enum ha_extra_function operation); + /* For ALTER ONLINE TABLE */ + bool check_if_incompatible_data(HA_CREATE_INFO *create_info, + uint table_changes); /* Functions that are directly mapped to the underlying handler */ int rnd_init(bool scan) diff --git a/sql/item_func.cc b/sql/item_func.cc index 8df5c301f3a..c3048a04d61 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -6786,7 +6786,7 @@ longlong Item_func_nextval::val_int() } } entry->null_value= null_value= 0; - value= table->s->sequence->next_value(table,0, &error); + value= table->s->sequence->next_value(table, 0, &error); entry->value= value; entry->set_version(table); @@ -6878,3 +6878,76 @@ longlong Item_func_lastval::val_int() null_value= entry->null_value; DBUG_RETURN(entry->value); } + + +/* + Sets next value to be returned from sequences + + SELECT setval('foo', 42, 0); Next nextval will return 43 + SELECT setval('foo', 42, 0, true); Same as above + SELECT setval('foo', 42, 0, false); Next nextval will return 42 +*/ + +longlong Item_func_setval::val_int() +{ + longlong value; + int error; + TABLE *table= table_list->table; + DBUG_ASSERT(table && table->s->sequence); + DBUG_ENTER("Item_func_setval::val_int"); + + value= nextval; + error= table->s->sequence->set_value(table, nextval, round, is_used); + if (error) + { + null_value= 1; + value= 0; + } + DBUG_RETURN(value); +} + + +/* Print for setval */ + +void Item_func_setval::print(String *str, enum_query_type query_type) +{ + char d_name_buff[MAX_ALIAS_NAME], t_name_buff[MAX_ALIAS_NAME]; + const char *d_name= table_list->db, *t_name= table_list->table_name; + bool use_db_name= d_name && d_name[0]; + THD *thd= table_list->table->in_use; + + str->append(func_name()); + str->append('('); + + /* + for next_val we assume that table_list has been updated to contain + the current db. + */ + + if (lower_case_table_names > 0) + { + strmake(t_name_buff, t_name, MAX_ALIAS_NAME-1); + my_casedn_str(files_charset_info, t_name_buff); + t_name= t_name_buff; + if (use_db_name) + { + strmake(d_name_buff, d_name, MAX_ALIAS_NAME-1); + my_casedn_str(files_charset_info, d_name_buff); + d_name= d_name_buff; + } + } + + if (use_db_name) + { + append_identifier(thd, str, d_name, (uint)strlen(d_name)); + str->append('.'); + } + append_identifier(thd, str, t_name, (uint) strlen(t_name)); + str->append(','); + str->append_longlong(nextval); + str->append(','); + str->append_longlong(is_used); + str->append(','); + str->append_ulonglong(round); + str->append(')'); +} diff --git a/sql/item_func.h b/sql/item_func.h index 74c986545cd..18612dbe79f 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -2863,6 +2863,7 @@ public: } }; + /* Implementation for sequences: LASTVAL(sequence), PostgreSQL style */ class Item_func_lastval :public Item_func_nextval @@ -2877,6 +2878,27 @@ public: }; +/* Implementation for sequences: SETVAL(sequence), PostgreSQL style */ + +class Item_func_setval :public Item_func_nextval +{ + longlong nextval; + ulonglong round; + bool is_used; +public: + Item_func_setval(THD *thd, TABLE_LIST *table, longlong nextval_arg, + ulonglong round_arg, bool is_used_arg) + : Item_func_nextval(thd, table), + nextval(nextval_arg), round(round_arg), is_used(is_used_arg) + {} + longlong val_int(); + const char *func_name() const { return "setval"; } + void print(String *str, enum_query_type query_type); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_setval>(thd, mem_root, this); } +}; + + Item *get_system_var(THD *thd, enum_var_type var_type, LEX_CSTRING name, LEX_CSTRING component); extern bool check_reserved_words(const LEX_CSTRING *name); diff --git a/sql/lex.h b/sql/lex.h index e67b207a75d..f2dac95fce5 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -522,6 +522,7 @@ static SYMBOL symbols[] = { { "REQUIRE", SYM(REQUIRE_SYM)}, { "RESET", SYM(RESET_SYM)}, { "RESIGNAL", SYM(RESIGNAL_SYM)}, + { "RESTART", SYM(RESTART_SYM)}, { "RESTORE", SYM(RESTORE_SYM)}, { "RESTRICT", SYM(RESTRICT)}, { "RESUME", SYM(RESUME_SYM)}, @@ -562,6 +563,7 @@ static SYMBOL symbols[] = { { "SESSION", SYM(SESSION_SYM)}, { "SERVER", SYM(SERVER_SYM)}, { "SET", SYM(SET)}, + { "SETVAL", SYM(SETVAL_SYM)}, { "SHARE", SYM(SHARE_SYM)}, { "SHOW", SYM(SHOW)}, { "SHUTDOWN", SYM(SHUTDOWN)}, diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 065f4d83367..eb39e542c41 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3767,6 +3767,7 @@ SHOW_VAR com_status_vars[]= { {"alter_function", STMT_STATUS(SQLCOM_ALTER_FUNCTION)}, {"alter_procedure", STMT_STATUS(SQLCOM_ALTER_PROCEDURE)}, {"alter_server", STMT_STATUS(SQLCOM_ALTER_SERVER)}, + {"alter_sequence", STMT_STATUS(SQLCOM_ALTER_SEQUENCE)}, {"alter_table", STMT_STATUS(SQLCOM_ALTER_TABLE)}, {"alter_tablespace", STMT_STATUS(SQLCOM_ALTER_TABLESPACE)}, {"alter_user", STMT_STATUS(SQLCOM_ALTER_USER)}, diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 12b956623f8..80730a9b060 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -260,6 +260,7 @@ sp_get_flags_for_command(LEX *lex) case SQLCOM_CREATE_USER: case SQLCOM_CREATE_ROLE: case SQLCOM_ALTER_TABLE: + case SQLCOM_ALTER_SEQUENCE: case SQLCOM_ALTER_USER: case SQLCOM_GRANT: case SQLCOM_GRANT_ROLE: diff --git a/sql/sql_alter.h b/sql/sql_alter.h index beec5090343..c0232dd7358 100644 --- a/sql/sql_alter.h +++ b/sql/sql_alter.h @@ -385,6 +385,29 @@ public: /** + Sql_cmd_alter_sequence represents the ALTER SEQUENCE statement. +*/ +class Sql_cmd_alter_sequence : public Sql_cmd +{ +public: + /** + Constructor, used to represent a ALTER TABLE statement. + */ + Sql_cmd_alter_sequence() + {} + + ~Sql_cmd_alter_sequence() + {} + + enum_sql_command sql_command_code() const + { + return SQLCOM_ALTER_SEQUENCE; + } + bool execute(THD *thd); +}; + + +/** Sql_cmd_alter_table_tablespace represents ALTER TABLE IMPORT/DISCARD TABLESPACE statements. */ diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h index 875dcf0e575..4ae9353d6ff 100644 --- a/sql/sql_cmd.h +++ b/sql/sql_cmd.h @@ -98,6 +98,7 @@ enum enum_sql_command { SQLCOM_EXECUTE_IMMEDIATE, SQLCOM_CREATE_SEQUENCE, SQLCOM_DROP_SEQUENCE, + SQLCOM_ALTER_SEQUENCE, /* When a command is added here, be sure it's also added in mysqld.cc diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 3c8f088d196..8cabe795d21 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -6404,7 +6404,6 @@ Item *LEX::create_item_func_nextval(THD *thd, Table_ident *table_ident) MDL_SHARED_WRITE))) return NULL; return new (thd->mem_root) Item_func_nextval(thd, table); - } @@ -6442,6 +6441,21 @@ Item *LEX::create_item_func_lastval(THD *thd, } +Item *LEX::create_item_func_setval(THD *thd, Table_ident *table_ident, + longlong nextval, ulonglong round, + bool is_used) +{ + TABLE_LIST *table; + if (!(table= current_select->add_table_to_list(thd, table_ident, 0, + TL_OPTION_SEQUENCE, + TL_WRITE_ALLOW_WRITE, + MDL_SHARED_WRITE))) + return NULL; + return new (thd->mem_root) Item_func_setval(thd, table, nextval, round, + is_used); +} + + Item *LEX::create_item_ident(THD *thd, const LEX_CSTRING *a, const LEX_CSTRING *b, diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b90d3402afe..85ce07be5a8 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -3276,6 +3276,12 @@ public: const LEX_CSTRING *name); /* + Create an item for "SETVAL(sequence_name, value [, is_used [, round]]) + */ + Item *create_item_func_setval(THD *thd, Table_ident *ident, longlong value, + ulonglong round, bool is_used); + + /* Create an item for a name in LIMIT clause: LIMIT var @param THD - THD, for mem_root @param var_name - the variable name diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index b6955ee885f..edf4f77f70c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -454,6 +454,7 @@ static bool stmt_causes_implicit_commit(THD *thd, uint mask) (thd->variables.option_bits & OPTION_GTID_BEGIN)); break; case SQLCOM_ALTER_TABLE: + case SQLCOM_ALTER_SEQUENCE: /* If ALTER TABLE of non-temporary table, do implicit commit */ skip= (lex->tmp_table()); break; @@ -555,6 +556,8 @@ void init_update_queries(void) sql_command_flags[SQLCOM_ALTER_TABLE]= CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND | 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; 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; @@ -6213,9 +6216,11 @@ end_with_restore_list: case SQLCOM_REPAIR: case SQLCOM_TRUNCATE: case SQLCOM_ALTER_TABLE: - thd->query_plan_flags|= QPLAN_ADMIN; DBUG_ASSERT(first_table == all_tables && first_table != 0); /* fall through */ + case SQLCOM_ALTER_SEQUENCE: + thd->query_plan_flags|= QPLAN_ADMIN; + /* fall through */ case SQLCOM_SIGNAL: case SQLCOM_RESIGNAL: case SQLCOM_GET_DIAGNOSTICS: diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 2a523fd61b7..0e15bf45cc7 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2480,6 +2480,7 @@ static bool check_prepared_statement(Prepared_statement *stmt) case SQLCOM_DROP_SEQUENCE: case SQLCOM_RENAME_TABLE: case SQLCOM_ALTER_TABLE: + case SQLCOM_ALTER_SEQUENCE: case SQLCOM_COMMIT: case SQLCOM_CREATE_INDEX: case SQLCOM_DROP_INDEX: diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc index 6b0a03a4e07..a6e7b073251 100644 --- a/sql/sql_sequence.cc +++ b/sql/sql_sequence.cc @@ -22,6 +22,7 @@ #include "sql_base.h" #include "transaction.h" #include "lock.h" +#include "sql_acl.h" struct Field_definition { @@ -162,7 +163,7 @@ void sequence_definition::store_fields(TABLE *table) /* - Check the sequence fields through seq_fields when create sequence.qq + Check the sequence fields through seq_fields when createing a sequence. RETURN VALUES false Success @@ -269,7 +270,6 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *table_list) Reprepare_observer *save_reprepare_observer; sequence_definition *seq= lex->create_info.seq_create_info; bool temporary_table= table_list->table != 0; - MY_BITMAP *save_write_set; DBUG_ENTER("sequence_insert"); /* If not temporary table */ @@ -316,33 +316,7 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *table_list) } seq->reserved_until= seq->start; - seq->store_fields(table); - /* Store the sequence values in table share */ - table->s->sequence->copy(seq); - - /* - Sequence values will be replicated as a statement - like 'create sequence'. So disable binary log temporarily - */ - tmp_disable_binlog(thd); - save_write_set= table->write_set; - table->write_set= &table->s->all_set; - table->s->sequence->initialized= SEQUENCE::SEQ_IN_PREPARE; - error= table->file->ha_write_row(table->record[0]); - table->s->sequence->initialized= SEQUENCE::SEQ_UNINTIALIZED; - reenable_binlog(thd); - table->write_set= save_write_set; - - if (error) - table->file->print_error(error, MYF(0)); - else - { - /* - Sequence structure is up to date and table has one row, - sequence is now usable - */ - table->s->sequence->initialized= SEQUENCE::SEQ_READY_TO_USE; - } + error= seq->write_initial_sequence(table); trans_commit_stmt(thd); trans_commit_implicit(thd); @@ -463,7 +437,7 @@ int SEQUENCE::read_stored_values() DBUG_RETURN(error); } read_fields(table); - adjust_values(); + adjust_values(reserved_until); all_values_used= 0; DBUG_RETURN(0); @@ -474,12 +448,12 @@ int SEQUENCE::read_stored_values() Adjust values after reading a the stored state */ -void SEQUENCE::adjust_values() +void SEQUENCE::adjust_values(longlong next_value) { - offset= 0; - next_free_value= reserved_until; + next_free_value= next_value; if (!(real_increment= increment)) { + longlong offset= 0; longlong off, to_add; /* Use auto_increment_increment and auto_increment_offset */ @@ -515,6 +489,72 @@ void SEQUENCE::adjust_values() /** + Write initial sequence information for CREATE and ALTER to sequence table +*/ + +int sequence_definition::write_initial_sequence(TABLE *table) +{ + int error; + THD *thd= table->in_use; + MY_BITMAP *save_write_set; + + store_fields(table); + /* Store the sequence values in table share */ + table->s->sequence->copy(this); + /* + Sequence values will be replicated as a statement + like 'create sequence'. So disable binary log temporarily + */ + tmp_disable_binlog(thd); + save_write_set= table->write_set; + table->write_set= &table->s->all_set; + table->s->sequence->initialized= SEQUENCE::SEQ_IN_PREPARE; + error= table->file->ha_write_row(table->record[0]); + table->s->sequence->initialized= SEQUENCE::SEQ_UNINTIALIZED; + reenable_binlog(thd); + table->write_set= save_write_set; + if (error) + table->file->print_error(error, MYF(0)); + else + { + /* + Sequence structure is up to date and table has one row, + sequence is now usable + */ + table->s->sequence->initialized= SEQUENCE::SEQ_READY_TO_USE; + } + return error; +} + + +/** + Store current sequence values into the sequence table +*/ + +int sequence_definition::write(TABLE *table) +{ + 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; + + /* Update table */ + save_write_set= table->write_set; + table->rpl_write_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; + if ((error= table->file->ha_write_row(table->record[0]))) + table->file->print_error(error, MYF(0)); + ((ha_sequence*) table->file)->sequence_locked= 0; + table->rpl_write_set= save_rpl_write_set; + table->write_set= save_write_set; + return error; +} + + +/** Get next value for sequence @param in table Sequence table @@ -546,7 +586,6 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) { longlong res_value, org_reserved_until, add_to; bool out_of_values; - MY_BITMAP *save_rpl_write_set, *save_write_set; DBUG_ENTER("SEQUENCE::next_value"); *error= 0; @@ -554,24 +593,7 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) lock(); res_value= next_free_value; - - /* Increment next_free_value */ - if (real_increment > 0) - { - if (next_free_value + real_increment > max_value || - next_free_value > max_value - real_increment) - next_free_value= max_value + 1; - else - next_free_value+= real_increment; - } - else - { - if (next_free_value + real_increment < min_value || - next_free_value < min_value - real_increment) - next_free_value= min_value - 1; - else - next_free_value+= real_increment; - } + next_free_value= increment_value(next_free_value); if ((real_increment > 0 && res_value < reserved_until) || (real_increment < 0 && res_value > reserved_until)) @@ -621,7 +643,7 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) goto err; round++; reserved_until= real_increment >0 ? min_value : max_value; - adjust_values(); // Fix next_free_value + adjust_values(reserved_until); // Fix next_free_value /* We have to do everything again to ensure that the given range was not empty, which could happen if increment == 0 @@ -629,25 +651,11 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) DBUG_RETURN(next_value(table, 1, error)); } - /* Log a full insert (ok as table is small) */ - save_rpl_write_set= table->rpl_write_set; - - /* Update table */ - save_write_set= table->write_set; - table->rpl_write_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; - if ((*error= table->file->ha_write_row(table->record[0]))) + if ((*error= write(table))) { - table->file->print_error(*error, MYF(0)); - /* Restore original range */ reserved_until= org_reserved_until; next_free_value= res_value; } - ((ha_sequence*) table->file)->sequence_locked= 0; - table->rpl_write_set= save_rpl_write_set; - table->write_set= save_write_set; unlock(); DBUG_RETURN(res_value); @@ -681,3 +689,179 @@ void SEQUENCE_LAST_VALUE::set_version(TABLE *table) { memcpy(table_version, table->s->tabledef_version.str, MY_UUID_SIZE); } + +/** + Set the next value for sequence + + @param in table Sequence table + @param in next_val Next free value + @param in next_round Round for 'next_value' (in cace of cycles) + @param in is_used 1 if next_val is already used + + @retval 0 ok, value adjusted + 1 value was less than current value or + error when storing value + + @comment + A new value is set only if "nextval,next_round" is less than + "next_free_value,round". This is needed as in replication + setvalue() calls may come out to the slave out-of-order. + Storing only the highest value ensures that sequence object will always + contain the highest used value when the slave is promoted to a master. +*/ + +bool SEQUENCE::set_value(TABLE *table, longlong next_val, ulonglong next_round, + bool is_used) +{ + bool error= 1; + bool needs_to_be_stored= 0; + longlong org_reserved_until= reserved_until; + longlong org_next_free_value= next_free_value; + ulonglong org_round= round; + DBUG_ENTER("SEQUENCE::set_value"); + + lock(); + if (is_used) + next_val= increment_value(next_val); + + if (round > next_round) + goto end; + if (round == next_round) + { + if (real_increment > 0 ? + next_val < next_free_value : + next_val > next_free_value) + goto end; + if (next_val == next_free_value) + { + error= 0; + goto end; + } + } + else if (cycle == 0) + goto end; // round < next_round && no cycles + else + needs_to_be_stored= 1; + + round= next_round; + adjust_values(next_val); + if ((real_increment > 0 ? + next_free_value > reserved_until : + next_free_value < reserved_until) || + needs_to_be_stored) + { + reserved_until= next_free_value; + if (write(table)) + { + reserved_until= org_reserved_until; + next_free_value= org_next_free_value; + round= org_round; + goto end; + } + } + error= 0; + +end: + unlock(); + DBUG_RETURN(error); +} + + +bool Sql_cmd_alter_sequence::execute(THD *thd) +{ + int error= 0; + int trapped_errors= 0; + LEX *lex= thd->lex; + TABLE_LIST *first_table= lex->query_tables; + TABLE *table; + sequence_definition *new_seq= lex->create_info.seq_create_info; + SEQUENCE *seq; + No_such_table_error_handler no_such_table_handler; + DBUG_ENTER("Sql_cmd_alter_sequence::execute"); + + if (check_access(thd, ALTER_ACL, first_table->db, + &first_table->grant.privilege, + &first_table->grant.m_internal, + 0, 0)) + DBUG_RETURN(TRUE); /* purecov: inspected */ + + if (check_grant(thd, ALTER_ACL, first_table, FALSE, UINT_MAX, FALSE)) + DBUG_RETURN(TRUE); /* purecov: inspected */ + + if (lex->check_exists) + thd->push_internal_handler(&no_such_table_handler); + error= open_and_lock_tables(thd, first_table, FALSE, 0); + if (lex->check_exists) + { + trapped_errors= no_such_table_handler.safely_trapped_errors(); + thd->pop_internal_handler(); + } + if (error) + { + if (trapped_errors) + { + StringBuffer<FN_REFLEN> tbl_name; + tbl_name.append(first_table->db); + tbl_name.append('.'); + tbl_name.append(first_table->table_name); + push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, + ER_UNKNOWN_SEQUENCES, + ER_THD(thd, ER_UNKNOWN_SEQUENCES), + tbl_name.c_ptr_safe()); + my_ok(thd); + DBUG_RETURN(FALSE); + } + DBUG_RETURN(TRUE); + } + + table= first_table->table; + seq= table->s->sequence; + new_seq->reserved_until= seq->reserved_until; + + /* Copy from old sequence those fields that the user didn't specified */ + if (!(new_seq->used_fields & seq_field_used_increment)) + new_seq->increment= seq->increment; + if (!(new_seq->used_fields & seq_field_used_min_value)) + new_seq->min_value= seq->min_value; + if (!(new_seq->used_fields & seq_field_used_max_value)) + new_seq->max_value= seq->max_value; + if (!(new_seq->used_fields & seq_field_used_start)) + new_seq->start= seq->start; + if (!(new_seq->used_fields & seq_field_used_cache)) + new_seq->cache= seq->cache; + if (!(new_seq->used_fields & seq_field_used_cycle)) + new_seq->cycle= seq->cycle; + + /* If we should restart from a new value */ + if (new_seq->used_fields & seq_field_used_restart) + { + if (!(new_seq->used_fields & seq_field_used_restart_value)) + new_seq->restart= new_seq->start; + new_seq->reserved_until= new_seq->restart; + } + + /* Let check_and_adjust think all fields are used */ + new_seq->used_fields= ~0; + if (new_seq->check_and_adjust()) + { + my_error(ER_SEQUENCE_INVALID_DATA, MYF(0), + first_table->db, + first_table->table_name); + error= 1; + goto end; + } + + if (!(error= new_seq->write(table))) + { + /* Store the sequence values in table share */ + table->s->sequence->copy(new_seq); + } + trans_commit_stmt(thd); + trans_commit_implicit(thd); + 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 1409f86649f..ffe5ded4cff 100644 --- a/sql/sql_sequence.h +++ b/sql/sql_sequence.h @@ -20,6 +20,11 @@ #define seq_field_used_min_value 1 #define seq_field_used_max_value 2 #define seq_field_used_start 4 +#define seq_field_used_increment 8 +#define seq_field_used_cache 16 +#define seq_field_used_cycle 32 +#define seq_field_used_restart 64 +#define seq_field_used_restart_value 128 /** sequence_definition is used when defining a sequence as part of create @@ -41,11 +46,14 @@ public: ulonglong round; bool cycle; uint used_fields; // Which fields where used in CREATE + longlong restart; // alter sequence restart value bool check_and_adjust(); void store_fields(TABLE *table); void read_fields(TABLE *table); - void print_dbug() + int write_initial_sequence(TABLE *table); + int write(TABLE *table); + 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, @@ -80,13 +88,35 @@ public: mysql_mutex_unlock(&mutex); } /* This must be called after sequence data has been updated */ - void adjust_values(); + void adjust_values(longlong next_value); void copy(sequence_definition *seq) { sequence_definition::operator= (*seq); - adjust_values(); + adjust_values(reserved_until); } longlong next_value(TABLE *table, bool second_round, int *error); + bool set_value(TABLE *table, longlong next_value, ulonglong round_arg, + bool is_used); + longlong increment_value(longlong value) + { + if (real_increment > 0) + { + if (value + real_increment > max_value || + value > max_value - real_increment) + value= max_value + 1; + else + value+= real_increment; + } + else + { + if (value + real_increment < min_value || + value < min_value - real_increment) + value= min_value - 1; + else + value+= real_increment; + } + return value; + } bool all_values_used; seq_init initialized; @@ -100,7 +130,6 @@ private: merged with global auto_increment_offset and auto_increment_increment */ longlong real_increment; - longlong offset; }; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 2ad8c2d5562..f985f6e73fc 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -9631,6 +9631,7 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to, thd->abort_on_warning= !ignore && thd->is_strict_mode(); from->file->info(HA_STATUS_VARIABLE); + to->file->extra(HA_EXTRA_PREPARE_FOR_ALTER_TABLE); to->file->ha_start_bulk_insert(from->file->stats.records, ignore ? 0 : HA_CREATE_UNIQUE_INDEX_BY_SORT); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e8120c325fb..c065cd7dcf7 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1386,6 +1386,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token REPLICATION %token REQUIRE_SYM %token RESET_SYM +%token RESTART_SYM %token RESIGNAL_SYM /* SQL-2003-R */ %token RESOURCES %token RESTORE_SYM @@ -1427,6 +1428,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token SERVER_SYM %token SERVER_OPTIONS %token SET /* SQL-2003-R */ +%token SETVAL_SYM /* PostgreSQL sequence function */ %token SET_VAR %token SHARE_SYM %token SHIFT_LEFT /* OPERATOR */ @@ -1699,7 +1701,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); ws_nweights ws_level_flag_desc ws_level_flag_reverse ws_level_flags opt_ws_levels ws_level_list ws_level_list_item ws_level_number - ws_level_range ws_level_list_or_range + ws_level_range ws_level_list_or_range bool %type <ulonglong_number> ulonglong_num real_ulonglong_num size_number @@ -2632,14 +2634,15 @@ sequence_def: { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_min_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; } | NOMINVALUE_SYM { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_min_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; } | MAXVALUE_SYM opt_equal longlong_num - { Lex->create_info.seq_create_info->max_value= $3; Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; @@ -2648,11 +2651,13 @@ sequence_def: { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_max_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; } | NOMAXVALUE_SYM { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_max_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; } | START_SYM opt_with longlong_num { @@ -2662,22 +2667,46 @@ sequence_def: | INCREMENT_SYM opt_by longlong_num { Lex->create_info.seq_create_info->increment= $3; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_increment; } | CACHE_SYM opt_equal longlong_num { Lex->create_info.seq_create_info->cache= $3; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cache; } | NOCACHE_SYM { Lex->create_info.seq_create_info->cache= 0; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cache; } | CYCLE_SYM { Lex->create_info.seq_create_info->cycle= 1; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cycle; } | NOCYCLE_SYM { Lex->create_info.seq_create_info->cycle= 0; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cycle; + } + | RESTART_SYM + { + if (Lex->sql_command != SQLCOM_ALTER_SEQUENCE) + { + thd->parse_error(ER_SYNTAX_ERROR, "RESTART"); + YYABORT; + } + Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart; + } + | RESTART_SYM opt_with longlong_num + { + if (Lex->sql_command != SQLCOM_ALTER_SEQUENCE) + { + thd->parse_error(ER_SYNTAX_ERROR, "RESTART"); + YYABORT; + } + Lex->create_info.seq_create_info->restart= $3; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart | seq_field_used_restart_value; } ; @@ -7276,6 +7305,33 @@ alter: Lex->create_info.set($2); Lex->sql_command= SQLCOM_ALTER_USER; } + | ALTER SEQUENCE_SYM opt_if_exists_table_element + { + LEX *lex= Lex; + lex->name= null_clex_str; + lex->table_type= TABLE_TYPE_UNKNOWN; + lex->sql_command= SQLCOM_ALTER_SEQUENCE; + lex->create_info.init(); + lex->no_write_to_binlog= 0; + DBUG_ASSERT(!lex->m_sql_cmd); + } + table_ident + { + LEX *lex= Lex; + if (!(lex->create_info.seq_create_info= new (thd->mem_root) + sequence_definition()) || + !lex->select_lex.add_table_to_list(thd, $5, NULL, + TL_OPTION_SEQUENCE, + TL_WRITE, MDL_EXCLUSIVE)) + MYSQL_YYABORT; + } + sequence_defs + { + /* Create a generic ALTER SEQUENCE statment. */ + Lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_sequence(); + if (Lex->m_sql_cmd == NULL) + MYSQL_YYABORT; + } ; ev_alter_on_schedule_completion: @@ -9408,6 +9464,21 @@ column_default_non_parenthesized_expr: if (!($$= Lex->create_item_func_lastval(thd, $3))) MYSQL_YYABORT; } + | SETVAL_SYM '(' table_ident ',' longlong_num ')' + { + if (!($$= Lex->create_item_func_setval(thd, $3, $5, 0, 1))) + MYSQL_YYABORT; + } + | SETVAL_SYM '(' table_ident ',' longlong_num ',' bool ')' + { + if (!($$= Lex->create_item_func_setval(thd, $3, $5, 0, $7))) + MYSQL_YYABORT; + } + | SETVAL_SYM '(' table_ident ',' longlong_num ',' bool ',' ulonglong_num ')' + { + if (!($$= Lex->create_item_func_setval(thd, $3, $5, $9, $7))) + MYSQL_YYABORT; + } ; simple_expr: @@ -11953,6 +12024,12 @@ choice: | DEFAULT { $$= HA_CHOICE_UNDEF; } ; +bool: + ulong_num { $$= $1 != 0; } + | TRUE_SYM { $$= 1; } + | FALSE_SYM { $$= 0; } + + procedure_clause: PROCEDURE_SYM ident /* Procedure name */ { @@ -14787,6 +14864,7 @@ keyword_sp_not_data_type: | REPEATABLE_SYM {} | REPLICATION {} | RESOURCES {} + | RESTART_SYM {} | RESUME_SYM {} | RETURNED_SQLSTATE_SYM {} | RETURNS_SYM {} @@ -14806,6 +14884,7 @@ keyword_sp_not_data_type: | SEQUENCE_SYM {} | SERIALIZABLE_SYM {} | SESSION_SYM {} + | SETVAL_SYM {} | SIMPLE_SYM {} | SHARE_SYM {} | SLAVE_POS_SYM {} diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 91b541410bc..61577b53006 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -795,6 +795,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token REPLICATION %token REQUIRE_SYM %token RESET_SYM +%token RESTART_SYM %token RESIGNAL_SYM /* SQL-2003-R */ %token RESOURCES %token RESTORE_SYM @@ -836,6 +837,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token SERVER_SYM %token SERVER_OPTIONS %token SET /* SQL-2003-R */ +%token SETVAL_SYM /* PostgreSQL sequence function */ %token SET_VAR %token SHARE_SYM %token SHIFT_LEFT /* OPERATOR */ @@ -1117,7 +1119,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); ws_nweights ws_level_flag_desc ws_level_flag_reverse ws_level_flags opt_ws_levels ws_level_list ws_level_list_item ws_level_number - ws_level_range ws_level_list_or_range + ws_level_range ws_level_list_or_range bool %type <ulonglong_number> ulonglong_num real_ulonglong_num size_number @@ -2073,14 +2075,15 @@ sequence_def: { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_min_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; } | NOMINVALUE_SYM { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_min_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; } | MAXVALUE_SYM opt_equal longlong_num - { Lex->create_info.seq_create_info->max_value= $3; Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; @@ -2089,11 +2092,13 @@ sequence_def: { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_max_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; } | NOMAXVALUE_SYM { if (Lex->create_info.seq_create_info->used_fields & seq_field_used_max_value) MYSQL_YYABORT; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; } | START_SYM opt_with longlong_num { @@ -2103,22 +2108,46 @@ sequence_def: | INCREMENT_SYM opt_by longlong_num { Lex->create_info.seq_create_info->increment= $3; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_increment; } | CACHE_SYM opt_equal longlong_num { Lex->create_info.seq_create_info->cache= $3; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cache; } | NOCACHE_SYM { Lex->create_info.seq_create_info->cache= 0; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cache; } | CYCLE_SYM { Lex->create_info.seq_create_info->cycle= 1; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cycle; } | NOCYCLE_SYM { Lex->create_info.seq_create_info->cycle= 0; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_cycle; + } + | RESTART_SYM + { + if (Lex->sql_command != SQLCOM_ALTER_SEQUENCE) + { + thd->parse_error(ER_SYNTAX_ERROR, "RESTART"); + YYABORT; + } + Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart; + } + | RESTART_SYM opt_with longlong_num + { + if (Lex->sql_command != SQLCOM_ALTER_SEQUENCE) + { + thd->parse_error(ER_SYNTAX_ERROR, "RESTART"); + YYABORT; + } + Lex->create_info.seq_create_info->restart= $3; + Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart | seq_field_used_restart_value; } ; @@ -7259,6 +7288,33 @@ alter: Lex->create_info.set($2); Lex->sql_command= SQLCOM_ALTER_USER; } + | ALTER SEQUENCE_SYM opt_if_exists_table_element + { + LEX *lex= Lex; + lex->name= null_clex_str; + lex->table_type= TABLE_TYPE_UNKNOWN; + lex->sql_command= SQLCOM_ALTER_SEQUENCE; + lex->create_info.init(); + lex->no_write_to_binlog= 0; + DBUG_ASSERT(!lex->m_sql_cmd); + } + table_ident + { + LEX *lex= Lex; + if (!(lex->create_info.seq_create_info= new (thd->mem_root) + sequence_definition()) || + !lex->select_lex.add_table_to_list(thd, $5, NULL, + TL_OPTION_SEQUENCE, + TL_WRITE, MDL_EXCLUSIVE)) + MYSQL_YYABORT; + } + sequence_defs + { + /* Create a generic ALTER SEQUENCE statment. */ + Lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_sequence(); + if (Lex->m_sql_cmd == NULL) + MYSQL_YYABORT; + } ; ev_alter_on_schedule_completion: @@ -9460,6 +9516,21 @@ column_default_non_parenthesized_expr: if (!($$= Lex->create_item_func_lastval(thd, $3))) MYSQL_YYABORT; } + | SETVAL_SYM '(' table_ident ',' longlong_num ')' + { + if (!($$= Lex->create_item_func_setval(thd, $3, $5, 0, 1))) + MYSQL_YYABORT; + } + | SETVAL_SYM '(' table_ident ',' longlong_num ',' bool ')' + { + if (!($$= Lex->create_item_func_setval(thd, $3, $5, 0, $7))) + MYSQL_YYABORT; + } + | SETVAL_SYM '(' table_ident ',' longlong_num ',' bool ',' ulonglong_num ')' + { + if (!($$= Lex->create_item_func_setval(thd, $3, $5, $9, $7))) + MYSQL_YYABORT; + } ; simple_expr: @@ -12035,6 +12106,12 @@ choice: | DEFAULT { $$= HA_CHOICE_UNDEF; } ; +bool: + ulong_num { $$= $1 != 0; } + | TRUE_SYM { $$= 1; } + | FALSE_SYM { $$= 0; } + + procedure_clause: PROCEDURE_SYM ident /* Procedure name */ { @@ -14981,6 +15058,7 @@ keyword_sp_not_data_type: | REPEATABLE_SYM {} | REPLICATION {} | RESOURCES {} + | RESTART_SYM {} | RESUME_SYM {} | RETURNED_SQLSTATE_SYM {} | RETURNS_SYM {} @@ -14999,6 +15077,7 @@ keyword_sp_not_data_type: | SEQUENCE_SYM {} | SERIALIZABLE_SYM {} | SESSION_SYM {} + | SETVAL_SYM {} | SIMPLE_SYM {} | SHARE_SYM {} | SLAVE_POS_SYM {} diff --git a/storage/mroonga/ha_mroonga.cpp b/storage/mroonga/ha_mroonga.cpp index 2c83c3f891e..cb353db53b0 100644 --- a/storage/mroonga/ha_mroonga.cpp +++ b/storage/mroonga/ha_mroonga.cpp @@ -463,6 +463,9 @@ static const char *mrn_inspect_extra_function(enum ha_extra_function operation) case HA_EXTRA_PREPARE_FOR_DROP: inspected = "HA_EXTRA_PREPARE_FOR_DROP"; break; + case HA_EXTRA_PREPARE_FOR_ALTER_TABLE: + inspected = "HA_EXTRA_PREPARE_FOR_ALTER_TABLE"; + break; case HA_EXTRA_PREPARE_FOR_UPDATE: inspected = "HA_EXTRA_PREPARE_FOR_UPDATE"; break; |