mariadb/mysql-test/suite/sql_sequence/binlog.test

41 lines
1.1 KiB
Text
Raw Permalink Normal View History

--source include/have_sequence.inc
--source include/have_binlog_format_mixed_or_row.inc
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.
2017-03-25 23:36:56 +02:00
--source include/binlog_start_pos.inc
#
# Testing binary logging of sequences
#
--disable_query_log
reset master; # get rid of previous tests binlog
--enable_query_log
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.
2017-03-25 23:36:56 +02:00
create or replace sequence s1 cache 3;
--disable_ps2_protocol
select next value for s1, minimum_value from s1 where maximum_value> 1;
select next value for s1, minimum_value from s1 where maximum_value> 2;
select next value for s1, minimum_value from s1 where maximum_value> 3;
select next value for s1, minimum_value from s1 where maximum_value> 4;
--enable_ps2_protocol
#
# Alter sequence
#
alter sequence s1 maxvalue 1000;
# MDEV-24617 OPTIMIZE on a sequence causes unexpected
# ER_BINLOG_UNSAFE_STATEMENT The test below verifies no unsafe
# warnings anymore for any relavant commands that like OPTIMIZE can
# not produce ROW format events therefore the unsafe warning either.
optimize table s1;
analyze table s1;
repair table s1;
check table s1;
rename table s1 to tmp_s;
rename table tmp_s to s1;
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.
2017-03-25 23:36:56 +02:00
drop sequence s1;
--let $binlog_file = LAST
source include/show_binlog_events.inc;