MySQL Bugs: #95605: “Cannot drop index ... needed in a foreign key constraint” without a foreign key

Bug #95605	“Cannot drop index ... needed in a foreign key constraint” without a foreign key
Submitted:	3 Jun 2019 0:45	Modified:	3 Jun 2019 19:42
Reporter:	Victor Porton	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.26-0ubuntu0.19.04.1	OS:	Ubuntu (19.04)
Assigned to:		CPU Architecture:	x86 (i5)

Description:
When I try to remove a unique index, MySQL Ver 5.7.26-0ubuntu0.19.04.1 says that it is needed in a foreign key constraint:

mysql> ALTER TABLE project_erc20token DROP INDEX unique_token_data;
ERROR 1553 (HY000): Cannot drop index 'unique_token_data': needed in a foreign key constraint
But there is no such foreign key in the DB!

What is the error?

How to repeat:
1. Load the above attached dump.

2. Run:

ALTER TABLE project_erc20token DROP INDEX unique_token_data;

A database dump

Attachment: dump.sql (application/sql, text), 19.28 KiB.

I mean if it is not a big bug of MySQL, then at least make error message more clear.

Hello Victor Porton,

Thank you for the report.
Imho this is duplicate of Bug#93387, please see Bug#93387(behavior is changed post fix of Bug #70260).

regards,
Umesh

Related - Bug #70260

I think it is not a duplicate Bug#93387.

The issue I raised in this bug is that I cannot remove unique_token_data key even despite there are other unique constraints (unqiue_hack and wallet_id) which are enough for foreign keys to be accessed through a unique constraint (not a full table scan) even after removal of unique_token_data. But the docs claim that such an error appears when a full table scan would be required due to the absence of unique constraints.

So, in my opinion, it is a bug of MySQL!

The bug seems even more serious:

When I try to migrate this DB to MySQL 8.0.16, I get the following error:

2019-06-03T19:16:04.984588Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-06-03T19:16:09.631688Z 2 [ERROR] [MY-012069] [InnoDB] table: product_product has 11 columns but InnoDB dictionary has 12 columns
2019-06-03T19:16:09.631748Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for test_turingly.product_product
2019-06-03T19:16:11.050650Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2019-06-03T19:16:11.050946Z 0 [ERROR] [MY-010119] [Server] Aborting

Please reopen the bug.

Sorry, it was a migration error in ANOTHER database.

Hard to check it for this particular dump file. Somebody do it, it is a big bug.

I have encountered a similar failure when migrating scripts that have been running for a year in 5.x to 8.x. An index on 3 fields not referred to by any foreign keys fails to be dropped for the same reason as described. This occurred after recreating the primary key.moving the statement to between dropping the pk and re-creating it allo works, although my scripts are now non functional.