MDEV-10139 Support for SEQUENCE objects

Working features:
CREATE OR REPLACE [TEMPORARY] SEQUENCE [IF NOT EXISTS] name
    [ INCREMENT [ BY | = ] increment ]
    [ MINVALUE [=] minvalue | NO MINVALUE ]
    [ MAXVALUE [=] maxvalue | NO MAXVALUE ]
    [ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ]
    ENGINE=xxx COMMENT=".."
SELECT NEXT VALUE FOR sequence_name;
SELECT NEXTVAL(sequence_name);
SELECT PREVIOUS VALUE FOR sequence_name;
SELECT LASTVAL(sequence_name);

SHOW CREATE SEQUENCE sequence_name;
SHOW CREATE TABLE sequence_name;
CREATE TABLE sequence-structure ... SEQUENCE=1
ALTER TABLE sequence RENAME TO sequence2;
RENAME TABLE sequence TO sequence2;
DROP [TEMPORARY] SEQUENCE  [IF EXISTS] sequence_names

Missing features
- SETVAL(value,sequence_name), to be used with replication.
- Check replication, including checking that sequence tables are marked
  not transactional.
- Check that a commit happens for NEXT VALUE that changes table data (may
  already work)
- ALTER SEQUENCE. ANSI SQL version of setval.
- Share identical sequence entries to not add things twice to table list.
- testing insert/delete/update/truncate/load data
- Run and fix Alibaba sequence tests (part of mysql-test/suite/sql_sequence)
- Write documentation for NEXT VALUE / PREVIOUS_VALUE
- NEXTVAL in DEFAULT
  - Ensure that NEXTVAL in DEFAULT uses database from base table
- Two NEXTVAL for same row should give same answer.
- Oracle syntax sequence_table.nextval, without any FOR or FROM.
- Sequence tables are treated as 'not read constant tables' by SELECT; Would
  be better if we would have a separate list for sequence tables so that
  select doesn't know about them, except if refereed to with FROM.

Other things done:
- Improved output for safemalloc backtrack
- frm_type_enum changed to Table_type
- Removed lex->is_view and replaced with lex->table_type. This allows
  use to more easy check if item is view, sequence or table.
- Added table flag HA_CAN_TABLES_WITHOUT_ROLLBACK, needed for handlers
  that want's to support sequences
- Added handler calls:
 - engine_name(), to simplify getting engine name for partition and sequences
 - update_first_row(), to be able to do efficient sequence implementations.
 - Made binlog_log_row() global to be able to call it from ha_sequence.cc
- Added handler variable: row_already_logged, to be able to flag that the
  changed row is already logging to replication log.
- Added CF_DB_CHANGE and CF_SCHEMA_CHANGE flags to simplify
  deny_updates_if_read_only_option()
- Added sp_add_cfetch() to avoid new conflicts in sql_yacc.yy
- Moved code for add_table_options() out from sql_show.cc::show_create_table()
- Added String::append_longlong() and used it in sql_show.cc to simplify code.
- Added extra option to dd_frm_type() and ha_table_exists to indicate if
  the table is a sequence. Needed by DROP SQUENCE to not drop a table.

27 files changed, 5020 insertions, 60 deletions

diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 5d81ee3265b..17a2d7db632 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -194,6 +194,7 @@ my @DEFAULT_SUITES= qw(
     roles-
     rpl-
     sys_vars-
+    sql_sequence-
     unit-
     vcol-
     wsrep-
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index cfd2ede6ef9..778bfb13bd3 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 187
+performance-schema-max-statement-classes 189
 performance-schema-max-table-handles -1
 performance-schema-max-table-instances -1
 performance-schema-max-thread-classes 50
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 98aa2b50fc6..6af6b167511 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -4,7 +4,7 @@ CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
 CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
 ERROR HY000: Can't find symbol 'myfunc_nonexist' in library
 CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
-CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
 CREATE FUNCTION lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
 CREATE FUNCTION reverse_lookup
 RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
@@ -226,7 +226,7 @@ DROP FUNCTION myfunc_double;
 DROP FUNCTION myfunc_nonexist;
 ERROR 42000: FUNCTION test.myfunc_nonexist does not exist
 DROP FUNCTION myfunc_int;
-DROP FUNCTION sequence;
+DROP FUNCTION udf_sequence;
 DROP FUNCTION lookup;
 DROP FUNCTION reverse_lookup;
 DROP FUNCTION avgcost;
@@ -340,32 +340,32 @@ DROP FUNCTION check_const_len;
 DROP PROCEDURE check_const_len_sp;
 DROP TRIGGER check_const_len_trigger;
 DROP TABLE const_len_bug;
-CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
+CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT PRIMARY KEY);
 INSERT INTO t1 VALUES (4),(3),(2),(1);
 INSERT INTO t2 SELECT * FROM t1;
-SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC;
+SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq ASC;
 seq	a
 1	4
 2	3
 3	2
 4	1
-SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC;
+SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq DESC;
 seq	a
 4	1
 3	2
 2	3
 1	4
-SELECT * FROM t1 WHERE a = sequence();
+SELECT * FROM t1 WHERE a = udf_sequence();
 a
-SELECT * FROM t2 WHERE a = sequence();
+SELECT * FROM t2 WHERE a = udf_sequence();
 a
 1
 2
 3
 4
-DROP FUNCTION sequence;
+DROP FUNCTION udf_sequence;
 DROP TABLE t1,t2;
 drop function if exists test.metaphon;
 drop function if exists metaphon;
diff --git a/mysql-test/r/udf_notembedded.result b/mysql-test/r/udf_notembedded.result
index 3fdcdbbe9d3..377af563d3e 100644
--- a/mysql-test/r/udf_notembedded.result
+++ b/mysql-test/r/udf_notembedded.result
@@ -1,6 +1,6 @@
-create function sequence returns integer soname "UDF_EXAMPLE_LIB";
-create table t1 (n int key not null auto_increment,  msg int as (sequence()) virtual);
+create function udf_sequence returns integer soname "UDF_EXAMPLE_LIB";
+create table t1 (n int key not null auto_increment,  msg int as (udf_sequence()) virtual);
 select * from t1;
 n	msg
 drop table t1;
-drop function sequence;
+drop function udf_sequence;
diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result
index 1cde61620a6..a2a5f434f18 100644
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result
@@ -85,8 +85,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_retval_2 returning value from function func_retval_1 returning value from unsafe UUID() function.
 CREATE VIEW view_retval_2 AS SELECT func_retval_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_retval_2;
 Warnings:
@@ -149,8 +147,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe UUID() function.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -411,8 +407,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_retval_2 returning value from function func_retval_1 returning value from unsafe @@hostname variable.
 CREATE VIEW view_retval_2 AS SELECT func_retval_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_retval_2;
 Warnings:
@@ -475,8 +469,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe @@hostname variable.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -657,8 +649,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -1049,8 +1039,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -1245,8 +1233,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_retval_2 returning value from function func_retval_1 returning value from unsafe UDF.
 CREATE VIEW view_retval_2 AS SELECT func_retval_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_retval_2;
 Warnings:
@@ -1309,8 +1295,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe UDF.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -1551,8 +1535,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
 * binlog_format = STATEMENT: expect 1 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -1823,14 +1805,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
 * binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -2099,9 +2073,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe several times.
 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 * binlog_format = STATEMENT: expect 2 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
@@ -2235,9 +2206,6 @@ DROP TRIGGER trig_1;
 
 Invoking view view_sidef_1 invoking statement that is unsafe several times.
 CREATE VIEW view_sidef_1 AS SELECT multi_unsafe_func();
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 * binlog_format = STATEMENT: expect 2 warnings.
 INSERT INTO t1 SELECT * FROM view_sidef_1;
 Warnings:
@@ -2281,9 +2249,6 @@ DROP TRIGGER trig_2;
 
 Invoking view view_sidef_2 invoking view view_sidef_1 invoking statement that is unsafe several times.
 CREATE VIEW view_sidef_2 AS SELECT * FROM view_sidef_1;
-Warnings:
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
-Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 * binlog_format = STATEMENT: expect 2 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
diff --git a/mysql-test/suite/compat/oracle/r/sequence.result b/mysql-test/suite/compat/oracle/r/sequence.result
new file mode 100644
index 00000000000..e3bf9d4daee
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sequence.result
@@ -0,0 +1,60 @@
+SET sql_mode=ORACLE;
+CREATE SEQUENCE s1;
+SHOW CREATE SEQUENCE s1;
+Table	Create Table
+s1	CREATE SEQUENCE "s1" start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle
+SELECT s1.currval;
+s1.currval
+NULL
+SELECT s1.nextval;
+s1.nextval
+1
+SELECT s1.nextval;
+s1.nextval
+2
+SELECT s1.nextval;
+s1.nextval
+3
+EXPLAIN EXTENDED SELECT s1.nextval;
+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 "s1.nextval"
+SELECT nextval(s1);
+nextval(s1)
+4
+EXPLAIN EXTENDED SELECT s1.currval;
+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 "s1.currval"
+SELECT lastval(s1);
+lastval(s1)
+4
+DROP SEQUENCE s1;
+CREATE SEQUENCE s1;
+CREATE VIEW v1 AS SELECT s1.nextval AS a;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+VIEW_DEFINITION
+select nextval(`test`.`s1`) AS `a`
+SELECT * FROM v1;
+a
+1
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS select nextval("test"."s1") AS "a"	latin1	latin1_swedish_ci
+DROP VIEW v1;
+DROP SEQUENCE s1;
+CREATE SEQUENCE s1;
+CREATE VIEW v1 AS SELECT s1.currval AS a;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+VIEW_DEFINITION
+select lastval(`test`.`s1`) AS `a`
+SELECT * FROM v1;
+a
+NULL
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE VIEW "v1" AS select lastval("test"."s1") AS "a"	latin1	latin1_swedish_ci
+DROP VIEW v1;
+DROP SEQUENCE s1;
diff --git a/mysql-test/suite/compat/oracle/t/sequence.test b/mysql-test/suite/compat/oracle/t/sequence.test
new file mode 100644
index 00000000000..bc861c03014
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sequence.test
@@ -0,0 +1,33 @@
+--source include/have_binlog_format_row.inc
+
+SET sql_mode=ORACLE;
+
+CREATE SEQUENCE s1;
+SHOW CREATE SEQUENCE s1;
+SELECT s1.currval;
+SELECT s1.nextval;
+SELECT s1.nextval;
+SELECT s1.nextval;
+EXPLAIN EXTENDED SELECT s1.nextval;
+SELECT nextval(s1);
+EXPLAIN EXTENDED SELECT s1.currval;
+SELECT lastval(s1);
+DROP SEQUENCE s1;
+
+
+CREATE SEQUENCE s1;
+CREATE VIEW v1 AS SELECT s1.nextval AS a;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+SELECT * FROM v1;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+DROP SEQUENCE s1;
+
+
+CREATE SEQUENCE s1;
+CREATE VIEW v1 AS SELECT s1.currval AS a;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='v1';
+SELECT * FROM v1;
+SHOW CREATE VIEW v1;
+DROP VIEW v1;
+DROP SEQUENCE s1;
diff --git a/mysql-test/suite/sql_sequence/binlog.result b/mysql-test/suite/sql_sequence/binlog.result
new file mode 100644
index 00000000000..80746dcdfcf
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/binlog.result
@@ -0,0 +1,31 @@
+create or replace sequence s1 cache 3;
+select next value for s1, min_value from s1 where max_value> 1;
+next value for s1	min_value
+1	1
+select next value for s1, min_value from s1 where max_value> 2;
+next value for s1	min_value
+2	1
+select next value for s1, min_value from s1 where max_value> 3;
+next value for s1	min_value
+3	1
+select next value for s1, min_value from s1 where max_value> 4;
+next value for s1	min_value
+4	1
+drop sequence s1;
+"Runnig SHOW BINLOG EVENTS"
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+#	#	Gtid	1	#	GTID #-#-#
+#	#	Query	1	#	use `test`; create or replace sequence s1 cache 3
+#	#	Gtid	1	#	BEGIN GTID #-#-#
+#	#	Annotate_rows	1	#	select next value for s1, min_value from s1 where max_value> 1
+#	#	Table_map	1	#	table_id: 30 (test.s1)
+#	#	Write_rows_v1	1	#	table_id: 30 flags: STMT_END_F
+#	#	Query	1	#	COMMIT
+#	#	Gtid	1	#	BEGIN GTID #-#-#
+#	#	Annotate_rows	1	#	select next value for s1, min_value from s1 where max_value> 4
+#	#	Table_map	1	#	table_id: 30 (test.s1)
+#	#	Write_rows_v1	1	#	table_id: 30 flags: STMT_END_F
+#	#	Query	1	#	COMMIT
+#	#	Gtid	1	#	GTID #-#-#
+#	#	Query	1	#	use `test`; DROP TABLE `s1` /* generated by server */
+RESET MASTER;
diff --git a/mysql-test/suite/sql_sequence/binlog.test b/mysql-test/suite/sql_sequence/binlog.test
new file mode 100644
index 00000000000..4ad74e04083
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/binlog.test
@@ -0,0 +1,26 @@
+--source include/have_udf.inc
+--source include/have_log_bin.inc
+--source include/binlog_start_pos.inc
+
+#
+# Testing binary logging of sequences
+#
+
+--let $pos=`select $binlog_start_pos + 73`
+--let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1)
+--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1)
+
+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;
+drop sequence s1;
+
+--echo "Runnig SHOW BINLOG EVENTS"
+--replace_column 1 # 2 # 5 #
+--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VERSIONS/
+--disable_query_log
+--eval SHOW BINLOG EVENTS FROM $binlog_start;
+--enable_query_log
+RESET MASTER;
diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result
new file mode 100644
index 00000000000..70f9745d409
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/create.result
@@ -0,0 +1,497 @@
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+create or replace sequence t1 engine=myisam;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=MyISAM SEQUENCE=1
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	0	0
+create or replace sequence t1 engine=innodb;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=InnoDB SEQUENCE=1
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	0	0
+create or replace sequence t1 engine=maria;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=Aria SEQUENCE=1
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	0	0
+create or replace sequence t1 engine=archive;
+ERROR HY000: Table storage engine 'ARCHIVE' does not support the create option 'SEQUENCE'
+show create table t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+create or replace sequence t1 start with 10;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+10	1	9223372036854775806	10	1	1000	0	0
+create or replace sequence t1 minvalue=11;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+11	11	9223372036854775806	11	1	1000	0	0
+create or replace sequence t1 maxvalue=13 increment by -1;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+13	-9223372036854775807	13	13	-1	1000	0	0
+create or replace sequence t1 increment by -1 cache 100;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+-1	-9223372036854775807	-1	-1	-1	100	0	0
+create or replace sequence t1 cycle;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	1	0
+create or replace sequence t1 nocycle;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	0	0
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+create or replace sequence t1 cycle minvalue= 14;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+14	14	9223372036854775806	14	1	1000	1	0
+create or replace sequence t1 cycle increment by -1;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 cycle ENGINE=MyISAM
+drop sequence t1;
+create sequence if not exists t1;
+create sequence if not exists t1 start with 10;
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	0	0
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+10	10	11	10	1	0	1	0
+create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+10	-10	11	10	10	10	1	0
+create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE;
+create or replace sequence t1 start with 10 maxvalue 10;
+create or replace sequence t1 start with 10 minvalue 10;
+create or replace sequence t1 start with 10 minvalue 10 maxvalue 11 cycle;
+create or replace sequence t1 start with 10 maxvalue=9223372036854775806;
+create or replace sequence t1 start with 10 minvalue=-9223372036854775807;
+drop sequence if exists t1;
+create sequence t1 increment by 0;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	0	1000	0	0
+drop sequence t1;
+create table t1 (a int);
+show create sequence t1;
+ERROR HY000: 'test.t1' is not SEQUENCE
+drop sequence t1;
+ERROR 42S02: 'test.t1' is not a SEQUENCE
+drop sequence if exists t1;
+Warnings:
+Note	4066	Unknown SEQUENCE: 'test.t1'
+create sequence t1 start with 10 maxvalue=9;
+ERROR HY000: Sequence 'test.t1' values are conflicting
+create sequence t1 minvalue= 100 maxvalue=10;
+ERROR HY000: Sequence 'test.t1' values are conflicting
+create sequence t1 start with 9 minvalue=10;
+ERROR HY000: Sequence 'test.t1' values are conflicting
+create or replace sequence t1 maxvalue=13, increment by -1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' increment by -1' at line 1
+create or replace sequence t1 start with= 10 maxvalue=13;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 10 maxvalue=13' at line 1
+create or replace sequence t1 maxvalue=13, increment= -1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' increment= -1' at line 1
+create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NO MINVALUE' at line 1
+create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NO MINVALUE' at line 1
+create sequence t1 start with 10 maxvalue=9223372036854775807;
+ERROR HY000: Sequence 'test.t1' 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 or replace sequence t1 start with 10 NO MINVALUE minvalue=1;
+drop sequence t1;
+create sequence t1;
+show fields from t1;
+Field	Type	Null	Key	Default	Extra
+next_value	bigint(21)	NO		NULL	
+min_value	bigint(21)	NO		NULL	
+max_value	bigint(21)	NO		NULL	
+start	bigint(21)	NO		NULL	
+increment	bigint(21)	NO		NULL	
+cache	bigint(21)	NO		NULL	
+cycle	tinyint(1) unsigned	NO		NULL	
+round	bigint(21)	NO		NULL	
+flush tables;
+show fields from t1;
+Field	Type	Null	Key	Default	Extra
+next_value	bigint(21)	NO		NULL	
+min_value	bigint(21)	NO		NULL	
+max_value	bigint(21)	NO		NULL	
+start	bigint(21)	NO		NULL	
+increment	bigint(21)	NO		NULL	
+cache	bigint(21)	NO		NULL	
+cycle	tinyint(1) unsigned	NO		NULL	
+round	bigint(21)	NO		NULL	
+create or replace sequence t1 engine=aria;
+show fields from t1;
+Field	Type	Null	Key	Default	Extra
+next_value	bigint(21)	NO		NULL	
+min_value	bigint(21)	NO		NULL	
+max_value	bigint(21)	NO		NULL	
+start	bigint(21)	NO		NULL	
+increment	bigint(21)	NO		NULL	
+cache	bigint(21)	NO		NULL	
+cycle	tinyint(1) unsigned	NO		NULL	
+round	bigint(21)	NO		NULL	
+show fields from t1;
+Field	Type	Null	Key	Default	Extra
+next_value	bigint(21)	NO		NULL	
+min_value	bigint(21)	NO		NULL	
+max_value	bigint(21)	NO		NULL	
+start	bigint(21)	NO		NULL	
+increment	bigint(21)	NO		NULL	
+cache	bigint(21)	NO		NULL	
+cycle	tinyint(1) unsigned	NO		NULL	
+round	bigint(21)	NO		NULL	
+flush tables;
+create or replace sequence t1 comment= "test 1";
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 1'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=MyISAM SEQUENCE=1 COMMENT='test 1'
+create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 2'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=MyISAM MIN_ROWS=1 MAX_ROWS=2 SEQUENCE=1 COMMENT='test 2'
+create or replace sequence t1 start=1 increment= 2;
+create or replace sequence t1 start 1 increment 2;
+drop sequence t1;
+CREATE TABLE t1 (
+`next_value` bigint(21) NOT NULL,
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL
+) sequence=1;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_value` bigint(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL
+) ENGINE=MyISAM SEQUENCE=1
+drop sequence t1;
+CREATE OR REPLACE TABLE t1 (
+`next_val` bigint(21) NOT NULL,
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (next_val)
+CREATE OR REPLACE TABLE t1 (
+`next_value` int(21) NOT NULL,
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (next_value)
+CREATE OR REPLACE TABLE t1 (
+`next_val` bigint(21) NOT NULL,
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` bigint(21) unsigned NOT NULL, /* error */
+`round` bigint(21) NOT NULL
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (next_val)
+CREATE OR REPLACE TABLE t1 (
+`next_value` bigint(21),
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (next_value)
+CREATE OR REPLACE TABLE t1 (
+`next_value` bigint(21) NOT NULL,
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL, 
+extra_field bigint(21)
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (Wrong number of columns)
+CREATE OR REPLACE TABLE t1 (
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`next_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (min_value)
+CREATE OR REPLACE TABLE t1 (
+`next_value` bigint(21) NOT NULL,
+`min_value` bigint(21) NOT NULL,
+`max_value` bigint(21) NOT NULL,
+`start` bigint(21) NOT NULL,
+`increment` bigint(21) NOT NULL,
+`cache` bigint(21) NOT NULL,
+`cycle` tinyint(1) unsigned NOT NULL,
+`round` bigint(21) NOT NULL,
+key key1 (next_value)
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys)
+drop sequence if exists t1;
+Warnings:
+Note	4066	Unknown SEQUENCE: 'test.t1'
+create sequence t1;
+create sequence t2;
+create table t3 (a int) engine=myisam;
+select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog="test";
+table_catalog	table_schema	table_name	table_type
+CREATE SEQUENCE s1;
+drop sequence s1;
+drop sequence if exists t1,t2,t3,t4;
+Warnings:
+Note	4066	Unknown SEQUENCE: 'test.t3'
+Note	4066	Unknown SEQUENCE: 'test.t4'
+drop table if exists t1,t2,t3;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+Note	1051	Unknown table 'test.t2'
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop table t1,t2,s1;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop table if exists t1,t2,s1,s2;
+Warnings:
+Note	1051	Unknown table 'test.s2'
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop sequence t1,t2,s1,s2;
+ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.t2,test.s2'
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop sequence if exists t1,t2,s1,s2;
+Warnings:
+Note	4066	Unknown SEQUENCE: 'test.t1'
+Note	4066	Unknown SEQUENCE: 'test.t2'
+Note	4066	Unknown SEQUENCE: 'test.s2'
+drop table if exists t1,t2;
+CREATE TEMPORARY SEQUENCE s1;
+DROP SEQUENCE s1;
+DROP TEMPORARY SEQUENCE s1;
+ERROR 42S02: Unknown SEQUENCE: 'test.s1'
+CREATE TEMPORARY SEQUENCE s1;
+CREATE SEQUENCE s2;
+CREATE TEMPORARY TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+DROP TEMPORARY SEQUENCE t1,t2,s1,s2;
+ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.t2,test.s2'
+DROP TEMPORARY SEQUENCE s1;
+ERROR 42S02: Unknown SEQUENCE: 'test.s1'
+DROP TEMPORARY TABLE t1;
+DROP TABLE t1,t2,s1,s2;
+ERROR 42S02: Unknown table 'test.t1,test.s1'
+create view v1 as (select 1);
+CREATE SEQUENCE s1;
+DROP SEQUENCE s1,v1;
+ERROR 42S02: 'test.v1' is a view
+drop view v1;
+CREATE TEMPORARY SEQUENCE t1;
+select next value for t1;
+next value for t1
+1
+drop temporary table t1;
+select previous value for t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10;
+select next value for t1;
+next value for t1
+1
+select previous value for t1;
+previous value for t1
+1
+CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10;
+select previous value for t1;
+previous value for t1
+NULL
+select next value for t1;
+next value for t1
+100
+select previous value for t1;
+previous value for t1
+100
+drop temporary sequence t1;
+select previous value for t1;
+previous value for t1
+1
+drop sequence t1;
+CREATE TEMPORARY SEQUENCE t1 engine=innodb;
+select next value for t1;
+next value for t1
+1
+drop temporary table t1;
+select previous value for t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10 engine=innodb;
+select next value for t1;
+next value for t1
+1
+select previous value for t1;
+previous value for t1
+1
+CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb;
+select previous value for t1;
+previous value for t1
+NULL
+select next value for t1;
+next value for t1
+100
+select previous value for t1;
+previous value for t1
+100
+drop temporary sequence t1;
+select previous value for t1;
+previous value for t1
+1
+drop sequence t1;
+create table t1 (a int) engine=sql_sequence;
+ERROR 42000: Unknown storage engine 'sql_sequence'
diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test
new file mode 100644
index 00000000000..6cb6dedd91b
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/create.test
@@ -0,0 +1,374 @@
+#
+# Test create options with sequences
+#
+--source include/have_innodb.inc
+--source include/have_archive.inc
+
+drop table if exists t1;
+
+#
+# Check some sample engines
+#
+
+create or replace sequence t1 engine=myisam;
+show create sequence t1;
+show create table t1;
+select * from t1;
+create or replace sequence t1 engine=innodb;
+show create sequence t1;
+show create table t1;
+select * from t1;
+create or replace sequence t1 engine=maria;
+show create sequence t1;
+show create table t1;
+select * from t1;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+create or replace sequence t1 engine=archive;
+#
+# The following error should be fixed. We shouldn't delete old table on errors
+#
+--error ER_NO_SUCH_TABLE
+show create table t1;
+
+
+# Check start values
+create or replace sequence t1 start with 10;
+show create sequence t1;
+select * from t1;
+create or replace sequence t1 minvalue=11;
+show create sequence t1;
+select * from t1;
+create or replace sequence t1 maxvalue=13 increment by -1;
+show create sequence t1;
+select * from t1;
+
+create or replace sequence t1 increment by -1 cache 100;
+show create sequence t1;
+select * from t1;
+create or replace sequence t1 cycle;
+show create sequence t1;
+select * from t1;
+create or replace sequence t1 nocycle;
+show create sequence t1;
+select * from t1;
+show create sequence t1;
+create or replace sequence t1 cycle minvalue= 14;
+show create sequence t1;
+select * from t1;
+create or replace sequence t1 cycle increment by -1;
+show create sequence t1;
+
+drop sequence t1;
+create sequence if not exists t1;
+create sequence if not exists t1 start with 10;
+select * from t1;
+show create sequence t1;
+
+create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle;
+show create sequence t1;
+select * from t1;
+create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10;
+show create sequence t1;
+select * from t1;
+
+# NO MINVALUE, NO MAXVALUE
+create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE;
+
+# Some edge cases
+create or replace sequence t1 start with 10 maxvalue 10;
+create or replace sequence t1 start with 10 minvalue 10;
+create or replace sequence t1 start with 10 minvalue 10 maxvalue 11 cycle;
+create or replace sequence t1 start with 10 maxvalue=9223372036854775806;
+create or replace sequence t1 start with 10 minvalue=-9223372036854775807;
+drop sequence if exists t1;
+
+create sequence t1 increment by 0;
+show create sequence t1;
+select * from t1;
+drop sequence t1;
+
+#
+# Wrong usage and arguments to create sequence
+#
+
+create table t1 (a int);
+--error ER_WRONG_OBJECT
+show create sequence t1;
+--error ER_NOT_SEQUENCE2
+drop sequence t1;
+drop sequence if exists t1;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence t1 start with 10 maxvalue=9;
+--error ER_SEQUENCE_INVALID_DATA
+create sequence t1 minvalue= 100 maxvalue=10;
+--error ER_SEQUENCE_INVALID_DATA
+create sequence t1 start with 9 minvalue=10;
+--error ER_PARSE_ERROR
+create or replace sequence t1 maxvalue=13, increment by -1;
+--error ER_PARSE_ERROR
+create or replace sequence t1 start with= 10 maxvalue=13;
+--error ER_PARSE_ERROR
+create or replace sequence t1 maxvalue=13, increment= -1;
+--error ER_PARSE_ERROR
+create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
+--error ER_PARSE_ERROR
+create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
+--error ER_SEQUENCE_INVALID_DATA
+create sequence t1 start with 10 maxvalue=9223372036854775807;
+--error ER_PARSE_ERROR
+create sequence t1 start with 10 minvalue=-9223372036854775808;
+
+# This should probably give an error
+create or replace sequence t1 start with 10 NO MINVALUE minvalue=1;
+drop sequence t1;
+
+#
+# Test with LIST COLUMNS as first command
+#
+create sequence t1;
+show fields from t1;
+flush tables;
+show fields from t1;
+create or replace sequence t1 engine=aria;
+show fields from t1;
+show fields from t1;
+flush tables;
+
+#
+# Test with other create options
+#
+
+create or replace sequence t1 comment= "test 1";
+show create sequence t1;
+show create table t1;
+create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2;
+show create sequence t1;
+show create table t1;
+create or replace sequence t1 start=1 increment= 2;
+create or replace sequence t1 start 1 increment 2;
+drop sequence t1;
+
+#
+# test with create table
+#
+
+CREATE TABLE t1 (
+  `next_value` bigint(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL
+) sequence=1;
+
+show create sequence t1;
+show create table t1;
+drop sequence t1;
+
+# Wrong column name
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `next_val` bigint(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL
+) sequence=1;
+
+# Wrong type
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `next_value` int(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL
+) sequence=1;
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `next_val` bigint(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` bigint(21) unsigned NOT NULL, /* error */
+  `round` bigint(21) NOT NULL
+) sequence=1;
+
+
+# Missing NOT NULL
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `next_value` bigint(21),
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL
+) sequence=1;
+
+# Extra field
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `next_value` bigint(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL, 
+  extra_field bigint(21)
+) sequence=1;
+
+# Wrong field order
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `next_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL
+) sequence=1;
+
+# key
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+CREATE OR REPLACE TABLE t1 (
+  `next_value` bigint(21) NOT NULL,
+  `min_value` bigint(21) NOT NULL,
+  `max_value` bigint(21) NOT NULL,
+  `start` bigint(21) NOT NULL,
+  `increment` bigint(21) NOT NULL,
+  `cache` bigint(21) NOT NULL,
+  `cycle` tinyint(1) unsigned NOT NULL,
+  `round` bigint(21) NOT NULL,
+  key key1 (next_value)
+) sequence=1;
+
+drop sequence if exists t1;
+
+#
+# DROP SEQUENCE
+#
+
+create sequence t1;
+create sequence t2;
+create table t3 (a int) engine=myisam;
+select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog="test";
+
+CREATE SEQUENCE s1;
+drop sequence s1;
+
+drop sequence if exists t1,t2,t3,t4;
+drop table if exists t1,t2,t3;
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop table t1,t2,s1;
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop table if exists t1,t2,s1,s2;
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+--error ER_UNKNOWN_SEQUENCES
+drop sequence t1,t2,s1,s2;
+drop table if exists t1,t2;
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE SEQUENCE s1;
+drop sequence if exists t1,t2,s1,s2;
+drop table if exists t1,t2;
+
+CREATE TEMPORARY SEQUENCE s1;
+DROP SEQUENCE s1;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE s1;
+
+CREATE TEMPORARY SEQUENCE s1;
+CREATE SEQUENCE s2;
+CREATE TEMPORARY TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE t1,t2,s1,s2;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE s1;
+DROP TEMPORARY TABLE t1;
+--error ER_BAD_TABLE_ERROR
+DROP TABLE t1,t2,s1,s2;
+
+create view v1 as (select 1);
+CREATE SEQUENCE s1;
+--error ER_IT_IS_A_VIEW
+DROP SEQUENCE s1,v1;
+drop view v1;
+
+#
+# CREATE TEMPORARY SEQUENCE
+#
+
+CREATE TEMPORARY SEQUENCE t1;
+select next value for t1;
+drop temporary table t1;
+--error ER_NO_SUCH_TABLE
+select previous value for t1;
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10;
+select next value for t1;
+select previous value for t1;
+CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10;
+select previous value for t1;
+select next value for t1;
+select previous value for t1;
+drop temporary sequence t1;
+select previous value for t1;
+drop sequence t1;
+
+CREATE TEMPORARY SEQUENCE t1 engine=innodb;
+select next value for t1;
+drop temporary table t1;
+--error ER_NO_SUCH_TABLE
+select previous value for t1;
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10 engine=innodb;
+select next value for t1;
+select previous value for t1;
+CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb;
+select previous value for t1;
+select next value for t1;
+select previous value for t1;
+drop temporary sequence t1;
+select previous value for t1;
+drop sequence t1;
+
+#
+# Check that we can't create anything with the sequence engine
+#
+
+--error ER_UNKNOWN_STORAGE_ENGINE
+create table t1 (a int) engine=sql_sequence;
diff --git a/mysql-test/suite/sql_sequence/disabled.def b/mysql-test/suite/sql_sequence/disabled.def
new file mode 100644
index 00000000000..507617dd75d
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/disabled.def
@@ -0,0 +1,2 @@
+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-master.opt b/mysql-test/suite/sql_sequence/gtid-master.opt
new file mode 100644
index 00000000000..dd4fb8c5f9a
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/gtid-master.opt
@@ -0,0 +1,3 @@
+--binlog_format=row
+--query_cache_type=1
+--log-slave-updates
diff --git a/mysql-test/suite/sql_sequence/gtid-slave.opt b/mysql-test/suite/sql_sequence/gtid-slave.opt
new file mode 100644
index 00000000000..dc0ff1864e0
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/gtid-slave.opt
@@ -0,0 +1,4 @@
+--binlog_format=row
+--query_cache_type=1
+--read_only=true
+--log-slave-updates
diff --git a/mysql-test/suite/sql_sequence/gtid.result b/mysql-test/suite/sql_sequence/gtid.result
new file mode 100644
index 00000000000..f9a2d8ab60a
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/gtid.result
@@ -0,0 +1,722 @@
+include/master-slave.inc
+Warnings:
+Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
+Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
+[connection master]
+create database s_db;
+grant all on s_db.* to normal_1@'%' identified by 'pass';
+grant all on test.* to normal_2@'%' identified by 'pass';
+grant all on s_db.* to normal_3@'%' identified by 'pass';
+grant all on test.* to normal_4@'%' identified by 'pass';
+set global read_only=on;
+###########################################
+master and slave sync sequence.
+###########################################
+use s_db;
+create sequence s1;
+show create table s1;
+Table	Create Table
+s1	CREATE SEQUENCE `s1` (
+  `currval` bigint(21) NOT NULL COMMENT 'current value',
+  `nextval` bigint(21) NOT NULL COMMENT 'next value',
+  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
+  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+  `start` bigint(21) NOT NULL COMMENT 'start value',
+  `increment` bigint(21) NOT NULL COMMENT 'increment value',
+  `cache` bigint(21) NOT NULL COMMENT 'cache size',
+  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+use s_db;
+show create table s1;
+Table	Create Table
+s1	CREATE SEQUENCE `s1` (
+  `currval` bigint(21) NOT NULL COMMENT 'current value',
+  `nextval` bigint(21) NOT NULL COMMENT 'next value',
+  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
+  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+  `start` bigint(21) NOT NULL COMMENT 'start value',
+  `increment` bigint(21) NOT NULL COMMENT 'increment value',
+  `cache` bigint(21) NOT NULL COMMENT 'cache size',
+  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+use s_db;
+drop sequence s1;
+###########################################
+not support create table engine=sequence.
+###########################################
+create table t(id int)engine=sequence;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+create table t(id int)engine=innodb;
+alter table t engine=sequence;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+drop table t;
+###########################################
+not support alter sequence table.
+###########################################
+create sequence s2;
+alter table s2 add id int;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+alter table s2 add index ind_x(start);
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+drop sequence s2;
+###########################################
+not support create temproary sequence.
+###########################################
+create temporary sequence s2;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sequence s2' at line 1
+###########################################
+all invalid sequence value
+###########################################
+use s_db;
+create sequence s2 start with 1
+minvalue 1
+maxvalue 100000
+increment by 1
+cache 10000
+cycle;
+drop sequence s2;
+create sequence s2 start with 1
+minvalue 1
+maxvalue 100000
+increment by 1
+cache 10000
+nocycle;
+drop sequence s2;
+create sequence s2 start with 1
+minvalue 1
+maxvalue 100000
+increment by 1
+nocache
+nocycle;
+drop sequence s2;
+create sequence s2 start with 1
+minvalue 5
+maxvalue 100000
+increment by 1
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+create sequence s2 start with 1
+minvalue 5
+maxvalue 5
+increment by 1
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+create sequence s2 start with 1
+minvalue 5
+maxvalue 4
+increment by 1
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+create sequence s2 start with 1
+minvalue 5
+maxvalue 4
+increment by 0
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+###########################################
+global read lock prevent query sequence
+###########################################
+use s_db;
+create sequence s_db.s1;
+flush table with read lock;
+select * for s1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+unlock tables;
+drop sequence s_db.s1;
+###########################################
+session setting
+###########################################
+use s_db;
+create sequence s1;
+set session sequence_read_skip_cache=true;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+0
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+0
+set session sequence_read_skip_cache=false;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+2
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	3	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+4
+drop sequence s1;
+###########################################
+query cache test
+###########################################
+use s_db;
+show global variables like 'query_cache_type';
+Variable_name	Value
+query_cache_type	ON
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+show status like 'Qcache_inserts';
+Variable_name	Value
+Qcache_inserts	0
+###########################################
+priv test
+###########################################
+create sequence s_db.s1;
+select * for s_db.s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+create sequence s_db.s2;
+drop sequence s_db.s2;
+select * for s_db.s1;
+ERROR 42000: SELECT command denied to user 'normal_2'@'localhost' for table 's1'
+create sequence s_db.s2;
+ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table 's2'
+drop sequence s_db.s1;
+###########################################
+run out sequence value
+###########################################
+use s_db;
+create sequence s_t start with 1 cache 2 maxvalue 5;
+create table t(id int);
+insert into t values(1111);
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' has been run out.
+insert into t select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' has been run out.
+commit;
+select * from t;
+id
+1111
+1
+2
+3
+4
+5
+use s_db;
+select * from t;
+id
+1111
+1
+2
+3
+4
+5
+use s_db;
+drop sequence s_t;
+drop table t;
+###########################################
+read_only prevent query sequence
+###########################################
+create sequence s_db.s1;
+show global variables like 'read_only';
+Variable_name	Value
+read_only	OFF
+select * for s_db.s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+show global variables like 'read_only';
+Variable_name	Value
+read_only	ON
+select * for s_db.s1;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+drop sequence s_db.s1;
+###########################################
+update based table
+###########################################
+use s_db;
+create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle;
+use s_db;
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	20	1	1	5	1	0
+select nextval for s_t;
+nextval
+1
+select nextval from s_t;
+nextval
+7
+------------------------------------------
+master update nextval;
+------------------------------------------
+select nextval for s_t;
+nextval
+2
+update s_t set nextval= 11;
+commit;
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	11	1	20	1	1	5	1	0
+------------------------------------------
+show slave nextval;
+------------------------------------------
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	11	1	20	1	1	5	1	0
+set session sequence_read_skip_cache=off;
+select * for s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	11	1	20	1	1	5	1	0
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	17	1	20	1	1	5	1	0
+------------------------------------------
+update into invalid sequence
+------------------------------------------
+select nextval for s_t;
+nextval
+12
+update s_t set nextval= 11,start=10, minvalue=11;
+commit;
+create table t_1(id int);
+insert into t_1 value(1111);
+select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' structure or number is invalid.
+insert into t_1 select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' structure or number is invalid.
+commit;
+select * from t_1;
+id
+1111
+------------------------------------------
+delete sequence row
+------------------------------------------
+delete from s_t;
+commit;
+select nextval for s_t;
+nextval
+drop sequence s_t;
+drop table t_1;
+###########################################
+test transaction context (innodb)
+###########################################
+------------------------------------------
+transaction table and sequence
+normal transaction commit
+------------------------------------------
+use s_db;
+set session sequence_read_skip_cache=off;
+create sequence s_1 cache 5;
+create table t_1(id int)engine=innodb;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 values(2222);
+commit;
+select * from t_1;
+id
+1111
+1
+2
+2222
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+------------------------------------------
+normal transaction rollback
+------------------------------------------
+begin;
+insert into t_1 values(3333);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+rollback;
+select * from t_1;
+id
+1111
+1
+2
+2222
+select nextval for s_1;
+nextval
+11
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+use s_db;
+drop sequence s_1;
+drop table t_1;
+###########################################
+test transaction context (myisam)
+###########################################
+------------------------------------------
+transaction table and sequence
+normal transaction commit
+------------------------------------------
+use s_db;
+set session sequence_read_skip_cache=off;
+create sequence s_1 cache 5;
+create table t_1(id int)engine=myisam;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 values(2222);
+commit;
+select * from t_1;
+id
+1111
+1
+2
+2222
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+------------------------------------------
+normal transaction rollback
+------------------------------------------
+begin;
+insert into t_1 values(3333);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+rollback;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+select nextval for s_1;
+nextval
+11
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+use s_db;
+drop sequence s_1;
+drop table t_1;
+###########################################
+close binlog
+###########################################
+use s_db;
+create sequence s1 cache 2;
+select nextval for s1;
+nextval
+1
+select nextval for s1;
+nextval
+2
+select nextval for s1;
+nextval
+3
+select nextval for s1;
+nextval
+4
+commit;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	9223372036854775807	1	1	2	0	0
+------------------------------------------
+close session binlog.
+------------------------------------------
+set session sql_log_bin=off;
+select nextval for s1;
+nextval
+5
+select nextval for s1;
+nextval
+6
+select nextval for s1;
+nextval
+7
+select nextval for s1;
+nextval
+8
+set session sql_log_bin=on;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	10	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	9223372036854775807	1	1	2	0	0
+use s_db;
+drop sequence s1;
+###########################################
+statement binlog
+###########################################
+------------------------------------------
+set binlog_format=statement
+------------------------------------------
+set session sequence_read_skip_cache=off;
+set session binlog_format=statement;
+show  session variables like '%binlog_format%';
+Variable_name	Value
+binlog_format	STATEMENT
+create sequence s1 cache 2;
+select nextval for s1;
+ERROR HY000: Sequence requires binlog_format= row
+set session binlog_format=row;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	4	1	9223372036854775807	1	1	2	0	0
+set session sequence_read_skip_cache=off;
+use s_db;
+drop sequence s1;
+###########################################
+test savepoint
+###########################################
+set session sequence_read_skip_cache=off;
+set session binlog_format=row;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+begin;
+insert into t1 values(1111);
+savepoint sp1;
+insert into t1 select nextval for s1;
+insert into t1 select nextval for s1;
+insert into t1 select nextval for s1;
+insert into t1 values(2222);
+select * from t1;
+id
+1111
+1
+2
+3
+2222
+rollback to sp1;
+select * from t1;
+id
+1111
+select nextval for s1;
+nextval
+4
+commit;
+drop sequence s1;
+drop table t1;
+###########################################
+test proc
+###########################################
+set session sequence_read_skip_cache=off;
+use s_db;
+create table t(id int)engine=innodb;
+create procedure p1()
+begin
+create sequence s1 cache 2;
+end//
+create procedure p2()
+begin
+insert into t select nextval for s1;
+commit;
+end//
+call p1();
+call p2();
+call p2();
+call p2();
+call p2();
+select * from t;
+id
+1
+2
+3
+4
+use s_db;
+select * from t;
+id
+1
+2
+3
+4
+drop table t;
+drop sequence s1;
+drop procedure p1;
+drop procedure p2;
+###########################################
+test trigger
+###########################################
+set session sequence_read_skip_cache=off;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+create table t2(id int)engine=innodb;
+CREATE TRIGGER tri_1
+before INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t1 select nextval for s1;
+END//
+begin;
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+select * from t2;
+id
+1111
+1111
+1111
+1111
+select * from t1;
+id
+1
+2
+3
+4
+rollback;
+select * from t2;
+id
+select * from t1;
+id
+select nextval for s1;
+nextval
+5
+drop trigger tri_1;
+drop table t1;
+drop table t2;
+drop sequence s1;
+###########################################
+test function
+###########################################
+set session sequence_read_skip_cache=off;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+CREATE function f1() returns int
+BEGIN
+INSERT INTO t1 select nextval for s1;
+return (1);
+END//
+begin;
+select f1();
+f1()
+1
+select f1();
+f1()
+1
+select f1();
+f1()
+1
+select f1();
+f1()
+1
+select * from t1;
+id
+1
+2
+3
+4
+rollback;
+select * from t1;
+id
+select nextval for s1;
+nextval
+5
+drop function f1;
+drop table t1;
+drop sequence s1;
+use s_db;
+drop database s_db;
+drop user normal_1@'%';
+drop user normal_2@'%';
+drop user normal_3@'%';
+drop user normal_4@'%';
+include/rpl_end.inc
diff --git a/mysql-test/suite/sql_sequence/gtid.test b/mysql-test/suite/sql_sequence/gtid.test
new file mode 100644
index 00000000000..30717a4e53c
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/gtid.test
@@ -0,0 +1,660 @@
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+--source include/have_innodb.inc
+
+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';
+
+--sync_slave_with_master
+
+connect(m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT);
+connect(m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT);
+
+connect(s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT);
+connect(s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT);
+
+connection slave;
+set global read_only=on;
+
+--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;
+
+--echo ###########################################
+--echo not support create table engine=sequence.
+--echo ###########################################
+connection master;
+
+--error ER_UNKNOWN_STORAGE_ENGINE
+create table t(id int)engine=sequence;
+
+create table t(id int)engine=innodb;
+
+--error ER_UNKNOWN_STORAGE_ENGINE
+alter table t engine=sequence;
+
+drop table t;
+--echo ###########################################
+--echo not support alter sequence table.
+--echo ###########################################
+connection master;
+
+create sequence s2;
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+alter table s2 add id int;
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+alter table s2 add index ind_x(start);
+drop sequence s2;
+
+--echo ###########################################
+--echo Support create temporary sequence.
+--echo ###########################################
+connection master;
+
+create temporary sequence s2;
+drop temporary sequence s2;
+
+--echo ###########################################
+--echo all invalid sequence value
+--echo ###########################################
+
+connection master;
+use s_db;
+create sequence s2 start with 1
+  minvalue 1
+  maxvalue 100000
+  increment by 1
+  cache 10000
+  cycle;
+drop sequence s2;
+create sequence s2 start with 1
+  minvalue 1
+  maxvalue 100000
+  increment by 1
+  cache 10000
+  nocycle;
+drop sequence s2;
+create sequence s2 start with 1
+  minvalue 1
+  maxvalue 100000
+  increment by 1
+  nocache
+  nocycle;
+drop sequence s2;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 100000
+  increment by 1
+  nocache
+  nocycle;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 5
+  increment by 1
+  nocache
+  nocycle;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 4
+  increment by 1
+  nocache
+  nocycle;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 4
+  increment by 0
+  nocache
+  nocycle;
+
+--echo ###########################################
+--echo global read lock prevent query sequence
+--echo ###########################################
+connection master;
+use s_db;
+create sequence s_db.s1;
+flush table with read lock;
+--error ER_CANT_UPDATE_WITH_READLOCK
+select next value for s1;
+
+unlock tables;
+
+drop sequence s_db.s1;
+
+--echo ###########################################
+--echo query cache test
+--echo ###########################################
+connection master;
+use s_db;
+show global variables like 'query_cache_type';
+
+show status like 'Qcache_hits';
+show status like 'Qcache_inserts';
+
+--echo ###########################################
+--echo priv test
+--echo ###########################################
+connection m_normal_1;
+create sequence s_db.s1;
+select next value for s_db.s1;
+create sequence s_db.s2;
+drop sequence s_db.s2;
+
+
+connection m_normal_2;
+--error ER_TABLEACCESS_DENIED_ERROR
+select next value for s_db.s1;
+--error ER_TABLEACCESS_DENIED_ERROR
+create sequence s_db.s2;
+
+connection m_normal_1;
+drop sequence s_db.s1;
+
+--echo ###########################################
+--echo run out sequence value
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s_t start with 1 cache 2 maxvalue 5;
+create table t(id int);
+insert into t values(1111);
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+--error ER_SEQUENCE_RUN_OUT
+insert into t select next value for s_t;
+--error ER_SEQUENCE_RUN_OUT
+insert into t select next value for s_t;
+commit;
+select * from t;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t;
+
+connection m_normal_1;
+use s_db;
+drop sequence s_t;
+drop table t;
+
+--echo ###########################################
+--echo read_only prevent query sequence
+--echo ###########################################
+connection m_normal_1;
+create sequence s_db.s1;
+show global variables like 'read_only';
+select next value for s_db.s1;
+
+connection s_normal_3;
+show global variables like 'read_only';
+--error ER_OPTION_PREVENTS_STATEMENT
+select next value for s_db.s1;
+
+connection m_normal_1;
+drop sequence s_db.s1;
+
+--echo ###########################################
+--echo update based table
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from s_t;
+
+
+connection m_normal_1;
+select next value for s_t;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+select * from s_t;
+
+--echo ------------------------------------------
+--echo master update nextval;
+--echo ------------------------------------------
+connection m_normal_1;
+select next value for s_t;
+update s_t set next_value= 11;
+commit;
+
+select * from s_t;
+
+connection master;
+--sync_slave_with_master
+
+--echo ------------------------------------------
+--echo show slave nextval;
+--echo ------------------------------------------
+connection s_normal_3;
+select * from s_t;
+
+connection m_normal_1;
+select next value for s_t;
+select * from s_t;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+select * from s_t;
+
+
+--echo ------------------------------------------
+--echo update into invalid sequence
+--echo ------------------------------------------
+connection m_normal_1;
+select * from s_t;
+--error ER_SEQUENCE_INVALID_DATA
+update s_t set next_value= 11,start=10, min_value=11;
+commit;
+
+create table t_1(id int);
+insert into t_1 value(1111);
+select next value for s_t;
+insert into t_1 select next value for s_t;
+commit;
+
+select * from t_1;
+
+--echo ------------------------------------------
+--echo delete sequence row
+--echo ------------------------------------------
+connection m_normal_1;
+--error ER_ILLEGAL_HA
+delete from s_t;
+commit;
+
+select next value for s_t;
+
+connection m_normal_1;
+drop sequence s_t;
+drop table t_1;
+
+--echo ###########################################
+--echo test transaction context (innodb)
+--echo ###########################################
+
+--echo ------------------------------------------
+--echo transaction table and sequence
+--echo normal transaction commit
+--echo ------------------------------------------
+connection m_normal_1;
+use s_db;
+create sequence s_1 cache 5;
+
+create table t_1(id int)engine=innodb;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 values(2222);
+commit;
+
+select * from t_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+--echo ------------------------------------------
+--echo normal transaction rollback
+--echo ------------------------------------------
+connection m_normal_1;
+begin;
+insert into t_1 values(3333);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+
+select * from t_1;
+rollback;
+
+select * from t_1;
+select next value for s_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s_1;
+drop table t_1;
+
+--echo ###########################################
+--echo test transaction context (myisam)
+--echo ###########################################
+
+--echo ------------------------------------------
+--echo transaction table and sequence
+--echo normal transaction commit
+--echo ------------------------------------------
+connection m_normal_1;
+use s_db;
+create sequence s_1 cache 5;
+
+create table t_1(id int)engine=myisam;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 values(2222);
+commit;
+
+select * from t_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+--echo ------------------------------------------
+--echo normal transaction rollback
+--echo ------------------------------------------
+connection m_normal_1;
+begin;
+insert into t_1 values(3333);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+
+select * from t_1;
+rollback;
+
+select * from t_1;
+select next value for s_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s_1;
+drop table t_1;
+
+--echo ###########################################
+--echo close binlog
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s1 cache 2;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+
+commit;
+select * from s1;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from s1;
+
+--echo ------------------------------------------
+--echo close session binlog.
+--echo ------------------------------------------
+connection master;
+set session sql_log_bin=off;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+
+set session sql_log_bin=on;
+select * from s1;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from s1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s1;
+
+--echo ###########################################
+--echo statement binlog
+--echo ###########################################
+--echo ------------------------------------------
+--echo set binlog_format=statement
+--echo ------------------------------------------
+connection master;
+set session binlog_format=statement;
+show  session variables like '%binlog_format%';
+create sequence s1 cache 2;
+--error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
+select next value for s1;
+
+set session binlog_format=row;
+select next value for s1;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from s1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s1;
+
+--echo ###########################################
+--echo test savepoint
+--echo ###########################################
+connection master;
+set session binlog_format=row;
+
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+
+begin;
+insert into t1 values(1111);
+savepoint sp1;
+insert into t1 select next value for s1;
+insert into t1 select next value for s1;
+insert into t1 select next value for s1;
+
+insert into t1 values(2222);
+
+select * from t1;
+rollback to sp1;
+select * from t1;
+select next value for s1;
+
+commit;
+
+drop sequence s1;
+drop table t1;
+
+--echo ###########################################
+--echo test proc
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create table t(id int)engine=innodb;
+
+delimiter //;
+
+create procedure p1()
+begin
+  create sequence s1 cache 2;
+end//
+
+create procedure p2()
+begin
+  insert into t select next value for s1;
+  commit;
+end//
+
+delimiter  ;//
+
+call p1();
+call p2();
+call p2();
+call p2();
+call p2();
+
+select * from t;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from t;
+
+connection m_normal_1;
+drop table t;
+drop sequence s1;
+drop procedure p1;
+drop procedure p2;
+
+--echo ###########################################
+--echo test trigger
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+create table t2(id int)engine=innodb;
+
+delimiter //;
+CREATE TRIGGER tri_1
+  before INSERT ON t2 FOR EACH ROW
+BEGIN
+  INSERT INTO t1 select next value for s1;
+END//
+delimiter  ;//
+
+begin;
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+
+select * from t2;
+select * from t1;
+rollback;
+select * from t2;
+select * from t1;
+
+select next value for s1;
+
+
+drop trigger tri_1;
+drop table t1;
+drop table t2;
+drop sequence s1;
+
+--echo ###########################################
+--echo test function
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+
+delimiter //;
+CREATE function f1() returns int
+BEGIN
+  INSERT INTO t1 select next value for s1;
+  return (1);
+END//
+delimiter  ;//
+
+begin;
+select f1();
+select f1();
+select f1();
+select f1();
+
+select * from t1;
+rollback;
+select * from t1;
+
+select next value for s1;
+
+drop function f1;
+drop table t1;
+drop sequence s1;
+
+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/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result
new file mode 100644
index 00000000000..fc28152a2b7
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/next.result
@@ -0,0 +1,404 @@
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=MyISAM SEQUENCE=1
+select next value for t1;
+next value for t1
+1
+select next_value,round from t1;
+next_value	round
+3	0
+select next value for t1;
+next value for t1
+2
+select next_value,round from t1;
+next_value	round
+3	0
+select next value for t1;
+next value for t1
+3
+select next_value,round from t1;
+next_value	round
+5	0
+select next value for t1;
+next value for t1
+4
+select next_value,round from t1;
+next_value	round
+5	0
+select next value for t1;
+next value for t1
+5
+select next_value,round from t1;
+next_value	round
+7	0
+select next value for t1;
+next value for t1
+6
+select next_value,round from t1;
+next_value	round
+7	0
+select next value for t1;
+next value for t1
+7
+select next_value,round from t1;
+next_value	round
+9	0
+select next value for t1;
+next value for t1
+8
+select next_value,round from t1;
+next_value	round
+9	0
+select next value for t1;
+next value for t1
+9
+select next_value,round from t1;
+next_value	round
+11	0
+select next value for t1;
+next value for t1
+10
+select next_value,round from t1;
+next_value	round
+11	0
+select next value for t1;
+next value for t1
+1
+select next_value,round from t1;
+next_value	round
+3	1
+select NEXT VALUE for t1,seq from seq_1_to_20;
+NEXT VALUE for t1	seq
+2	1
+3	2
+4	3
+5	4
+6	5
+7	6
+8	7
+9	8
+10	9
+1	10
+2	11
+3	12
+4	13
+5	14
+6	15
+7	16
+8	17
+9	18
+10	19
+1	20
+drop sequence t1;
+CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria;
+select next value for t1;
+next value for t1
+10
+select next_value,round from t1;
+next_value	round
+8	0
+select next value for t1;
+next value for t1
+9
+select next_value,round from t1;
+next_value	round
+8	0
+select next value for t1;
+next value for t1
+8
+select next_value,round from t1;
+next_value	round
+6	0
+select next value for t1;
+next value for t1
+7
+select next_value,round from t1;
+next_value	round
+6	0
+select next value for t1;
+next value for t1
+6
+select next_value,round from t1;
+next_value	round
+4	0
+select next value for t1;
+next value for t1
+5
+select next_value,round from t1;
+next_value	round
+4	0
+select next value for t1;
+next value for t1
+4
+select next_value,round from t1;
+next_value	round
+2	0
+select next value for t1;
+next value for t1
+3
+select next_value,round from t1;
+next_value	round
+2	0
+select next value for t1;
+next value for t1
+2
+select next_value,round from t1;
+next_value	round
+0	0
+select next value for t1;
+next value for t1
+1
+select next_value,round from t1;
+next_value	round
+0	0
+select next value for t1;
+next value for t1
+10
+select next_value,round from t1;
+next_value	round
+8	1
+select NEXT VALUE for t1,seq from seq_1_to_20;
+NEXT VALUE for t1	seq
+9	1
+8	2
+7	3
+6	4
+5	5
+4	6
+3	7
+2	8
+1	9
+10	10
+9	11
+8	12
+7	13
+6	14
+5	15
+4	16
+3	17
+2	18
+1	19
+10	20
+drop sequence t1;
+CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle;
+select next value for t1;
+next value for t1
+8
+select next value for t1;
+next value for t1
+9
+select next value for t1;
+next value for t1
+10
+select previous value for t1;
+previous value for t1
+10
+select next value for t1;
+ERROR HY000: Sequence 'test.t1' has run out
+select previous value for t1;
+previous value for t1
+NULL
+select next value for t1;
+ERROR HY000: Sequence 'test.t1' has run out
+drop sequence t1;
+create sequence s1 start with 1 cache 2 maxvalue 5;
+select next value for s1;
+next value for s1
+1
+select next value for s1;
+next value for s1
+2
+select next value for s1;
+next value for s1
+3
+select next value for s1;
+next value for s1
+4
+select next value for s1;
+next value for s1
+5
+select next value for s1;
+ERROR HY000: Sequence 'test.s1' has run out
+drop sequence s1;
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10;
+select next value for t1;
+next value for t1
+1
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+11	1	100	1	1	10	0	0
+flush tables;
+select next value for t1;
+next value for t1
+11
+select nextval(t1);
+nextval(t1)
+12
+drop sequence t1;
+CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+select previous value for t9;
+previous value for t9
+NULL
+select next value for t9;
+next value for t9
+1
+select previous value for t9, lastval(t9);
+previous value for t9	lastval(t9)
+1	1
+select next value for t9;
+next value for t9
+2
+select previous value for t9, lastval(t9);
+previous value for t9	lastval(t9)
+2	2
+select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20;
+seq	previous value for t9	NEXT VALUE for t9	previous value for t9
+1	2	3	3
+2	3	4	4
+3	4	5	5
+4	5	6	6
+5	6	7	7
+6	7	8	8
+7	8	9	9
+8	9	10	10
+9	10	1	1
+10	1	2	2
+11	2	3	3
+12	3	4	4
+13	4	5	5
+14	5	6	6
+15	6	7	7
+16	7	8	8
+17	8	9	9
+18	9	10	10
+19	10	1	1
+20	1	2	2
+select * from t9;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+6	1	10	1	1	5	1	2
+drop sequence t9;
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+select next value for t1;
+next value for t1
+1
+select previous value for t1;
+previous value for t1
+1
+flush tables;
+select previous value for t1;
+previous value for t1
+1
+drop sequence t1;
+select previous value for t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+select previous value for t1;
+previous value for t1
+NULL
+select next value for t1;
+next value for t1
+5
+select previous value for t1;
+previous value for t1
+5
+drop sequence t1;
+CREATE or replace SEQUENCE  s1  MINVALUE 1 MAXVALUE 9999999999
+INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb;
+show create table s1;
+Table	Create Table
+s1	CREATE TABLE `s1` (
+  `next_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` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
+) ENGINE=InnoDB SEQUENCE=1
+select * from s1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3984356	1	9999999999	3984356	1	20	1	0
+select NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+3984356
+select NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+3984357
+select NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+3984358
+select * from s1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3984376	1	9999999999	3984356	1	20	1	0
+FLUSH TABLES;
+select * from s1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3984376	1	9999999999	3984356	1	20	1	0
+select NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+3984376
+select * from s1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3984396	1	9999999999	3984356	1	20	1	0
+drop sequence s1;
+CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+explain select next value for t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain select next value for t1, min_value from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+CREATE SEQUENCE s1;
+CREATE TABLE t1 (a int);
+insert into t1 values (next value for s1);
+insert into t1 values (next value for s1);
+select * from t1;
+a
+1
+2
+drop table t1,s1;
+CREATE SEQUENCE s1;
+CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam;
+insert into t1 values (),(),(),(),(),(),();
+update t1 set b= next value for s1 where a <= 3;
+select * from t1;
+a	b
+1	1
+2	2
+3	3
+4	0
+5	0
+6	0
+7	0
+drop table t1,s1;
+CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb';
+select * from s1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3984356	1	9999999999	3984356	1	0	1	0
+select next value for s1;
+next value for s1
+3984356
+drop sequence s1;
+create table t1 (a int);
+select next value for t1;
+ERROR 42S02: 'test.t1' is not a SEQUENCE
+drop table t1;
+create sequence t1;
+select next value for t1;
+next value for t1
+1
+select next value for t1, min_value;
+ERROR 42S22: Unknown column 'min_value' in 'field list'
+drop sequence t1;
diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test
new file mode 100644
index 00000000000..426ee5709a1
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/next.test
@@ -0,0 +1,201 @@
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+
+#
+# Test sequence generation
+#
+
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle;
+show create table t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+
+select NEXT VALUE for t1,seq from seq_1_to_20;
+
+drop sequence t1;
+
+CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+
+select NEXT VALUE for t1,seq from seq_1_to_20;
+
+drop sequence t1;
+
+CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle;
+select next value for t1;
+select next value for t1;
+select next value for t1;
+select previous value for t1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for t1;
+select previous value for t1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for t1;
+drop sequence t1;
+
+create sequence s1 start with 1 cache 2 maxvalue 5;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for s1;
+drop sequence s1;
+
+#
+# Test that flush tables jumps to next next_value
+#
+
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10;
+select next value for t1;
+select * from t1;
+flush tables;
+select next value for t1;
+select nextval(t1);
+drop sequence t1;
+
+#
+# Test currval/previous
+#
+
+CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+select previous value for t9;
+select next value for t9;
+select previous value for t9, lastval(t9);
+select next value for t9;
+select previous value for t9, lastval(t9);
+select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20;
+select * from t9;
+drop sequence t9;
+
+#
+# Check what happens when one refers to a sequence that has been closed/deleted
+#
+
+CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+select next value for t1;
+select previous value for t1;
+flush tables;
+select previous value for t1;
+drop sequence t1;
+--error ER_NO_SUCH_TABLE
+select previous value for t1;
+CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+select previous value for t1;
+select next value for t1;
+select previous value for t1;
+drop sequence t1;
+
+# This failed in an early build
+
+CREATE or replace SEQUENCE  s1  MINVALUE 1 MAXVALUE 9999999999
+INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb;
+show create table s1;
+select * from s1;
+select NEXT VALUE FOR s1;
+select NEXT VALUE FOR s1;
+select NEXT VALUE FOR s1;
+select * from s1;
+FLUSH TABLES;
+select * from s1;
+select NEXT VALUE FOR s1;
+select * from s1;
+drop sequence s1;
+
+#
+# Explain
+#
+
+CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
+explain select next value for t1;
+explain select next value for t1, min_value from t1;
+drop table t1;
+
+#
+# Using insert with NEXT VALUE
+#
+
+CREATE SEQUENCE s1;
+CREATE TABLE t1 (a int);
+insert into t1 values (next value for s1);
+insert into t1 values (next value for s1);
+select * from t1;
+drop table t1,s1;
+
+#
+# Using update with NEXT VALUE
+#
+
+CREATE SEQUENCE s1;
+CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam;
+insert into t1 values (),(),(),(),(),(),();
+update t1 set b= next value for s1 where a <= 3;
+select * from t1;
+drop table t1,s1;
+
+#
+# NO CACHE and InnoDB
+#
+
+CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb';
+select * from s1;
+select next value for s1;
+drop sequence s1;
+
+
+#
+# Some error testing
+#
+
+create table t1 (a int);
+--error ER_NOT_SEQUENCE
+select next value for t1;
+drop table t1;
+
+create sequence t1;
+select next value for t1;
+--error ER_BAD_FIELD_ERROR
+select next value for t1, min_value;
+drop sequence t1;
diff --git a/mysql-test/suite/sql_sequence/read_only.result b/mysql-test/suite/sql_sequence/read_only.result
new file mode 100644
index 00000000000..3f6a95610dd
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/read_only.result
@@ -0,0 +1,25 @@
+create sequence s1 cache 2 engine=innodb;
+connection default;
+show global variables like 'innodb_read_only';
+Variable_name	Value
+innodb_read_only	ON
+use test;
+set session binlog_format= row;
+###########################################
+read_only create error.
+###########################################
+show global variables like 'innodb_read_only';
+Variable_name	Value
+innodb_read_only	ON
+use test;
+create sequence s2 cache 5 engine=innodb;
+ERROR HY000: Can't create table `test`.`s2` (errno: 165 "Table is read only")
+###########################################
+read_only query error.
+###########################################
+select next value for s1;
+ERROR HY000: Table 's1' is read only
+select next value for s1;
+ERROR HY000: Table 's1' is read only
+select next value for s1;
+ERROR HY000: Table 's1' is read only
diff --git a/mysql-test/suite/sql_sequence/read_only.test b/mysql-test/suite/sql_sequence/read_only.test
new file mode 100644
index 00000000000..d8743617ad2
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/read_only.test
@@ -0,0 +1,39 @@
+--source include/have_innodb.inc
+
+#
+# Test innodb read only
+#
+
+create sequence s1 cache 2 engine=innodb;
+
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--shutdown_server 10
+--source include/wait_until_disconnected.inc
+--enable_reconnect
+--exec echo "restart":--innodb_read_only=1 > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+
+connection default;
+show global variables like 'innodb_read_only';
+use test;
+set session binlog_format= row;
+
+--echo ###########################################
+--echo read_only create error.
+--echo ###########################################
+
+show global variables like 'innodb_read_only';
+use test;
+
+--error ER_CANT_CREATE_TABLE
+create sequence s2 cache 5 engine=innodb;
+
+--echo ###########################################
+--echo read_only query error.
+--echo ###########################################
+--error ER_OPEN_AS_READONLY
+select next value for s1;
+--error ER_OPEN_AS_READONLY
+select next value for s1;
+--error ER_OPEN_AS_READONLY
+select next value for s1;
diff --git a/mysql-test/suite/sql_sequence/replication-master.opt b/mysql-test/suite/sql_sequence/replication-master.opt
new file mode 100644
index 00000000000..bbea8eabc91
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/replication-master.opt
@@ -0,0 +1 @@
+--binlog_format=row --query_cache_type=1
diff --git a/mysql-test/suite/sql_sequence/replication-slave.opt b/mysql-test/suite/sql_sequence/replication-slave.opt
new file mode 100644
index 00000000000..a4e068e4b43
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/replication-slave.opt
@@ -0,0 +1 @@
+--binlog_format=row --query_cache_type=1 --read_only=true
diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result
new file mode 100644
index 00000000000..eed4c130a9b
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/replication.result
@@ -0,0 +1,1030 @@
+include/master-slave.inc
+Warnings:
+Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
+Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
+[connection master]
+create database s_db;
+grant all on s_db.* to normal_1@'%' identified by 'pass';
+grant all on test.* to normal_2@'%' identified by 'pass';
+grant all on s_db.* to normal_3@'%' identified by 'pass';
+grant all on test.* to normal_4@'%' identified by 'pass';
+set global read_only=on;
+###########################################
+master and slave sync sequence.
+###########################################
+use s_db;
+create sequence s1;
+show create table s1;
+Table	Create Table
+s1	CREATE SEQUENCE `s1` (
+  `currval` bigint(21) NOT NULL COMMENT 'current value',
+  `nextval` bigint(21) NOT NULL COMMENT 'next value',
+  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
+  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+  `start` bigint(21) NOT NULL COMMENT 'start value',
+  `increment` bigint(21) NOT NULL COMMENT 'increment value',
+  `cache` bigint(21) NOT NULL COMMENT 'cache size',
+  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+use s_db;
+show create table s1;
+Table	Create Table
+s1	CREATE SEQUENCE `s1` (
+  `currval` bigint(21) NOT NULL COMMENT 'current value',
+  `nextval` bigint(21) NOT NULL COMMENT 'next value',
+  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
+  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+  `start` bigint(21) NOT NULL COMMENT 'start value',
+  `increment` bigint(21) NOT NULL COMMENT 'increment value',
+  `cache` bigint(21) NOT NULL COMMENT 'cache size',
+  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+use s_db;
+drop sequence s1;
+###########################################
+not support create table engine=sequence.
+###########################################
+create table t(id int)engine=sequence;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+create table t(id int)engine=innodb;
+alter table t engine=sequence;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+drop table t;
+###########################################
+not support alter sequence table.
+###########################################
+create sequence s2;
+alter table s2 add id int;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+alter table s2 add index ind_x(start);
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+drop sequence s2;
+###########################################
+support create sequence
+###########################################
+create table t_1(id int);
+show create sequence t_1;
+ERROR HY000: 's_db.t_1' is not SEQUENCE
+drop table t_1;
+CREATE SEQUENCE `s2` (
+`currval` bigint(21) NOT NULL COMMENT 'current value',
+`nextval` bigint(21) NOT NULL COMMENT 'next value',
+`minvalue` bigint(21) NOT NULL COMMENT 'min value',
+`maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+`start` bigint(21) NOT NULL COMMENT 'start value',
+`increment` bigint(21) NOT NULL COMMENT 'increment value',
+`cache` bigint(21) NOT NULL COMMENT 'cache size',
+`cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+`round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+insert into s2 values(0, 0, 1, 10, 1, 2, 1, 1, 0);
+commit;
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	10	1	2	1	1	0
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	3	1	10	1	2	1	1	0
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	5	1	10	1	2	1	1	0
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	10	1	2	1	1	0
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	9	1	10	1	2	1	1	0
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	10	1	2	1	1	1
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	3	1	10	1	2	1	1	1
+select * from s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	5	1	10	1	2	1	1	1
+select * from s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	5	1	10	1	2	1	1	1
+drop sequence s2;
+CREATE SEQUENCE `s2` (
+`currval` bigint(21) NULL COMMENT 'current value',
+`nextval` bigint(21) NOT NULL COMMENT 'next value',
+`minvalue` bigint(21) NOT NULL COMMENT 'min value',
+`maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+`start` bigint(21) NOT NULL COMMENT 'start value',
+`increment` bigint(21) NOT NULL COMMENT 'increment value',
+`cache` bigint(21) NOT NULL COMMENT 'cache size',
+`cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+`round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+CREATE SEQUENCE `s2` (
+`rrval` bigint(21) NULL COMMENT 'current value',
+`nextval` bigint(21) NOT NULL COMMENT 'next value',
+`minvalue` bigint(21) NOT NULL COMMENT 'min value',
+`maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+`start` bigint(21) NOT NULL COMMENT 'start value',
+`increment` bigint(21) NOT NULL COMMENT 'increment value',
+`cache` bigint(21) NOT NULL COMMENT 'cache size',
+`cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+`round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+CREATE SEQUENCE `s2` (
+`currval` bigint(21) NOT NULL AUTO_INCREMENT PRIMARY KEY  COMMENT 'current value',
+`nextval` bigint(21) NOT NULL COMMENT 'next value',
+`minvalue` bigint(21) NOT NULL COMMENT 'min value',
+`maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+`start` bigint(21) NOT NULL COMMENT 'start value',
+`increment` bigint(21) NOT NULL COMMENT 'increment value',
+`cache` bigint(21) NOT NULL COMMENT 'cache size',
+`cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+`round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+CREATE SEQUENCE `s2` (
+`currval` bigint(21) NOT NULL COMMENT 'current value',
+`nextval` bigint(21) NOT NULL COMMENT 'next value',
+`minvalue` bigint(21) NOT NULL COMMENT 'min value',
+`maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+`start` bigint(21) NOT NULL COMMENT 'start value',
+`increment` bigint(21) NOT NULL COMMENT 'increment value',
+`cache` bigint(21) NOT NULL COMMENT 'cache size',
+`cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+`round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=myisam DEFAULT CHARSET=latin1;
+show create sequence s2;
+Table	Create Table
+s2	CREATE SEQUENCE `s2` (
+  `currval` bigint(21) NOT NULL COMMENT 'current value',
+  `nextval` bigint(21) NOT NULL COMMENT 'next value',
+  `minvalue` bigint(21) NOT NULL COMMENT 'min value',
+  `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
+  `start` bigint(21) NOT NULL COMMENT 'start value',
+  `increment` bigint(21) NOT NULL COMMENT 'increment value',
+  `cache` bigint(21) NOT NULL COMMENT 'cache size',
+  `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
+  `round` bigint(21) NOT NULL COMMENT 'already how many round'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop sequence s2;
+###########################################
+select sequence syntax test
+###########################################
+create sequence s2;
+create table t2 (id int);
+select * from s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	9223372036854775807	1	1	10000	0	0
+select * from t2;
+id
+insert into t2 select nextval for s2;
+commit;
+select * for s2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	2	1	9223372036854775807	1	1	10000	0	0
+select * for t2;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
+select * from s2, t2;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round	id
+0	10002	1	9223372036854775807	1	1	10000	0	0	1
+select * for s2, t2;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' t2' at line 1
+drop sequence s2;
+drop table t2;
+###########################################
+support rename, not support truncate
+###########################################
+create sequence s2;
+alter table s2 rename to s2_1;
+ERROR HY000: Table storage engine 'sequence' does not support the create option 'SEQUENCE'
+rename table s2 to s2_1;
+select * for s2_1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+truncate table s2_1;
+ERROR HY000: Table storage engine for 's2_1' doesn't have this option
+rename table s2_1 to s2;
+drop sequence s2;
+###########################################
+not support create temproary sequence.
+###########################################
+create temporary sequence s2;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sequence s2' at line 1
+###########################################
+all invalid sequence value
+###########################################
+use s_db;
+create sequence s2 start with 1
+minvalue 1
+maxvalue 100000
+increment by 1
+cache 10000
+cycle;
+drop sequence s2;
+create sequence s2 start with 1
+minvalue 1
+maxvalue 100000
+increment by 1
+cache 10000
+nocycle;
+drop sequence s2;
+create sequence s2 start with 1
+minvalue 1
+maxvalue 100000
+increment by 1
+nocache
+nocycle;
+drop sequence s2;
+create sequence s2 start with 1
+minvalue 5
+maxvalue 100000
+increment by 1
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+create sequence s2 start with 1
+minvalue 5
+maxvalue 5
+increment by 1
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+create sequence s2 start with 1
+minvalue 5
+maxvalue 4
+increment by 1
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+create sequence s2 start with 1
+minvalue 5
+maxvalue 4
+increment by 0
+nocache
+nocycle;
+ERROR HY000: Sequence 's_db.s2' structure or number is invalid.
+###########################################
+global read lock prevent query sequence
+###########################################
+use s_db;
+create sequence s_db.s1;
+flush table with read lock;
+select * for s1;
+ERROR HY000: Can't execute the query because you have a conflicting read lock
+unlock tables;
+drop sequence s_db.s1;
+###########################################
+session setting
+###########################################
+use s_db;
+create sequence s1;
+set session sequence_read_skip_cache=true;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+0
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+0
+set session sequence_read_skip_cache=false;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+2
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	3	1	9223372036854775807	1	1	10000	0	0
+select nextval for s1;
+nextval
+4
+drop sequence s1;
+###########################################
+query cache test
+###########################################
+use s_db;
+show global variables like 'query_cache_type';
+Variable_name	Value
+query_cache_type	ON
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+show status like 'Qcache_inserts';
+Variable_name	Value
+Qcache_inserts	1
+###########################################
+priv test
+###########################################
+create sequence s_db.s1;
+select * for s_db.s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+create sequence s_db.s2;
+drop sequence s_db.s2;
+select * for s_db.s1;
+ERROR 42000: SELECT command denied to user 'normal_2'@'localhost' for table 's1'
+create sequence s_db.s2;
+ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table 's2'
+drop sequence s_db.s1;
+###########################################
+run out sequence value
+###########################################
+use s_db;
+create sequence s_t start with 1 cache 2 maxvalue 5;
+create table t(id int);
+insert into t values(1111);
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+insert into t select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' has been run out.
+insert into t select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' has been run out.
+commit;
+select * from t;
+id
+1111
+1
+2
+3
+4
+5
+use s_db;
+select * from t;
+id
+1111
+1
+2
+3
+4
+5
+use s_db;
+drop sequence s_t;
+drop table t;
+###########################################
+read_only prevent query sequence
+###########################################
+create sequence s_db.s1;
+show global variables like 'read_only';
+Variable_name	Value
+read_only	OFF
+select * for s_db.s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	10000	0	0
+show global variables like 'read_only';
+Variable_name	Value
+read_only	ON
+select * for s_db.s1;
+ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement
+drop sequence s_db.s1;
+###########################################
+update based table
+###########################################
+use s_db;
+create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle;
+use s_db;
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	0	1	20	1	1	5	1	0
+select nextval for s_t;
+nextval
+1
+select nextval from s_t;
+nextval
+7
+------------------------------------------
+master update nextval;
+------------------------------------------
+select nextval for s_t;
+nextval
+2
+update s_t set nextval= 11;
+commit;
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	11	1	20	1	1	5	1	0
+------------------------------------------
+show slave nextval;
+------------------------------------------
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	11	1	20	1	1	5	1	0
+set session sequence_read_skip_cache=off;
+select * for s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	11	1	20	1	1	5	1	0
+select * from s_t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	17	1	20	1	1	5	1	0
+------------------------------------------
+update into invalid sequence
+------------------------------------------
+select nextval for s_t;
+nextval
+12
+update s_t set nextval= 11,start=10, minvalue=11;
+commit;
+create table t_1(id int);
+insert into t_1 value(1111);
+select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' structure or number is invalid.
+insert into t_1 select nextval for s_t;
+ERROR HY000: Sequence 's_db.s_t' structure or number is invalid.
+commit;
+select * from t_1;
+id
+1111
+------------------------------------------
+delete sequence row
+------------------------------------------
+delete from s_t;
+commit;
+select nextval for s_t;
+nextval
+drop sequence s_t;
+drop table t_1;
+###########################################
+test transaction context (innodb)
+###########################################
+------------------------------------------
+transaction table and sequence
+normal transaction commit
+------------------------------------------
+use s_db;
+set session sequence_read_skip_cache=off;
+create sequence s_1 cache 5;
+create table t_1(id int)engine=innodb;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 values(2222);
+commit;
+select * from t_1;
+id
+1111
+1
+2
+2222
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+------------------------------------------
+normal transaction rollback
+------------------------------------------
+begin;
+insert into t_1 values(3333);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+rollback;
+select * from t_1;
+id
+1111
+1
+2
+2222
+select nextval for s_1;
+nextval
+11
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+use s_db;
+drop sequence s_1;
+drop table t_1;
+###########################################
+test transaction context (myisam)
+###########################################
+------------------------------------------
+transaction table and sequence
+normal transaction commit
+------------------------------------------
+use s_db;
+set session sequence_read_skip_cache=off;
+create sequence s_1 cache 5;
+create table t_1(id int)engine=myisam;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 values(2222);
+commit;
+select * from t_1;
+id
+1111
+1
+2
+2222
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+------------------------------------------
+normal transaction rollback
+------------------------------------------
+begin;
+insert into t_1 values(3333);
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+insert into t_1 select nextval for s_1;
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+rollback;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+select nextval for s_1;
+nextval
+11
+set session sequence_read_skip_cache=off;
+use s_db;
+select * from t_1;
+id
+1111
+1
+2
+2222
+3333
+3
+4
+5
+6
+7
+8
+9
+10
+use s_db;
+drop sequence s_1;
+drop table t_1;
+###########################################
+close binlog
+###########################################
+use s_db;
+create sequence s1 cache 2;
+select nextval for s1;
+nextval
+1
+select nextval for s1;
+nextval
+2
+select nextval for s1;
+nextval
+3
+select nextval for s1;
+nextval
+4
+commit;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	9223372036854775807	1	1	2	0	0
+------------------------------------------
+close session binlog.
+------------------------------------------
+set session sql_log_bin=off;
+select nextval for s1;
+nextval
+5
+select nextval for s1;
+nextval
+6
+select nextval for s1;
+nextval
+7
+select nextval for s1;
+nextval
+8
+set session sql_log_bin=on;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	10	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	1	9223372036854775807	1	1	2	0	0
+use s_db;
+drop sequence s1;
+###########################################
+statement binlog
+###########################################
+------------------------------------------
+set binlog_format=statement
+------------------------------------------
+set session sequence_read_skip_cache=off;
+set session binlog_format=statement;
+show  session variables like '%binlog_format%';
+Variable_name	Value
+binlog_format	STATEMENT
+create sequence s1 cache 2;
+select nextval for s1;
+ERROR HY000: Sequence requires binlog_format= row
+set session binlog_format=row;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	4	1	9223372036854775807	1	1	2	0	0
+set session sequence_read_skip_cache=off;
+use s_db;
+drop sequence s1;
+------------------------------------------
+set binlog_format=mixed
+------------------------------------------
+set session sequence_read_skip_cache=off;
+set session binlog_format=mixed;
+show  session variables like '%binlog_format%';
+Variable_name	Value
+binlog_format	MIXED
+create sequence s1 cache 2;
+select nextval for s1;
+ERROR HY000: Sequence requires binlog_format= row
+set session binlog_format=row;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	2	0	0
+use s_db;
+select * from s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	4	1	9223372036854775807	1	1	2	0	0
+set session sequence_read_skip_cache=off;
+use s_db;
+drop sequence s1;
+###########################################
+test savepoint
+###########################################
+set session sequence_read_skip_cache=off;
+set session binlog_format=row;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+begin;
+insert into t1 values(1111);
+savepoint sp1;
+insert into t1 select nextval for s1;
+insert into t1 select nextval for s1;
+insert into t1 select nextval for s1;
+insert into t1 values(2222);
+select * from t1;
+id
+1111
+1
+2
+3
+2222
+rollback to sp1;
+select * from t1;
+id
+1111
+select nextval for s1;
+nextval
+4
+commit;
+drop sequence s1;
+drop table t1;
+###########################################
+create as
+###########################################
+set session sequence_read_skip_cache=off;
+create sequence s1 cache 2;
+create table t as select * for s1;
+select * from t;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	9223372036854775807	1	1	2	0	0
+drop sequence s1;
+drop table t;
+###########################################
+test proc
+###########################################
+set session sequence_read_skip_cache=off;
+use s_db;
+create table t(id int)engine=innodb;
+create procedure p1()
+begin
+create sequence s1 cache 2;
+end//
+create procedure p2()
+begin
+insert into t select nextval for s1;
+commit;
+end//
+call p1();
+call p2();
+call p2();
+call p2();
+call p2();
+select * from t;
+id
+1
+2
+3
+4
+use s_db;
+select * from t;
+id
+1
+2
+3
+4
+drop table t;
+drop sequence s1;
+drop procedure p1;
+drop procedure p2;
+###########################################
+test trigger
+###########################################
+set session sequence_read_skip_cache=off;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+create table t2(id int)engine=innodb;
+CREATE TRIGGER tri_1
+before INSERT ON t2 FOR EACH ROW
+BEGIN
+INSERT INTO t1 select nextval for s1;
+END//
+begin;
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+select * from t2;
+id
+1111
+1111
+1111
+1111
+select * from t1;
+id
+1
+2
+3
+4
+rollback;
+select * from t2;
+id
+select * from t1;
+id
+select nextval for s1;
+nextval
+5
+drop trigger tri_1;
+drop table t1;
+drop table t2;
+drop sequence s1;
+###########################################
+test function
+###########################################
+set session sequence_read_skip_cache=off;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+CREATE function f1() returns int
+BEGIN
+INSERT INTO t1 select nextval for s1;
+return (1);
+END//
+begin;
+select f1();
+f1()
+1
+select f1();
+f1()
+1
+select f1();
+f1()
+1
+select f1();
+f1()
+1
+select * from t1;
+id
+1
+2
+3
+4
+rollback;
+select * from t1;
+id
+select nextval for s1;
+nextval
+5
+drop function f1;
+drop table t1;
+drop sequence s1;
+###########################################
+test value boundary
+###########################################
+use s_db;
+------------------------------------------
+round increment by round
+------------------------------------------
+create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	5	2	7	5	1	1	1	0
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	6	2	7	5	1	1	1	0
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	7	2	7	5	1	1	1	0
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	2	2	7	5	1	1	1	1
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	3	2	7	5	1	1	1	1
+drop sequence s1;
+create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle;
+select nextval, round for s1;
+nextval	round
+5	0
+select nextval, round for s1;
+nextval	round
+6	0
+select nextval, round for s1;
+nextval	round
+7	0
+select nextval, round for s1;
+ERROR HY000: Sequence 's_db.s1' has been run out.
+drop sequence s1;
+create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	2	1	3	2	3	0	1	0
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	3	2	3	0	1	1
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	3	2	3	0	1	2
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	3	2	3	0	1	3
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	1	1	3	2	3	0	1	4
+drop sequence s1;
+create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle;
+select * for s1;
+currval	nextval	minvalue	maxvalue	start	increment	cache	cycle	round
+0	2	1	3	2	3	2	0	0
+select * for s1;
+ERROR HY000: Sequence 's_db.s1' has been run out.
+drop sequence s1;
+------------------------------------------
+beyond ulonglong maxvalue
+------------------------------------------
+create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775807  cache 1 cycle;
+select nextval, round for s1;
+nextval	round
+9223372036854775805	0
+select nextval, round for s1;
+nextval	round
+9223372036854775806	0
+select nextval, round for s1;
+nextval	round
+9223372036854775807	0
+select nextval, round for s1;
+nextval	round
+9223372036854775804	1
+select nextval, round for s1;
+nextval	round
+9223372036854775805	1
+select nextval, round for s1;
+nextval	round
+9223372036854775806	1
+select nextval, round for s1;
+nextval	round
+9223372036854775807	1
+select nextval, round for s1;
+nextval	round
+9223372036854775804	2
+select nextval, round for s1;
+nextval	round
+9223372036854775805	2
+select nextval, round for s1;
+nextval	round
+9223372036854775806	2
+drop sequence s1;
+create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775807  cache 10 cycle;
+select nextval, round for s1;
+nextval	round
+9223372036854775805	0
+select nextval, round for s1;
+nextval	round
+9223372036854775806	0
+select nextval, round for s1;
+nextval	round
+9223372036854775807	0
+select nextval, round for s1;
+nextval	round
+9223372036854775804	1
+select nextval, round for s1;
+nextval	round
+9223372036854775805	1
+select nextval, round for s1;
+nextval	round
+9223372036854775806	1
+select nextval, round for s1;
+nextval	round
+9223372036854775807	1
+select nextval, round for s1;
+nextval	round
+9223372036854775804	2
+select nextval, round for s1;
+nextval	round
+9223372036854775805	2
+select nextval, round for s1;
+nextval	round
+9223372036854775806	2
+drop sequence s1;
+use s_db;
+drop database s_db;
+drop user normal_1@'%';
+drop user normal_2@'%';
+drop user normal_3@'%';
+drop user normal_4@'%';
+include/rpl_end.inc
diff --git a/mysql-test/suite/sql_sequence/replication.test b/mysql-test/suite/sql_sequence/replication.test
new file mode 100644
index 00000000000..bf7b6bbdbf7
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/replication.test
@@ -0,0 +1,881 @@
+--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;
+grant all on s_db.* to normal_1@'%' identified by 'pass';
+grant all on test.* to normal_2@'%' identified by 'pass';
+grant all on s_db.* to normal_3@'%' identified by 'pass';
+grant all on test.* to normal_4@'%' identified by 'pass';
+
+--sync_slave_with_master
+
+connect(m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT);
+connect(m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT);
+
+connect(s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT);
+connect(s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT);
+
+connection slave;
+set global read_only=on;
+
+--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;
+
+--echo ###########################################
+--echo not support create table engine=sequence.
+--echo ###########################################
+connection master;
+
+--error ER_UNKNOWN_STORAGE_ENGINE
+create table t(id int)engine=sequence;
+
+create table t(id int)engine=innodb;
+
+--error ER_UNKNOWN_STORAGE_ENGINE
+alter table t engine=sequence;
+
+drop table t;
+--echo ###########################################
+--echo not support alter sequence table.
+--echo ###########################################
+connection master;
+
+create sequence s2;
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+alter table s2 add id int;
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+alter table s2 add index ind_x(start);
+drop sequence s2;
+
+--echo ###########################################
+--echo support create sequence
+--echo ###########################################
+connection master;
+
+create table t_1(id int);
+--error ER_WRONG_OBJECT
+show create sequence t_1;
+
+drop table t_1;
+
+--error ER_PARSE_ERROR
+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 sequence=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(0, 1, 10, 1, 2, 1, 1, 0);
+commit;
+--error ER_PARSE_ERROR
+select * for s2;
+select NEXT VALUE for s2;
+select NEXT VALUE for s2;
+select NEXT VALUE for s2;
+select * from s2;
+commit;
+
+connection master;
+--sync_slave_with_master
+select * from s2;
+
+connection slave;
+select * from s2;
+
+
+connection master;
+drop sequence s2;
+
+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=myisam DEFAULT CHARSET=latin1 sequence=1;
+
+show create sequence s2;
+drop sequence s2;
+
+--echo ###########################################
+--echo select sequence syntax test
+--echo ###########################################
+connection master;
+create sequence s2;
+create table t2 (id int);
+
+select * from s2;
+select * from t2;
+insert into t2 select next value for s2;
+commit;
+
+select NEXT VALUE for s2;
+--error ER_NOT_SEQUENCE
+select NEXT VALUE for t2;
+
+select * from s2, t2;
+
+--error ER_PARSE_ERROR
+select * for s2;
+--error ER_PARSE_ERROR
+select * for s2, t2;
+
+connection master;
+drop sequence s2;
+drop table t2;
+
+--echo ###########################################
+--echo support rename, not support truncate
+--echo ###########################################
+connection master;
+
+create sequence s2;
+
+alter table s2 rename to s2_1;
+rename table s2_1 to s2_2;
+show create sequence s2_2;
+select * from s2_2;
+
+--error ER_ILLEGAL_HA
+truncate table s2_2;
+rename table s2_2 to s2;
+drop sequence s2;
+
+--echo ###########################################
+--echo all invalid sequence value
+--echo ###########################################
+
+connection master;
+use s_db;
+create sequence s2 start with 1
+  minvalue 1
+  maxvalue 100000
+  increment by 1
+  cache 10000
+  cycle;
+drop sequence s2;
+create sequence s2 start with 1
+  minvalue 1
+  maxvalue 100000
+  increment by 1
+  cache 10000
+  nocycle;
+drop sequence s2;
+create sequence s2 start with 1
+  minvalue 1
+  maxvalue 100000
+  increment by 1
+  nocache
+  nocycle;
+drop sequence s2;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 100000
+  increment by 1
+  nocache
+  nocycle;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 5
+  increment by 1
+  nocache
+  nocycle;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 4
+  increment by 1
+  nocache
+  nocycle;
+
+--error ER_SEQUENCE_INVALID_DATA
+create sequence s2 start with 1
+  minvalue 5
+  maxvalue 4
+  increment by 0
+  nocache
+  nocycle;
+
+--echo ###########################################
+--echo global read lock prevent query sequence
+--echo ###########################################
+connection master;
+use s_db;
+create sequence s_db.s1;
+flush table with read lock;
+--error ER_CANT_UPDATE_WITH_READLOCK
+select NEXT VALUE for s1;
+unlock tables;
+drop sequence s_db.s1;
+
+--echo ###########################################
+--echo query cache test
+--echo ###########################################
+connection master;
+use s_db;
+flush status;
+show global variables like 'query_cache_type';
+
+show status like 'Qcache_hits';
+show status like 'Qcache_inserts';
+
+--echo ###########################################
+--echo priv test
+--echo ###########################################
+connection m_normal_1;
+create sequence s_db.s1;
+select NEXT VALUE for s_db.s1;
+create sequence s_db.s2;
+drop sequence s_db.s2;
+
+
+connection m_normal_2;
+--error ER_TABLEACCESS_DENIED_ERROR
+select NEXT VALUE for s_db.s1;
+--error ER_TABLEACCESS_DENIED_ERROR
+create sequence s_db.s2;
+
+connection m_normal_1;
+drop sequence s_db.s1;
+
+--echo ###########################################
+--echo run out sequence value
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s_t start with 1 cache 2 maxvalue 5;
+create table t(id int);
+insert into t values(1111);
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+insert into t select next value for s_t;
+--error ER_SEQUENCE_RUN_OUT
+insert into t select next value for s_t;
+--error ER_SEQUENCE_RUN_OUT
+insert into t select next value for s_t;
+commit;
+select * from t;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t;
+
+connection m_normal_1;
+use s_db;
+drop sequence s_t;
+drop table t;
+
+--echo ###########################################
+--echo read_only prevent query sequence
+--echo ###########################################
+connection m_normal_1;
+create sequence s_db.s1;
+show global variables like 'read_only';
+select * from s_db.s1;
+
+connection s_normal_3;
+show global variables like 'read_only';
+--error ER_OPTION_PREVENTS_STATEMENT
+select next value for s_db.s1;
+
+connection m_normal_1;
+drop sequence s_db.s1;
+
+--echo ###########################################
+--echo update based table
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle engine=innodb;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from s_t;
+
+
+connection m_normal_1;
+select next value for s_t;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+select next_value from s_t;
+
+--echo ------------------------------------------
+--echo master update nextval;
+--echo ------------------------------------------
+connection m_normal_1;
+select next value for s_t;
+update s_t set next_value= 11;
+commit;
+
+select * from s_t;
+SELECT NEXT VALUE for s_t;
+
+connection master;
+--sync_slave_with_master
+
+--echo ------------------------------------------
+--echo show slave nextval;
+--echo ------------------------------------------
+connection s_normal_3;
+select * from s_t;
+
+connection m_normal_1;
+select next value for s_t;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+select * from s_t;
+
+
+--echo ------------------------------------------
+--echo update into invalid sequence
+--echo ------------------------------------------
+connection m_normal_1;
+select next value for s_t;
+select * from s_t;
+--error ER_SEQUENCE_INVALID_DATA
+update s_t set min_value=11, max_value=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;
+select * from s_t;
+
+--echo ------------------------------------------
+--echo delete sequence row
+--echo ------------------------------------------
+connection m_normal_1;
+--error ER_ILLEGAL_HA
+delete from s_t;
+commit;
+
+select next value for s_t;
+
+connection m_normal_1;
+drop sequence s_t;
+
+--echo ###########################################
+--echo test transaction context (innodb)
+--echo ###########################################
+
+--echo ------------------------------------------
+--echo transaction table and sequence
+--echo normal transaction commit
+--echo ------------------------------------------
+connection m_normal_1;
+use s_db;
+create sequence s_1 cache 5 engine=innodb;
+
+create table t_1(id int)engine=innodb;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 values(2222);
+commit;
+
+select * from t_1;
+select * from s_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+--echo ------------------------------------------
+--echo normal transaction rollback
+--echo ------------------------------------------
+connection m_normal_1;
+begin;
+insert into t_1 values(3333);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+
+select * from t_1;
+rollback;
+
+select * from t_1;
+select * from s_1;
+select next value for s_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s_1;
+drop table t_1;
+
+--echo ###########################################
+--echo test transaction context (myisam)
+--echo ###########################################
+
+--echo ------------------------------------------
+--echo transaction table and sequence
+--echo normal transaction commit
+--echo ------------------------------------------
+connection m_normal_1;
+use s_db;
+create sequence s_1 cache 5;
+
+create table t_1(id int)engine=myisam;
+begin;
+insert into t_1 values(1111);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 values(2222);
+commit;
+
+select * from t_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+--echo ------------------------------------------
+--echo normal transaction rollback
+--echo ------------------------------------------
+connection m_normal_1;
+begin;
+insert into t_1 values(3333);
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+insert into t_1 select next value for s_1;
+
+select * from t_1;
+rollback;
+
+select * from t_1;
+select next value for s_1;
+
+connection master;
+--sync_slave_with_master
+
+connection s_normal_3;
+use s_db;
+select * from t_1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s_1;
+drop table t_1;
+
+--echo ###########################################
+--echo close binlog
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s1 cache 2;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+
+commit;
+select * from s1;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from s1;
+
+--echo ------------------------------------------
+--echo close session binlog.
+--echo ------------------------------------------
+connection master;
+set session sql_log_bin=off;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+
+set session sql_log_bin=on;
+select * from s1;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from s1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s1;
+
+--echo ###########################################
+--echo statement binlog
+--echo ###########################################
+--echo ------------------------------------------
+--echo set binlog_format=statement
+--echo ------------------------------------------
+connection master;
+set session binlog_format=statement;
+show  session variables like '%binlog_format%';
+create sequence s1 cache 2;
+--error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
+select next value for s1;
+
+set session binlog_format=row;
+select next value for s1;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from s1;
+
+connection m_normal_1;
+use s_db;
+drop sequence s1;
+
+--echo ------------------------------------------
+--echo set binlog_format=mixed
+--echo ------------------------------------------
+connection master;
+set session binlog_format=mixed;
+show  session variables like '%binlog_format%';
+create sequence s1 cache 2;
+select next value for s1;
+
+set session binlog_format=row;
+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 ###########################################
+--echo test savepoint
+--echo ###########################################
+connection master;
+--sync_slave_with_master
+set session binlog_format=row;
+
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+
+begin;
+insert into t1 values(1111);
+savepoint sp1;
+insert into t1 select next value for s1;
+insert into t1 select next value for s1;
+insert into t1 select next value for s1;
+
+insert into t1 values(2222);
+
+select * from t1;
+rollback to sp1;
+select * from t1;
+select next value for s1;
+
+commit;
+
+drop sequence s1;
+drop table t1;
+
+connection master;
+--sync_slave_with_master
+
+--echo ###########################################
+--echo create as
+--echo ###########################################
+connection m_normal_1;
+
+create sequence s1 cache 2;
+create table t as select next value for s1;
+select * from t;
+
+drop sequence s1;
+drop table t;
+
+connection master;
+--sync_slave_with_master
+
+--echo ###########################################
+--echo test proc
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create table t(id int)engine=innodb;
+
+delimiter //;
+
+create procedure p1()
+begin
+  create sequence s1 cache 2;
+end//
+
+create procedure p2()
+begin
+  insert into t select next value for s1;
+  commit;
+end//
+
+delimiter  ;//
+
+call p1();
+call p2();
+call p2();
+call p2();
+call p2();
+
+select * from t;
+
+connection master;
+--sync_slave_with_master
+
+connection slave;
+use s_db;
+select * from t;
+
+connection m_normal_1;
+drop table t;
+drop sequence s1;
+drop procedure p1;
+drop procedure p2;
+
+--echo ###########################################
+--echo test trigger
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+create table t2(id int)engine=innodb;
+
+delimiter //;
+CREATE TRIGGER tri_1
+  before INSERT ON t2 FOR EACH ROW
+BEGIN
+  INSERT INTO t1 select next value for s1;
+END//
+delimiter  ;//
+
+begin;
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+insert into t2 values(1111);
+
+select * from t2;
+select * from t1;
+rollback;
+select * from t2;
+select * from t1;
+
+select next value for s1;
+
+
+drop trigger tri_1;
+drop table t1;
+drop table t2;
+drop sequence s1;
+
+--echo ###########################################
+--echo test function
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+create sequence s1 cache 2;
+create table t1(id int)engine=innodb;
+
+delimiter //;
+CREATE function f1() returns int
+BEGIN
+  INSERT INTO t1 select next value for s1;
+  return (1);
+END//
+delimiter  ;//
+
+begin;
+select f1();
+select f1();
+select f1();
+select f1();
+
+select * from t1;
+rollback;
+select * from t1;
+
+select next value for s1;
+
+drop function f1;
+drop table t1;
+drop sequence s1;
+
+--echo ###########################################
+--echo test value boundary
+--echo ###########################################
+connection m_normal_1;
+use s_db;
+
+--echo ------------------------------------------
+--echo round increment by round
+--echo ------------------------------------------
+create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 1 cycle;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+drop sequence s1;
+
+create sequence s1 start with 5 minvalue 2 maxvalue 7 cache 10 nocycle;
+select next value for s1;
+select next value for s1;
+select next value for s1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for s1;
+select * from s1;
+drop sequence s1;
+
+create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 nocache cycle;
+select next value for s1;
+select * from s1;
+select next value for s1;
+select * from s1;
+select next value for s1;
+select * from s1;
+select next value for s1;
+select * from s1;
+select next value for s1;
+select * from s1;
+drop sequence s1;
+
+create sequence s1 start with 2 minvalue 1 maxvalue 3 increment by 3 cache 2 nocycle;
+select next value for s1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for s1;
+drop sequence s1;
+
+--echo ------------------------------------------
+--echo beyond ulonglong maxvalue
+--echo ------------------------------------------
+create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806  cache 1 cycle;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+drop sequence s1;
+
+create sequence s1 start with 9223372036854775805 minvalue 9223372036854775804 maxvalue 9223372036854775806  cache 10 cycle;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+select next value for s1, round from s1;
+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/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 052d30563b2..f93f340b4fc 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	187
+GLOBAL_VALUE	189
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	187
+DEFAULT_VALUE	189
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	Maximum number of statement instruments.
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index 42a813b0782..c3a25c6bcce 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -25,7 +25,7 @@ eval CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
 eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
-eval CREATE FUNCTION sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
+eval CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
 eval CREATE FUNCTION lookup RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
@@ -238,7 +238,7 @@ DROP FUNCTION myfunc_double;
 --error ER_SP_DOES_NOT_EXIST
 DROP FUNCTION myfunc_nonexist;
 DROP FUNCTION myfunc_int;
-DROP FUNCTION sequence;
+DROP FUNCTION udf_sequence;
 DROP FUNCTION lookup;
 DROP FUNCTION reverse_lookup;
 DROP FUNCTION avgcost;
@@ -401,19 +401,19 @@ DROP TABLE const_len_bug;
 #
 
 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
-eval CREATE FUNCTION sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
+eval CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT PRIMARY KEY);
 INSERT INTO t1 VALUES (4),(3),(2),(1);
 INSERT INTO t2 SELECT * FROM t1;
 
-SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC;
-SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC;
+SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq ASC;
+SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq DESC;
 
-SELECT * FROM t1 WHERE a = sequence();
-SELECT * FROM t2 WHERE a = sequence();
+SELECT * FROM t1 WHERE a = udf_sequence();
+SELECT * FROM t2 WHERE a = udf_sequence();
 
-DROP FUNCTION sequence;
+DROP FUNCTION udf_sequence;
 DROP TABLE t1,t2;
 
 #
diff --git a/mysql-test/t/udf_notembedded.test b/mysql-test/t/udf_notembedded.test
index bf54af7256c..d6658604005 100644
--- a/mysql-test/t/udf_notembedded.test
+++ b/mysql-test/t/udf_notembedded.test
@@ -5,10 +5,10 @@
 # MDEV-8644 Using a UDF in a virtual column causes a crash when stopping the server
 #
 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
-eval create function sequence returns integer soname "$UDF_EXAMPLE_SO";
-create table t1 (n int key not null auto_increment,  msg int as (sequence()) virtual);
+eval create function udf_sequence returns integer soname "$UDF_EXAMPLE_SO";
+create table t1 (n int key not null auto_increment,  msg int as (udf_sequence()) virtual);
 select * from t1;
 source include/restart_mysqld.inc;
 drop table t1;
-drop function sequence;
+drop function udf_sequence;