diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/suite/versioning/r/delete_history.result | 12 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/partition.result | 135 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/rpl.result | 19 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/delete_history.test | 11 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/partition.test | 86 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/rpl.test | 24 |
6 files changed, 251 insertions, 36 deletions
diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result index cb865a835b3..b3086c867d1 100644 --- a/mysql-test/suite/versioning/r/delete_history.result +++ b/mysql-test/suite/versioning/r/delete_history.result @@ -154,3 +154,15 @@ select * from t1; a 1 drop table t1; +# +# MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT +# +# Don't auto-create new partition on DELETE HISTORY: +create or replace table t (a int) +with system versioning +partition by system_time limit 1000; +delete history from t; +select count(*) - 1 as hist_partitions from information_schema.partitions where table_name = 't' and table_schema = 'test'; +hist_partitions +1 +drop table t; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 038051055e7..87d7c112f3e 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -1,4 +1,7 @@ call mtr.add_suppression("need more HISTORY partitions"); +call mtr.add_suppression("Duplicate partition name"); +call mtr.add_suppression("Wrong partitioning type"); +call mtr.add_suppression("Table 'test.t1' doesn't exist"); set system_versioning_alter_history=keep; # Check conventional partitioning on temporal tables create or replace table t1 ( @@ -237,7 +240,9 @@ select @ts0 = @ts1; select @ts2 = @ts3; @ts2 = @ts3 1 -## rotation by LIMIT +# +# Rotation by LIMIT +# create or replace table t1 (x int) with system versioning partition by system_time limit 0 partitions 3; @@ -289,7 +294,37 @@ x 6 7 8 -## rotation by INTERVAL +# Auto-create history partitions +create or replace table t1 (x int) with system versioning +partition by system_time limit 1000 auto_increment; +lock tables t1 write; +insert into t1 values (1); +update t1 set x= x + 1; +select state, info from information_schema.processlist where info like 'ALTER TABLE %'; +state info +Waiting for table metadata lock ALTER TABLE `test`.`t1` ADD PARTITION (PARTITION `p1` HISTORY) +unlock tables; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT +(PARTITION `p0` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +### Test ALTER failure +set @lock_wait_timeout0= @@global.lock_wait_timeout; +set global lock_wait_timeout= 1; +create or replace table t1 (x int) with system versioning partition by system_time limit 1000; +lock tables t1 write; +insert t1 values (1); +insert t1 values (1); +unlock tables; +set global lock_wait_timeout= @lock_wait_timeout0; +# +# Rotation by INTERVAL +# create or replace table t1 (x int) with system versioning partition by system_time interval 0 second partitions 3; @@ -302,7 +337,7 @@ partition by system_time interval 10 year partitions 3; ERROR 22003: TIMESTAMP value is out of range in 'INTERVAL' # INTERVAL and ALTER TABLE create or replace table t1 (i int) with system versioning -partition by system_time interval 1 hour; +partition by system_time interval 59 minute; set @ts=(select partition_description from information_schema.partitions where table_schema='test' and table_name='t1' and partition_name='p0'); alter table t1 add column b int; @@ -316,16 +351,16 @@ 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'; 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 -p2 3 SYSTEM_TIME 02:00:00.000000 +p1 2 SYSTEM_TIME 00:59:00.000000 +p2 3 SYSTEM_TIME 01:58:00.000000 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'; 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 +p1 1 SYSTEM_TIME 00:59:00.000000 +p2 2 SYSTEM_TIME 01:58:00.000000 pn 3 SYSTEM_TIME NULL Warnings: Warning 1292 Incorrect time value: 'CURRENT' @@ -333,8 +368,8 @@ 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'; 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 +p1 1 SYSTEM_TIME 00:59:00.000000 +p2 2 SYSTEM_TIME 01:58:00.000000 pn 3 SYSTEM_TIME NULL Warnings: Warning 1292 Incorrect time value: 'CURRENT' @@ -343,6 +378,7 @@ create or replace table t1 (i int) with system versioning partition by system_time interval 1 day subpartition by key (i) subpartitions 2 (partition p1 history, partition pn current); +lock tables t1 write; set timestamp=unix_timestamp('2001-02-03 10:20:40'); insert t1 values (1); delete from t1; @@ -353,28 +389,51 @@ Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out o delete from t1; Warnings: Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions -select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; -subpartition_name partition_description table_rows -p1sp0 2001-02-04 00:00:00 1 -p1sp1 2001-02-04 00:00:00 1 -pnsp0 CURRENT 0 -pnsp1 CURRENT 0 +select subpartition_name, partition_description from information_schema.partitions where table_schema='test' and table_name='t1'; +subpartition_name partition_description +p1sp0 2001-02-04 00:00:00 +p1sp1 2001-02-04 00:00:00 +pnsp0 CURRENT +pnsp1 CURRENT +select * from t1 partition (p1); +i +1 +2 set timestamp=unix_timestamp('2001-02-04 10:20:55'); alter table t1 add partition (partition p0 history, partition p2 history); set timestamp=unix_timestamp('2001-02-04 10:30:00'); insert t1 values (4),(5); set timestamp=unix_timestamp('2001-02-04 10:30:10'); update t1 set i=6 where i=5; -select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; -subpartition_name partition_description table_rows -p1sp0 2001-02-04 00:00:00 1 -p1sp1 2001-02-04 00:00:00 0 -p0sp0 2001-02-05 00:00:00 1 -p0sp1 2001-02-05 00:00:00 1 -p2sp0 2001-02-06 00:00:00 0 -p2sp1 2001-02-06 00:00:00 0 -pnsp0 CURRENT 0 -pnsp1 CURRENT 2 +select subpartition_name, partition_description from information_schema.partitions where table_schema='test' and table_name='t1'; +subpartition_name partition_description +p1sp0 2001-02-04 00:00:00 +p1sp1 2001-02-04 00:00:00 +p0sp0 2001-02-05 00:00:00 +p0sp1 2001-02-05 00:00:00 +p2sp0 2001-02-06 00:00:00 +p2sp1 2001-02-06 00:00:00 +pnsp0 CURRENT +pnsp1 CURRENT +select * from t1 partition (p1); +i +1 +2 +select * from t1 partition (p0); +i +5 +select * from t1 partition (p2); +i +alter table t1 rebuild partition p0, p1, p2; +select * from t1 partition (p1); +i +1 +select * from t1 partition (p0); +i +5 +2 +select * from t1 partition (p2); +i ## pruning check set @ts=(select partition_description from information_schema.partitions where table_schema='test' and table_name='t1' and partition_name='p0' limit 1); @@ -395,6 +454,7 @@ i explain partitions select * from t1 for system_time all where row_end = @ts; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1_p1sp0,p1_p1sp1 # NULL NULL NULL NULL # # +unlock tables; ## INTERVAL ... STARTS create or replace table t1 (i int) with system versioning partition by system_time interval 1 day starts 'a'; @@ -542,6 +602,9 @@ set timestamp= unix_timestamp('2001-01-01 00:00:00'); create or replace table t1 (i int) with system versioning partition by system_time interval 1 day starts '2000-01-01 00:00:00' partitions 3; +lock tables t1 write; +Warnings: +Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions # we are warned when we push to present: insert into t1 values (0); Warnings: @@ -584,11 +647,13 @@ i row_end select *, row_end from t1 partition (p1); i row_end 3 2000-01-04 00:00:01.000000 -set timestamp= unix_timestamp('2000-01-01 00:00:00'); +unlock tables; # and this is how it usually goes: +set timestamp= unix_timestamp('2000-01-01 00:00:00'); create or replace table t1 (i int) with system versioning partition by system_time interval 1 day partitions 3; +lock tables t1 write; insert into t1 values (0); set timestamp= unix_timestamp('2000-01-01 00:00:01'); update t1 set i= i + 1; @@ -609,12 +674,26 @@ i row_end select *, row_end from t1 partition (p1); i row_end 1 2000-01-02 00:00:01.000000 +2 2000-01-03 00:00:01.000000 +3 2000-01-04 00:00:01.000000 +select *, row_end from t1 partition (p2); +i row_end +select *, row_end from t1 partition (p3); +i row_end +alter table t1 rebuild partition p0, p1, p2, p3; +select *, row_end from t1 partition (p0); +i row_end +0 2000-01-01 00:00:01.000000 +select *, row_end from t1 partition (p1); +i row_end +1 2000-01-02 00:00:01.000000 select *, row_end from t1 partition (p2); i row_end 2 2000-01-03 00:00:01.000000 select *, row_end from t1 partition (p3); i row_end 3 2000-01-04 00:00:01.000000 +unlock tables; drop tables t1, t2; ## Subpartitions create or replace table t1 (x int) @@ -632,9 +711,9 @@ select * from t1 partition (pnsp1); x 2 4 -### warn about full partition delete from t1 where x < 3; delete from t1; +### warn about full partition delete from t1; Warnings: Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions @@ -845,7 +924,7 @@ ERROR HY000: Can not DROP SYSTEM VERSIONING for table `t` partitioned BY SYSTEM_ create or replace table t1 (i int) with system versioning; insert into t1 values (1), (2); update t1 set i= 3; -alter table t1 partition by system_time interval 1 month (partition p1 history, partition pn current); +alter table t1 partition by system_time interval 59 minute (partition p1 history, partition pn current); lock table t1 write; alter table t1 add partition (partition p2 history); insert into t1 values (4); diff --git a/mysql-test/suite/versioning/r/rpl.result b/mysql-test/suite/versioning/r/rpl.result index 627f3991499..a303e8f8e61 100644 --- a/mysql-test/suite/versioning/r/rpl.result +++ b/mysql-test/suite/versioning/r/rpl.result @@ -164,4 +164,23 @@ update t1 set i = 0; connection slave; connection master; drop table t1; +# +# MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT +# +create or replace table t1 (x int) with system versioning +partition by system_time limit 1000 auto_increment; +lock tables t1 write; +insert t1 values (); +delete from t1; +unlock tables; +select count(*) - 1 as hist_partitions from information_schema.partitions where table_name = 't1' and table_schema = 'test'; +hist_partitions +2 +connection slave; +select count(*) - 1 as hist_partitions from information_schema.partitions where table_name = 't1' and table_schema = 'test'; +hist_partitions +2 +connection master; +drop database test; +create database test; include/rpl_end.inc diff --git a/mysql-test/suite/versioning/t/delete_history.test b/mysql-test/suite/versioning/t/delete_history.test index fb5c8520bcb..be9f6afaec8 100644 --- a/mysql-test/suite/versioning/t/delete_history.test +++ b/mysql-test/suite/versioning/t/delete_history.test @@ -169,4 +169,15 @@ insert into t1 values (1); select * from t1; drop table t1; +--echo # +--echo # MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT +--echo # +--echo # Don't auto-create new partition on DELETE HISTORY: +create or replace table t (a int) +with system versioning +partition by system_time limit 1000; +delete history from t; +select count(*) - 1 as hist_partitions from information_schema.partitions where table_name = 't' and table_schema = 'test'; +drop table t; + --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 f55b43f56da..38755b3daec 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -3,6 +3,11 @@ -- source suite/versioning/engines.inc call mtr.add_suppression("need more HISTORY partitions"); +call mtr.add_suppression("Duplicate partition name"); +call mtr.add_suppression("Wrong partitioning type"); +call mtr.add_suppression("Table 'test.t1' doesn't exist"); +let $wait_no_alter= select count(*) = 0 from information_schema.processlist where info like 'ALTER TABLE %'; +let $wait_alter= select count(*) > 0 from information_schema.processlist where info like 'ALTER TABLE %'; set system_versioning_alter_history=keep; --echo # Check conventional partitioning on temporal tables @@ -213,7 +218,9 @@ prepare stmt from @str; execute stmt; drop prepare stmt; select @ts0 = @ts1; select @ts2 = @ts3; ---echo ## rotation by LIMIT +--echo # +--echo # Rotation by LIMIT +--echo # --error ER_PART_WRONG_VALUE create or replace table t1 (x int) with system versioning @@ -241,8 +248,37 @@ insert into t1 values (7), (8); delete from t1; select * from t1 partition (p1) order by x; +--echo # Auto-create history partitions +create or replace table t1 (x int) with system versioning +partition by system_time limit 1000 auto_increment; +lock tables t1 write; +insert into t1 values (1); +update t1 set x= x + 1; +--let $wait_condition= $wait_alter +--source include/wait_condition.inc +select state, info from information_schema.processlist where info like 'ALTER TABLE %'; +unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; ---echo ## rotation by INTERVAL +--echo ### Test ALTER failure +set @lock_wait_timeout0= @@global.lock_wait_timeout; +set global lock_wait_timeout= 1; +create or replace table t1 (x int) with system versioning partition by system_time limit 1000; +lock tables t1 write; +insert t1 values (1); +--real_sleep 1 +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc +insert t1 values (1); +unlock tables; +set global lock_wait_timeout= @lock_wait_timeout0; + +--echo # +--echo # Rotation by INTERVAL +--echo # --error ER_PART_WRONG_VALUE create or replace table t1 (x int) with system versioning @@ -258,7 +294,7 @@ partition by system_time interval 10 year partitions 3; --echo # INTERVAL and ALTER TABLE create or replace table t1 (i int) with system versioning -partition by system_time interval 1 hour; +partition by system_time interval 59 minute; set @ts=(select partition_description from information_schema.partitions where table_schema='test' and table_name='t1' and partition_name='p0'); @@ -281,12 +317,15 @@ create or replace table t1 (i int) with system versioning partition by system_time interval 1 day subpartition by key (i) subpartitions 2 (partition p1 history, partition pn current); +lock tables t1 write; # prevent partition auto-creation + set timestamp=unix_timestamp('2001-02-03 10:20:40'); insert t1 values (1); delete from t1; set timestamp=unix_timestamp('2001-02-04 10:20:50'); insert t1 values (2); delete from t1; -select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; +select subpartition_name, partition_description from information_schema.partitions where table_schema='test' and table_name='t1'; +select * from t1 partition (p1); set timestamp=unix_timestamp('2001-02-04 10:20:55'); alter table t1 add partition (partition p0 history, partition p2 history); @@ -295,7 +334,15 @@ insert t1 values (4),(5); set timestamp=unix_timestamp('2001-02-04 10:30:10'); update t1 set i=6 where i=5; -select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; +select subpartition_name, partition_description from information_schema.partitions where table_schema='test' and table_name='t1'; +select * from t1 partition (p1); +select * from t1 partition (p0); +select * from t1 partition (p2); + +alter table t1 rebuild partition p0, p1, p2; +select * from t1 partition (p1); +select * from t1 partition (p0); +select * from t1 partition (p2); --echo ## pruning check set @ts=(select partition_description from information_schema.partitions @@ -310,6 +357,9 @@ set @ts=(select row_end from t1 for system_time all where i=1); select * from t1 for system_time all where row_end = @ts; --replace_column 5 # 10 # 11 # explain partitions select * from t1 for system_time all where row_end = @ts; +unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc --echo ## INTERVAL ... STARTS --error ER_PART_WRONG_VALUE @@ -406,6 +456,7 @@ set timestamp= unix_timestamp('2001-01-01 00:00:00'); create or replace table t1 (i int) with system versioning partition by system_time interval 1 day starts '2000-01-01 00:00:00' partitions 3; +lock tables t1 write; # prevent partition auto-creation --echo # we are warned when we push to present: insert into t1 values (0); @@ -436,11 +487,16 @@ update t1 set i= i + 1; select *, row_end from t1 partition (p0); select *, row_end from t1 partition (p1); -set timestamp= unix_timestamp('2000-01-01 00:00:00'); +unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc + --echo # and this is how it usually goes: +set timestamp= unix_timestamp('2000-01-01 00:00:00'); create or replace table t1 (i int) with system versioning partition by system_time interval 1 day partitions 3; +lock tables t1 write; # prevent partition auto-creation insert into t1 values (0); set timestamp= unix_timestamp('2000-01-01 00:00:01'); @@ -453,12 +509,20 @@ set timestamp= unix_timestamp('2000-01-04 00:00:01'); update t1 set i= i + 1; alter table t1 add partition (partition p2 history, partition p3 history); +select *, row_end from t1 partition (p0); +select *, row_end from t1 partition (p1); +select *, row_end from t1 partition (p2); +select *, row_end from t1 partition (p3); +alter table t1 rebuild partition p0, p1, p2, p3; select *, row_end from t1 partition (p0); select *, row_end from t1 partition (p1); select *, row_end from t1 partition (p2); select *, row_end from t1 partition (p3); +unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc drop tables t1, t2; --echo ## Subpartitions @@ -472,9 +536,9 @@ insert into t1 (x) values (1), (2), (3), (4), (5); select * from t1 partition (pnsp0); select * from t1 partition (pnsp1); ---echo ### warn about full partition delete from t1 where x < 3; delete from t1; +--echo ### warn about full partition delete from t1; select * from t1 partition (p0sp0); select * from t1 partition (p0sp1); @@ -627,6 +691,8 @@ alter table t1 partition by system_time interval 1 week ( partition p1 history, partition pn current); unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc --echo # --echo # MDEV-14748 Assertion in ha_myisammrg::attach_children() @@ -663,6 +729,8 @@ lock table t1 write; alter table t1 add partition (partition p0 history); insert into t1 values (1); unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc --echo # --echo # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW @@ -687,11 +755,13 @@ alter table t drop system versioning; create or replace table t1 (i int) with system versioning; insert into t1 values (1), (2); update t1 set i= 3; -alter table t1 partition by system_time interval 1 month (partition p1 history, partition pn current); +alter table t1 partition by system_time interval 59 minute (partition p1 history, partition pn current); lock table t1 write; alter table t1 add partition (partition p2 history); insert into t1 values (4); unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc --echo # --echo # MDEV-15036 Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' in Diagnostics_area::set_ok_status or unexpected ER_RANGE_NOT_INCREASING_ERROR diff --git a/mysql-test/suite/versioning/t/rpl.test b/mysql-test/suite/versioning/t/rpl.test index b5be68feece..ec612551578 100644 --- a/mysql-test/suite/versioning/t/rpl.test +++ b/mysql-test/suite/versioning/t/rpl.test @@ -133,4 +133,28 @@ sync_slave_with_master; connection master; drop table t1; +--echo # +--echo # MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT +--echo # +let $wait_no_alter= select count(*) = 0 from information_schema.processlist where info like 'ALTER TABLE %'; +let $wait_alter= select count(*) > 0 from information_schema.processlist where info like 'ALTER TABLE %'; + +create or replace table t1 (x int) with system versioning +partition by system_time limit 1000 auto_increment; +lock tables t1 write; +insert t1 values (); +delete from t1; +--let $wait_condition= $wait_alter +--source include/wait_condition.inc +unlock tables; +--let $wait_condition= $wait_no_alter +--source include/wait_condition.inc +select count(*) - 1 as hist_partitions from information_schema.partitions where table_name = 't1' and table_schema = 'test'; +--sync_slave_with_master +select count(*) - 1 as hist_partitions from information_schema.partitions where table_name = 't1' and table_schema = 'test'; +--connection master + +drop database test; +create database test; + --source include/rpl_end.inc |