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

[15 Apr 2016 12:21] Stijn Vermeeren
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;
[15 Apr 2016 16:27] Sinisa Milivojevic
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.
[18 Apr 2016 6:59] Stijn Vermeeren
Hi Sinisa,
Thanks for looking at my bug report.
Yes, I get the same output as you.
Best,
Stijn
[18 Apr 2016 13:59] Sinisa Milivojevic
Thank you for your confirmation, Mr. Vermeeren. This is a low priority bug, but still a bug !!!

Fully verified.