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

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.