Bug #109489 MySQL should not implicitly change primary key
Submitted: 27 Dec 2022 12:47 Modified: 28 Dec 2022 3:58
Reporter: Iwo P Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.31, 5.7 OS:Any
Assigned to: 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