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.