Bug #115958 Support adding AUTO_INCREMENT flag to existing numeric using INPLACE
Submitted: 28 Aug 2024 23:05 Modified: 29 Aug 2024 7:49
Reporter: Mike Griffin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[28 Aug 2024 23:05] Mike Griffin 
Description:
Currently, a table copy is required to toggle AUTO_INCREMENT on or off:

Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

How to repeat:
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (`id` bigint unsigned NOT NULL, PRIMARY KEY (`id`));
INSERT INTO `t` (id) VALUES (1), (100), (1000);
INSERT INTO `t` (id) VALUES (300);
SELECT `id` FROM `t`;
ALTER TABLE `t` MODIFY `id` bigint unsigned NOT NULL AUTO_INCREMENT, ALGORITHM=INPLACE;

Suggested fix:
Allow this change INPLACE, which could then potentially be supported by util.loadDump({deferTableIndexes}).

Testing after util.dumpInstance(), for a few million rows, I see:

* Using mysqld option innodb_autoinc_lock_mode=2 is around 5% faster than default import

* Without AUTO_INCREMENT is about 10% faster than original import
[29 Aug 2024 7:49] MySQL Verification Team 
Hello Mike Griffin,

Thank you for the feature request!!

regards,
Umesh