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: | |
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
[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