Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
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'; |
--send
|
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
|
--error ER_UNSUPPORTED_EXTENSION
|
--reap
|
|
|
# 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
|
Attachments
Issue Links
- is caused by
-
MDEV-16329 Engine-independent online ALTER TABLE
-
- Closed
-