Bug #109489 MySQL should not implicitly change primary key

• Submitted: 27 Dec 2022 12:47
• Modified: 28 Dec 2022 3:58
• Reporter: Iwo P
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.31, 5.7
• OS: Any
• CPU Architecture: Any

[27 Dec 2022 12:47] Iwo P

Description:
-

How to repeat:
01) Create a table with below schema:

CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7

02) Run an alter that changes id column name 

mysql> ALTER TABLE test_table change id old_id int, add column test int not null auto_increment first, AUTO_INCREMENT = 1;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column, and it must be defined as a key

That's expected

03) ALTER TABLE test_table change id old_id int, add column id int not null auto_increment first, AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0,03 sec)
Records: 0  Duplicates: 0  Warnings: 0

That's not expected. It should fail as well, as it looks MySQL does change implicitly the PK for that table from old_id into a newly added column name id.

Suggested fix:
It should not be allowed.

[28 Dec 2022 3:58] MySQL Verification Team

Hello Iwo P,

Thank you for the report and feedback.

regards,
Umesh

[31 Dec 2022 8:50] huahua xu

Hi, all:

In MySQL5.7, both the sql statements is executed failed:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.7.38-debug-log |
+------------------+

mysql> CREATE TABLE `test_table` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `a` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE test_table change id old_id int, add column test int not null auto_increment first, AUTO_INCREMENT = 1;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> ALTER TABLE test_table change id old_id int, add column id int not null auto_increment first, AUTO_INCREMENT = 1;
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

[31 Dec 2022 8:58] huahua xu

For the error `ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys`, it would be a good idea that merging the patch https://github.com/mysql/mysql-server/commit/8afbe2ff3a869ec2bac26a624b22e5ada144b0fb into mysql5.7