From f6a7730c4505c14c8b63b00623917ddaf90c60ae Mon Sep 17 00:00:00 2001 From: Nikita Malyavin Date: Fri, 22 Jun 2018 23:26:43 +1000 Subject: MDEV-16490: It's possible to make a system versioned table without any versioning field * do not allow versioned table to be without versioned (non-system) fields * prohibit changing field versioning, when removing table versioning * handle CREATE...SELECT as well --- mysql-test/suite/versioning/r/alter.result | 42 +++++++++++++++++++++++++++++ mysql-test/suite/versioning/r/create.result | 10 +++++++ mysql-test/suite/versioning/t/alter.test | 34 +++++++++++++++++++++++ mysql-test/suite/versioning/t/create.test | 11 ++++++++ 4 files changed, 97 insertions(+) (limited to 'mysql-test/suite/versioning') diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 61665b32f2e..b5d44332955 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -639,3 +639,45 @@ create or replace table t1 (f1 int) with system versioning; alter table t1 drop system versioning, add f2 int with system versioning; ERROR HY000: Table `t1` is not system-versioned drop table t1; +# MDEV-16490 It's possible to make a system versioned table without any versioning field +set @@system_versioning_alter_history=keep; +create or replace table t (a int) with system versioning engine=innodb; +alter table t change column a a int without system versioning; +ERROR HY000: Table `t` must have at least one versioned column +alter table t +change column a a int without system versioning, +add column b int with system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t +change column a new_a int, +drop system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `new_a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +alter table t add system versioning; +alter table t change column new_a a int without system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t +add column c int, +change column c c int without system versioning, +change column b b int without system versioning; +ERROR HY000: Table `t` must have at least one versioned column +alter table t +add column c int without system versioning, +change column c c int, +change column b b int without system versioning; +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 89cec6c038a..f9d13123db5 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -515,3 +515,13 @@ row_end datetime(6) generated always as row end, period for system_time(row_start, row_end) ) with system versioning; ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t` +# MDEV-16490 It's possible to make a system versioned table without any versioning field +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as y; +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as x; +ERROR HY000: Table `t1` must have at least one versioned column +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index ac6f4acc7d3..735bde0614d 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -542,3 +542,37 @@ alter table t1 drop system versioning, add f2 int with system versioning; drop table t1; --source suite/versioning/common_finish.inc +--echo # MDEV-16490 It's possible to make a system versioned table without any versioning field + +set @@system_versioning_alter_history=keep; +create or replace table t (a int) with system versioning engine=innodb; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t change column a a int without system versioning; + +alter table t + change column a a int without system versioning, + add column b int with system versioning; +show create table t; + +alter table t + change column a new_a int, + drop system versioning; +show create table t; + +alter table t add system versioning; +alter table t change column new_a a int without system versioning; +show create table t; + +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t + add column c int, + change column c c int without system versioning, + change column b b int without system versioning; + +alter table t + add column c int without system versioning, + change column c c int, + change column b b int without system versioning; + +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index b53a9d6f369..b8caf6b7097 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -396,3 +396,14 @@ create table t ( ) with system versioning; --source suite/versioning/common_finish.inc +--echo # MDEV-16490 It's possible to make a system versioned table without any versioning field +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as y; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as x; + +drop database test; +create database test; -- cgit v1.2.1