MySQL Bugs: #118072: Optimize DDL operations involving AUTO

Bug #118072	Optimize DDL operations involving AUTO_INCREMENT columns
Submitted:	28 Apr 2025 14:10	Modified:	28 Apr 2025 14:24
Reporter:	alex xing (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Problem:
When an invalid AUTO_INCREMENT column is specified in a CREATE TABLE or ALTER TABLE statement, MySQL currently proceeds with DDL preparations—such as key list traversal, duplicate key checks (dup_check_key), and invisible index validation.

Solution:
Introduce an early check for AUTO_INCREMENT column  in mysql_prepare_create_table() . If invalid， the operation fails immediately, skipping:

Traversal of alter_info->key_list

Redundant dup_check_key verification

Invisible index correctness checks

How to repeat:
just read the code

Suggested fix:
optimize as the below patch

a simple patch to describe the optimization (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql_prepare_create_table_optimize.patch (application/octet-stream, text), 1018 bytes.

Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh

Hello Alex Zing,

Thank you for the contribution, but we can’t take the patch as written. By moving the auto_increment > 0 block into the per-key loop, the server will reject tables that are valid. Cases where the PRIMARY KEY (or other qualifying index) appears later in the key list would start failing.

For example:

CREATE TABLE t (
  id INT AUTO_INCREMENT,
  x  INT,
  KEY(x),
  PRIMARY KEY (id)
);

In the current code, mysql_prepare_create_table() first counts all AUTO_INCREMENT columns, then scans every non-foreign key and calls prepare_key(). Inside prepare_key(), prepare_key_column() decrements the counter as soon as it encounters id in the primary key, so the statement succeeds.

With the proposed change, the auto_increment check runs immediately after KEY (x), before we’ve examined the primary key. So the counter is still 1 and the statement fails with ER_WRONG_AUTO_KEY.

Because the patch introduces errors, we’ll have to reject it. If you’d like to discuss alternative approaches, please let us know. Thank you!