MySQL Bugs: #81097: Index disappears depending on whether it was created by adding a foreign key

Bug #81097	Index disappears depending on whether it was created by adding a foreign key
Submitted:	15 Apr 2016 12:21	Modified:	18 Apr 2016 13:59
Reporter:	Stijn Vermeeren	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In a table with two columns `a` and `b`, there is an index and a foreign key on column `a`.

When I add a composite index on columns (`a` and `b`), the results are not always the same:
1) When the index on column `a` was created explicitly using ADD INDEX before the foreign key was added, the index on column `a` remains there after adding the new index on (`a`, `b`).
2) When the index on column `a` was implicitly created when adding the foreign key on column `a`, this index disappears and only the new index on (`a`, `b`) remains...

There does not even seem to be a way to inspect the table and predict what the result of adding the index on (`a`, `b`) will be (without knowledge of the history of how the table was created).

How to repeat:
CREATE TABLE `ref` (
  `id` int(11) NOT NULL PRIMARY KEY
);

CREATE TABLE `test1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL
);
ALTER TABLE `test1` ADD INDEX `a` (`a`);
ALTER TABLE `test1`ADD FOREIGN KEY (`a`) REFERENCES `ref` (`id`);

CREATE TABLE `test2` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL
);
ALTER TABLE `test2`ADD FOREIGN KEY (`a`) REFERENCES `ref` (`id`);

SHOW CREATE TABLE test1;
SHOW CREATE TABLE test2;

ALTER TABLE `test1` ADD INDEX `a_b` (`a`, `b`);
ALTER TABLE `test2` ADD INDEX `a_b` (`a`, `b`);

SHOW CREATE TABLE test1;
SHOW CREATE TABLE test2;

Hi Mr. Vermeeren,

This is the output that I see. Is it the same as yours ???? If yes, then this definitely looks like a bug:

Table   Create Table
test1   CREATE TABLE `test1` (\n  `a` int(11) NOT NULL,\n  `b` int(11) NOT NULL,\n  KEY `a` (`a`),\n  CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `ref` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table   Create Table
test2   CREATE TABLE `test2` (\n  `a` int(11) NOT NULL,\n  `b` int(11) NOT NULL,\n  KEY `a` (`a`),\n  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `ref` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table   Create Table
test1   CREATE TABLE `test1` (\n  `a` int(11) NOT NULL,\n  `b` int(11) NOT NULL,\n  KEY `a` (`a`),\n  KEY `a_b` (`a`,`b`),\n  CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `ref` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table   Create Table
test2   CREATE TABLE `test2` (\n  `a` int(11) NOT NULL,\n  `b` int(11) NOT NULL,\n  KEY `a_b` (`a`,`b`),\n  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `ref` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

Thanks in advance.

Hi Sinisa,
Thanks for looking at my bug report.
Yes, I get the same output as you.
Best,
Stijn

Thank you for your confirmation, Mr. Vermeeren. This is a low priority bug, but still a bug !!!

Fully verified.

Still affects MySQL 5.7.27.

This bug really should be addressed. It's actually very serious. It makes migration scripts unpredictable - they may work on some installations but fail on others because the indexes don't match.

There appears to be no way to tell if any given key is implicit or explicit - and this can change unpredictably! Recreating the table using the output of `SHOW CREATE TABLE` will cause all implicit keys to become explicit keys. This means that restoring the database from a mysqldump backup will restore with explicit keys.

There are five possible fixes to this:

1. Never auto-create keys
2. Auto-create keys but never auto-delete
3. Auto-delete based on the name, not on some hidden flag. For example, only auto-delete keys if they exactly match the name of a foreign key which is being removed.
4. Hide implicit keys so they don't appear in the output of `SHOW CREATE TABLE`
5. Add a flag to the `CREATE/ALTER TABLE` syntax to allow implicit keys to be created, and include this flag in the output of `SHOW CREATE TABLE`. For example: `ADD INDEX fk_foobar (foobar) IMPLICIT`.

Of these options, (3) seems very dangerous and (5) looks like a lot of work for something that could be fixed more simply. I would suggest option (2), it's by the far simplest and just makes everything predictable.

We can confirm this bug still affects MySQL 8.0.37, and it is causing real problems in our production migration workflow.

We use automated schema migrations across multiple environments (local, test, staging, production). This bug causes non-deterministic behavior depending on whether a foreign key's supporting index was created explicitly or implicitly. If the FK index is implicit, adding a new composite index with the FK column as a prefix causes the original index to be silently dropped. If the FK index was explicit, both indexes remain. This makes the same migration succeed in one environment and fail in another with:

ERROR 1553 (HY000): Cannot drop index 'a_b': needed in a foreign key constraint

This unpredictability makes our migrations fragile and sometimes causes production rollbacks. There is no way to detect whether an index is implicit before running the migration, so we cannot write universally safe scripts.

Please prioritize a fix. The best solution would be to stop auto-deleting implicit indexes, or at least make their status visible in the metadata or controllable with a server variable. This issue breaks safe, repeatable migrations in modern CI/CD pipelines and is a serious operational risk.

It’s honestly surprising that this issue was verified back in 2016 and is still unresolved in 2025, especially given that it affects MySQL 8.0.37 — a recent GA version.

This doesn’t just look like a bug; it seems like a fundamentally poor design decision. Silently dropping foreign key support indexes behind the scenes — depending on whether they were created explicitly or implicitly — breaks the assumption of predictable, declarative schema management. The fact that it’s completely undetectable in metadata makes it even worse.

For a bug that was acknowledged 9 years ago and confirmed by the MySQL team, it’s concerning that there has been no progress or clear roadmap for addressing it. With modern infrastructure increasingly relying on CI/CD and reproducible database state, this behavior undermines trust in the schema layer.

Please consider raising the priority of this issue. It affects production stability and makes safe database migrations unnecessarily risky.

Just a quick follow-up:
Since we've confirmed this issue still affects MySQL 8.0.37, could you please update the "Affects Version/s" field accordingly? This would ensure the current impact is accurately reflected. Thank you.