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

ADD SYSTEM VERSIONING and modify column WITHOUT SYSTEM VERSIONING in single ALTER TABLE command

Details

    Description

      It does not currently seem possible to use a single ALTER TABLE statement to add system versioning to a table and exclude columns from system versioning. For example, try running the following statements:

      CREATE TABLE accounts_exclude_col (
      		 
         id SERIAL PRIMARY KEY,
      		 
         name VARCHAR(255),
      		 
         amount INT,
      		 
         comment VARCHAR(1000)
      		 
      );
      		 
       
      		 
      ALTER TABLE accounts_exclude_col
      		 
         ADD SYSTEM VERSIONING,
      		 
         MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING;
      		 
       
      		 
      ALTER TABLE accounts_exclude_col
      		 
         ADD SYSTEM VERSIONING,
      		 
         MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
      		 
         ALGORITHM=COPY;

      This produces the following output:

      MariaDB [test]> CREATE TABLE accounts_exclude_col (
      		 
          ->    id SERIAL PRIMARY KEY,
      		 
          ->    name VARCHAR(255),
      		 
          ->    amount INT,
      		 
          ->    comment VARCHAR(1000)
      		 
          -> );
      		 
      Query OK, 0 rows affected (0.019 sec)
      		 
       
      		 
      MariaDB [test]> ALTER TABLE accounts_exclude_col
      		 
          ->    ADD SYSTEM VERSIONING,
      		 
          ->    MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING;
      		 
      ERROR 4124 (HY000): Table `accounts_exclude_col` is not system-versioned
      		 
      MariaDB [test]> ALTER TABLE accounts_exclude_col
      		 
          ->    ADD SYSTEM VERSIONING,
      		 
          ->    MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
      		 
          ->    ALGORITHM=COPY;
      		 
      ERROR 4124 (HY000): Table `accounts_exclude_col` is not system-versioned

      The workaround is to use multiple ALTER TABLE statements:

      MariaDB [test]> ALTER TABLE accounts_exclude_col
      		 
          ->    ADD SYSTEM VERSIONING;
      		 
      Query OK, 0 rows affected (0.032 sec)              
      Records: 0  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> SET SESSION system_versioning_alter_history=KEEP;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      MariaDB [test]> ALTER TABLE accounts_exclude_col
      		 
          ->    MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING;
      		 
      Query OK, 0 rows affected (0.024 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-12894 System-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-26993 Add system versioning via modify column WITH SYSTEM VERSIONING

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            nikitamalyavin Nikita Malyavin added a comment -

            I remember MDEV-16490 which is close to this

            nikitamalyavin Nikita Malyavin added a comment - I remember MDEV-16490 which is close to this
            nikitamalyavin Nikita Malyavin added a comment -

            midenok However it is possible to add a key:

            ALTER TABLE accounts_exclude_col
            		 
              CHANGE name name2 VARCHAR(255),
            		 
              add KEY(name2);

            another example:

            create table t (
            		 
              x int,
            		 
              y int,
            		 
              x2 int as (x)
            		 
            );
            		 
             
            		 
            alter table t
            		 
              change y z int,
            		 
              change x2 z2 int as (z);

            We can refer to the changes from alter table.

            I think it is affordable to refer to a new versioning state of the table in CHANGE clause as well.

            nikitamalyavin Nikita Malyavin added a comment - midenok However it is possible to add a key: ALTER TABLE accounts_exclude_col CHANGE name name2 VARCHAR (255), add KEY (name2); another example: create table t ( x int , y int , x2 int as (x) );   alter table t change y z int , change x2 z2 int as (z); We can refer to the changes from alter table. I think it is affordable to refer to a new versioning state of the table in CHANGE clause as well.
            midenok Aleksey Midenkov added a comment - - edited

            That didn't work by design. As a new feature would be nice to have it. Should be implemented with MDEV-26993.

            midenok Aleksey Midenkov added a comment - - edited That didn't work by design. As a new feature would be nice to have it. Should be implemented with MDEV-26993 .

            People

              midenok Aleksey Midenkov
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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