Framework: dropUnique() failing when a column is part of foreign key constraint (mysql bug)

Created on 3 Jun 2016 · 10Comments · Source: laravel/framework

Hi guys,

I encountered this issues with a migration rollback failing to remove a unique index (in my case, composed) if one of the columns used in it are also used in a foreign key constraint and, hence, in a automatically created index.

This seems to be a known bug in mysql: http://bugs.mysql.com/bug.php?id=37910%22%3EMySQL

If I have the following code in the up() method of the migration:
Schema::table('group_user', function (Blueprint $table) { $table->unique(['user_id', 'group_id']); });

I expect the down() method to remove the unique index:
Schema::table('groups_users', function (Blueprint $table) { $table->dropUnique('group_user_user_id_group_id_unique'); });

But the dropUnique() fails with the following error:
[PDOException] SQLSTATE[HY000]: General error: 1553 Cannot drop index 'group_user_user_id_group_id_unique': needed in a foreign key constraint

The workaround suggested in the link to mysql bug report is to recreate a foreign key index in the same mysql statement right after a unique index is dropped.

Unfortunately, the way the dropUnique() method is written, it doesn't allow for chained methods to drop and create index in the same query.

This sql query executed directly works:
ALTER TABLEgroup_userDROP INDEXgroup_user_group_id_user_id_unique, ADD INDEX (user_id), ADD INDEX (group_id) ;

The most obvious use case for that would be when we have a pivot table that we want to have unique records (friendship relationships, group relationships, etc.).

One of the solutions would be to allow chaining of dropUnique() with index().

Best regards,
Alex

Source

alexrayan

👍3

Most helpful comment

@GrahamCampbell Why close this? The bug looks legit.

Here is what I found:

I have two migrations set up like this:

// First migration:
$table->integer('experiment_id')->unsigned()->nullable();
$table->foreign('experiment_id')->references('id')->on('experiments');
$table->integer('order_batch_id')->unsigned();
$table->foreign('order_batch_id')->references('id')->on('order_batches')->onDelete('set null');

// Second migration:
$table->unique(['experiment_id', 'order_batch_id'], 'exp_batch');

Running them yields the following SQL schema:

# After the first migration:
mysql> SHOW INDEX FROM orders;
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                      | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders |          0 | PRIMARY                       |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | orders_experiment_id_foreign  |            1 | experiment_id  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| orders |          1 | orders_order_batch_id_foreign |            1 | order_batch_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

# After the second migration:
mysql> SHOW INDEX FROM orders;
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                      | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders |          0 | PRIMARY                       |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          0 | exp_batch                     |            1 | experiment_id  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| orders |          0 | exp_batch                     |            2 | order_batch_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| orders |          1 | orders_order_batch_id_foreign |            1 | order_batch_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

We can see the index orders_experiment_id_foreign disappear when the second migration is run and therefore a subsequent call to $table->dropUnique('exp_batch'); crashes with

Cannot drop index 'exp_batch': needed in a foreign key constraint (SQL: alter table `orders` drop index `exp_batch`)

The workaround I came up with is to manually add the foreign key index during the rollback so that the table actually comes back as it was before the migration.

// Rollback script
Schema::table('orders', function($table)
{
    // Add the missing index
    $table->index('experiment_id', 'orders_experiment_id_foreign');
});
Schema::table('orders', function($table)
{
    $table->dropUnique('exp_batch');
});

nb: this fix actually survives multiple migrate/rollback cycles unlike the solution above.

hmil on 31 Aug 2017

👍16 ❤1 😄1

All 10 comments

Why don't you do something like this before dropping any foreign keys etc ..

Schema::disableForeignKeyConstraints();

// Your code

Schema::enableForeignKeyConstraints();

srmklive on 6 Jun 2016

👎16 👍3

Feel free to discuss on the forums. :)

GrahamCampbell on 6 Jun 2016

👎9

Thanks for the response. Yep, seems that disabling the foreign keys in the migration would work for the purpose of dropping the unique index. : )

alexrayan on 6 Jun 2016

I ran into the same problem today as well. I found that disabling foreign key constraints did not help. I also found that there was slightly different behaviour depending on whether all migrations were run in a batch, or the most recent migration was run in its own batch.

In the end I decided to go with this which, while not the greatest, works:

if (DB::connection() instanceof MySqlConnection) {
    DB::statement('ALTER TABLE `foos` DROP INDEX `foos_bar_id_name_unique`, ADD INDEX (bar_id)');
} else {
    Schema::table('ratings', function (Blueprint $table) {
        $table->dropUnique(['bar_id', 'name']);
    });
}

Note: If the index being added were to be named according to the foreign key (i.e. how laravel usually does it) then the ADD INDEX part should read:

ADD INDEX `foos_bar_id_foreign` (bar_id)

but this breaks if you do two migrate/rollback cycles (on the second rollback it complains about having a "duplicate key name"). With this solution, if you do two migrate/rollback cycles then you end up with two indexes on bar_id.

JamesGuthrie on 8 Jun 2016

@GrahamCampbell Why close this? The bug looks legit.

Here is what I found:

I have two migrations set up like this:

// First migration:
$table->integer('experiment_id')->unsigned()->nullable();
$table->foreign('experiment_id')->references('id')->on('experiments');
$table->integer('order_batch_id')->unsigned();
$table->foreign('order_batch_id')->references('id')->on('order_batches')->onDelete('set null');

// Second migration:
$table->unique(['experiment_id', 'order_batch_id'], 'exp_batch');

Running them yields the following SQL schema:

# After the first migration:
mysql> SHOW INDEX FROM orders;
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                      | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders |          0 | PRIMARY                       |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | orders_experiment_id_foreign  |            1 | experiment_id  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| orders |          1 | orders_order_batch_id_foreign |            1 | order_batch_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

# After the second migration:
mysql> SHOW INDEX FROM orders;
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                      | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders |          0 | PRIMARY                       |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          0 | exp_batch                     |            1 | experiment_id  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| orders |          0 | exp_batch                     |            2 | order_batch_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| orders |          1 | orders_order_batch_id_foreign |            1 | order_batch_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

We can see the index orders_experiment_id_foreign disappear when the second migration is run and therefore a subsequent call to $table->dropUnique('exp_batch'); crashes with

Cannot drop index 'exp_batch': needed in a foreign key constraint (SQL: alter table `orders` drop index `exp_batch`)

The workaround I came up with is to manually add the foreign key index during the rollback so that the table actually comes back as it was before the migration.

// Rollback script
Schema::table('orders', function($table)
{
    // Add the missing index
    $table->index('experiment_id', 'orders_experiment_id_foreign');
});
Schema::table('orders', function($table)
{
    $table->dropUnique('exp_batch');
});

nb: this fix actually survives multiple migrate/rollback cycles unlike the solution above.

hmil on 31 Aug 2017

👍16 ❤1 😄1

The original poster's issue was resolved. If you have an issue that you feel is a bug, create an new issue with details. It does look like expected behavior though.

devcircus on 31 Aug 2017

I created the compound unique index for some columns before creating the foreign keys for the individual columns. I think this may be what caused it for me. In my migration, I dropped the foreign keys, then I removed the unique index, then re-added the foreign keys. This was all that would work for me.

NineToeNerd on 6 Mar 2018

👍1

Thank you, @hmil . I'll follow your approach.

I agree that this bug should be reopened until Laravel fixes this unexpected behavior.

ryancwalsh on 11 Oct 2018

👍1

Thanks @hmil - it worked for me too

pgrosvenor on 7 Mar 2019

I created the compound unique index for some columns _before_ creating the foreign keys for the individual columns. I think this may be what caused it for me. In my migration, I dropped the foreign keys, then I removed the unique index, then re-added the foreign keys. This was all that would work for me.

For me, this is the best solution.