Bug #78980 DROP INDEX FAILURE CORRUPTS THE INDEX METADATA IN MYSQLD

• Submitted: 27 Oct 2015 14:09
• Modified: 11 Jan 2016 13:10
• Reporter: Lakshmi Narayanan Sreethar
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 7.4.8
• OS: Any
• CPU Architecture: Any

[27 Oct 2015 14:09] Lakshmi Narayanan Sreethar

Description:
During `alter table drop index` query, ha_ndbcluster::prepare_drop_index changes the m_index array(which stores the handles of the ndb index objects) before actually dropping the indexes. If there is a failure during the actual drop index, the changes are not reverted back. So any subsequent queries either fail or give erroneous results due to the bad index data.

This happens only in a table with more than one index. 

How to repeat:
create table t1(
 a int,
 b int,
 unique key uk1(a),
 unique key uk2(b)
)engine ndb;

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

#Now dropping uk1 should now fail due to fk constraint
mysql> drop index uk1 on t1;
ERROR 1553 (HY000): Cannot drop index 'uk2': needed in a foreign key constraint

But notice the error above
The constraint is on uk1 but the error says it on uk2.
This is due to the wrong index metadata.
A subsequent drop index uk2 also fails due to bad m_index data.

mysql> drop index uk2 on t1;
ERROR 1553 (HY000): Cannot drop index 'uk2': needed in a foreign key constraint

Observe error above.
But it should drop without any error as there is no FK dependency.

Another example : 
create table t1(
 a int,
 b int,
 unique key uk1(a),
 unique key uk2(b) using hash
)engine ndb;

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

#Dropping uk1 again.
mysql> drop index uk1 on t1;
ERROR 1553 (HY000): Cannot drop index 'uk2': needed in a foreign key constraint

^ Fails with bad error
And if we give a select query now,

mysql> select * from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

^ mysqld crashes due to bad m_index.

Suggested fix:
The m_index should be changed only after successful drop indexes and any other cleanup, if necessary, should be done.

[11 Jan 2016 13:10] Jon Stephens

Documented fix as follows in the NDB 7.4.9 changelog:

    When executed on an NDB table, ALTER TABLE ... DROP INDEX made
    changes to an internal array referencing the indexes before the
    index was actually dropped, and did not revert these changes in
    the event that the drop was not completed. One effect of this
    was that, after attempting to drop an index on which there was a
    foreign key dependency, the expected error referred to the wrong
    index, and subsequent attempts using SQL to modify indexes of
    this table failed.

Closed.