Hi, Sergei! I think there should be hard limit on maximum partitions created, as there can be manual timestamp jump. F.ex. timestamp jump for 20 years creates >170000 hourly partitions. What the limit should be? I'd better refuse to create more than 100 partitions. On Thu, Apr 9, 2020 at 9:25 PM Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Aleksey!
On Apr 09, Aleksey Midenkov wrote:
+# +# 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?
AUTO_INCREMENT looks more explanatory. Isn't it?
Not to me. In my opinion it looks 1) confusing
Not more confusing than say "create table" and "create user". It is natural feature of verbs to be applied to different nouns.
2) hackish, a.k.a. "they didn't want to introduce a new keyword, so picked an existing one that looked at least remotely relevant"
I don't think it looks like a hack. Partitions number is auto-incremented, isn't it? Besides, "auto-increment" is best sounding feature name: "auto-create" sounds inconvenient, "auto-add" sounds even more clumsy. But to avoid long dispute on this rather minor topic I removed AUTO_INCREMENT keyword.
To be continued...
I've attached a proof-of-concept patch that adds a partition using fallback-and-retry mechanism of open_table().
Thanks! Here is the final part of my reply to your review: On Tue, Apr 7, 2020 at 3:03 PM Sergei Golubchik <serg@mariadb.org> wrote:
...
+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
Added.
+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
Removed this from message. All error messages are actually pushed to warning stack.
+select * from t1; +x +1
I don't understand, there was an error above, why did insert succeed?
The history is miscellaneous facility and should not disrupt main business process. Now as the locking is done by Open_table_context this principle is violated.
+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
Fixed.
+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?
close_tables_for_reopen() requires thd->transaction.stmt.is_empty(). AFAICS auto-create does statement commit, not transaction commit.
2. please add a test with insert under start transaction. what should happen in this case?
Added. It just works.
{ 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)
We have to restrict auto-creation to history generating DML only. Other commands should not be affected by wait for MDL_EXCLUSIVE. SQLCOM_INSERT, SQLCOM_INSERT_SELECT and SQLCOM_LOAD should be removed from the list. And when ALTER does write-lock it's obvious we don't do auto-creation.
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;
Yes, it's difficult. The other cases are when UPDATE updates non-versioned field or updates to same values. I don't think we must handle these.
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.
Added.
+ { + 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?
This code was removed.
+ 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.
Fixed with the help of your PoC.
+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.
Fixed.
+ 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?
I had compilation error on release build.
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
-- All the best, Aleksey Midenkov @midenok