Bug #78306 Unique Key is partially dropped from mysqld even when a 'drop index' fails
Submitted: 2 Sep 2015 14:26 Modified: 5 Jan 2016 15:01
Reporter: Lakshmi Narayanan Sreethar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S3 (Non-critical)
Version:7.4 OS:Any
Assigned to: CPU Architecture:Any

[2 Sep 2015 14:26] Lakshmi Narayanan Sreethar
Description:
When a Unique Key is referred by a Foreign key, it cannot be dropped.
The Unique keys on NDB have two indexes internally : 
   An OrderedIndex and a UniqueHashIndex.
For a Fk that has the UniqueHashIndex part of the unique key as
its child/parent index, the drop index causes a problem.

When a drop on the unique index is issued,
ha_ndbcluster::remove_indexes starts dropping the unique.
The first part, OrderedIndex will be removed from the dict.
But the UniqueHashIndex part can't be removed due to the FK reference. 

So this drop index as a whole fails and returns an error.
But without restoring the already dropped OrderedIndex part.

So when you do a 'select' next, the mysqld crashes, unable to find the OrderedIndex.

How to repeat:
Tables definitions: 

create table t1(
  a int,
  unique key uk(a)
) engine=ndb;

create table t2(
  a int,
  foreign key fk(a) references t1(a)
) engine=ndb;

MySQL log : 
mysql> create table t1(
    ->   a int,
    ->   unique key uk(a)
    -> ) engine=ndb;
Query OK, 0 rows affected (0.73 sec)

mysql> create table t2(
    ->   a int,
    ->   foreign key fk(a) references t1(a)
    -> ) engine=ndb;
Query OK, 0 rows affected (0.66 sec)

mysql> drop index uk on t1;
ERROR 1553 (HY000): Cannot drop index 'uk1': needed in a foreign key constraint
mysql> select * from t1;

This makes the mysqld to crash with a SEGV, unable to find the ordered index.

Suggested fix:
fix ha_ndbcluster::remove_indexes()
[5 Jan 2016 15:01] Jon Stephens
Documented fix in the NDB 7.4.9 changelog, as follows:

        A unique index on a column of an NDB table has an internal
        ordered index, used for scanning. While dropping an index, this
        ordered index was dropped first, followed by the drop of the
        unique index itself. This meant that, when the drop was rejected
        due to a constraint violation, the statement was rejected but
        the associated ordered index remained deleted, so that any
        subsequent operation using a scan on this table failed. We fix
        this problem by causing the unique index to be removed first,
        before removing the ordered index; removal of the related
        ordered index is no longer performed when removal of a unique
        index fails.
      

Closed.