diff options
| -rw-r--r-- | mysql-test/suite/versioning/r/partition.result | 120 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/partition.test | 70 | ||||
| -rw-r--r-- | sql/sql_partition.cc | 31 |
3 files changed, 220 insertions, 1 deletions
diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 0c93c9d810e..f81d4ad76bc 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -2664,4 +2664,124 @@ drop procedure sp; drop procedure sp2; drop prepare ps; drop prepare ps2; +# +# MDEV-27328 Change of SYSTEM_TIME partitioning options is not possible without data copy +# +create or replace table t1 (f int) with system versioning +partition by hash(f); +alter table t1 partition by system_time; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME +PARTITIONS 2 +create or replace table t1 (f int) with system versioning +partition by system_time; +alter table t1 partition by hash(f); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY HASH (`f`) +create or replace table t1 (x int) with system versioning; +alter table t1 partition by system_time; +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 +PARTITIONS 2 +create or replace table t1 (x int) with system versioning +partition by system_time limit 100 auto partitions 4; +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 100 AUTO +PARTITIONS 4 +alter table t1 add partition partitions 2; +alter table t1 partition by system_time; +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 +PARTITIONS 6 +alter table t1 partition by system_time limit 33; +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 33 +PARTITIONS 6 +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +insert t1 values (0); +set timestamp= unix_timestamp('2000-01-01 00:10:00'); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 01:30:00'); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 02:00:00'); +update t1 set x= x + 1; +alter table t1 partition by system_time interval 1 hour +starts '2000-01-01 00:00:00'; +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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' +PARTITIONS 6 +select * from t1 partition (p0); +x +0 +select * from t1 partition (p1); +x +1 +2 +select * from t1 partition (p2); +x +3 +select * from t1 partition (pn); +x +4 +set timestamp= default; +alter table t1 partition by system_time limit 1; +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 1 +PARTITIONS 6 +update t1 set x= x + 1; +update t1 set x= x + 1; +select * from t1 partition (p0); +x +0 +select * from t1 partition (p1); +x +1 +2 +select * from t1 partition (p2); +x +3 +select * from t1 partition (p3); +x +4 +select * from t1 partition (p4); +x +5 +select * from t1 partition (pn); +x +6 +drop table t1; SET GLOBAL innodb_stats_persistent=@save_persistent; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 4110247c21f..2af39485823 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -1869,6 +1869,76 @@ drop procedure sp2; drop prepare ps; drop prepare ps2; +--echo # +--echo # MDEV-27328 Change of SYSTEM_TIME partitioning options is not possible without data copy +--echo # +create or replace table t1 (f int) with system versioning +partition by hash(f); +alter table t1 partition by system_time; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 (f int) with system versioning +partition by system_time; +alter table t1 partition by hash(f); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 (x int) with system versioning; +alter table t1 partition by system_time; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; + +create or replace table t1 (x int) with system versioning +partition by system_time limit 100 auto partitions 4; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +alter table t1 add partition partitions 2; +alter table t1 partition by system_time; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +alter table t1 partition by system_time limit 33; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +insert t1 values (0); +set timestamp= unix_timestamp('2000-01-01 00:10:00'); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 01:30:00'); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 02:00:00'); +update t1 set x= x + 1; + +# When we switch to INTERVAL we must reorganize partitions. +# Otherwise pruning won't work correctly. +alter table t1 partition by system_time interval 1 hour +starts '2000-01-01 00:00:00'; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +select * from t1 partition (p0); +select * from t1 partition (p1); +select * from t1 partition (p2); +select * from t1 partition (pn); +set timestamp= default; + +# When we switch to LIMIT we probably don't want to reorganize old partitions. +# Note: reorganize for LIMIT is broken, it pushes all history into first partition. +alter table t1 partition by system_time limit 1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +update t1 set x= x + 1; +update t1 set x= x + 1; +select * from t1 partition (p0); +select * from t1 partition (p1); +select * from t1 partition (p2); +select * from t1 partition (p3); +select * from t1 partition (p4); +select * from t1 partition (pn); + +drop table t1; + --disable_prepare_warnings SET GLOBAL innodb_stats_persistent=@save_persistent; --source suite/versioning/common_finish.inc diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index 2e23662349f..9f939ec93d0 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -5046,6 +5046,10 @@ uint prep_alter_part_table(THD *thd, TABLE *table, Alter_info *alter_info, my_error(ER_PARTITION_WRONG_VALUES_ERROR, MYF(0), "LIST", "IN"); } + /* + Adding history partitions to non-history partitioning or + non-history parittions to history partitioning is prohibited. + */ else if (thd->work_part_info->part_type == VERSIONING_PARTITION || tab_part_info->part_type == VERSIONING_PARTITION) { @@ -5328,6 +5332,7 @@ that are reorganised. { if (el->type == partition_element::CURRENT) { + /* now_part is always last partition, we add it to the end of partitions list. */ it.remove(); now_part= el; } @@ -5957,11 +5962,35 @@ the generated partition syntax in a correct manner. { partition_info *part_info= thd->work_part_info; bool is_native_partitioned= FALSE; + if (tab_part_info && tab_part_info->part_type == VERSIONING_PARTITION && + tab_part_info != part_info && part_info->part_type == VERSIONING_PARTITION && + part_info->num_parts == 0) + { + if (part_info->vers_info->interval.is_set() && + /* TODO: equivalent intervals like 1 hour and 60 mins should be considered equal */ + memcmp(&part_info->vers_info->interval, + &tab_part_info->vers_info->interval, + sizeof(Vers_part_info::interval))) + { + /* If interval is changed we can not do fast alter */ + tab_part_info= tab_part_info->get_clone(thd); + } + else + { + /* NOTE: fast_alter_partition_table() works on existing TABLE data. */ + *fast_alter_table= true; + table->mark_table_for_reopen(); + } + *tab_part_info->vers_info= *part_info->vers_info; + thd->work_part_info= part_info= tab_part_info; + *partition_changed= true; + } + /* Need to cater for engine types that can handle partition without using the partition handler. */ - if (part_info != tab_part_info) + else if (part_info != tab_part_info) { if (part_info->fix_parser_data(thd)) { |