Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.9.8
-
None
Description
As per the KB, https://mariadb.com/kb/en/system-versioned-tables/#storing-the-history-separately
Partitioning by SYSTEM_TIME also supports automatic partition rotation. One can rotate historical partitions by time or by size. This example shows how to rotate partitions by size:
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME LIMIT 100000 (
PARTITION p0 HISTORY,
PARTITION p1 HISTORY,
PARTITION pcur CURRENT
);
MariaDB will start writing history rows into partition p0, and when it reaches a size of 100000 rows, MariaDB will switch to partition p1. There are only two historical partitions, so when p1 overflows, MariaDB will issue a warning, but will continue writing into it.
But this does not work as expected.
Although it generates appropriate warning when the number of historical rows are more than the LIMIT.
MariaDB [test]> CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
|
-> PARTITION BY SYSTEM_TIME LIMIT 5 ( |
-> PARTITION p0 HISTORY,
|
-> PARTITION p1 HISTORY,
|
-> PARTITION pcur CURRENT
|
-> );
|
Query OK, 0 rows affected (0.029 sec) |
|
|
MariaDB [test]> INSERT INTO t VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); |
Query OK, 10 rows affected (0.005 sec) |
Records: 10 Duplicates: 0 Warnings: 0 |
|
|
MariaDB [test]> DELETE FROM t;
|
Query OK, 10 rows affected (0.010 sec) |
|
|
MariaDB [test]> select * from t partition(p0);
|
+------+
|
| x |
|
+------+
|
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
+------+
|
10 rows in set (0.000 sec) |
|
|
MariaDB [test]> INSERT INTO t VALUES(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); |
Query OK, 10 rows affected (0.008 sec) |
Records: 10 Duplicates: 0 Warnings: 0 |
|
|
MariaDB [test]> DELETE FROM t;
|
Query OK, 10 rows affected, 1 warning (0.003 sec) |
|
|
MariaDB [test]> SHOW WARNINGS;
|
+---------+------+---------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------------------------------------------------------------------------+
|
| Warning | 4114 | Versioned table `test`.`t`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions | |
+---------+------+---------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [test]> select * from t partition(p1);
|
+------+
|
| x |
|
+------+
|
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 15 | |
| 16 | |
| 17 | |
| 18 | |
| 19 | |
| 20 | |
+------+
|
10 rows in set (0.001 sec) |
I tried to trigger a reorganize but that made it even worse
MariaDB [test]> ALTER TABLE t PARTITION BY SYSTEM_TIME LIMIT 5 ( |
-> PARTITION p0 HISTORY,
|
-> PARTITION p1 HISTORY,
|
-> PARTITION pcur CURRENT
|
-> );
|
Query OK, 20 rows affected (0.104 sec) |
Records: 20 Duplicates: 0 Warnings: 0 |
|
|
MariaDB [test]> select * from t partition(p0);
|
+------+
|
| x |
|
+------+
|
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 15 | |
| 16 | |
| 17 | |
| 18 | |
| 19 | |
| 20 | |
+------+
|
20 rows in set (0.013 sec) |
|
|
MariaDB [test]> select * from t partition(p1);
|
Empty set (0.003 sec) |
|
Attachments
Issue Links
- relates to
-
-
- Confirmed
-