Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3
-
None
-
single instance
Description
Reproduce
--source include/have_partition.inc
|
--source include/have_sequence.inc
|
|
|
set timestamp= unix_timestamp('2000-01-01 00:00:00'); |
create table t1 (x int not null) engine=MyISAM with system versioning |
partition by system_time interval 1 minute ( |
partition p0 history,
|
partition p1 history,
|
partition pn current); |
|
|
insert into t1 select seq from seq_1_to_100; |
|
|
set timestamp= unix_timestamp('2000-01-01 00:00:30'); |
delete from t1 where x = 1; |
|
|
set timestamp= unix_timestamp('2000-01-01 00:01:00'); |
delete from t1 where x = 2; |
|
|
set timestamp= unix_timestamp('2000-01-01 00:01:30'); |
delete from t1 where x = 3; |
|
|
set timestamp= unix_timestamp('2000-01-01 00:02:00'); |
delete from t1 where x = 4; |
|
|
set timestamp= unix_timestamp('2000-01-01 00:02:30'); |
delete from t1 where x = 5; |
|
|
set timestamp= unix_timestamp('2000-01-01 00:03:00'); |
delete from t1 where x = 6; |
|
|
select *, row_start, row_end from t1 partition (p0); |
alter table t1 add partition (partition p2 history); |
|
|
# This causes history not copied to p3 |
set timestamp= 1; |
alter table t1 add partition (partition p3 history); |
select *, row_start, row_end from t1 partition (p1); |
select *, row_start, row_end from t1 partition (p2); |
select *, row_start, row_end from t1 partition (p3); |
|
|
drop table t1; |
Result
Record 6 in p2
select *, row_start, row_end from t1 partition (p2); |
x row_start row_end
|
4 2000-01-01 00:00:00.000000 2000-01-01 00:02:00.000000
|
5 2000-01-01 00:00:00.000000 2000-01-01 00:02:30.000000
|
6 2000-01-01 00:00:00.000000 2000-01-01 00:03:00.000000
|
select *, row_start, row_end from t1 partition (p3); |
x row_start row_end
|
Expected
Record 6 in p3
select *, row_start, row_end from t1 partition (p2); |
x row_start row_end
|
4 2000-01-01 00:00:00.000000 2000-01-01 00:02:00.000000
|
5 2000-01-01 00:00:00.000000 2000-01-01 00:02:30.000000
|
select *, row_start, row_end from t1 partition (p3); |
x row_start row_end
|
6 2000-01-01 00:00:00.000000 2000-01-01 00:03:00.000000
|
Cause
This condition in prep_alter_part_table() adds dependency on query timestamp:
if (hist_part->range_value <= thd->query_start()) |
hist_part->part_state= PART_CHANGED;
|
Original description
With System-Versioned using partitioning is possible to have a violation definition of the data partitioning.
The result is having data in the wrong partition according to the metadata . Obviously the consequence on query just a specific partition or drop a partition will end with inconsistent actions ( not see all data or drop data that want to keep instead) .
This is absolutely critical for customer that using versioning with partitioning and keep drop and add partitioning .
MariaDB [vers]> show create table mycase \G
|
*************************** 1. row *************************** |
Table: mycase
|
Create Table: CREATE TABLE `mycase` (
|
`x` int(11) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
|
PARTITION BY SYSTEM_TIME INTERVAL 1 MINUTE STARTS TIMESTAMP'2022-08-04 11:41:00' |
(PARTITION `p0` HISTORY ENGINE = InnoDB,
|
PARTITION `p1` HISTORY ENGINE = InnoDB,
|
PARTITION `p2` HISTORY ENGINE = InnoDB,
|
PARTITION `p5` HISTORY ENGINE = InnoDB,
|
PARTITION `p6` HISTORY ENGINE = InnoDB,
|
PARTITION `pn` CURRENT ENGINE = InnoDB)
|
1 row in set (0.001 sec) |
MariaDB [vers]> select * from mycase partition (p6);
|
+------+
|
| x |
|
+------+
|
| 99 | |
+------+
|
1 row in set (0.000 sec) |
MariaDB [vers]>
|
MariaDB [vers]>
|
MariaDB [vers]> select * from mycase partition (pn);
|
+------+
|
| x |
|
+------+
|
| 91 | |
| 2 | |
| 3 | |
| 33 | |
+------+
|
4 rows in set (0.002 sec) |
MariaDB [vers]> update mycase set x=777 where x=3; |
Query OK, 1 row affected (0.004 sec) |
Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0 |
MariaDB [vers]> insert into mycase values(22),(33); |
Query OK, 2 rows affected (0.002 sec) |
Records: 2 Duplicates: 0 Warnings: 0 |
MariaDB [vers]> select * from mycase partition (pn);
|
+------+
|
| x |
|
+------+
|
| 91 | |
| 2 | |
| 777 | |
| 33 | |
| 22 | |
| 33 | |
+------+
|
6 rows in set (0.000 sec) |
MariaDB [vers]> select * from mycase partition (p6);
|
+------+
|
| x |
|
+------+
|
| 99 | |
| 3 | |
+------+
|
2 rows in set (0.000 sec) |
MariaDB [vers]> set timestamp= 1; |
Query OK, 0 rows affected (0.000 sec) |
MariaDB [vers]> alter table mycase add partition(partition p7 HISTORY );
|
Query OK, 0 rows affected (0.019 sec) |
Records: 0 Duplicates: 0 Warnings: 0 |
MariaDB [vers]> set timestamp= default; |
Query OK, 0 rows affected (0.000 sec) |
MariaDB [vers]> show create table mycase \G
|
*************************** 1. row *************************** |
Table: mycase
|
Create Table: CREATE TABLE `mycase` (
|
`x` int(11) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
|
PARTITION BY SYSTEM_TIME INTERVAL 1 MINUTE STARTS TIMESTAMP'2022-08-04 11:41:00' |
(PARTITION `p0` HISTORY ENGINE = InnoDB,
|
PARTITION `p1` HISTORY ENGINE = InnoDB,
|
PARTITION `p2` HISTORY ENGINE = InnoDB,
|
PARTITION `p5` HISTORY ENGINE = InnoDB,
|
PARTITION `p6` HISTORY ENGINE = InnoDB,
|
PARTITION `p7` HISTORY ENGINE = InnoDB,
|
PARTITION `pn` CURRENT ENGINE = InnoDB)
|
1 row in set (0.001 sec) |
MariaDB [vers]> select * from mycase partition (p7);
|
Empty set (0.000 sec) |
MariaDB [vers]> select x, ROW_START, ROW_END from mycase partition (p6);
|
+------+----------------------------+----------------------------+
|
| x | ROW_START | ROW_END |
|
+------+----------------------------+----------------------------+
|
| 99 | 2022-08-04 11:42:41.196224 | 2022-08-04 11:54:25.271030 | |
| 3 | 2022-08-04 11:41:54.846287 | 2022-08-05 09:05:42.398729 | |
+------+----------------------------+----------------------------+
|
2 rows in set (0.000 sec) |
MariaDB [vers]> select * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema') and table_name='mycase' ORDER BY TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_ORDINAL_POSITION\G |
*************************** 1. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
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-04 11:42: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:08:10 |
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 2. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
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-04 11:43: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:08:10 |
UPDATE_TIME: 2022-08-04 11:42:41 |
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 3. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
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-04 11:44: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:08:10 |
UPDATE_TIME: 2022-08-04 11:42:41 |
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 4. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
PARTITION_NAME: p5
|
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-08-04 11:45: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:08:10 |
UPDATE_TIME: 2022-08-05 08:30:53 |
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 5. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
PARTITION_NAME: p6
|
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: 2022-08-04 11:46: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:08:10 |
UPDATE_TIME: 2022-08-05 09:05:42 |
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 6. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
PARTITION_NAME: p7
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 6 |
SUBPARTITION_ORDINAL_POSITION: NULL
|
PARTITION_METHOD: SYSTEM_TIME
|
SUBPARTITION_METHOD: NULL
|
PARTITION_EXPRESSION: unix_timestamp(`row_end`)
|
SUBPARTITION_EXPRESSION: NULL
|
PARTITION_DESCRIPTION: 2022-08-04 11:47: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:08:10 |
UPDATE_TIME: 2022-08-05 09:05:42 |
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
*************************** 7. row *************************** |
TABLE_CATALOG: def
|
TABLE_SCHEMA: vers
|
TABLE_NAME: mycase
|
PARTITION_NAME: pn
|
SUBPARTITION_NAME: NULL
|
PARTITION_ORDINAL_POSITION: 7 |
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: 6 |
AVG_ROW_LENGTH: 2730 |
DATA_LENGTH: 16384 |
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: 0 |
DATA_FREE: 0 |
CREATE_TIME: 2022-08-05 09:08:10 |
UPDATE_TIME: 2022-08-05 09:06:08 |
CHECK_TIME: NULL
|
CHECKSUM: NULL
|
PARTITION_COMMENT:
|
NODEGROUP: default |
TABLESPACE_NAME: NULL
|
7 rows in set (0.001 sec) |
according to the definition of metadata for p6:
PARTITION_DESCRIPTION: 2022-08-04 11:46:00
but looking at p6 data it contain data where
ROW_END 2022-08-05 09:05:42.398729
which should be not in parition p6.
now if you drop the p6 expecting to drop where data has PARTITION_DESCRIPTION: 2022-08-04 11:46:00 you will end up to drop a record that you want to keep.
same for the SELECT from p6 the record should not be there.
The operation to violate the partition definition should not be possible ( which is a the base of partitioning login implementation. )