How to add an Auto Increment Primary Key to a MariaDB System Versioned Aria table

Question

I've made a bit of a mistake and I cannot correct it. I have a table Email and I've used the email address as the Primary Key. This is a mistake and I want to add an Auto Increment ID to the table instead.

However, the table is System Versioned and there is historical data. For this reason, MariaDB won't allow me to add an Auto Increment column to the table.

This demonstrates the problem:

CREATE OR REPLACE TABLE `Email_Test` (
  `email` VARCHAR(100) NOT NULL,
  `modifiedBy` INT(10) UNSIGNED NOT NULL,
  `modifyReason` VARCHAR(200) NULL DEFAULT NULL,
  `Person_idPerson` INT(10) UNSIGNED NULL DEFAULT NULL,
  `Account_idAccount` INT(10) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (email, Account_idAccount),
  INDEX `fk_Email_Person1_idx` (`Person_idPerson` ASC),
  UNIQUE INDEX `UNIQUE` (`email` ASC, `Account_idAccount` ASC)
)
ENGINE = Aria
DEFAULT CHARACTER SET = utf8mb4
  WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION part_history HISTORY,
    PARTITION part_current CURRENT
  );

INSERT INTO Email_Test SET email = 'test@example.com', ModifiedBy = 3, Person_idPerson = 2, Account_idAccount = 1;
UPDATE Email_Test SET email = 'newemail@example.com' WHERE Person_idPerson = 2;

SET @@system_versioning_alter_history = 'keep';

ALTER TABLE `Email_Test`
   DROP PRIMARY KEY,
   ADD COLUMN `idEmail` INT(10) UNSIGNED NOT NULL PRIMARY KEY FIRST;

ALTER TABLE `Email_Test`
   MODIFY COLUMN `idEmail` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

If the UPDATE statement is omitted, it works fine. However, with the UPDATE statement, is generates Error 1112 - "Table Email_Test uses an extension that doesn't exist in this MariaDB version". The error comes from the second ALTER TABLE statement where we add the Auto Increment.

The same error occurs if we try to add the Primary Key and Auto Increment in one atomic operation:

ALTER TABLE `Email_Test`
   DROP PRIMARY KEY,
   ADD COLUMN `idEmail` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

Is there a way of adding an Auto Increment column to an existing system versioned table with historical data?

If there isn't a way, can you suggest a way of moving the existing data, including the historical data, to another table temporarily so that the Email table can be dropped and created properly before copying the data back into it?

Answer 1

I managed to do your final step using sequences like:

MariaDB [test]> create sequence sIdEMail;
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> ALTER TABLE `Email_Test`    MODIFY COLUMN `idEmail` INT(10) UNSIGNED NOT NULL default(NEXTVAL(sIdEMail));
Query OK, 2 rows affected (0.003 sec)              
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from Email_Test;
+---------+----------------------+------------+--------------+-----------------+-------------------+
| idEmail | email                | modifiedBy | modifyReason | Person_idPerson | Account_idAccount |
+---------+----------------------+------------+--------------+-----------------+-------------------+
|       0 | newemail@example.com |          3 | NULL         |               2 |                 1 |
+---------+----------------------+------------+--------------+-----------------+-------------------+

MariaDB [test]> select * from Email_Test for system_time all;
+---------+----------------------+------------+--------------+-----------------+-------------------+
| idEmail | email                | modifiedBy | modifyReason | Person_idPerson | Account_idAccount |
+---------+----------------------+------------+--------------+-----------------+-------------------+
|       0 | test@example.com     |          3 | NULL         |               2 |                 1 |
|       0 | newemail@example.com |          3 | NULL         |               2 |                 1 |
+---------+----------------------+------------+--------------+-----------------+-------------------+

MariaDB [test]> UPDATE Email_Test SET email = 'special@example.com' WHERE Person_idPerson = 2;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

new values get a new sequence number:

MariaDB [test]> insert into Email_Test (email, modifiedBy, Account_idAccount) values ('bob@localhost', 3,4);
Query OK, 1 row affected (0.001 sec)

MariaDB [test]> select * from Email_Test;
+---------+---------------------+------------+--------------+-----------------+-------------------+
| idEmail | email               | modifiedBy | modifyReason | Person_idPerson | Account_idAccount |
+---------+---------------------+------------+--------------+-----------------+-------------------+
|       0 | special@example.com |          3 | NULL         |               2 |                 1 |
|       1 | bob@localhost       |          3 | NULL         |            NULL |                 4 |
+---------+---------------------+------------+--------------+-----------------+-------------------+
2 rows in set (0.001 sec)

Any problems, please report a bug.

Why not auto_increment? There's a very specific check resulting in this in handler::update_auto_increment() in the server code preventing the ALTER TABLE from adding auto_increment fields where on NOT NULL columns. I suspect the mechanism to populate a system versioned history with an auto_increment value just hasn't been implemented. If you really want this, write a bug report (as a "task" aka feature request).