Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15951

system versioning by trx id doesn't work with partitioning

Details

    Description

      Creating a system-versioned table with row version by trx id works as described in the KB. However when I add partitions to separate current and historic data, I get an error.

      The CREATE TABLE statement:

      CREATE TABLE sbtest1 (
      		 
      id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
      k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
      		 
      c CHAR(120) DEFAULT '' NOT NULL,
      		 
      pad CHAR(60) DEFAULT '' NOT NULL,
      		 
      start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
      		 
      end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
      		 
      PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),
      		 
      PRIMARY KEY (id),
      		 
      INDEX k_ (k)
      		 
      ) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME (
      		 
        PARTITION p_hist HISTORY,
      		 
        PARTITION p_cur CURRENT
      		 
      )

      and the error message:

      ERROR 4110 (HY000): `start_trxid` must be of type TIMESTAMP(6) 
      for system-versioned table `sbtest1`

      If partitioning isn't supposed to work with trx ids, this should be documented.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23773 Cannot store the history separately for the transaction precise history tables.

          • Major - Major loss of function.
          • Open
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20271 Partitioned versioned tables do not accept Trx IDs

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-15690 benchmark the overhead caused by system versioning

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-25173 paritioning not supported on transaction-precise versioned tabled

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            axel Axel Schwenke created issue -
            axel Axel Schwenke made changes -
            Field Original Value New Value
            axel Axel Schwenke made changes -
            Assignee Sergei Golubchik [ serg ]
            elenst Elena Stepanova added a comment -

            What's worse, it appears that PARTITION BY RANGE also doesn't work.

            elenst Elena Stepanova added a comment - What's worse, it appears that PARTITION BY RANGE also doesn't work.
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Krizhanovsky [ krizhanovsky ]
            serg Sergei Golubchik added a comment -

            That's because "partition" storage engine doesn't say that it supports trxid-based versioning.
            I think it should support it at least for PARTITION BY RANGE and other not BY SYSTEM_TIME partitioning.

            serg Sergei Golubchik added a comment - That's because "partition" storage engine doesn't say that it supports trxid-based versioning. I think it should support it at least for PARTITION BY RANGE and other not BY SYSTEM_TIME partitioning.
            krizhanovsky Alexander Krizhanovsky made changes -
            Assignee Alexander Krizhanovsky [ krizhanovsky ] Eugene Kosov [ kevg ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Eugene Kosov [ kevg ] Nikita Malyavin [ nikitamalyavin ]
            nikitamalyavin Nikita Malyavin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            nikitamalyavin Nikita Malyavin made changes -
            Description Creating a system-versioned table with row version by trx id works as described in the KB. However when I add partitions to separate current and historic data, I get an error.

            The CREATE TABLE statement:
            {noformat}
            CREATE TABLE sbtest1 (
            id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
            k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
            c CHAR(120) DEFAULT '' NOT NULL,
            pad CHAR(60) DEFAULT '' NOT NULL,
            start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
            end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
            PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),
            PRIMARY KEY (id),
            INDEX k_ (k)
            ) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME (
              PARTITION p_hist HISTORY,
              PARTITION p_cur CURRENT
            )
            {noformat}

            and the error message:
            {noformat}
            ERROR 4110 (HY000): `start_trxid` must be of type TIMESTAMP(6)
            for system-versioned table `sbtest1`
            {noformat}

            If partitioning isn't supposed to work with trx ids, this should be documented.             		Creating a system-versioned table with row version by trx id works as described in the KB. However when I add partitions to separate current and historic data, I get an error.

            The CREATE TABLE statement:
            {code:sql}
            CREATE TABLE sbtest1 (
            id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
            k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
            c CHAR(120) DEFAULT '' NOT NULL,
            pad CHAR(60) DEFAULT '' NOT NULL,
            start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
            end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
            PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),
            PRIMARY KEY (id),
            INDEX k_ (k)
            ) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME (
              PARTITION p_hist HISTORY,
              PARTITION p_cur CURRENT
            )
            {code}

            and the error message:
            {noformat}
            ERROR 4110 (HY000): `start_trxid` must be of type TIMESTAMP(6)
            for system-versioned table `sbtest1`
            {noformat}

            If partitioning isn't supposed to work with trx ids, this should be documented.
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Sergei Golubchik [ serg ] Nikita Malyavin [ nikitamalyavin ]
            nikitamalyavin Nikita Malyavin made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            nikitamalyavin Nikita Malyavin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            nikitamalyavin Nikita Malyavin made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            nikitamalyavin Nikita Malyavin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Nikita Malyavin [ nikitamalyavin ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            nikitamalyavin Nikita Malyavin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Nikita Malyavin [ nikitamalyavin ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            nikitamalyavin Nikita Malyavin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            nikitamalyavin Nikita Malyavin made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3.14 [ 23216 ]
            Fix Version/s 10.4.4 [ 23310 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            DanPoult Daniel Poulton made changes -
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 86685 ] MariaDB v4 [ 154202 ]

            People

              serg Sergei Golubchik
              axel Axel Schwenke
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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