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

Support ALTER TABLE ... ADD/DROP COLUMN for system versioned tables

    XMLWordPrintable

Details

    Description

      The manual warns that altering system-versioned tables is not supported, as we can read here:
      https://mariadb.com/kb/en/library/system-versioned-tables/#system_versioning_alter_history


      Description: SQL:2011 does not allow ALTER TABLE on system-versioned tables. When this variable is set to ERROR, an attempt to alter a system-versioned table will result in an error. When this variable is set to KEEP, ALTER TABLE will be allowed, but the history will become incorrect — querying historical data will show the new table structure. This mode is still useful, for example, when adding new columns to a table

      It would be useful to support adding or dropping columns without breaking the history.

      There is one case where the Master has a table T1 which is verioned on the Slave. if you add a column on the Master the replication either breaks or the history on the Slave's table becomes unpredictable, depending on the value of `system_versioning_alter_history`.
      With this feature request I want to check the possibility of fully supporting relatively unharmful DDLs for system versioned tables.
      To do that one key element is to always keep the hidden timestamp field of system versioned table as last in the row.
      There are several challenges to implement this, for example the need of keeping one version of each table if the structure changes, or how to flag/hide columns that don't exist at a certain point in time.

      Attachments

        Activity

          People

            midenok Aleksey Midenkov
            claudio.nanni Claudio Nanni
            Votes:
            1 Vote for this issue
            Watchers:
            4 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.