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: | |
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
[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 | | | +---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+