Bug #102335 Add support for INSTANT algorithm for changing the auto-increment value

• Submitted: 21 Jan 2021 16:57
• Modified: 22 Jan 2021 12:33
• Reporter: Yann Odeyer
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[21 Jan 2021 16:57] Yann Odeyer

Description:
Hello, 

For testing, between each test, you may need to delete all rows and set the auto_increment counter to 0 for a lot of tables. Truncating a table or changing the auto-increment value rebuilds the table, which can be slow on very small tables.
A solution is to add support for INSTANT algorithm for changing the auto-increment value.

Thank you,

How to repeat:
mysql> CREATE TABLE foo (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

* Using "TRUNCATE"
mysql> TRUNCATE TABLE foo;
Query OK, 0 rows affected (0.05 sec)

* Using "DELETE" + "ALTER TABLE"
mysql> DELETE FROM foo; ALTER TABLE foo AUTO_INCREMENT=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
Add support for INSTANT algorithm for changing the auto-increment value (like MariaDB) : 
https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-instant-alter-algorithm/#a....

[22 Jan 2021 12:33] MySQL Verification Team

Hello Yann,

Thank you for the feature request!

regards,
Umesh