MySQL Bugs: #118151: When creating an index: ERROR 1553 (HY000): Cannot drop index '<unknown key name>': needed in a foreign key constraint

Bug #118151	When creating an index: ERROR 1553 (HY000): Cannot drop index '<unknown key name>': needed in a foreign key constraint
Submitted:	9 May 8:35	Modified:	11 Jun 4:45
Reporter:	Catalin BOIE	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.4.5	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86

Description:
Hello!

When I try to create an index, the following error occurs:
ERROR 1553 (HY000): Cannot drop index '<unknown key name>': needed in a foreign key constraint

The command to create an index is:
ALTER TABLE product_price ADD INDEX x (created_at),  ALGORITHM=INPLACE, LOCK=NONE;

The table definition:
CREATE TABLE `product_price` (
  `id` binary(16) NOT NULL,
  `version_id` binary(16) NOT NULL,
  `rule_id` binary(16) NOT NULL,
  `product_id` binary(16) NOT NULL,
  `product_version_id` binary(16) NOT NULL,
  `price` json NOT NULL,
  `quantity_start` int NOT NULL,
  `quantity_end` int DEFAULT NULL,
  `custom_fields` json DEFAULT NULL,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `productPricesPerStation` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`,`version_id`),
  KEY `fk.product_price.product_id` (`product_id`,`product_version_id`),
  KEY `fk.product_price.rule_id` (`rule_id`),
  KEY `i_updated_at` (`updated_at`),
  CONSTRAINT `fk.product_price.product_id` FOREIGN KEY (`product_id`, `product_version_id`) REFERENCES `product` (`id`, `version_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk.product_price.rule_id` FOREIGN KEY (`rule_id`) REFERENCES `rule` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `json.product_price.custom_fields` CHECK (json_valid(`custom_fields`)),
  CONSTRAINT `json.product_price.price` CHECK (json_valid(`price`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I checked and there is no FK referring 'created_at' field.

On 8.4.4 this is not happening.

Thank you!

How to repeat:
Hard to say.

I tried to create a simpler table with no FKs and the index creation works.

Same error if I try to "repair" the table:

optimize table product;
+--------------------------+----------+----------+----------------------------------------------------------------------------+
| Table                    | Op       | Msg_type | Msg_text                                                                   |
+--------------------------+----------+----------+----------------------------------------------------------------------------+
| central_apo_prod.product | optimize | note     | Table does not support optimize, doing recreate + analyze instead          |
| central_apo_prod.product | optimize | error    | Cannot drop index '<unknown key name>': needed in a foreign key constraint |
| central_apo_prod.product | optimize | status   | Operation failed                                                           |
+--------------------------+----------+----------+----------------------------------------------------------------------------+

If I check the table:
check table product extended;
+--------------------------+-------+----------+----------+
| Table                    | Op    | Msg_type | Msg_text |
+--------------------------+-------+----------+----------+
| central_apo_prod.product | check | status   | OK       |
+--------------------------+-------+----------+----------+

Hello Catalin BOIE,

Thank you for the bug report.
To reproduce this issue at our end, could you please provide create table statements for tables which are used in FK constraint in table "product_price"?

Regards,
Ashwini Patil

Hello!

Thank you for your answer!

Here is the information:

CREATE TABLE `product` (
  `id` binary(16) NOT NULL,
  `version_id` binary(16) NOT NULL,
  `auto_increment` int NOT NULL AUTO_INCREMENT,
  `product_number` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active` tinyint unsigned DEFAULT NULL,
  `parent_id` binary(16) DEFAULT NULL,
  `parent_version_id` binary(16) DEFAULT NULL,
  `tax_id` binary(16) DEFAULT NULL,
  `product_manufacturer_id` binary(16) DEFAULT NULL,
  `product_manufacturer_version_id` binary(16) DEFAULT NULL,
  `delivery_time_id` binary(16) DEFAULT NULL,
  `deliveryTime` binary(16) DEFAULT NULL,
  `product_media_id` binary(16) DEFAULT NULL,
  `product_media_version_id` binary(16) DEFAULT NULL,
  `cms_page_id` binary(16) DEFAULT NULL,
  `cms_page_version_id` binary(16) NOT NULL DEFAULT 0x0FA91CE3E96A4BC2BE4BD9CE752C3425,
  `unit_id` binary(16) DEFAULT NULL,
  `product_feature_set_id` binary(16) DEFAULT NULL,
  `category_tree` json DEFAULT NULL,
  `category_ids` json DEFAULT NULL,
  `stream_ids` json DEFAULT NULL,
  `option_ids` json DEFAULT NULL,
  `property_ids` json DEFAULT NULL,
  `tax` binary(16) DEFAULT NULL,
  `manufacturer` binary(16) DEFAULT NULL,
  `cover` binary(16) DEFAULT NULL,
  `unit` binary(16) DEFAULT NULL,
  `media` binary(16) DEFAULT NULL,
  `prices` binary(16) DEFAULT NULL,
  `visibilities` binary(16) DEFAULT NULL,
  `properties` binary(16) DEFAULT NULL,
  `categories` binary(16) DEFAULT NULL,
  `translations` binary(16) DEFAULT NULL,
  `price` json DEFAULT NULL,
  `manufacturer_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ean` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sales` int NOT NULL DEFAULT '0',
  `stock` int NOT NULL,
  `available_stock` int DEFAULT NULL,
  `available` tinyint(1) NOT NULL DEFAULT '0',
  `restock_time` int DEFAULT NULL,
  `is_closeout` tinyint(1) DEFAULT NULL,
  `purchase_steps` int unsigned DEFAULT NULL,
  `max_purchase` int unsigned DEFAULT NULL,
  `min_purchase` int unsigned DEFAULT NULL,
  `purchase_unit` decimal(11,4) unsigned DEFAULT NULL,
  `reference_unit` decimal(10,3) unsigned DEFAULT NULL,
  `shipping_free` tinyint(1) DEFAULT NULL,
  `purchase_prices` json DEFAULT NULL,
  `mark_as_topseller` tinyint unsigned DEFAULT NULL,
  `weight` decimal(10,3) unsigned DEFAULT NULL,
  `width` decimal(10,3) unsigned DEFAULT NULL,
  `height` decimal(10,3) unsigned DEFAULT NULL,
  `length` decimal(10,3) unsigned DEFAULT NULL,
  `release_date` datetime(3) DEFAULT NULL,
  `tag_ids` json DEFAULT NULL,
  `tags` binary(16) DEFAULT NULL,
  `variant_restrictions` json DEFAULT NULL,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `rating_average` float DEFAULT NULL,
  `display_group` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `child_count` int DEFAULT NULL,
  `crossSellings` binary(16) DEFAULT NULL,
  `featureSet` binary(16) DEFAULT NULL,
  `customFieldSets` binary(16) DEFAULT NULL,
  `custom_field_set_selection_active` tinyint(1) DEFAULT NULL,
  `canonical_product_id` binary(16) DEFAULT NULL,
  `canonical_product_version_id` binary(16) DEFAULT NULL,
  `canonicalProduct` binary(16) DEFAULT NULL,
  `cmsPage` binary(16) DEFAULT NULL,
  `cheapest_price` longtext COLLATE utf8mb4_unicode_ci,
  `cheapest_price_accessor` longtext COLLATE utf8mb4_unicode_ci,
  `states` json DEFAULT NULL,
  `variant_listing_config` json DEFAULT NULL,
  `customPrice` binary(16) DEFAULT NULL,
  `subscriptionPlans` binary(16) DEFAULT NULL,
  `productEpharmacyInfo` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`,`version_id`),
  UNIQUE KEY `auto_increment` (`auto_increment`),
  UNIQUE KEY `uniq.product.product_number__version_id` (`product_number`,`version_id`),
  KEY `fk.product.tax_id` (`tax_id`),
  KEY `fk.product.unit_id` (`unit_id`),
  KEY `fk.product.parent_id` (`parent_id`,`parent_version_id`),
  KEY `fk.product.product_media_id` (`product_media_id`,`product_media_version_id`),
  KEY `fk.product.feature_set_id` (`product_feature_set_id`),
  KEY `fk.product.cms_page_id` (`cms_page_id`,`cms_page_version_id`),
  KEY `product_manufacturer_id` (`product_manufacturer_id`,`product_manufacturer_version_id`),
  KEY `fk.product.canonical_product_id` (`canonical_product_id`,`canonical_product_version_id`),
  KEY `i_updated_at` (`updated_at`),
  KEY `i_categories` (`categories`),
  CONSTRAINT `fk.product.canonical_product_id` FOREIGN KEY (`canonical_product_id`, `canonical_product_version_id`) REFERENCES `product` (`id`, `version_id`) ON DELETE SET NULL,
  CONSTRAINT `fk.product.cms_page_id` FOREIGN KEY (`cms_page_id`, `cms_page_version_id`) REFERENCES `cms_page` (`id`, `version_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk.product.feature_set_id` FOREIGN KEY (`product_feature_set_id`) REFERENCES `product_feature_set` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk.product.parent_id` FOREIGN KEY (`parent_id`, `parent_version_id`) REFERENCES `product` (`id`, `version_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk.product.tax_id` FOREIGN KEY (`tax_id`) REFERENCES `tax` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk.product.unit_id` FOREIGN KEY (`unit_id`) REFERENCES `unit` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`product_manufacturer_id`, `product_manufacturer_version_id`) REFERENCES `product_manufacturer` (`id`, `version_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `json.product.category_tree` CHECK (json_valid(`category_tree`)),
  CONSTRAINT `json.product.option_ids` CHECK (json_valid(`option_ids`)),
  CONSTRAINT `json.product.price` CHECK (json_valid(`price`)),
  CONSTRAINT `json.product.property_ids` CHECK (json_valid(`property_ids`)),
  CONSTRAINT `json.product.states` CHECK (json_valid(`states`)),
  CONSTRAINT `json.product.tag_ids` CHECK (json_valid(`tag_ids`)),
  CONSTRAINT `json.product.variant_restrictions` CHECK (json_valid(`variant_restrictions`))
) ENGINE=InnoDB AUTO_INCREMENT=731075 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rule` (
  `id` binary(16) NOT NULL,
  `name` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `priority` int NOT NULL,
  `payload` longblob,
  `invalid` tinyint(1) NOT NULL DEFAULT '0',
  `areas` json DEFAULT NULL,
  `module_types` json DEFAULT NULL,
  `custom_fields` json DEFAULT NULL,
  `created_at` datetime(3) NOT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `json.rule.custom_fields` CHECK (json_valid(`custom_fields`)),
  CONSTRAINT `json.rule.module_types` CHECK (json_valid(`module_types`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Thank you!

P.S. Please note that there is a GTID replication in place, I do not know if this is relevant.
P.P.S The index creation worked on 8.4.4.

Hello Catalin BOIE,

Thank you for the details.
In the table "Product", there are Foreign Keys referenced to many other tables. I skipped those FK and I don't see any issues creating the index. If you can provide more info feel free to add to this bug report. Thanks.

Regards,
Ashwini Patil

mysql version is 8.4.4
we had recently upgraded from 8.0.35--8.0.41--8.4.4

I tried to run the ALTER statement by setting the foreign key constarints to OFF. but still received the same error.

Same structure table in another database with similar references, we are able to drop the column successfully.

Hello!

What I can add is that we did some index creation, sometimes on the same column (a duplicate index) and some drops of the indexes.

I would start the investigation from the strange error message: "Cannot drop index '<unknown key name>'". Why it cannot list which index is involved?
Why it needs to drop an index to create another index?

Please note that this is a serious issue because we cannot create indexes anymore, trashing the performance of the queries.

Thank you!

Hi,

I'm experiencing the same issue when attempting to add a new column to a table after migrating from MySQL 8.0.35 to 8.4.4.

- MySQL version: 8.4.4 (migrated from 8.0.35)
- Operation: ALTER TABLE ADD COLUMN (with or without AFTER clause)
- Error: ERROR 1553 (HY000): Cannot drop index '<unknown key name>': needed in a foreign key constraint
- Table contains multiple indexes (including composite unique keys)
- The error occurs even with FOREIGN_KEY_CHECKS=0

Has anyone found a reliable solution or workaround for this?
Any insights would be greatly appreciated.

Thanks!

I've been able to reproduce this behavior on a new 8.4.5 install, it seems these bugs are related to MySQL 8.4's restrict_fk_on_non_standard_key=ON default setting, which breaks the find_fk_parent_key() function's ability to identify foreign keys on non-unique columns. This causes the function to return nullptr and fall back to the generic "<unknown key name>" error message instead of showing the actual constraint name.

This issue seems to affect any foreign key referencing non-unique indexed columns (both self-referencing and cross-table) and is triggered by ANY ALTER TABLE operation—including seemingly unrelated operations like ADD COLUMN. Prob because MySQL's ALTER TABLE process validates all existing foreign key constraints during the operation. When the validation fails to find the parent key due to the new logic, it incorrectly reports the constraint as blocking the operation with an unhelpful placeholder error message.

Based on my limited test, disabling restrict_fk_on_non_standard_key at the session level seems to allow the following, which should probably be the default behavior:
A) Correctly return index names rather than '<unknown key name>' for FK violations
B) Allows unrelated DDL commands to proceed, even when the table has a non-unique FK col reference.

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html (look for restrict_fk_on_non_standard_key )
https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_restrict_fk_on...

===

Repro steps:
-- Run this in MySQL 8.4+ to reproduce the "unknown key name" bug

-- Step 1: Temporarily disable restrict_fk_on_non_standard_key to create the problematic tables
SET session restrict_fk_on_non_standard_key = OFF;

-- Step 2: Create tables with FK on non-unique column
CREATE DATABASE IF NOT EXISTS `bug_117341`;
USE `bug_117341`;
CREATE TABLE parent_tbl (
    id INT PRIMARY KEY,
    code VARCHAR(10),
    name VARCHAR(50),
    INDEX idx_code (code)  -- Non-unique index
);

CREATE TABLE child_tbl (
    id INT PRIMARY KEY,
    parent_code VARCHAR(10),
    description VARCHAR(100),
    FOREIGN KEY (parent_code) REFERENCES parent_tbl(code)  -- FK to non-unique column
);

-- Step 3: Add test data
INSERT INTO parent_tbl VALUES (1, 'A001', 'Parent A');
INSERT INTO child_tbl VALUES (1, 'A001', 'Child of A');

-- Step 4: Enable restrict_fk_on_non_standard_key (MySQL 8.4 default)
SET restrict_fk_on_non_standard_key = ON;

-- Step 5: Trigger the bug - try ANY ALTER TABLE operation, not just ones on FK related obj
-- This should show: "Cannot drop index '<unknown key name>'"
ALTER TABLE parent_tbl ADD COLUMN extra INT;

-- Step 6: Compare with restrict_fk_on_non_standard_key disabled
SET restrict_fk_on_non_standard_key = OFF;

-- This should show: "Cannot drop index 'idx_code'" (proper error/idx name)
ALTER TABLE parent_tbl DROP INDEX idx_code;

-- This should work now that check is disabled
ALTER TABLE parent_tbl ADD COLUMN extra INT;

-- Confirm addition of column
SHOW CREATE TABLE parent_tbl;
--        Table: parent_tbl
-- Create Table: CREATE TABLE `parent_tbl` (
--   `id` int NOT NULL,
--   `code` varchar(10) DEFAULT NULL,
--   `name` varchar(50) DEFAULT NULL,
--   `extra` int DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   KEY `idx_code` (`code`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 1 row in set (0.00 sec)

Thank you, Catalin, Mark.
Verified as described.

regards,
Umesh