Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3(EOL)
-
None
-
single instance
Description
According to the documentation
https://mariadb.com/kb/en/system-versioned-tables/#storing-the-history-separately
using a table like this
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
PARTITION p0 HISTORY,
PARTITION p1 HISTORY,
PARTITION p2 HISTORY,
PARTITION pcur CURRENT
" the history for the first week after the table was created will be stored in p0. The history for the second week — in p1, and ALL later history will go into p2 "
|
|
MariaDB [wcase]> select * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema') and TABLE_SCHEMA='wcase' ORDER BY TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_ORDINAL_POSITION\G |
*************************** 1. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p0
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 1 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-12 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:53:36 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 2. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p1
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 2 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-19 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:53:36 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 3. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p2
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 3 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-26 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:53:36 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 4. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: pcur
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 4 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: CURRENT
|
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:53:36 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
4 rows in set (0.002 sec) |
|
|
|
so if all later history will go to p2 the metadata of p2 should report that correctly, which it does not, actually it even remain the same if we add a new partition.
looking at medata report of p2:
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 2022-08-26 00:00:00
which isnt true, because as the documentation report, all remain historical data will end up to p2 if not other partition will add.
If we add even a new partition:
|
|
MariaDB [wcase]> alter table t add partition(partition p3 HISTORY );
|
Query OK, 0 rows affected (0.012 sec) |
Records: 0 Duplicates: 0 Warnings: 0 |
|
|
|
|
MariaDB [wcase]> show create table t \G
|
*************************** 1. row *************************** |
Table: t
|
Create Table: CREATE TABLE `t` (
|
`x` int(11) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
|
PARTITION BY SYSTEM_TIME INTERVAL 7 DAY STARTS TIMESTAMP'2022-08-05 00:00:00' |
(PARTITION `p0` HISTORY ENGINE = InnoDB,
|
PARTITION `p1` HISTORY ENGINE = InnoDB,
|
PARTITION `p2` HISTORY ENGINE = InnoDB,
|
PARTITION `p3` HISTORY ENGINE = InnoDB,
|
PARTITION `pcur` CURRENT ENGINE = InnoDB)
|
1 row in set (0.000 sec) |
|
|
|
|
we should expect to see the metadata definition change for p2 : |
|
|
|
|
|
|
{code:java}
|
|
|
MariaDB [wcase]> select * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema') and TABLE_SCHEMA='wcase' ORDER BY TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_ORDINAL_POSITION\G |
*************************** 1. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p0
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 1 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-12 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:58:03 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 2. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p1
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 2 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-19 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:58:03 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 3. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p2
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 3 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-26 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:58:03 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 4. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: p3
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 4 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-09-02 00:00:00 |
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:58:03 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 5. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: wcase
|
TABLE_NAME: t
|
PARTITION_NAME: pcur
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 5 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: CURRENT
|
TABLE_ROWS: 0 |
AVG_ROW_LENGTH: 0 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:58:03 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
5 rows in set (0.002 sec) |
|
|
|
|
|
so the new metadata definition for p2:
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 2022-08-26 00:00:00
which is exactly the same metadata definition before adding a new partition p3
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 2022-08-26 00:00:00
So if the last historical partition will get all the possible historical data that are not into other partitioning as reported from the documentation , the metadata need to reflect that.
Attachments
Issue Links
- relates to
-
MDEV-21003 Per-partition INTERVAL for history partitions
-
- Open
-