Details
-
Bug
-
Status: In Testing (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.9(EOL)
-
None
-
Q3/2026 Server Maintenance, Q4/2026 Server Maintenance, Q4/2026 Server Development
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 ALTER puts row into different partition for system_time interval partitioning
-
- Closed
-
- 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
-
-
MDEV-41543 Changing INTERVAL of SYSTEM_TIME partitioning does not move history to the new partitions
-
- Closed
-
-
MDEV-41545 Unchecked get_clone() in ALTER of partitioned table crashes on out-of-memory
-
- Closed
-
-
MDEV-41643 Temporal to_string() returns a String with unset length when allocation fails
-
- Open
-
-
MDEV-41644 date_add_interval(): signed overflow on huge YEAR/MONTH interval values
-
- Open
-
-
MDEV-41645 Auto-create of SYSTEM_TIME history partitions checks the partition limit without existing partitions and subpartitions
-
- Open
-
-
MDEV-41646 Clean up MAX_DAY_NUMBER: duplicated untyped #define, unclear comment, C-style casts
-
- Open
-