Cannot drop index on MySQL Aurora only

Question

Background

We run MySQL Aurora (5.7.mysql_aurora.2.07.2) hosting a vendor product. The vendor supplies migration SQL scripts for their version upgrades.

We have an issue where a migration does not run successfully on MySQL Aurora, but does run on other MySQL 5.7 databases, and we really want to know why this happens in Aurora only.

Details

-- 1
DROP DATABASE IF EXISTS drop_index_test;

-- 2
CREATE DATABASE IF NOT EXISTS drop_index_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

-- 3
use drop_index_test;

-- 4
CREATE TABLE msg
(
    id                      BIGINT AUTO_INCREMENT NOT NULL,
    uuid                    VARCHAR(36)           NOT NULL,
    user_id                 VARCHAR(36),
    title                   LONGTEXT,

    CONSTRAINT pk_msg PRIMARY KEY (id),
    CONSTRAINT uq_msg_uuid UNIQUE (uuid)
);

-- 5
CREATE TABLE ack
(
    id                   BIGINT AUTO_INCREMENT NOT NULL,
    msg_id      BIGINT                NOT NULL,
    user_id              VARCHAR(36)           NOT NULL,

    CONSTRAINT pk_ack PRIMARY KEY (id),
    CONSTRAINT fk_ack2msg_01 FOREIGN KEY (msg_id) REFERENCES msg (id)
);

-- 6
CREATE UNIQUE INDEX ix_ack_mid_uid
    ON ack (msg_id, user_id);

-- 7
ALTER TABLE msg
  ADD user_id_upper VARCHAR(36) AS (UPPER(user_id));

-- 8
CREATE INDEX ix_msg_upper_user_id ON msg(user_id_upper);

-- 9
ALTER TABLE ack
  ADD user_id_upper VARCHAR(36) AS (UPPER(user_id));

-- 10
CREATE UNIQUE INDEX ix_ack_mid_upper_uid
    ON ack (msg_id, user_id_upper);

-- 11
DROP INDEX ix_ack_mid_uid ON ack;

Above is a stripped down version of the statements that still replicate this issue on Aurora only and still work in 'vanilla' MySQL 5.7 (tested brew install, RDS instance and mysql/mysql-server:5.7 from Docker Hub). Statements 1-6 are initial setup statements, 7-11 are the migration statements that cause the following error.

Error

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

Exploration findings so far

If we run statements 1-11 on a vanilla MySQL 5.7 we never see any error.
If we run statements 1-11 on MySQL Aurora (5.7.mysql_aurora.2.07.2), we always see this error.
If we run statements 1-6, followed by statement 11 on a vanilla MySQL 5.7 we see the same error as in #2 above.
Statements 7-10 are the key difference determining if this errors or not. Without 7-10 vanilla MySQL 5.7 throws the same Cannot drop index error as Aurora. Aurora throws this error with or without lines 7-10.

Problem statement

What might be the root cause of this issue? Is this a bug in Aurora or a known version difference or a potential Aurora misconfiguration on our end?

How do statements 7-10 impact wether or not index ix_ack_nid_uid can be dropped in MySQL 5.7, when they seemingly don't touch that index? And why does the same not impact Aurora?

user_1853243 · Answer

I also encountered the same issue. I anyways fixed the issue by dropping the foreign key first and then dropping the index.

Now, for the cause, one of my friend(SQL Architect) said it looks like an existing issue mentioned in Bug #17449901 in MySQL Aurora.

With foreign_key_checks=0, InnoDB permitted an index required by a foreign key constraint to be dropped, placing the table into an inconsistent and causing the foreign key check that occurs at table load to fail. InnoDB now prevents dropping an index required by a foreign key constraint, even with foreign_key_checks=0. The foreign key constraint must be removed before dropping the foreign key index.

This could be the cause of the issue, because InnoDB now prevents dropping an index required by a foreign key constraint

user_7520005 · Answer

Try disabling key checking at the beginning of the sql file.

SET FOREIGN_KEY_CHECKS=0;

and enable at the end:

SET FOREIGN_KEY_CHECKS=1;

Cannot drop index on MySQL Aurora only

2 Answers