diff options
author | Aleksey Midenkov <midenok@gmail.com> | 2018-05-30 12:43:54 +0300 |
---|---|---|
committer | Aleksey Midenkov <midenok@gmail.com> | 2019-10-10 00:20:34 +0300 |
commit | 6684989801cd0e93be5646716bb3cffedd271ce6 (patch) | |
tree | daeaa8bb2f15a0e3f7850bf51a6456e9b79ffd68 | |
parent | cf71cc838e608b4a4b8ae2557d4957ecb9bef97a (diff) | |
download | mariadb-git-6684989801cd0e93be5646716bb3cffedd271ce6.tar.gz |
versioning test suite fixes
Preparation for MDEV-16210:
replace.test:
key_type combinations: PK and UNIQUE.
foreign.test:
Preparation for key_type combinations.
Other fixes:
* Merged versioning.update2 into versioning.update;
* Removed test2 database and done individual drop instead.
35 files changed, 223 insertions, 183 deletions
diff --git a/mysql-test/suite/federated/federatedx_versioning.result b/mysql-test/suite/federated/federatedx_versioning.result index 9a7b180a5ab..c708b5f7932 100644 --- a/mysql-test/suite/federated/federatedx_versioning.result +++ b/mysql-test/suite/federated/federatedx_versioning.result @@ -4,14 +4,14 @@ row_start SYS_TYPE as row start invisible, row_end SYS_TYPE as row end invisible, period for system_time (row_start, row_end)) with system versioning; -create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test2/t1'; +create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1'; show create table tf; Table Create Table tf CREATE TABLE `tf` ( `x` int(11) DEFAULT NULL, `row_start` SYS_TYPE NOT NULL INVISIBLE DEFAULT 0, `row_end` SYS_TYPE NOT NULL INVISIBLE DEFAULT 0 -) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:MASTER_MYPORT/test2/t1' +) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1' # INSERT insert into t1 values (1); select * from tf; @@ -57,7 +57,7 @@ row_start SYS_TYPE as row start invisible, row_end SYS_TYPE as row end invisible, period for system_time (row_start, row_end)) with system versioning; -create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test2/t2'; +create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t2'; insert t2f (id, y) values (1, 2); replace t2f (id, y) values (1, 3); select *, check_row(row_start, row_end) from t2 for system_time all @@ -96,3 +96,5 @@ order by y; id y check_row(row_start, row_end) 2 2 HISTORICAL ROW 2 22 CURRENT ROW +drop view vt1; +drop tables t1, t2, t2f, tf; diff --git a/mysql-test/suite/federated/federatedx_versioning.test b/mysql-test/suite/federated/federatedx_versioning.test index b003403b629..40d0bc5018a 100644 --- a/mysql-test/suite/federated/federatedx_versioning.test +++ b/mysql-test/suite/federated/federatedx_versioning.test @@ -11,7 +11,7 @@ eval create or replace table t1 ( period for system_time (row_start, row_end)) with system versioning; --replace_result $MASTER_MYPORT MASTER_MYPORT -eval create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test2/t1'; +eval create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1'; --replace_result $MASTER_MYPORT MASTER_MYPORT $sys_datatype_expl SYS_TYPE "'0000-00-00 00:00:00.000000'" 0 show create table tf; --echo # INSERT @@ -45,7 +45,7 @@ eval create or replace table t2 ( period for system_time (row_start, row_end)) with system versioning; --replace_result $MASTER_MYPORT MASTER_MYPORT -eval create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test2/t2'; +eval create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t2'; insert t2f (id, y) values (1, 2); replace t2f (id, y) values (1, 3); select *, check_row(row_start, row_end) from t2 for system_time all @@ -72,4 +72,7 @@ order by x; select *, check_row(row_start, row_end) from t2 for system_time all order by y; +drop view vt1; +drop tables t1, t2, t2f, tf; + --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index 39abccd7ecd..355b571e5a0 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -4,8 +4,6 @@ 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 90a305078c8..61641c6c5ce 100644 --- a/mysql-test/suite/versioning/common_finish.inc +++ b/mysql-test/suite/versioning/common_finish.inc @@ -5,7 +5,4 @@ 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/key_type.combinations b/mysql-test/suite/versioning/key_type.combinations new file mode 100644 index 00000000000..93c0ea8bc06 --- /dev/null +++ b/mysql-test/suite/versioning/key_type.combinations @@ -0,0 +1,3 @@ +[unique] +[pk] +[sec] diff --git a/mysql-test/suite/versioning/key_type.inc b/mysql-test/suite/versioning/key_type.inc new file mode 100644 index 00000000000..888a1d97098 --- /dev/null +++ b/mysql-test/suite/versioning/key_type.inc @@ -0,0 +1,14 @@ +--disable_query_log +if ($MTR_COMBINATION_UNIQUE) +{ + let $KEY_TYPE= unique; +} +if ($MTR_COMBINATION_PK) +{ + let $KEY_TYPE= primary key; +} +if ($MTR_COMBINATION_SEC) +{ + let $KEY_TYPE= key; +} +--enable_query_log diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index b5d44332955..a453617d3b3 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 `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t add primary key (a); -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t add unique key (a); -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t engine innodb; -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change to/from native system versioning engine is not supported. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change to/from native system versioning engine is not supported. alter table t drop system versioning; show create table t; Table Create Table @@ -527,7 +527,7 @@ create or replace table t (x int) with system versioning; ERROR HY000: System versioning tables in the `mysql` database are not suported alter table user add system versioning; ERROR HY000: System versioning tables in the `mysql` database are not suported -use test2; +use test; # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column create or replace table t1 (i int, j int as (i), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; alter table t1 modify s timestamp(6) as row start; @@ -537,10 +537,10 @@ create or replace table t (a int) with system versioning; insert into t values (0), (1); delete from t where a = 0; alter table t add check (a > 1); -ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test2`.`t` +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t` alter table t add check (a > 0); insert into t values (0); -ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test2`.`t` +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t` insert into t values (2); # # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning @@ -679,5 +679,4 @@ 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; +drop table t; diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index f9d13123db5..5189e55bc30 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 test2 +table_schema test 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 test2 +table_schema test 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 test2 +table_schema test 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 test2 +table_schema test 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 `test2.tt1` +Warning 1105 System versioning is stripped from temporary `test.tt1` # Temporary is stripped from versioning show create table tt1; Table Create Table @@ -523,5 +523,4 @@ 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; +drop tables t0, t1, t2, t3; diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index cfce8aa3069..77b7fc80286 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -115,3 +115,4 @@ select x from t1 for system_time all; x 2 1 +drop table t1; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 001577750ba..e5ffb6c5f0c 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -2,7 +2,8 @@ # Test RESTRICT # ################# create table parent( -id int unique key +id int, +KEY_TYPE (id) ) engine innodb; create table child( parent_id int, @@ -16,13 +17,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 (`test2`.`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 (`test`.`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 (`test2`.`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 (`test`.`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; @@ -35,7 +36,8 @@ drop table parent; # Test when clustered index is a foreign key # ############################################## create table parent( -id int(10) unsigned unique key +id int(10) unsigned, +KEY_TYPE (id) ) engine innodb; create table child( parent_id int(10) unsigned primary key, @@ -47,14 +49,15 @@ 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 (`test2`.`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 (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; ################ # Test CASCADE # ################ create table parent( -id int unique key +id int, +KEY_TYPE (id) ) engine innodb; create table child( parent_id int, @@ -87,7 +90,8 @@ parent_id drop table child; drop table parent; create or replace table parent ( -id int primary key, +id int, +KEY_TYPE(id), sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time(sys_start, sys_end) @@ -110,7 +114,8 @@ x parent_id drop table child; drop table parent; create or replace table parent ( -id int primary key +id int, +KEY_TYPE(id) ) engine innodb; create or replace table child ( @@ -138,8 +143,9 @@ drop table parent; ################# # Test SET NULL # ################# -create or replace table parent( -id int unique key +create table parent( +id int, +KEY_TYPE (id) ) engine innodb; create or replace table child( parent_id int, @@ -183,7 +189,8 @@ drop table parent; # Parent table is foreign # ########################### create or replace table parent( -id int unique key, +id int, +KEY_TYPE (id), sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time(sys_start, sys_end) @@ -195,26 +202,27 @@ 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 (`test2`.`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 (`test`.`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 (`test2`.`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 (`test`.`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 (`test2`.`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 (`test`.`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 (`test2`.`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 (`test`.`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 (`test2`.`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 (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; ################### # crash on DELETE # ################### create or replace table a ( -cola int(10) primary key, +cola int(10), +KEY_TYPE (cola), v_cola int(10) as (cola mod 10) virtual, sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, @@ -233,7 +241,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 (`test2`.`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 (`test`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)) drop table b, a; ############################################### # CASCADE UPDATE foreign not system versioned # @@ -296,7 +304,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 (`test2`.`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 (`test`.`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 2e5a165dd81..aabf2b98cbf 100644 --- a/mysql-test/suite/versioning/r/online.result +++ b/mysql-test/suite/versioning/r/online.result @@ -89,13 +89,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='test2/t' and c.name='b'; +where t.name='test/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='test2/t' and c.name='b'; +where t.name='test/t' and c.name='b'; prtype 50179 show create table t; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index b364dca5376..2163ebeb1a0 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 `test2`.`t1`. Change to/from native system versioning engine is not supported. +ERROR HY000: Not allowed for system-versioned `test`.`t1`. Change to/from native system versioning engine is not supported. ## CREATE TABLE create or replace table t1 (x int) partition by system_time ( @@ -254,11 +254,11 @@ x 6 insert into t1 values (7), (8); Warnings: -Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions ### warn about full partition delete from t1; Warnings: -Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions select * from t1 partition (p1) order by x; x 4 @@ -433,16 +433,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='test2' and table_name='t1' and partition_name='p0'); +where table_schema='test' 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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 Truncated 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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 @@ -451,7 +451,7 @@ pn 4 SYSTEM_TIME NULL Warnings: Warning 1292 Truncated 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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 @@ -460,7 +460,7 @@ Warnings: Warning 1292 Truncated 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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 @@ -556,7 +556,7 @@ insert into t1 values (0), (1), (2), (3); delete from t1 where x < 3; delete from t1; Warnings: -Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`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 @@ -564,4 +564,5 @@ unlock tables; create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current); execute immediate 'select * from t1 for update'; pk -drop table t1; +drop view v1; +drop tables t, t1, t2, t3, t4; diff --git a/mysql-test/suite/versioning/r/partition_innodb.result b/mysql-test/suite/versioning/r/partition_innodb.result index 5fd2d7b4dce..3c4adce099a 100644 --- a/mysql-test/suite/versioning/r/partition_innodb.result +++ b/mysql-test/suite/versioning/r/partition_innodb.result @@ -21,6 +21,7 @@ partition p0 history, partition pn current ); ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `#sql-temporary` +drop table t1; create or replace table t ( a int primary key, row_start bigint unsigned as row start invisible, diff --git a/mysql-test/suite/versioning/r/replace.result b/mysql-test/suite/versioning/r/replace.result index e5dcf61678e..bda61f118b0 100644 --- a/mysql-test/suite/versioning/r/replace.result +++ b/mysql-test/suite/versioning/r/replace.result @@ -1,9 +1,10 @@ -create table t ( -id int primary key, +create or replace table t( +id int, +KEY_TYPE(id), x int, row_start SYS_DATATYPE as row start invisible, row_end SYS_DATATYPE as row end invisible, -period for system_time (row_start, row_end) +period for system_time(row_start, row_end) ) with system versioning; insert t values (1, 2); replace t values (1, 3); @@ -32,7 +33,8 @@ insert into t1 values (1,1); create or replace table t2 (c int); create or replace view v as select t1.* from t1 join t2; replace into v (a, b) select a, b from t1; -drop table t1; +drop view v; +drop tables t1, t2; CREATE TABLE t1 ( pk INT AUTO_INCREMENT, f INT, diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index 40202edf4a6..c887e524e63 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -153,21 +153,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 `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` +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` 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 `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` +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` 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 `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` +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` 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 @@ -294,7 +294,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 `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' +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' select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; a a 2 1 @@ -511,7 +511,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 `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' +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' 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 9d016a2bcd7..93f240272c1 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: 'test2.v' is a view +ERROR 42S02: 'test.v' is a view lock table v write; delete history from v before system_time now(6); ERROR 42S02: 'v' is a view diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index 98543caadbc..413272f55f9 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -181,9 +181,7 @@ select row_start from t1 into @trx_id; select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD; BEGIN_TS_GOOD 1 -drop database test; -create database test; -use test; +drop table t1; # # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs # diff --git a/mysql-test/suite/versioning/r/update-big.result b/mysql-test/suite/versioning/r/update-big.result index 5accf0a0b56..89297fe5d89 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,,test2; +connect con1,localhost,root,,test; alter table t1 add system versioning; connection default; update t1 set a= 7 where a = 3; diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result index 334d8d8899b..af263a63fae 100644 --- a/mysql-test/suite/versioning/r/update.result +++ b/mysql-test/suite/versioning/r/update.result @@ -241,3 +241,27 @@ B2 salary 1 2500 drop table t1; drop table t2; +### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row) +create or replace table t1 (a int primary key, b int) +with system versioning engine myisam; +insert into t1 (a) values (1); +replace t1 values (1,2),(1,3),(2,4); +# +# MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE +# +create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) +engine=innodb with system versioning; +insert into t1 (pk) values (1); +connect con1,localhost,root,,test; +start transaction; +select * from t1 for update; +pk a b +1 NULL NULL +connection default; +update t1 set b = 'foo'; +connection con1; +update t1 set a = 'bar'; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +disconnect con1; +connection default; +drop table t1; diff --git a/mysql-test/suite/versioning/r/update2.result b/mysql-test/suite/versioning/r/update2.result deleted file mode 100644 index 73e6c5b393d..00000000000 --- a/mysql-test/suite/versioning/r/update2.result +++ /dev/null @@ -1,21 +0,0 @@ -### Issue #365, bug 7 (duplicate of historical row) -create or replace table t1 (a int primary key, b int) -with system versioning engine myisam; -insert into t1 (a) values (1); -replace t1 values (1,2),(1,3),(2,4); -create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) -engine=innodb with system versioning; -insert into t1 (pk) values (1); -connect con1,localhost,root,,test; -start transaction; -select * from t1 for update; -pk a b -1 NULL NULL -connection default; -update t1 set b = 'foo'; -connection con1; -update t1 set a = 'bar'; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -disconnect con1; -connection default; -drop table t1; diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result index e897f0e17d9..850eba32c0d 100644 --- a/mysql-test/suite/versioning/r/view.result +++ b/mysql-test/suite/versioning/r/view.result @@ -118,9 +118,8 @@ execute stmt; a execute stmt; a -drop database test2; -create database test2; -use test2; +drop view v1; +drop tables t1, t2; # # MDEV-15146 SQLError[4122]: View is not system versioned # @@ -148,5 +147,5 @@ create or replace view v1 as select * from t1 for system_time as of date_sub(now show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` FOR SYSTEM_TIME AS OF current_timestamp() - interval 6 second latin1 latin1_swedish_ci -drop view v1; -drop table t1; +drop view v1, vt1, vt12; +drop tables t1, t3; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index 735bde0614d..c8275a5d3f4 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -453,7 +453,7 @@ use mysql; create or replace table t (x int) with system versioning; --error ER_VERS_DB_NOT_SUPPORTED alter table user add system versioning; -use test2; +use test; --echo # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column create or replace table t1 (i int, j int as (i), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; @@ -574,5 +574,4 @@ alter table t change column c c int, change column b b int without system versioning; -drop database test; -create database test; +drop table t; diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index b8caf6b7097..fc4120ef2aa 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -405,5 +405,4 @@ create or replace table t1 (x int without system versioning) with system versioning select 1 as x; -drop database test; -create database test; +drop tables t0, t1, t2, t3; diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test index 01c8ba438ab..b9045898bb0 100644 --- a/mysql-test/suite/versioning/t/delete.test +++ b/mysql-test/suite/versioning/t/delete.test @@ -81,5 +81,6 @@ insert into t1 values (1); update t1 set x= 2; delete from t1; select x from t1 for system_time all; +drop table t1; --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/engines.combinations b/mysql-test/suite/versioning/t/engines.combinations deleted file mode 100644 index 561c5656929..00000000000 --- a/mysql-test/suite/versioning/t/engines.combinations +++ /dev/null @@ -1,8 +0,0 @@ -[timestamp] -default-storage-engine=innodb - -[trx_id] -default-storage-engine=innodb - -[myisam] -default-storage-engine=myisam diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 1f4b3404618..d60172f99bc 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -1,11 +1,15 @@ --source suite/versioning/common.inc +let $KEY_TYPE= primary key; + --echo ################# --echo # Test RESTRICT # --echo ################# -create table parent( - id int unique key +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) ) engine innodb; --replace_result $sys_datatype_expl SYS_DATATYPE @@ -42,8 +46,10 @@ drop table parent; --echo # Test when clustered index is a foreign key # --echo ############################################## -create table parent( - id int(10) unsigned unique key +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int(10) unsigned, + $KEY_TYPE (id) ) engine innodb; --replace_result $sys_datatype_expl SYS_DATATYPE @@ -68,8 +74,10 @@ drop table parent; --echo # Test CASCADE # --echo ################ -create table parent( - id int unique key +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) ) engine innodb; --replace_result $sys_datatype_expl SYS_DATATYPE @@ -99,9 +107,10 @@ select * from child for system_time all; drop table child; drop table parent; ---replace_result $sys_datatype_expl SYS_DATATYPE +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE eval create or replace table parent ( - id int primary key, + id int, + $KEY_TYPE(id), sys_start $sys_datatype_expl as row start invisible, sys_end $sys_datatype_expl as row end invisible, period for system_time(sys_start, sys_end) @@ -126,8 +135,10 @@ select * from child; drop table child; drop table parent; -create or replace table parent ( - id int primary key +--replace_result "$KEY_TYPE" KEY_TYPE +eval create or replace table parent ( + id int, + $KEY_TYPE(id) ) engine innodb; @@ -158,8 +169,10 @@ drop table parent; --echo # Test SET NULL # --echo ################# -create or replace table parent( - id int unique key +--replace_result "$KEY_TYPE" KEY_TYPE +eval create table parent( + id int, + $KEY_TYPE (id) ) engine innodb; --replace_result $sys_datatype_expl SYS_DATATYPE @@ -196,9 +209,10 @@ drop table parent; --echo # Parent table is foreign # --echo ########################### ---replace_result $sys_datatype_expl SYS_DATATYPE +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE eval create or replace table parent( - id int unique key, + id int, + $KEY_TYPE (id), sys_start $sys_datatype_expl as row start invisible, sys_end $sys_datatype_expl as row end invisible, period for system_time(sys_start, sys_end) @@ -236,9 +250,10 @@ drop table parent; --echo # crash on DELETE # --echo ################### ---replace_result $sys_datatype_expl SYS_DATATYPE +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE eval create or replace table a ( - cola int(10) primary key, + cola int(10), + $KEY_TYPE (cola), v_cola int(10) as (cola mod 10) virtual, sys_start $sys_datatype_expl as row start invisible, sys_end $sys_datatype_expl as row end invisible, @@ -406,8 +421,8 @@ REPLACE INTO t2 SELECT * FROM t2; # Cleanup DROP TABLE t1, t2; --let $datadir= `select @@datadir` ---remove_file $datadir/test2/t1.data ---remove_file $datadir/test2/t1.data.2 ---remove_file $datadir/test2/t2.data +--remove_file $datadir/test/t1.data +--remove_file $datadir/test/t1.data.2 +--remove_file $datadir/test/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 c02df3d3044..cff3193407b 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='test2/t' and c.name='b'; + where t.name='test/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='test2/t' and c.name='b'; + where t.name='test/t' and c.name='b'; --replace_result $sys_datatype_expl SYS_DATATYPE show create table t; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index a9dff811d40..eca322d9ef4 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -402,17 +402,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='test2' and table_name='t1' and partition_name='p0'); + where table_schema='test' 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' 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='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; --echo # --echo # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW @@ -513,6 +513,8 @@ unlock tables; --echo # create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current); execute immediate 'select * from t1 for update'; -drop table t1; + +drop view v1; +drop tables t, t1, t2, t3, t4; --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 36adb6345b0..aaaf7001644 100644 --- a/mysql-test/suite/versioning/t/partition_innodb.test +++ b/mysql-test/suite/versioning/t/partition_innodb.test @@ -29,6 +29,8 @@ alter table t1 partition by system_time ( partition pn current ); +drop table t1; + --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED create or replace table t ( a int primary key, diff --git a/mysql-test/suite/versioning/t/replace.test b/mysql-test/suite/versioning/t/replace.test index 22d32ff3795..392c0ffcf35 100644 --- a/mysql-test/suite/versioning/t/replace.test +++ b/mysql-test/suite/versioning/t/replace.test @@ -1,13 +1,19 @@ +--source suite/versioning/key_type.inc +if ($MTR_COMBINATION_SEC) +{ + --skip pk or unique only +} --source suite/versioning/common.inc --source suite/versioning/engines.inc ---replace_result $sys_datatype_expl SYS_DATATYPE -eval create table t ( - id int primary key, +--replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE +eval create or replace table t( + id int, + $KEY_TYPE(id), x int, row_start $sys_datatype_expl as row start invisible, row_end $sys_datatype_expl as row end invisible, - period for system_time (row_start, row_end) + period for system_time(row_start, row_end) ) with system versioning; insert t values (1, 2); @@ -35,7 +41,8 @@ insert into t1 values (1,1); create or replace table t2 (c int); create or replace view v as select t1.* from t1 join t2; replace into v (a, b) select a, b from t1; -drop table t1; +drop view v; +drop tables t1, t2; --replace_result $sys_datatype_expl SYS_DATATYPE eval CREATE TABLE t1 ( diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test index 943d9182a8a..617c46a9332 100644 --- a/mysql-test/suite/versioning/t/trx_id.test +++ b/mysql-test/suite/versioning/t/trx_id.test @@ -173,10 +173,7 @@ commit; select row_start from t1 into @trx_id; select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD; -drop database test; -create database test; -use test; - +drop table t1; --echo # --echo # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs diff --git a/mysql-test/suite/versioning/t/update-big.test b/mysql-test/suite/versioning/t/update-big.test index f5a72b22454..175bfc79a48 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,,test2) +--connect (con1,localhost,root,,test) alter table t1 add system versioning; --connection default diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test index 95c844c88a2..a9550564a15 100644 --- a/mysql-test/suite/versioning/t/update.test +++ b/mysql-test/suite/versioning/t/update.test @@ -147,4 +147,34 @@ select @tmp2 = sys_trx_start as B2, salary from t2; drop table t1; drop table t2; +--echo ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row) +create or replace table t1 (a int primary key, b int) +with system versioning engine myisam; +insert into t1 (a) values (1); + +replace t1 values (1,2),(1,3),(2,4); + +--echo # +--echo # MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE +--echo # + +create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) + engine=innodb with system versioning; + +insert into t1 (pk) values (1); +connect (con1,localhost,root,,test); +start transaction; +select * from t1 for update; +connection default; +send update t1 set b = 'foo'; +connection con1; +let $wait_condition= select count(*) from information_schema.innodb_lock_waits; +source include/wait_condition.inc; +error ER_LOCK_DEADLOCK; +update t1 set a = 'bar'; +disconnect con1; +connection default; +reap; +drop table t1; + source suite/versioning/common_finish.inc; diff --git a/mysql-test/suite/versioning/t/update2.test b/mysql-test/suite/versioning/t/update2.test deleted file mode 100644 index 87ca3746b12..00000000000 --- a/mysql-test/suite/versioning/t/update2.test +++ /dev/null @@ -1,31 +0,0 @@ -source include/have_innodb.inc; - -echo ### Issue #365, bug 7 (duplicate of historical row); -create or replace table t1 (a int primary key, b int) -with system versioning engine myisam; -insert into t1 (a) values (1); - -replace t1 values (1,2),(1,3),(2,4); - -# -# MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE -# - -create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) - engine=innodb with system versioning; - -insert into t1 (pk) values (1); -connect (con1,localhost,root,,test); -start transaction; -select * from t1 for update; -connection default; -send update t1 set b = 'foo'; -connection con1; -let $wait_condition= select count(*) from information_schema.innodb_lock_waits; -source include/wait_condition.inc; -error ER_LOCK_DEADLOCK; -update t1 set a = 'bar'; -disconnect con1; -connection default; -reap; -drop table t1; diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test index d824f1c52c0..5a03a50f1d3 100644 --- a/mysql-test/suite/versioning/t/view.test +++ b/mysql-test/suite/versioning/t/view.test @@ -98,9 +98,8 @@ 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 test2; -create database test2; -use test2; +drop view v1; +drop tables t1, t2; --echo # --echo # MDEV-15146 SQLError[4122]: View is not system versioned @@ -122,7 +121,7 @@ select * from t1 for system_time as of now() - interval 6 second; create or replace view v1 as select * from t1 for system_time as of date_sub(now(), interval 6 second); show create view v1; -drop view v1; -drop table t1; +drop view v1, vt1, vt12; +drop tables t1, t3; --source suite/versioning/common_finish.inc |