Re: [Maria-developers] 9941c6a3179: MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT

Hi, Aleksey! On Apr 07, Aleksey Midenkov wrote:

revision-id: 9941c6a3179 (mariadb-10.5.2-164-g9941c6a3179) parent(s): 920c3c6b237 author: Aleksey Midenkov <midenok@gmail.com> committer: Aleksey Midenkov <midenok@gmail.com> timestamp: 2020-04-06 08:05:43 +0300 message:

MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT

See some comment below, please

diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index 355b571e5a0..b35a5138015 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -6,6 +6,7 @@ if (!$TEST_VERSIONING_SO) source include/have_innodb.inc;

set @@session.time_zone='+00:00'; +set @@global.time_zone='+00:00';

Why is that? I understand you might've needed it when a partition was added in a separate thread, but why now?

select ifnull(max(transaction_id), 0) into @start_trx_id from mysql.transaction_registry; set @test_start=now(6);

diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result index cb865a835b3..2e4a2bf9974 100644 --- a/mysql-test/suite/versioning/r/delete_history.result +++ b/mysql-test/suite/versioning/r/delete_history.result @@ -154,3 +154,18 @@ 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 auto_increment;

this looks like a hack, I think we need a dedicated syntax for that. but I couldn't think of anything good now. ok, I see that you allow both AUTO_INCREMENT and AUTO. May be better just to use AUTO?

+delete history from t; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT +PARTITIONS 2 +drop table t; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index a7047cbd11b..660d2c81961 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -289,11 +291,27 @@ 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; +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 +PARTITIONS 3

this test needs a comment, I don't understand what's happening here. why lock tables?

+# +# Rotation by INTERVAL +# create or replace table t1 (x int) with system versioning partition by system_time interval 0 second partitions 3; -ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL' +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for INTERVAL create table t1 (i int) with system versioning partition by system_time interval 6 day limit 98; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 98' at line 2 @@ -302,7 +320,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;

why?

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; @@ -353,28 +371,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'; +select subpartition_name,partition_description from information_schema.partitions where table_schema='test' and table_name='t1';

why?

-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 +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 +select * from t1 partition (p0); +i +5 +2 +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); @@ -1044,3 +1085,568 @@ t1 CREATE TABLE `t1` ( PARTITION BY SYSTEM_TIME PARTITIONS 8 drop tables 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 999 auto_increment; +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for LIMIT (< 1000) +create or replace table t1 (x int) with system versioning +partition by system_time interval 3599 second auto_increment; +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for INTERVAL (< 1 HOUR)

no arbitrary limitations, please

+create or replace table t1 (x int) with system versioning +partition by system_time limit 1000 auto_increment; +affected rows: 0 +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 +PARTITIONS 2 +affected rows: 1 +insert into t1 values (1); +affected rows: 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 1000 AUTO_INCREMENT +PARTITIONS 3 +affected rows: 1 +# Increment from 2 to 5 +create or replace table t1 (x int) with system versioning +partition by system_time interval 3600 second +starts '2000-01-01 00:00:00' auto_increment; +affected rows: 0 +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 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 2 +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +affected rows: 0 +insert into t1 values (1); +affected rows: 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 INTERVAL 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 3 +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +affected rows: 0 +update t1 set x= x + 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 4 +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 02:00:00'); +affected rows: 0 +update t1 set x= x + 2; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 5 +affected rows: 1 +# Increment from 3 to 6, manual names, LOCK TABLES

again, I think this is overcomplication and overengineering. I don't see any need for mixing automatic and manual partition names.

+set timestamp= unix_timestamp('2000-01-01 00:00:00'); +affected rows: 0 +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour auto_increment ( +partition p1 history, +partition p3 history, +partition pn current); +affected rows: 0 +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' AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +insert into t1 values (1); +affected rows: 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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +affected rows: 0 +update t1 set x= x + 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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' AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 02:00:00'); +affected rows: 0 +update t1 set x= x + 4; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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' AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p4` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +lock tables t1 write; +affected rows: 0 +set timestamp= unix_timestamp('2000-01-01 03:00:00'); +affected rows: 0 +update t1 set x= x + 5; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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' AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p4` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p5` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +unlock tables; +affected rows: 0 +# Test VIEW, LOCK TABLES +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +affected rows: 0 +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour auto_increment; +affected rows: 0 +create or replace view v1 as select * from t1; +affected rows: 0 +insert into v1 values (1); +affected rows: 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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 3 +affected rows: 1 +lock tables t1 write; +affected rows: 0 +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +affected rows: 0 +update t1 set x= x + 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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' AUTO_INCREMENT +PARTITIONS 4 +affected rows: 1 +unlock tables; +affected rows: 0 +drop view v1; +affected rows: 0 +drop tables t1; +affected rows: 0 +# Multiple increments in single command +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +affected rows: 0 +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour auto_increment partitions 3; +affected rows: 0 +create or replace table t2 (y int) with system versioning +partition by system_time interval 1 hour auto_increment partitions 4; +affected rows: 0 +insert into t1 values (1); +affected rows: 1 +insert into t2 values (2); +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +affected rows: 0 +update t1, t2 set x= x + 1, y= y + 1; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +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' AUTO_INCREMENT +PARTITIONS 4 +affected rows: 1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `y` 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' AUTO_INCREMENT +PARTITIONS 4 +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 02:00:00'); +affected rows: 0 +update t1, t2 set x= x + 1, y= y + 1; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +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' AUTO_INCREMENT +PARTITIONS 5 +affected rows: 1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `y` 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' AUTO_INCREMENT +PARTITIONS 5 +affected rows: 1 +drop tables t1, t2; +affected rows: 0 +# PS, SP, LOCK TABLES +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +affected rows: 0 +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour auto_increment; +affected rows: 0 +execute immediate 'insert into t1 values (1)'; +affected rows: 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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 3 +affected rows: 1 +prepare s from 'update t1 set x= x + 6'; +affected rows: 0 +info: Statement prepared +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +affected rows: 0 +execute s; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +execute s; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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' AUTO_INCREMENT +PARTITIONS 4 +affected rows: 1 +lock tables t1 write; +affected rows: 0 +set timestamp= unix_timestamp('2000-01-01 02:00:00'); +affected rows: 0 +execute s; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +execute s; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +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' AUTO_INCREMENT +PARTITIONS 5

add a test where timestamp is incremented by, say, 24 hours, please

+affected rows: 1 +unlock tables; +affected rows: 0 +drop prepare s; +affected rows: 0 +create procedure sp() update t1 set x= x + 7; +affected rows: 0 +set timestamp= unix_timestamp('2000-01-01 03:00:00'); +affected rows: 0 +call sp; +affected rows: 1 +call sp; +affected rows: 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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 6 +affected rows: 1 +lock tables t1 write; +affected rows: 0 +set timestamp= unix_timestamp('2000-01-01 04:00:00'); +affected rows: 0 +call sp; +affected rows: 1 +call sp; +affected rows: 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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 7 +affected rows: 1 +unlock tables; +affected rows: 0 +drop procedure sp; +affected rows: 0 +# Complex table +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +affected rows: 0 +create or replace table t1 ( +x int primary key auto_increment, +t timestamp(6) default '2001-11-11 11:11:11', +b blob(4096) compressed null, +c varchar(1033) character set utf8 not null, +u int unique, +m enum('a', 'b', 'c') not null default 'a' comment 'absolute', +i1 tinyint, i2 smallint, i3 bigint, +index three(i1, i2, i3), +v1 timestamp(6) generated always as (t + interval 1 day), +v2 timestamp(6) generated always as (t + interval 1 month) stored, +s timestamp(6) as row start, +e timestamp(6) as row end, +period for system_time (s, e), +ps date, pe date, +period for app_time (ps, pe), +constraint check_constr check (u > -1)) +with system versioning default charset=ucs2 +partition by system_time interval 1 hour auto_increment ( +partition p2 history, +partition pn current); +affected rows: 0 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) NOT NULL AUTO_INCREMENT, + `t` timestamp(6) NOT NULL DEFAULT '2001-11-11 11:11:11.000000', + `b` blob /*!100301 COMPRESSED*/ DEFAULT NULL, + `c` varchar(1033) CHARACTER SET utf8 NOT NULL, + `u` int(11) DEFAULT NULL, + `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute', + `i1` tinyint(4) DEFAULT NULL, + `i2` smallint(6) DEFAULT NULL, + `i3` bigint(20) DEFAULT NULL, + `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL, + `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED, + `s` timestamp(6) GENERATED ALWAYS AS ROW START, + `e` timestamp(6) GENERATED ALWAYS AS ROW END, + `ps` date NOT NULL, + `pe` date NOT NULL, + PRIMARY KEY (`x`,`e`), + UNIQUE KEY `u` (`u`,`e`), + KEY `three` (`i1`,`i2`,`i3`), + PERIOD FOR SYSTEM_TIME (`s`, `e`), + PERIOD FOR `app_time` (`ps`, `pe`), + CONSTRAINT `check_constr` CHECK (`u` > -1) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=ucs2 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +(PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +insert into t1 (x, c, u, i1, i2, i3, ps, pe) +values (1, 'cc', 0, 1, 2, 3, '1999-01-01', '2000-01-01'); +affected rows: 1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) NOT NULL AUTO_INCREMENT, + `t` timestamp(6) NOT NULL DEFAULT '2001-11-11 11:11:11.000000', + `b` blob /*!100301 COMPRESSED*/ DEFAULT NULL, + `c` varchar(1033) CHARACTER SET utf8 NOT NULL, + `u` int(11) DEFAULT NULL, + `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute', + `i1` tinyint(4) DEFAULT NULL, + `i2` smallint(6) DEFAULT NULL, + `i3` bigint(20) DEFAULT NULL, + `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL, + `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED, + `s` timestamp(6) GENERATED ALWAYS AS ROW START, + `e` timestamp(6) GENERATED ALWAYS AS ROW END, + `ps` date NOT NULL, + `pe` date NOT NULL, + PRIMARY KEY (`x`,`e`), + UNIQUE KEY `u` (`u`,`e`), + KEY `three` (`i1`,`i2`,`i3`), + PERIOD FOR SYSTEM_TIME (`s`, `e`), + PERIOD FOR `app_time` (`ps`, `pe`), + CONSTRAINT `check_constr` CHECK (`u` > -1) +) ENGINE=DEFAULT_ENGINE AUTO_INCREMENT=2 DEFAULT CHARSET=ucs2 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +(PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +affected rows: 0 +update t1 set x= x + 8; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) NOT NULL AUTO_INCREMENT, + `t` timestamp(6) NOT NULL DEFAULT '2001-11-11 11:11:11.000000', + `b` blob /*!100301 COMPRESSED*/ DEFAULT NULL, + `c` varchar(1033) CHARACTER SET utf8 NOT NULL, + `u` int(11) DEFAULT NULL, + `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute', + `i1` tinyint(4) DEFAULT NULL, + `i2` smallint(6) DEFAULT NULL, + `i3` bigint(20) DEFAULT NULL, + `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL, + `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED, + `s` timestamp(6) GENERATED ALWAYS AS ROW START, + `e` timestamp(6) GENERATED ALWAYS AS ROW END, + `ps` date NOT NULL, + `pe` date NOT NULL, + PRIMARY KEY (`x`,`e`), + UNIQUE KEY `u` (`u`,`e`), + KEY `three` (`i1`,`i2`,`i3`), + PERIOD FOR SYSTEM_TIME (`s`, `e`), + PERIOD FOR `app_time` (`ps`, `pe`), + CONSTRAINT `check_constr` CHECK (`u` > -1) +) ENGINE=DEFAULT_ENGINE AUTO_INCREMENT=10 DEFAULT CHARSET=ucs2 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +(PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +affected rows: 1 +# Concurrent DML +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour auto_increment; +insert into t1 values (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 INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 3 +connect con8, localhost, root; +connect con7, localhost, root; +connect con6, localhost, root; +connect con5, localhost, root; +connect con4, localhost, root; +connect con3, localhost, root; +connect con2, localhost, root; +connect con1, localhost, root; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 10; +connection con2; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 20; +connection con3; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 30; +connection con4; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 40; +connection con5; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 50; +connection con6; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 60; +connection con7; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 70; +connection con8; +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +update t1 set x= x + 80; +connection con1; +disconnect con1; +connection con2; +disconnect con2; +connection con3; +disconnect con3; +connection con4; +disconnect con4; +connection con5; +disconnect con5; +connection con6; +disconnect con6; +connection con7; +disconnect con7; +disconnect con8; +connection default; +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' AUTO_INCREMENT +PARTITIONS 4 +drop tables t1; +create or replace table t1 (x int) with system versioning engine innodb +partition by system_time interval 1 hour auto; +start transaction; +select * from t1; +x +connect con1, localhost, root; +set lock_wait_timeout= 1; +insert into t1 values (1); +Warnings: +Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of INTERVAL, need more HISTORY partitions +Error 1205 Lock wait timeout exceeded; try restarting transaction +Warning 4171 Auto-increment history partition: alter partition table failed +Warning 4171 Versioned table `test`.`t1`: adding HISTORY partition failed with error 0, see error log for details

"error 0" is strange and "see error log for details" isn't very user-friendly, a user might not have access to the error log at all

+select * from t1; +x +1

I don't understand, there was an error above, why did insert succeed?

+disconnect con1; +connection default; +drop table t1; diff --git a/mysql-test/suite/versioning/r/rpl.result b/mysql-test/suite/versioning/r/rpl.result index 627f3991499..68113190889 100644 --- a/mysql-test/suite/versioning/r/rpl.result +++ b/mysql-test/suite/versioning/r/rpl.result @@ -164,4 +164,65 @@ 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 +# +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour auto_increment; +insert t1 values (); +set timestamp= unix_timestamp('2000-01-01 01:00:00'); +delete from t1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 4 +connection slave; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO_INCREMENT +PARTITIONS 4 +connection master; +drop table t1; +# +# MENT-685 DML events for auto-partitioned tables are written into binary log twice +#

the test below doesn't seem to match the description above

+create table t1 (x int) partition by hash (x); +alter table t1 add partition partitions 1 auto_increment; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use +drop table t1; +create table t1 (x int) with system versioning +partition by system_time limit 1000 auto +(partition p1 history, partition pn current); +insert into t1 values (1); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = ENGINE, + PARTITION `p2` HISTORY ENGINE = ENGINE, + PARTITION `pn` CURRENT ENGINE = ENGINE) +connection slave; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT +(PARTITION `p1` HISTORY ENGINE = ENGINE, + PARTITION `p2` HISTORY ENGINE = ENGINE, + PARTITION `pn` CURRENT ENGINE = ENGINE) +select * from t1; +x +1 +connection master; +drop table t1; include/rpl_end.inc diff --git a/sql/handler.cc b/sql/handler.cc index c6ecc9566d8..c86f96b9689 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -1512,7 +1512,7 @@ int ha_commit_trans(THD *thd, bool all) DBUG_ASSERT(thd->transaction.stmt.ha_list == NULL || trans == &thd->transaction.stmt);

- if (thd->in_sub_stmt) + if (thd->in_sub_stmt & ~SUB_STMT_AUTO_HIST)

1. why? That is, why do you call ha_commit_trans() when adding a partition? 2. please add a test with insert under start transaction. what should happen in this case?

{ DBUG_ASSERT(0); /* diff --git a/sql/partition_info.cc b/sql/partition_info.cc index f4b7260f8b0..617c839721b 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -848,29 +850,289 @@ void partition_info::vers_set_hist_part(THD *thd) else vers_info->hist_part= next; } + } + else if (vers_info->interval.is_set()) + { + if (vers_info->hist_part->range_value <= thd->query_start()) + { + partition_element *next= NULL; + bool error= true; + List_iterator<partition_element> it(partitions); + while (next != vers_info->hist_part) + next= it++; + + while ((next= it++) != vers_info->now_part) + { + vers_info->hist_part= next; + if (next->range_value > thd->query_start()) + { + error= false; + break; + } + } + if (error) + my_error(WARN_VERS_PART_FULL, MYF(ME_WARNING|ME_ERROR_LOG), + table->s->db.str, table->s->table_name.str, + vers_info->hist_part->partition_name, "INTERVAL"); + } + } + + if (!vers_info->auto_inc || + vers_info->hist_part->id + VERS_MIN_EMPTY < vers_info->now_part->id) return; + + switch (thd->lex->sql_command) + { + case SQLCOM_DELETE: + if (thd->lex->last_table()->vers_conditions.delete_history) + break; + /* fallthrough */ + case SQLCOM_UPDATE: + case SQLCOM_INSERT: + case SQLCOM_INSERT_SELECT: + case SQLCOM_LOAD: + case SQLCOM_REPLACE: + case SQLCOM_REPLACE_SELECT: + case SQLCOM_DELETE_MULTI: + case SQLCOM_UPDATE_MULTI:

it's rather fragile to check for specific sql statements. why not to look at the table lock instead? (with a special check for delete history) Ok, it's a bug. Please add a test with multi-update, where a partitioned table is *not* updated. Like update t1, tpart set t1.x=5 where t1.y=tpart.z; here a new partition should clearly not be created. also, a simpler example (multi-update is difficult): insert t1 select * from tpart; add both, please.

+ { + TABLE_SHARE *share; + List_iterator_fast<TABLE_SHARE> it(thd->vers_auto_part_tables); + while ((share= it++)) + { + if (table->s == share) + break; + } + if (share) + break; + /* Prevent spawning multiple instances of vers_add_auto_parts() */ + bool altering; + mysql_mutex_lock(&table->s->LOCK_share); + altering= table->s->vers_auto_part; + if (!altering) + table->s->vers_auto_part= true; + mysql_mutex_unlock(&table->s->LOCK_share); + if (altering) + break;

what happens if you're altering already? logically this thread should wait. Where does it do it?

+ if (thd->vers_auto_part_tables.push_back(table->s)) + { + my_error(ER_OUT_OF_RESOURCES, MYF(0)); + } + } + default:; } +}

- if (vers_info->interval.is_set()) - { - if (vers_info->hist_part->range_value > thd->query_start()) - return;

- partition_element *next= NULL; - List_iterator<partition_element> it(partitions); - while (next != vers_info->hist_part) - next= it++; +/** + @brief Run fast_alter_partition_table() to add new history partitions + for tables requiring them. +*/ +void vers_add_auto_parts(THD *thd) +{ + HA_CREATE_INFO create_info; + Alter_info alter_info; + String query; + TABLE_LIST *table_list= NULL; + partition_info *save_part_info= thd->work_part_info; + Query_tables_list save_query_tables; + Reprepare_observer *save_reprepare_observer= thd->m_reprepare_observer; + Diagnostics_area new_stmt_da(thd->query_id, false, true); + Diagnostics_area *save_stmt_da= thd->get_stmt_da(); + bool save_no_write_to_binlog= thd->lex->no_write_to_binlog; + const CSET_STRING save_query= thd->query_string; + thd->m_reprepare_observer= NULL; + thd->lex->reset_n_backup_query_tables_list(&save_query_tables); + thd->in_sub_stmt|= SUB_STMT_AUTO_HIST; + thd->lex->no_write_to_binlog= !thd->is_current_stmt_binlog_format_row(); + TABLE_LIST *tl; + + DBUG_ASSERT(!thd->vers_auto_part_tables.is_empty()); + + for (TABLE_SHARE &share: thd->vers_auto_part_tables) + { + tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST)); + tl->init_one_table(&share.db, &share.table_name, NULL, TL_READ_NO_INSERT); + tl->open_type= OT_BASE_ONLY; + tl->i_s_requested_object= OPEN_TABLE_ONLY; + tl->next_global= table_list; + table_list= tl; + } + + /* NB: mysql_execute_command() can be recursive because of PS/SP. + Don't duplicate any processing including error messages. */ + thd->vers_auto_part_tables.empty(); + + DBUG_ASSERT(!thd->is_error()); + /* NB: we have to preserve m_affected_rows, m_row_count_func, m_last_insert_id, etc */ + thd->set_stmt_da(&new_stmt_da); + new_stmt_da.set_overwrite_status(true); + + DDL_options_st ddl_opts_none; + ddl_opts_none.init(); + if (open_and_lock_tables(thd, ddl_opts_none, table_list, false, 0)) + goto open_err; + + for (tl= table_list; tl; tl= tl->next_global) + { + TABLE *table= tl->table; + DBUG_ASSERT(table); + DBUG_ASSERT(table->s->get_table_ref_type() == TABLE_REF_BASE_TABLE); + DBUG_ASSERT(table->versioned()); + DBUG_ASSERT(table->part_info); + DBUG_ASSERT(table->part_info->vers_info); + alter_info.reset(); + alter_info.partition_flags= ALTER_PARTITION_ADD|ALTER_PARTITION_AUTO_HIST; + create_info.init(); + create_info.alter_info= &alter_info; + Alter_table_ctx alter_ctx(thd, tl, 1, &table->s->db, &table->s->table_name); + + if (thd->mdl_context.upgrade_shared_lock(table->mdl_ticket, + MDL_SHARED_NO_WRITE, + thd->variables.lock_wait_timeout)) + goto exit; + + create_info.db_type= table->s->db_type(); + create_info.options|= HA_VERSIONED_TABLE; + DBUG_ASSERT(create_info.db_type); + + create_info.vers_info.set_start(table->s->vers_start_field()->field_name); + create_info.vers_info.set_end(table->s->vers_end_field()->field_name); + + partition_info *part_info= new partition_info(); + if (unlikely(!part_info)) + { + my_error(ER_OUT_OF_RESOURCES, MYF(0)); + goto exit; + } + part_info->use_default_num_partitions= false; + part_info->use_default_num_subpartitions= false; + part_info->num_parts= 1; + part_info->num_subparts= table->part_info->num_subparts; + part_info->subpart_type= table->part_info->subpart_type; + if (unlikely(part_info->vers_init_info(thd))) + { + my_error(ER_OUT_OF_RESOURCES, MYF(0)); + goto exit; + } + /* Choose first non-occupied name suffix */ + uint32 suffix= table->part_info->num_parts - 1; + DBUG_ASSERT(suffix > 0); + char part_name[MAX_PART_NAME_SIZE + 1]; + if (make_partition_name(part_name, suffix)) + { +vers_make_name_err: + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + WARN_VERS_HIST_PART_ERROR, + "Auto-increment history partition: " + "name generation failed for suffix %d", suffix); + my_error(WARN_VERS_HIST_PART_ERROR, MYF(ME_WARNING), + table->s->db.str, table->s->table_name.str, 0); + goto exit; + } + List_iterator_fast<partition_element> it(table->part_info->partitions); + partition_element *el; + while ((el= it++)) + { + if (0 == my_strcasecmp(&my_charset_latin1, el->partition_name, part_name)) + { + if (make_partition_name(part_name, ++suffix)) + goto vers_make_name_err; + it.rewind(); + } + }

- while ((next= it++) != vers_info->now_part) + // NB: set_ok_status() requires DA_EMPTY + thd->get_stmt_da()->reset_diagnostics_area(); + + thd->work_part_info= part_info; + if (part_info->set_up_defaults_for_partitioning(thd, table->file, + NULL, suffix + 1)) { - vers_info->hist_part= next; - if (next->range_value > thd->query_start()) - return; + push_warning(thd, Sql_condition::WARN_LEVEL_WARN, + WARN_VERS_HIST_PART_ERROR, + "Auto-increment history partition: " + "setting up defaults failed"); + goto exit; + } + bool partition_changed= false; + bool fast_alter_partition= false; + if (prep_alter_part_table(thd, table, &alter_info, &create_info, + &partition_changed, &fast_alter_partition)) + { + push_warning(thd, Sql_condition::WARN_LEVEL_WARN, WARN_VERS_HIST_PART_ERROR, + "Auto-increment history partition: " + "alter partitition prepare failed"); + goto exit; + } + if (!fast_alter_partition) + { + push_warning(thd, Sql_condition::WARN_LEVEL_WARN, WARN_VERS_HIST_PART_ERROR, + "Auto-increment history partition: " + "fast alter partitition is not possible"); + my_error(WARN_VERS_HIST_PART_ERROR, MYF(ME_WARNING), + table->s->db.str, table->s->table_name.str, 0); + goto exit; + } + DBUG_ASSERT(partition_changed); + if (mysql_prepare_alter_table(thd, table, &create_info, &alter_info, + &alter_ctx)) + { + push_warning(thd, Sql_condition::WARN_LEVEL_WARN, WARN_VERS_HIST_PART_ERROR, + "Auto-increment history partition: " + "alter prepare failed"); + goto exit; + } + + // Forge query string for rpl logging + if (!thd->lex->no_write_to_binlog) + { + query.set(STRING_WITH_LEN("ALTER TABLE `"), &my_charset_latin1); + + if (query.append(table->s->db) || + query.append(STRING_WITH_LEN("`.`")) || + query.append(table->s->table_name) || + query.append("` ADD PARTITION (PARTITION `") || + query.append(part_name) || + query.append("` HISTORY) AUTO_INCREMENT")) + { + my_error(ER_OUT_OF_RESOURCES, MYF(ME_ERROR_LOG)); + goto exit; + } + CSET_STRING qs(query.c_ptr(), query.length(), &my_charset_latin1); + thd->set_query(qs); + } + + if (fast_alter_partition_table(thd, table, &alter_info, &create_info, + tl, &table->s->db, &table->s->table_name)) + { + push_warning(thd, Sql_condition::WARN_LEVEL_WARN, WARN_VERS_HIST_PART_ERROR, + "Auto-increment history partition: " + "alter partition table failed"); + my_error(WARN_VERS_HIST_PART_ERROR, MYF(ME_WARNING), + tl->db.str, tl->table_name.str, 0); } - my_error(WARN_VERS_PART_FULL, MYF(ME_WARNING|ME_ERROR_LOG), - table->s->db.str, table->s->table_name.str, - vers_info->hist_part->partition_name, "INTERVAL"); } + + if (!thd->transaction.stmt.is_empty()) + trans_commit_stmt(thd); + +exit: + // If we failed with error allow non-processed tables to be processed next time + if (tl) + while ((tl= tl->next_global)) + tl->table->s->vers_auto_part= false; + close_thread_tables(thd); +open_err: + thd->work_part_info= save_part_info; + thd->m_reprepare_observer= save_reprepare_observer; + thd->lex->restore_backup_query_tables_list(&save_query_tables); + thd->in_sub_stmt&= ~SUB_STMT_AUTO_HIST; + if (!new_stmt_da.is_warning_info_empty()) + save_stmt_da->copy_sql_conditions_from_wi(thd, new_stmt_da.get_warning_info()); + thd->set_stmt_da(save_stmt_da); + thd->lex->no_write_to_binlog= save_no_write_to_binlog; + thd->set_query(save_query); }

diff --git a/sql/partition_info.h b/sql/partition_info.h index eb8e53a381a..d02eecea073 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -34,10 +34,19 @@ typedef bool (*check_constants_func)(THD *thd, partition_info *part_info);

struct st_ddl_log_memory_entry;

+ +/* Auto-create history partition configuration */ +static const uint VERS_MIN_EMPTY= 1;

No, this doesn't work. One can easily set @@timestamp to do a multi-hour jump and no fixed value of VERS_MIN_EMPTY will help. You need to add partitions before the statement, as I wrote in my previous review.

+static const uint VERS_MIN_INTERVAL= 3600; // seconds +static const uint VERS_MIN_LIMIT= 1000; +static const uint VERS_ERROR_TIMEOUT= 300; // seconds + + struct Vers_part_info : public Sql_alloc { Vers_part_info() : limit(0), + auto_inc(false), now_part(NULL), hist_part(NULL) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 7cc1faea79b..4be3342e78e 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -7521,14 +7533,19 @@ add_partition_rule:

add_part_extra: /* empty */ - | '(' part_def_list ')' + | '(' part_def_list ')' opt_vers_auto_inc { LEX *lex= Lex; lex->part_info->num_parts= lex->part_info->partitions.elements; + if ($4) + lex->alter_info.partition_flags|= ALTER_PARTITION_AUTO_HIST; } - | PARTITIONS_SYM real_ulong_num + | PARTITIONS_SYM real_ulong_num opt_vers_auto_inc { - Lex->part_info->num_parts= $2; + LEX *lex= Lex;

^^^ this pattern is obsolete for, like, 10 years. It does no harm, but adds no value either.

+ lex->part_info->num_parts= $2; + if ($3) + lex->alter_info.partition_flags|= ALTER_PARTITION_AUTO_HIST; } ;

diff --git a/storage/mroonga/mrn_table.cpp b/storage/mroonga/mrn_table.cpp index b10668cfcce..6458402f572 100644 --- a/storage/mroonga/mrn_table.cpp +++ b/storage/mroonga/mrn_table.cpp @@ -932,7 +932,7 @@ MRN_SHARE *mrn_get_share(const char *table_name, TABLE *table, int *error) share->wrap_key_info = NULL; share->wrap_primary_key = MAX_KEY; } - memcpy(wrap_table_share, table->s, sizeof(*wrap_table_share)); + memcpy((void *)wrap_table_share, (void *)table->s, sizeof(*wrap_table_share));

why is that?

mrn_init_sql_alloc(current_thd, &(wrap_table_share->mem_root)); wrap_table_share->keys = share->wrap_keys; wrap_table_share->key_info = share->wrap_key_info;

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org