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
-
Activity
as explained in the other comment, PARTITION_DESCRIPTION column does not show the max value of all timestamps in the partition. It shows the upper boundary after which the server will try to switch to another history partition, if there is any
midenok what is incomplete in the case we report?
The metadata reflect the wrong definition, there is use case and the jira got close.
julien.fritsch could you please reopen that and make sure it get fix?