[MDEV-29279] set timestamp leads to wrong history copy when adding new history partition - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Partitioning, Versioned Tables
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. )

Attachments

Activity

Aleksey Midenkov added a comment - 2023-07-19 17:29

serg Please validate the problem and suggest solution.

Aleksey Midenkov added a comment - 2023-07-19 17:29 serg Please validate the problem and suggest solution.

People

Assignee:: Sergei Golubchik

Reporter:: Massimo

Votes:: 2 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2022-08-05 09:42

Updated:: 2023-08-20 18:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server