MySQL adapter fails to drop multi-column foreign keys

[topiolli]

MysqlAdapter::getDropForeignKeyByColumnsInstructions() fetches the same constraint name many times if a foreign key contains more than one column. It then creates the same DROP statement as many times as there are columns in the key. Furthermore, if the same column is used in many foreign keys, they all may be dropped (depending on ordering).

I didn't test this, but I believe the query could be fixed about so:

$columnNames = "'" . implode("','", $columns) . "'";
$columnCount = count($columns);
$sql = "select constraint_name
from information_schema.table_constraints
join information_schema.key_column_usage
using(constraint_name, table_schema, table_name)
where table_name = '$tableName'
and constraint_type = 'FOREIGN KEY'
and column_name in ($columnNames)
group by constraint_name
having count(column_name) = $columnCount";

This would fix my use case, but I don't know if there are other cases where dropping multiple constraints would be required.

EDIT: tested the query and fixed the constraint_type check.

[MasterOdin]

Could you provide a migration that demonstrates the bug?

[topiolli]

This piece of code in my migration triggered the bug:

        $this->table('component_version')
            ->dropForeignKey(['component_id', 'major'])

I was expecting Phinx to drop the foreign key that uses the listed columns. The migration failed because Phinx tried to drop the fk twice.