MySQL Bugs: #117341: Cannot drop index '<unknown key name>' when altering table in any way

Bug #117341	Cannot drop index '<unknown key name>' when altering table in any way
Submitted:	30 Jan 14:16	Modified:	31 Jan 13:30
Reporter:	Tarvo R	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	>=8.4	OS:	MacOS
Assigned to:		CPU Architecture:	ARM

Description:
A database which was created in MySQL 7.X and has been upgraded through all versions to 9.0 has managed to lock one table which now can not be altered in any way.

All altering queries and key create/drop queries fail with:
SQLSTATE[HY000]: General error: 1553 Cannot drop index '<unknown key name>': needed in a foreign key constraint

For example for this query:
alter table `orders` add `what_the_hell` varchar(255) not null

It makes absolutely no sense and seems to be an obvious issue with MySQL. I could only find one source where a user encounters the same <unknown key name> error online.

How to repeat:
Unsure, I might have to send the whole database. Maybe the issue is discoverable through backtracking where '<unknown key name>' comes from.

Hi Mr. Tarvo,

Thank you for your bug report.

Let us inform you how what is this forum about. This is a forum for the fully repeatable test cases. Each of the test cases should consist of the set of SQL statements leading to the error reporting. In your case, we would need to follow the entire path of upgrade, which you have not described at all !!!

Next, there was never a MySQL version 7, hence, we do not know what you are writing about.

Last, but not least, you can not drop a column with index, involved in foreign key relationship.

We are waiting for the fully repeatable test case.

Can't repeat.

Mr. Tarvo,

Is this problem reproducible ONLY on macOS with ARM CPU ???

Hi!

I am unsure since I have not had the chance to test it on other operating systems, MacOS on ARM is just the system I am experiencing the issue on.

Thanks!

Hi,

Please answer all other questions that we asked and provide a fully repeatable test case. Please, do not start with MySQL version 7 , since it has never existed.

We have no problems with ARM Mac, since everyone of us has one .....

Sorry, I meant 6.X.

Unfortunately, I am unable to send a reproducible test case without sending the whole data file. A freshly re-imported database (with and without data) does not exhibit the same issue. My wild guess is some internal migration caused it when upgrading from versions 8.3 to 9.0, but I don't feel like installing all these versions again.

Reverting to 8.3 fixes my issue.

I'll just stick to 8.3. You can close this. Thanks.

Hi Mr. Tarvo ......

Thanks ..... Closed.