[MDEV-29279] set timestamp leads to wrong history copy when adding new history partition Created: 2022-08-05  Updated: 2023-08-20

Status: Open
Project: MariaDB Server
Component/s: Partitioning, Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Massimo Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 2
Labels: None
Environment:

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. )

 Comments   
Comment by Aleksey Midenkov [ 2023-07-19 ]

serg Please validate the problem and suggest solution.

Generated at Thu Feb 08 10:07:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.