Bug #115958 Support adding AUTO_INCREMENT flag to existing numeric using INPLACE

• Submitted: 28 Aug 23:05
• Modified: 29 Aug 7:49
• Reporter: Mike Griffin
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[28 Aug 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 7:49] MySQL Verification Team

Hello Mike Griffin,

Thank you for the feature request!!

regards,
Umesh