Bug #113507 gipk add column failed

• Submitted: 25 Dec 2023 4:04
• Modified: 25 Dec 2023 10:30
• Reporter: zongyi chen (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 8.0.35
• OS: Any
• CPU Architecture: Any

[25 Dec 2023 4:04] zongyi chen

Description:
drop table if exists t1;
SET SESSION sql_generate_invisible_primary_key = OFF;
CREATE TABLE t1(f2 INT);
ALTER TABLE t1 ADD COLUMN f1 int not null, ADD UNIQUE KEY(f1);
SET SESSION sql_generate_invisible_primary_key = ON;
ALTER TABLE t1 ADD COLUMN f0 INT;

this sql attend to add column to a no_primary key table when sql_generate_invisible_primary_key = ON. but it will report a error:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'

because it has a not null unique column which will be use as primary. and in gipk function `check_primary_key_alter_restrictions` will be true , but Judging whether to report error is using ks->type. so it assume we drop a primary key and without add new one.

How to repeat:
drop table if exists t1;
SET SESSION sql_generate_invisible_primary_key = OFF;
CREATE TABLE t1(f2 INT);
ALTER TABLE t1 ADD COLUMN f1 int not null, ADD UNIQUE KEY(f1);
SET SESSION sql_generate_invisible_primary_key = ON;
ALTER TABLE t1 ADD COLUMN f0 INT;

Suggested fix:
do not use is_missing_primary_key() function to determine has a primary key

[25 Dec 2023 10:30] MySQL Verification Team

Hello zongyi chen,

Thank you for the report and test case.

regards,
Umesh

[15 Jan 12:22] Vinod Parmar

Can Severity of the bug can be increased . We have MySQL on Azure ( Flexible Server ) and more than 100K tables are affected. Its impacting our application release cycles.

[11 Jun 5:42] Vinod Parmar

Do we have any updates on the fix ?