Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.9(EOL)
-
None
Description
I have a system-versioned table with some historical data, e.g.
MariaDB [test]> show create table t \G |
*************************** 1. row ***************************
|
Table: t |
Create Table: CREATE TABLE `t` ( |
`pk` int(11) NOT NULL, |
`a` int(11) DEFAULT NULL, |
PRIMARY KEY (`pk`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
1 row in set (0.000 sec) |
|
MariaDB [test]> select year(row_end) y, count(*) from t for system_time all group by y; |
+------+----------+ |
| y | count(*) | |
+------+----------+ |
| 2019 | 8 |
|
| 2020 | 12 |
|
| 2021 | 4 |
|
| 2038 | 2 |
|
+------+----------+ |
4 rows in set (0.001 sec) |
Now I want to make it auto-partitioned, so I run ALTER TABLE:
MariaDB [test]> alter table t partition by system_time interval 1 year auto; |
Query OK, 26 rows affected (0.017 sec) |
Records: 26 Duplicates: 0 Warnings: 0
|
My expectation is that it will auto-create the partition structure suitable for the already existing historical data – that is, 2 or 3 historical partitions starting from the lowest row_end timestamp with 1 year interval. It doesn't happen; instead, the table gets 1 historical partition starting from the current time, and all historical data is put in there:
bb-10.6-midenok-MDEV-17554 b96b96f9f |
MariaDB [test]> show create table t \G |
*************************** 1. row ***************************
|
Table: t |
Create Table: CREATE TABLE `t` ( |
`pk` int(11) NOT NULL, |
`a` int(11) DEFAULT NULL, |
PRIMARY KEY (`pk`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'2021-04-26 00:00:00' AUTO |
PARTITIONS 2
|
1 row in set (0.000 sec) |
|
MariaDB [test]> select count(*) from t partition (p0); |
+----------+ |
| count(*) | |
+----------+ |
| 24 |
|
+----------+ |
1 row in set (0.000 sec) |
It is even semantically wrong, 2019- and 2020-year data shouldn't be in a partition with start time in 2021.
Admittedly non-auto partitioning would do it too, but it is somewhat natural to expect more intelligent action from "auto".
With LIMIT instead of INTERVAL, sufficient partitions aren't created either, but at least with LIMIT there is no "semantically wrong" aspect, the partition simply gets overflowed, which can happen anyway.
If the behavior is intentional, please document it (and in general, please make sure that the documentation says what happens upon ALTER TABLE .. PARTITION, as currently MDEV-17554 description only mentions CREATE TABLE and DML).
Attachments
Issue Links
- is blocked by
-
MDEV-29114 Pruning depends on current timestamp for partition by SYSTEM_TIME
- Stalled
- relates to
-
MDEV-22247 History partition overflow leads to wrong SELECT result
- Closed
-
MDEV-25390 Pruning boundary for history partitions is wrong by 1 second
- Closed
-
MDEV-17554 Auto-create history partitions for system-versioned tables
- Closed