diff options
Diffstat (limited to 'mysql-test/suite/versioning')
29 files changed, 94 insertions, 95 deletions
diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index 355b571e5a0..39abccd7ecd 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -4,6 +4,8 @@ if (!$TEST_VERSIONING_SO) --skip needs test_versioning plugin } source include/have_innodb.inc; +create database test2 character set latin1; +use test2; set @@session.time_zone='+00:00'; select ifnull(max(transaction_id), 0) into @start_trx_id from mysql.transaction_registry; diff --git a/mysql-test/suite/versioning/common_finish.inc b/mysql-test/suite/versioning/common_finish.inc index 6e568013827..90a305078c8 100644 --- a/mysql-test/suite/versioning/common_finish.inc +++ b/mysql-test/suite/versioning/common_finish.inc @@ -1,6 +1,11 @@ --disable_query_log -drop procedure verify_trt; -drop procedure verify_trt_dummy; -drop function current_row; -drop function check_row; +--disable_warnings +drop procedure if exists verify_trt; +drop procedure if exists verify_trt_dummy; +drop function if exists current_row; +drop function if exists check_row; +--enable_warnings + +use test; +drop database test2; --enable_query_log diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 51a0e7c5beb..e380c207555 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -18,13 +18,13 @@ t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING alter table t add column y int; -ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t add primary key (a); -ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t add unique key (a); -ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t engine innodb; -ERROR HY000: Not allowed for system-versioned `test`.`t`. Change to/from native system versioning engine is not supported. +ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change to/from native system versioning engine is not supported. alter table t drop system versioning; show create table t; Table Create Table @@ -544,6 +544,7 @@ alter table t add check (a > 0); insert into t values (0); ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t` insert into t values (2); +drop table t; # # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning # @@ -640,5 +641,4 @@ alter table t1 add f2 int with system versioning, drop system versioning; 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 database test; -create database test; +drop table t1; diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 4b4354e169d..47d069fc4ef 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -15,7 +15,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select table_catalog,table_schema,table_name,table_type,version,table_rows,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1'; table_catalog def -table_schema test +table_schema test2 table_name t1 table_type SYSTEM VERSIONED version 10 @@ -29,7 +29,7 @@ create_options table_comment select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,character_maximum_length,character_octet_length,character_set_name,collation_name,column_key,extra,column_comment,is_generated,generation_expression from information_schema.columns where table_name='t1'; table_catalog def -table_schema test +table_schema test2 table_name t1 column_name x1 ordinal_position 1 @@ -44,7 +44,7 @@ column_comment is_generated NEVER generation_expression NULL table_catalog def -table_schema test +table_schema test2 table_name t1 column_name Sys_start ordinal_position 2 @@ -59,7 +59,7 @@ column_comment start is_generated ALWAYS generation_expression ROW START table_catalog def -table_schema test +table_schema test2 table_name t1 column_name Sys_end ordinal_position 3 @@ -235,7 +235,7 @@ tt1 CREATE TABLE `tt1` ( drop table tt1; create temporary table tt1 like t1; Warnings: -Warning 1105 System versioning is stripped from temporary `test.tt1` +Warning 1105 System versioning is stripped from temporary `test2.tt1` # Temporary is stripped from versioning show create table tt1; Table Create Table @@ -527,5 +527,3 @@ 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` -drop database test; -create database test; diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index faf8bf6176f..cfce8aa3069 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -115,5 +115,3 @@ select x from t1 for system_time all; x 2 1 -drop database test; -create database test; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 30e65de5316..91942510417 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -16,13 +16,13 @@ on update restrict insert into parent values(1); insert into child values(1); delete from parent where id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) delete from child where parent_id = 1; delete from parent where id = 1; insert into parent values(1); insert into child values(1); update parent set id=id+1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) delete from child; update parent set id=id+1; select * from child for system_time all; @@ -47,7 +47,7 @@ foreign key(parent_id) references parent(id) insert into parent values(1); insert into child values(1); delete from parent where id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; ################ @@ -195,19 +195,19 @@ foreign key(parent_id) references parent(id) insert into parent values(1); insert into child values(1); delete from parent; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) update parent set id=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) delete from child; delete from parent; insert into child values(1); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) insert into parent values(1); insert into child values(1); delete from parent; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) update parent set id=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; ################### @@ -233,7 +233,7 @@ foreign key (v_cola) references a (v_cola); insert into a(cola) values (12); insert into b(cola, v_cola) values (10,2); delete from a; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)) drop table b, a; ############################################### # CASCADE UPDATE foreign not system versioned # @@ -298,7 +298,7 @@ SET FOREIGN_KEY_CHECKS= OFF; INSERT IGNORE INTO t2 VALUES (1); SET FOREIGN_KEY_CHECKS= ON; UPDATE t2 SET f2= 2; -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test2`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)) DELETE FROM t2; DROP TABLE t2, t1; # diff --git a/mysql-test/suite/versioning/r/online.result b/mysql-test/suite/versioning/r/online.result index 36251ddd550..33ace87d440 100644 --- a/mysql-test/suite/versioning/r/online.result +++ b/mysql-test/suite/versioning/r/online.result @@ -90,13 +90,13 @@ period for system_time(row_start, row_end) 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'; +where t.name='test2/t' and c.name='b'; prtype 50179 set @@system_versioning_alter_history=keep; 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'; +where t.name='test2/t' and c.name='b'; prtype 50179 show create table t; @@ -115,6 +115,7 @@ update t set b=11; select count(*) from t for system_time all; count(*) 2 +drop table t; # Start of 10.4 tests create or replace table t (a int, b int) engine=innodb; alter table t diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index aa651452108..a28b4377945 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -46,7 +46,7 @@ period for system_time(row_start, row_end)) engine=DEFAULT_ENGINE with system versioning partition by hash(i); alter table t1 engine=NON_DEFAULT_ENGINE; -ERROR HY000: Not allowed for system-versioned `test`.`t1`. Change to/from native system versioning engine is not supported. +ERROR HY000: Not allowed for system-versioned `test2`.`t1`. Change to/from native system versioning engine is not supported. ## CREATE TABLE create or replace table t1 (x int) partition by system_time ( @@ -266,11 +266,11 @@ x 6 insert into t1 values (7), (8); Warnings: -Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions ### warn about full partition delete from t1; Warnings: -Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions select * from t1 partition (p1) order by x; x 4 @@ -441,16 +441,16 @@ create or replace table t1 (i int) with system versioning partition by system_time interval 1 hour ( partition p0 history, partition pn current); set @ts=(select partition_description from information_schema.partitions -where table_schema='test' and table_name='t1' and partition_name='p0'); +where table_schema='test2' and table_name='t1' and partition_name='p0'); alter table t1 add column b int; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p0 1 SYSTEM_TIME 00:00:00.000000 pn 2 SYSTEM_TIME NULL Warnings: Warning 1292 Incorrect time value: 'CURRENT' alter table t1 add partition (partition p1 history, partition p2 history); -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p0 1 SYSTEM_TIME 00:00:00.000000 p1 2 SYSTEM_TIME 01:00:00.000000 @@ -459,7 +459,7 @@ pn 4 SYSTEM_TIME NULL Warnings: Warning 1292 Incorrect time value: 'CURRENT' alter table t1 drop partition p0; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p1 1 SYSTEM_TIME 01:00:00.000000 p2 2 SYSTEM_TIME 02:00:00.000000 @@ -468,7 +468,7 @@ Warnings: Warning 1292 Incorrect time value: 'CURRENT' alter table t1 drop partition p2; ERROR HY000: Can only drop oldest partitions when rotating by INTERVAL -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p1 1 SYSTEM_TIME 01:00:00.000000 p2 2 SYSTEM_TIME 02:00:00.000000 @@ -564,7 +564,7 @@ insert into t1 values (0), (1), (2), (3); delete from t1 where x < 3; delete from t1; Warnings: -Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions unlock tables; # # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table @@ -573,6 +573,3 @@ create or replace table t1 (pk int primary key) with system versioning partition execute immediate 'select * from t1 for update'; pk drop table t1; -# Test cleanup -drop database test; -create database test; diff --git a/mysql-test/suite/versioning/r/partition_innodb.result b/mysql-test/suite/versioning/r/partition_innodb.result index ad6075b7aa5..554303bcee3 100644 --- a/mysql-test/suite/versioning/r/partition_innodb.result +++ b/mysql-test/suite/versioning/r/partition_innodb.result @@ -64,6 +64,3 @@ partition by range columns (a, row_start) ( partition p1 values less than (100, 100) ); ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END -# Test cleanup -drop database test; -create database test; diff --git a/mysql-test/suite/versioning/r/replace.result b/mysql-test/suite/versioning/r/replace.result index 4173432ff45..e5dcf61678e 100644 --- a/mysql-test/suite/versioning/r/replace.result +++ b/mysql-test/suite/versioning/r/replace.result @@ -46,5 +46,3 @@ INSERT INTO t1 () VALUES (),(),(),(),(),(); UPDATE IGNORE t1 SET f = 1; REPLACE t1 SELECT * FROM t1; DROP TABLE t1; -drop database test; -create database test; diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index 3569268ce1d..f1ef954baa6 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -155,21 +155,21 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`x` AS `IJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` +Note 1003 select `test2`.`t1`.`x` AS `IJ2_x1`,`test2`.`t1`.`y` AS `y1`,`test2`.`t2`.`x` AS `x2`,`test2`.`t2`.`y` AS `y2` from `test2`.`t1` FOR SYSTEM_TIME ALL join `test2`.`t2` FOR SYSTEM_TIME ALL where `test2`.`t1`.`x` = `test2`.`t2`.`x` and `test2`.`t2`.`row_end` > @`t0` and `test2`.`t2`.`row_start` <= @`t0` and `test2`.`t1`.`row_end` > @`t0` and `test2`.`t1`.`row_start` <= @`t0` explain extended select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x) for system_time as of timestamp @t0 as t; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`x` AS `LJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL left join `test`.`t2` FOR SYSTEM_TIME ALL on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0`) where `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` +Note 1003 select `test2`.`t1`.`x` AS `LJ2_x1`,`test2`.`t1`.`y` AS `y1`,`test2`.`t2`.`x` AS `x2`,`test2`.`t2`.`y` AS `y2` from `test2`.`t1` FOR SYSTEM_TIME ALL left join `test2`.`t2` FOR SYSTEM_TIME ALL on(`test2`.`t2`.`x` = `test2`.`t1`.`x` and `test2`.`t2`.`row_end` > @`t0` and `test2`.`t2`.`row_start` <= @`t0`) where `test2`.`t1`.`row_end` > @`t0` and `test2`.`t1`.`row_start` <= @`t0` explain extended select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x) for system_time as of timestamp @t0 as t; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`x` AS `RJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t2` FOR SYSTEM_TIME ALL left join `test`.`t1` FOR SYSTEM_TIME ALL on(`test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0`) where `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0` +Note 1003 select `test2`.`t1`.`x` AS `RJ2_x1`,`test2`.`t1`.`y` AS `y1`,`test2`.`t2`.`x` AS `x2`,`test2`.`t2`.`y` AS `y2` from `test2`.`t2` FOR SYSTEM_TIME ALL left join `test2`.`t1` FOR SYSTEM_TIME ALL on(`test2`.`t1`.`x` = `test2`.`t2`.`x` and `test2`.`t1`.`row_end` > @`t0` and `test2`.`t1`.`row_start` <= @`t0`) where `test2`.`t2`.`row_end` > @`t0` and `test2`.`t2`.`row_start` <= @`t0` select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x) for system_time as of timestamp @t0 as t; IJ2_x1 y1 x2 y2 @@ -330,7 +330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` FOR SYSTEM_TIME ALL left join (`test`.`t1` FOR SYSTEM_TIME ALL `t2` left join `test`.`t1` FOR SYSTEM_TIME ALL `t3` on(`test`.`t3`.`a` = `test`.`t2`.`a` and `test`.`t3`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test`.`t2`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' and `test`.`t1`.`a` > 1) where `test`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 select `test2`.`t1`.`a` AS `a`,`test2`.`t2`.`a` AS `a` from `test2`.`t1` FOR SYSTEM_TIME ALL left join (`test2`.`t1` FOR SYSTEM_TIME ALL `t2` left join `test2`.`t1` FOR SYSTEM_TIME ALL `t3` on(`test2`.`t3`.`a` = `test2`.`t2`.`a` and `test2`.`t3`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test2`.`t2`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' and `test2`.`t1`.`a` > 1) where `test2`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; a a 2 1 @@ -549,7 +549,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` left join (`test`.`t3` left join `test`.`t4` FOR SYSTEM_TIME ALL on(`test`.`t4`.`f4` = `test`.`t2`.`f2` and `test`.`t4`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test`.`t3`.`f3` = `test`.`t2`.`f2`) where `test`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 select `test2`.`t1`.`f1` AS `f1` from `test2`.`t1` FOR SYSTEM_TIME ALL join `test2`.`t2` left join (`test2`.`t3` left join `test2`.`t4` FOR SYSTEM_TIME ALL on(`test2`.`t4`.`f4` = `test2`.`t2`.`f2` and `test2`.`t4`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test2`.`t3`.`f3` = `test2`.`t2`.`f2`) where `test2`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' drop view v1; drop table t1, t2, t3, t4; # diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index 88d38b18e1a..ad682f5e041 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -93,7 +93,7 @@ drop view v; create or replace table t (i int); create or replace view v as select * from t; drop table v; -ERROR 42S02: 'test.v' is a view +ERROR 42S02: 'test2.v' is a view lock table v write; delete history from v before system_time now(6); ERROR 42S02: 'v' is a view @@ -153,5 +153,3 @@ select * from t1; a 1 drop table t1; -drop database test; -create database test; diff --git a/mysql-test/suite/versioning/r/update-big.result b/mysql-test/suite/versioning/r/update-big.result index 89297fe5d89..5accf0a0b56 100644 --- a/mysql-test/suite/versioning/r/update-big.result +++ b/mysql-test/suite/versioning/r/update-big.result @@ -12,7 +12,7 @@ insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; -connect con1,localhost,root,,test; +connect con1,localhost,root,,test2; alter table t1 add system versioning; connection default; update t1 set a= 7 where a = 3; diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result index ff033a16877..e897f0e17d9 100644 --- a/mysql-test/suite/versioning/r/view.result +++ b/mysql-test/suite/versioning/r/view.result @@ -118,9 +118,9 @@ execute stmt; a execute stmt; a -drop database test; -create database test; -use test; +drop database test2; +create database test2; +use test2; # # MDEV-15146 SQLError[4122]: View is not system versioned # diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index 2874cda51cb..b7e623b2897 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -1,3 +1,6 @@ +--source include/default_charset.inc +--source suite/versioning/common.inc + select @@system_versioning_alter_history; create table t( @@ -156,7 +159,6 @@ alter table t modify a int with system versioning; show create table t; # TODO: move TRX_ID cases to separate test --- source suite/versioning/common.inc create or replace table t( a int ) engine=innodb; @@ -468,6 +470,7 @@ alter table t add check (a > 0); --error ER_CONSTRAINT_FAILED insert into t values (0); insert into t values (2); +drop table t; --echo # --echo # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning @@ -538,5 +541,5 @@ create or replace table t1 (f1 int) with system versioning; --error ER_VERS_NOT_VERSIONED alter table t1 drop system versioning, add f2 int with system versioning; -drop database test; -create database test; +drop table t1; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 21548c557c7..64858bbe0ec 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -395,6 +395,4 @@ create table t ( period for system_time(row_start, row_end) ) with system versioning; - -drop database test; -create database test; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test index 0055b2100d3..5a8fb1f8211 100644 --- a/mysql-test/suite/versioning/t/cte.test +++ b/mysql-test/suite/versioning/t/cte.test @@ -1,4 +1,6 @@ --- source include/have_innodb.inc +--source include/have_innodb.inc +--source include/default_optimizer_switch.inc + set time_zone="+00:00"; set default_storage_engine=innodb; create or replace table dept ( diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test index 4c2f4daccd7..01c8ba438ab 100644 --- a/mysql-test/suite/versioning/t/delete.test +++ b/mysql-test/suite/versioning/t/delete.test @@ -82,5 +82,4 @@ update t1 set x= 2; delete from t1; select x from t1 for system_time all; -drop database test; -create database test; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/derived.test b/mysql-test/suite/versioning/t/derived.test index 9784a4a0d2f..9d96856f01f 100644 --- a/mysql-test/suite/versioning/t/derived.test +++ b/mysql-test/suite/versioning/t/derived.test @@ -1,3 +1,5 @@ +--source include/default_optimizer_switch.inc + create table emp ( emp_id int, diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index ddff3945259..305a1e49813 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -406,8 +406,8 @@ REPLACE INTO t2 SELECT * FROM t2; # Cleanup DROP TABLE t1, t2; --let $datadir= `select @@datadir` ---remove_file $datadir/test/t1.data ---remove_file $datadir/test/t1.data.2 ---remove_file $datadir/test/t2.data +--remove_file $datadir/test2/t1.data +--remove_file $datadir/test2/t1.data.2 +--remove_file $datadir/test2/t2.data --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/online.test b/mysql-test/suite/versioning/t/online.test index 02fde474d55..f38af7c2706 100644 --- a/mysql-test/suite/versioning/t/online.test +++ b/mysql-test/suite/versioning/t/online.test @@ -107,7 +107,7 @@ 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'; + where t.name='test2/t' and c.name='b'; set @@system_versioning_alter_history=keep; @@ -125,7 +125,7 @@ set debug_dbug= default; 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'; + where t.name='test2/t' and c.name='b'; --replace_result $sys_datatype_expl SYS_DATATYPE show create table t; @@ -133,6 +133,7 @@ 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; --echo # Start of 10.4 tests @@ -194,3 +195,5 @@ alter table t1 drop system versioning; drop database test; create database test; + +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 73df539d965..eaf4f97a242 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -393,17 +393,17 @@ partition by system_time interval 1 hour ( partition p0 history, partition pn current); set @ts=(select partition_description from information_schema.partitions - where table_schema='test' and table_name='t1' and partition_name='p0'); + where table_schema='test2' and table_name='t1' and partition_name='p0'); alter table t1 add column b int; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; alter table t1 add partition (partition p1 history, partition p2 history); -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; alter table t1 drop partition p0; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; --error ER_VERS_DROP_PARTITION_INTERVAL alter table t1 drop partition p2; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; --echo # --echo # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW @@ -506,6 +506,4 @@ create or replace table t1 (pk int primary key) with system versioning partition execute immediate 'select * from t1 for update'; drop table t1; ---echo # Test cleanup -drop database test; -create database test; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/partition_innodb.test b/mysql-test/suite/versioning/t/partition_innodb.test index d7527ff7410..bf995bbb11d 100644 --- a/mysql-test/suite/versioning/t/partition_innodb.test +++ b/mysql-test/suite/versioning/t/partition_innodb.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc --source include/have_partition.inc +--source suite/versioning/common.inc --echo # MDEV-15951 system versioning by trx id doesn't work with partitioning --echo # currently trx_id does not support partitioning by system_time @@ -74,6 +75,4 @@ partition by range columns (a, row_start) ( partition p1 values less than (100, 100) ); ---echo # Test cleanup -drop database test; -create database test; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/replace.test b/mysql-test/suite/versioning/t/replace.test index 391cd2a12fc..22d32ff3795 100644 --- a/mysql-test/suite/versioning/t/replace.test +++ b/mysql-test/suite/versioning/t/replace.test @@ -52,6 +52,4 @@ UPDATE IGNORE t1 SET f = 1; REPLACE t1 SELECT * FROM t1; DROP TABLE t1; - -drop database test; -create database test; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test index ebe4503401d..c4c1d703ce5 100644 --- a/mysql-test/suite/versioning/t/select.test +++ b/mysql-test/suite/versioning/t/select.test @@ -1,5 +1,6 @@ --source suite/versioning/engines.inc --source suite/versioning/common.inc +--source include/default_optimizer_switch.inc # test_01 diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/truncate.test index b21956cc68f..89b49fe735d 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/truncate.test @@ -169,5 +169,4 @@ insert into t1 values (1); select * from t1; drop table t1; -drop database test; -create database test; +--source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test index f98c21fb26a..943d9182a8a 100644 --- a/mysql-test/suite/versioning/t/trx_id.test +++ b/mysql-test/suite/versioning/t/trx_id.test @@ -2,7 +2,8 @@ if (!$TEST_VERSIONING_SO) { --skip needs test_versioning plugin } --- source include/have_innodb.inc +--source include/have_innodb.inc +--source include/default_charset.inc set default_storage_engine= innodb; diff --git a/mysql-test/suite/versioning/t/update-big.test b/mysql-test/suite/versioning/t/update-big.test index 175bfc79a48..f5a72b22454 100644 --- a/mysql-test/suite/versioning/t/update-big.test +++ b/mysql-test/suite/versioning/t/update-big.test @@ -17,7 +17,7 @@ insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; ---connect (con1,localhost,root,,test) +--connect (con1,localhost,root,,test2) alter table t1 add system versioning; --connection default diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test index 68ee54d3df5..d824f1c52c0 100644 --- a/mysql-test/suite/versioning/t/view.test +++ b/mysql-test/suite/versioning/t/view.test @@ -98,9 +98,9 @@ create or replace table t2 (b int) with system versioning; prepare stmt from 'select a from v1 inner join t2 group by a order by a'; execute stmt; execute stmt; -drop database test; -create database test; -use test; +drop database test2; +create database test2; +use test2; --echo # --echo # MDEV-15146 SQLError[4122]: View is not system versioned @@ -124,3 +124,5 @@ show create view v1; drop view v1; drop table t1; + +--source suite/versioning/common_finish.inc |