Bug #77798 ERROR 1553 (HY000): Cannot drop index needed by foreign constraint
Submitted: 22 Jul 2015 10:56 Modified: 12 Oct 2015 11:16
Reporter: Vladislav Belogrudov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Cluster: NDB API Severity:S2 (Serious)
Version:7.4.6-1.el7.x86_64 OS:Oracle Linux (7.1)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: ndb

[22 Jul 2015 10:56] Vladislav Belogrudov 
Description:
CREATE TABLE `images` (
  `id` varchar(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `size` bigint(20) DEFAULT NULL,
  `status` varchar(30) NOT NULL,
  `is_public` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL,
  `disk_format` varchar(20) DEFAULT NULL,
  `container_format` varchar(20) DEFAULT NULL,
  `checksum` varchar(32) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL,
  `min_disk` int(11) NOT NULL,
  `min_ram` int(11) NOT NULL,
  `protected` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_images_is_public` (`is_public`),
  KEY `ix_images_deleted` (`deleted`)
) ENGINE=ndbcluster;

CREATE TABLE `image_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image_id` varchar(36) NOT NULL,
  `member` varchar(255) NOT NULL,
  `can_share` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL,
  `status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `image_id` (`image_id`,`member`),
  KEY `ix_image_members_image_id` (`image_id`),
  KEY `ix_image_members_deleted` (`deleted`),
  KEY `ix_image_members_image_id_member` (`image_id`,`member`),
  CONSTRAINT `FK_748_757` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster;

alter table image_members drop index `ix_image_members_image_id_member`;

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

Is this MySQL Cluster specific behavior or a bug? Also is unique key `image_id` just an alias to `ix_image_members_image_id_member`? Sometimes while deleting the former I get error about the latter as above - e.g. if I delete image_id key first it is deleted but if I try to delete ix* and then image_id then I get the error.

Thanks!
Vladislav Belogrudov 

How to repeat:
CREATE TABLE `images` (
  `id` varchar(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `size` bigint(20) DEFAULT NULL,
  `status` varchar(30) NOT NULL,
  `is_public` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL,
  `disk_format` varchar(20) DEFAULT NULL,
  `container_format` varchar(20) DEFAULT NULL,
  `checksum` varchar(32) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL,
  `min_disk` int(11) NOT NULL,
  `min_ram` int(11) NOT NULL,
  `protected` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_images_is_public` (`is_public`),
  KEY `ix_images_deleted` (`deleted`)
) ENGINE=ndbcluster;

CREATE TABLE `image_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image_id` varchar(36) NOT NULL,
  `member` varchar(255) NOT NULL,
  `can_share` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL,
  `status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `image_id` (`image_id`,`member`),
  KEY `ix_image_members_image_id` (`image_id`),
  KEY `ix_image_members_deleted` (`deleted`),
  KEY `ix_image_members_image_id_member` (`image_id`,`member`),
  CONSTRAINT `FK_748_757` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster;

alter table image_members drop index `ix_image_members_image_id_member`;
[12 Oct 2015 11:16] MySQL Verification Team 
Hi
I cannot reproduce this with 7.4.6:

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `images` (
    ->   `id` varchar(36) NOT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `size` bigint(20) DEFAULT NULL,
    ->   `status` varchar(30) NOT NULL,
    ->   `is_public` tinyint(1) NOT NULL,
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   `deleted` tinyint(1) NOT NULL,
    ->   `disk_format` varchar(20) DEFAULT NULL,
    ->   `container_format` varchar(20) DEFAULT NULL,
    ->   `checksum` varchar(32) DEFAULT NULL,
    ->   `owner` varchar(255) DEFAULT NULL,
    ->   `min_disk` int(11) NOT NULL,
    ->   `min_ram` int(11) NOT NULL,
    ->   `protected` tinyint(1) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `ix_images_is_public` (`is_public`),
    ->   KEY `ix_images_deleted` (`deleted`)
    -> ) ENGINE=ndbcluster;
Query OK, 0 rows affected (1.11 sec)

mysql> CREATE TABLE `image_members` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `image_id` varchar(36) NOT NULL,
    ->   `member` varchar(255) NOT NULL,
    ->   `can_share` tinyint(1) NOT NULL,
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   `deleted` tinyint(1) NOT NULL,
    ->   `status` varchar(20) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `image_id` (`image_id`,`member`),
    ->   KEY `ix_image_members_image_id` (`image_id`),
    ->   KEY `ix_image_members_deleted` (`deleted`),
    ->   KEY `ix_image_members_image_id_member` (`image_id`,`member`),
    ->   CONSTRAINT `FK_748_757` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    -> ) ENGINE=ndbcluster;
Query OK, 0 rows affected (2.16 sec)

mysql> alter table image_members drop index `ix_image_members_image_id_member`;
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select @@version;
+------------------------------+
| @@version                    |
+------------------------------+
| 5.6.24-ndb-7.4.6-cluster-gpl |
+------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@gedora mysql]# bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @127.0.0.1  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)
id=2    @127.0.0.1  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

kind regards
Bogdan Kecman
[24 Mar 2016 16:45] David Turner 
I can confirm seeing the same behavior on 5.6.28-ndb-7.4.10-cluster-gpl when trying to execute the db sync process for upstream Glance (Mitaka):

mysql> select @@version;
+-------------------------------+
| @@version                     |
+-------------------------------+
| 5.6.28-ndb-7.4.10-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `images` (
    ->   `id` varchar(36) NOT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   `size` bigint(20) DEFAULT NULL,
    ->   `status` varchar(30) NOT NULL,
    ->   `is_public` tinyint(1) NOT NULL,
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   `deleted` tinyint(1) NOT NULL,
    ->   `disk_format` varchar(20) DEFAULT NULL,
    ->   `container_format` varchar(20) DEFAULT NULL,
    ->   `checksum` varchar(32) DEFAULT NULL,
    ->   `owner` varchar(255) DEFAULT NULL,
    ->   `min_disk` int(11) NOT NULL,
    ->   `min_ram` int(11) NOT NULL,
    ->   `protected` tinyint(1) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `ix_images_deleted` (`deleted`),
    ->   KEY `ix_images_is_public` (`is_public`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE `image_members` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `image_id` varchar(36) NOT NULL,
    ->   `member` varchar(255) NOT NULL,
    ->   `can_share` tinyint(1) NOT NULL,
    ->   `created_at` datetime NOT NULL,
    ->   `updated_at` datetime DEFAULT NULL,
    ->   `deleted_at` datetime DEFAULT NULL,
    ->   `deleted` tinyint(1) NOT NULL,
    ->   `status` varchar(20) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `image_id` (`image_id`,`member`),
    ->   KEY `ix_image_members_deleted` (`deleted`),
    ->   KEY `ix_image_members_image_id` (`image_id`),
    ->   KEY `ix_image_members_image_id_member` (`image_id`,`member`),
    ->   CONSTRAINT `FK_185_203` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> show indexes from image_members;
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name                         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| image_members |          0 | PRIMARY                          |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          0 | image_id                         |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          0 | image_id                         |            2 | member      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_deleted         |            1 | deleted     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_image_id        |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_image_id_member |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_image_id_member |            2 | member      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE image_members DROP INDEX ix_image_members_image_id_member;
ERROR 1553 (HY000): Cannot drop index 'ix_image_members_image_id_member': needed in a foreign key constraint

As Vlad mentioned, index image_id can be dropped - (although for some reason when this is attempted by Glance db sync process, it fails with the same error as above. Manually, it works)  

This appears to map to the unique constraint defined for the image_id, member columns:

mysql> ALTER TABLE image_members DROP INDEX image_id;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

show indexes from image_members;
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name                         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| image_members |          0 | PRIMARY                          |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_deleted         |            1 | deleted     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_image_id        |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_image_id_member |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| image_members |          1 | ix_image_members_image_id_member |            2 | member      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+