MySQL Bugs: #93387: Can not delete post-creatin index if exist fk on this field.

Bug #93387	Can not delete post-creatin index if exist fk on this field.
Submitted:	28 Nov 2018 15:48	Modified:	28 Nov 2018 20:45
Reporter:	Bogdan Stepanenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	drop index

Description:
I expect of independent `index` and `fk` in this case. In create table fk may create another named index and don't use separated index. The index will added later:

How to repeat:
CREATE TABLE `test1`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT ='';
CREATE TABLE `test2`
(
  `id`     int(11) NOT NULL AUTO_INCREMENT,
  `field1` int(11) NOT NULL COMMENT '',
  PRIMARY KEY (`id`),
  CONSTRAINT `another_name` FOREIGN KEY (`field1`) REFERENCES `test1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT =''
;
show create table `test2`;
;
create unique index `index_name` on `test2` (`field1`)
;
show create table `test2`;
;
DROP INDEX `index_name` ON `test2`

[HY000][1553] Cannot drop index 'index_name': needed in a foreign key constraint

For complex index same situation

CREATE TABLE `test1`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT ='';
CREATE TABLE `test2`
(
  `id`     int(11) NOT NULL AUTO_INCREMENT,
  `field1` int(11) NOT NULL COMMENT '',
  `field2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`field1`, `field2`),
  CONSTRAINT `another_name` FOREIGN KEY (`field1`) REFERENCES `test1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT =''
;
DROP INDEX `index_name` ON `test2`
;

Thank you for the bug report. This is a documented behavior:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

foreign_key_checks
<cut>
"
Note

Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency.

Dropping an index required by a foreign key constraint is not permitted, even with foreign_key_checks=0. The foreign key constraint must be removed before dropping the index."

https://bugs.mysql.com/bug.php?id=81595
https://bugs.mysql.com/bug.php?id=70260

Bug #95605 marked as duplicate of this one