diff options
Diffstat (limited to 'mysql-test/suite')
4 files changed, 351 insertions, 1 deletions
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index 7b2ea04985d..2e0db5f2cde 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -379,3 +379,99 @@ DROP TABLE tts; DROP TABLE ttx; DROP FUNCTION fts; DROP FUNCTION ftx; +# +# MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute +# +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; +CREATE TABLE t ( +a INT, +b INT, +row_start BIGINT UNSIGNED AS ROW START INVISIBLE, +row_end BIGINT UNSIGNED AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +INSERT INTO t VALUES (1,1); +# without table rebuild +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c +INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t +ON c.table_id=t.table_id +WHERE t.name='test/t' AND c.name='a'; +prtype +50179 +ALTER TABLE t +CHANGE a a INT WITHOUT SYSTEM VERSIONING; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c +INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t +ON c.table_id=t.table_id +WHERE t.name='test/t' AND c.name='a'; +prtype +1027 +UPDATE t SET a=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; +COUNT(*) +1 +# with table rebuild +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c +INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t +ON c.table_id=t.table_id +WHERE t.name='test/t' AND c.name='a'; +prtype +1027 +ALTER TABLE t +CHANGE a a INT WITH SYSTEM VERSIONING, +ADD PRIMARY KEY pk(a); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c +INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t +ON c.table_id=t.table_id +WHERE t.name='test/t' AND c.name='a'; +prtype +50435 +UPDATE t SET a=1; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; +COUNT(*) +2 +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE, + `row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE, + PRIMARY KEY (`a`,`row_end`), + PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +# handles VIRTUAL columns too +CREATE OR REPLACE TABLE t ( +a INT AS (b + 1), +b INT, +row_start BIGINT UNSIGNED AS ROW START INVISIBLE, +row_end BIGINT UNSIGNED AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +INSERT INTO t VALUES (DEFAULT, 1); +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c +INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t +ON c.table_id=t.table_id +WHERE t.name='test/t' AND c.name='b'; +prtype +50179 +ALTER TABLE t +CHANGE b b INT WITHOUT SYSTEM VERSIONING; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c +INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t +ON c.table_id=t.table_id +WHERE t.name='test/t' AND c.name='b'; +prtype +1027 +UPDATE t SET b=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; +COUNT(*) +1 +DROP TABLE t; +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR; diff --git a/mysql-test/suite/versioning/r/trx_id_versioning_attribute_persistence.result b/mysql-test/suite/versioning/r/trx_id_versioning_attribute_persistence.result new file mode 100644 index 00000000000..bf555fb2a7d --- /dev/null +++ b/mysql-test/suite/versioning/r/trx_id_versioning_attribute_persistence.result @@ -0,0 +1,86 @@ +CREATE OR REPLACE TABLE t1 ( +a INT, +b INT, +row_start BIGINT UNSIGNED AS ROW START INVISIBLE, +row_end BIGINT UNSIGNED AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +CREATE OR REPLACE TABLE t2 ( +a INT WITHOUT SYSTEM VERSIONING, +b INT, +row_start BIGINT UNSIGNED AS ROW START INVISIBLE, +row_end BIGINT UNSIGNED AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1); +INSERT INTO t2 VALUES (1,1); +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; +# without rebuild +ALTER TABLE t1 +CHANGE a a INT WITHOUT SYSTEM VERSIONING, +ALGORITHM=INSTANT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t2 +CHANGE a a INT WITH SYSTEM VERSIONING, +ADD PRIMARY KEY pk (a), +ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +# with rebuild +ALTER TABLE t2 +CHANGE a a INT WITH SYSTEM VERSIONING, +ADD PRIMARY KEY pk (a); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +UPDATE t1 SET a=2; +SELECT COUNT(*) FROM t1 FOR SYSTEM_TIME ALL; +COUNT(*) +1 +UPDATE t2 SET a=2; +SELECT COUNT(*) FROM t2 FOR SYSTEM_TIME ALL; +COUNT(*) +2 +DROP TABLE t1, t2; +# rollback ALTER TABLE: nothing should change +CREATE TABLE t ( +a INT, +b INT, +row_start BIGINT UNSIGNED AS ROW START INVISIBLE, +row_end BIGINT UNSIGNED AS ROW END INVISIBLE, +PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; +INSERT INTO t VALUES (1, 1); +SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C +JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID +WHERE t.NAME='test/t' AND C.NAME='b'; +PRTYPE +50179 +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; +SET @SAVED_DEBUG_DBUG = @@SESSION.DEBUG_DBUG; +SET DEBUG_DBUG='+d,ib_commit_inplace_fail_1'; +ALTER TABLE t +CHANGE b b INT WITHOUT SYSTEM VERSIONING; +ERROR HY000: Internal error: Injected error! +SET DEBUG_DBUG = @SAVED_DEBUG_DBUG; +SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C +JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID +WHERE t.NAME='test/t' AND C.NAME='b'; +PRTYPE +50179 +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE, + `row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; +COUNT(*) +1 +UPDATE t SET b=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; +COUNT(*) +2 +DROP TABLE t; diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test index aab28d1057c..35ba6595440 100644 --- a/mysql-test/suite/versioning/t/trx_id.test +++ b/mysql-test/suite/versioning/t/trx_id.test @@ -1,5 +1,4 @@ -- source include/have_innodb.inc --- source include/not_embedded.inc set default_storage_engine= innodb; @@ -411,3 +410,85 @@ DROP TABLE tts; DROP TABLE ttx; DROP FUNCTION fts; DROP FUNCTION ftx; + +--echo # +--echo # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute +--echo # + +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; +CREATE TABLE t ( + a INT, + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +INSERT INTO t VALUES (1,1); + +--echo # without table rebuild +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; +--enable_info +ALTER TABLE t + CHANGE a a INT WITHOUT SYSTEM VERSIONING; +--disable_info +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; + +UPDATE t SET a=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +--echo # with table rebuild +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; +--enable_info +ALTER TABLE t + CHANGE a a INT WITH SYSTEM VERSIONING, + ADD PRIMARY KEY pk(a); +--disable_info +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='a'; + +UPDATE t SET a=1; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +SHOW CREATE TABLE t; + +-- echo # handles VIRTUAL columns too +CREATE OR REPLACE TABLE t ( + a INT AS (b + 1), + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +INSERT INTO t VALUES (DEFAULT, 1); + +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='b'; +--enable_info +ALTER TABLE t + CHANGE b b INT WITHOUT SYSTEM VERSIONING; +--disable_info +SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c + INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t + ON c.table_id=t.table_id + WHERE t.name='test/t' AND c.name='b'; + +UPDATE t SET b=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +DROP TABLE t; +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR; diff --git a/mysql-test/suite/versioning/t/trx_id_versioning_attribute_persistence.test b/mysql-test/suite/versioning/t/trx_id_versioning_attribute_persistence.test new file mode 100644 index 00000000000..4e92ee247e4 --- /dev/null +++ b/mysql-test/suite/versioning/t/trx_id_versioning_attribute_persistence.test @@ -0,0 +1,87 @@ +-- source include/have_innodb.inc +-- source include/have_debug.inc + +CREATE OR REPLACE TABLE t1 ( + a INT, + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +CREATE OR REPLACE TABLE t2 ( + a INT WITHOUT SYSTEM VERSIONING, + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +INSERT INTO t1 VALUES (1,1); +INSERT INTO t2 VALUES (1,1); + +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; + +--enable_info +--echo # without rebuild +ALTER TABLE t1 + CHANGE a a INT WITHOUT SYSTEM VERSIONING, + ALGORITHM=INSTANT; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t2 + CHANGE a a INT WITH SYSTEM VERSIONING, + ADD PRIMARY KEY pk (a), + ALGORITHM=INSTANT; + +--echo # with rebuild +ALTER TABLE t2 + CHANGE a a INT WITH SYSTEM VERSIONING, + ADD PRIMARY KEY pk (a); +--disable_info + +--source include/restart_mysqld.inc + +UPDATE t1 SET a=2; +SELECT COUNT(*) FROM t1 FOR SYSTEM_TIME ALL; + +UPDATE t2 SET a=2; +SELECT COUNT(*) FROM t2 FOR SYSTEM_TIME ALL; + +DROP TABLE t1, t2; + +--echo # rollback ALTER TABLE: nothing should change +CREATE TABLE t ( + a INT, + b INT, + row_start BIGINT UNSIGNED AS ROW START INVISIBLE, + row_end BIGINT UNSIGNED AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME(row_start, row_end) +) WITH SYSTEM VERSIONING ENGINE=INNODB; + +INSERT INTO t VALUES (1, 1); + +SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C + JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID + WHERE t.NAME='test/t' AND C.NAME='b'; + +SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP; + +SET @SAVED_DEBUG_DBUG = @@SESSION.DEBUG_DBUG; +SET DEBUG_DBUG='+d,ib_commit_inplace_fail_1'; +--error ER_INTERNAL_ERROR +ALTER TABLE t + CHANGE b b INT WITHOUT SYSTEM VERSIONING; +SET DEBUG_DBUG = @SAVED_DEBUG_DBUG; + +SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C + JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID + WHERE t.NAME='test/t' AND C.NAME='b'; + +SHOW CREATE TABLE t; + +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; +UPDATE t SET b=11; +SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL; + +DROP TABLE t; |