From 81870e499ff14afacc7e9cabde65a626165f209f Mon Sep 17 00:00:00 2001 From: Anel Husakovic Date: Mon, 31 Aug 2020 16:36:32 +0200 Subject: MDEV-21786 mysqldump will forget sequence definition details on --no-data dump - Original patch was contributed by Jani Tolonen https://github.com/an3l/server/commits/bb-10.3-anel-MDEV-21786-dump-sequence which distinguishes data structure (linked list) of sequences from tables. - Added standard sql output to prevent future changes of sequences and disabled locks for sequences. - Added test case for `MDEV-20070: mysqldump won't work correct on sequences` where table column depends on sequence value. - Restore sequence last value in the following way: - Find `next_not_cached_value` and use it to `setval()` - We just need for logical restore, so don't execute `setval()` - `setval()` should be showed also in case of `--no-data` option. Reviewed-by: daniel@mariadb.org --- mysql-test/suite/sql_sequence/mysqldump.result | 32 ++++---------------------- 1 file changed, 4 insertions(+), 28 deletions(-) (limited to 'mysql-test/suite/sql_sequence') diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result index 5a3711ebda3..e6aedb57ea6 100644 --- a/mysql-test/suite/sql_sequence/mysqldump.result +++ b/mysql-test/suite/sql_sequence/mysqldump.result @@ -2,20 +2,8 @@ CREATE SEQUENCE a1 engine=aria; CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; insert into t1 values (1),(2); CREATE SEQUENCE x1 engine=innodb; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `a1` ( - `next_not_cached_value` bigint(21) NOT NULL, - `minimum_value` bigint(21) NOT NULL, - `maximum_value` bigint(21) NOT NULL, - `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', - `increment` bigint(21) NOT NULL COMMENT 'increment value', - `cache_size` bigint(21) unsigned NOT NULL, - `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', - `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' -) ENGINE=Aria SEQUENCE=1; -/*!40101 SET character_set_client = @saved_cs_client */; -INSERT INTO `a1` VALUES (1,1,9223372036854775806,1,1,1000,0,0); +CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +SELECT SETVAL(`a1`, 1, 0); /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t1` ( @@ -24,20 +12,8 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `t1` VALUES (1),(2); -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `x1` ( - `next_not_cached_value` bigint(21) NOT NULL, - `minimum_value` bigint(21) NOT NULL, - `maximum_value` bigint(21) NOT NULL, - `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', - `increment` bigint(21) NOT NULL COMMENT 'increment value', - `cache_size` bigint(21) unsigned NOT NULL, - `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', - `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' -) ENGINE=InnoDB SEQUENCE=1; -/*!40101 SET character_set_client = @saved_cs_client */; -INSERT INTO `x1` VALUES (1,1,9223372036854775806,1,1,1000,0,0); +CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +SELECT SETVAL(`x1`, 1, 0); DROP TABLE a1,t1,x1; set default_storage_engine=InnoDB; create sequence t1; -- cgit v1.2.1 From e183aec1d75ea7b424ebe237e6b1643961903f2d Mon Sep 17 00:00:00 2001 From: Anel Husakovic Date: Tue, 27 Oct 2020 12:16:53 +0100 Subject: MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL Reviewed-by: wlad@mariadb.com --- mysql-test/suite/sql_sequence/next.result | 5 +++++ mysql-test/suite/sql_sequence/next.test | 7 +++++++ 2 files changed, 12 insertions(+) (limited to 'mysql-test/suite/sql_sequence') diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index fe761baa032..76991fbe68c 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -541,5 +541,10 @@ CREATE VIEW v AS SELECT 1; LOCK TABLE v READ; SELECT NEXT VALUE FOR v; ERROR 42S02: 'test.v' is not a SEQUENCE +# +# MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL +# +SELECT SETVAL (v,0); +ERROR 42S02: 'test.v' is not a SEQUENCE UNLOCK TABLES; DROP VIEW v; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index 5eac3b668ba..9f0eebdf774 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -288,5 +288,12 @@ CREATE VIEW v AS SELECT 1; LOCK TABLE v READ; --error ER_NOT_SEQUENCE SELECT NEXT VALUE FOR v; + +--echo # +--echo # MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL +--echo # +--error ER_NOT_SEQUENCE +SELECT SETVAL (v,0); + UNLOCK TABLES; DROP VIEW v; -- cgit v1.2.1