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

    XMLWordPrintable

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

            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.