Неожиданное поведение транзакций MySQL

  • разработка
  • mysql
  • решение проблем

Сегодня обнаружил, что на девелоперском стейджинге пару недель как перестали накатываться миграции. Оказалось, что MySQL не могла удалить уникальный ключ, который использовался внешним ключом, и команда падала с ошибкой.

Error An exception occurred while executing 'DROP INDEX user_unique ON role_user': SQLSTATE[HY000]: General error: 1553 Cannot drop index 'user_unique': needed in a foreign key constraint

Чтобы исправить проблему решил попробовать удалить сначала внешние ключи, поля которых входят в уникальный индекс, а затем снова выполнить DROP INDEX. Тестировать решил прямо на базе стейджинга, поэтому обернул свои запросы в транзакцию, а чтобы откатить изменения, добавил в конце ROLLBACK:

START TRANSACTION;
ALTER TABLE role_user
    DROP FOREIGN KEY FK_9054A4BA34539B55;
ALTER TABLE role_user
   DROP FOREIGN KEY FK_1488A4BAD21983AC;
ALTER TABLE role_user
    DROP KEY user_unique;
ALTER TABLE role_user
    ADD CONSTRAINT FK_9054A4BA34539B55
        FOREIGN KEY (item_id) REFERENCES item (id)
            ON DELETE CASCADE;
ALTER TABLE role_user
    ADD CONSTRAINT FK_1488A4BAD21983AC
        FOREIGN KEY (role_id) REFERENCES roles (id)
            ON DELETE CASCADE;
ROLLBACK;

Каково же было мое удивление, когда я обнаружил, что все запросы выполнились успешно, но схема не откатилась к исходному состоянию после выполнения ROLLBACK. Стал гуглить и обнаружил, что MySQL, перед тем как выполнять ALTER TABLE запросы, делает COMMIT, что выполняет транзакцию и делает невозможным откат изменений.

Для меня это оказалось очень странным, т.к. я мало работал с MySQL, а Postgres позволяет менять схему и потом откатывать изменения.