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

Online ALTER adding auto-increment column to a table with history behaves differently from non-online




      In existing MariaDB versions adding an auto-increment column to a system-versioned table which already has history is prohibited:

      MariaDB [test]> create or replace table t (a int) with system versioning;
      Query OK, 0 rows affected (0.054 sec)
      MariaDB [test]> insert into t values (1);
      Query OK, 1 row affected (0.013 sec)
      MariaDB [test]> update t set a = 2;
      Query OK, 1 row affected (0.006 sec)
      Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0
      MariaDB [test]> set system_versioning_alter_history= KEEP;
      Query OK, 0 rows affected (0.000 sec)
      MariaDB [test]> alter table t add b int auto_increment, add key(b);
      ERROR 1112 (42000): Table 'test/t' uses an extension that doesn't exist in this MariaDB version

      However, if UPDATE creating the history is performed in parallel with online ALTER, the result is different – the auto-increment column is created and populated with generated values for current rows, and with zeros for historical rows. Thus, if it is created with a unique key on it, ALTER fails with duplicate key error, and if it the key is non-unique, ALTER succeeds, both of which is different from what happens with non-online / non-concurrent ALTER.

      --source include/have_debug_sync.inc
      create table t (a int) with system versioning;
      insert into t values (1),(2);
      set system_versioning_alter_history= KEEP;
      set debug_sync= 'alter_table_online_progress WAIT_FOR go';
        alter table t add pk int auto_increment primary key, algorithm=copy, lock=none;
      --connect (con_dml,localhost,root,,test)
      update t set a = a + 10;
      set debug_sync= 'now signal go';
      --connection default
      # Cleanup
      drop table t;
      set debug_sync= reset;
      --disconnect con_dml

      Result with the test case above as is (primary key):

      bb-11.2-oalter adcf5dfa8d6, unique key

      mysqltest: At line 17: query 'reap' failed with wrong errno ER_DUP_ENTRY (1062): 'Duplicate entry '0' for key 'PRIMARY'', instead of ER_UNSUPPORTED_EXTENSION (1112)...

      Same test case but with non-unique key instead of PK:

      mysqltest: At line 17: query 'reap' succeeded - should have failed with error ER_UNSUPPORTED_EXTENSION (1112)...

      and the resulting table contents is

      select *, row_start, row_end from t for system_time all;
      a	pk	row_start	row_end
      11	1	2023-07-05 17:13:12.545737	2038-01-19 05:14:07.999999
      12	2	2023-07-05 17:13:12.545737	2038-01-19 05:14:07.999999
      1	0	2023-07-05 17:13:12.541333	2023-07-05 17:13:12.545737
      2	0	2023-07-05 17:13:12.541333	2023-07-05 17:13:12.545737


        Issue Links



              nikitamalyavin Nikita Malyavin
              elenst Elena Stepanova
              0 Vote for this issue
              3 Start watching this issue



                Git Integration

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