MySQL: Determining existing composite foreign keys does not take column positions (ORDINAL_POSITION) into account (missing ORDER BY)

[AndreasA]

Bug Report

When using a foreign key with multiple join columns, the method \Doctrine\DBAL\Platforms\MySqlPlatform::getListTableForeignKeysSQL does not order by ORDINAL_POSITION in its SQL. See:

dbal/src/Platforms/MySQLPlatform.php

Line 179 in 96b0053

public function getListTableForeignKeysSQL($table, $database = null)

This can result in the order of the columns not being the same as expected.

E.g. on a MySQL 8.0 installation it seems to be ordered alphabetically by column name and 5.7 it seems to be by ordinal position which results in potential schema differences with 8.0

It might also be something else and not version related.

I think it should be enough to add

ORDER BY k.`ORDINAL_POSITION` ASC

Not 100% sure if this is the correct field but I think it is. It probably also has to be added to the select for the ORDER BY to be usable in this scenario.

With it, 8.0 and 5.7 both return the same order for the referenced columns.

For other similar statements the columns are already ordered, e.g.

dbal/src/Platforms/MySQLPlatform.php

Line 147 in 96b0053

public function getListTableIndexesSQL($table, $database = null)

or

dbal/src/Platforms/MySQLPlatform.php

Line 350 in 96b0053

public function getListTableColumnsSQL($table, $database = null)

Q	A
BC Break	no
Version	2.13.4 (but probably also 3.1.3)

[morozov]

Unless the missing ORDER BY impacts the behavior from the API consumer perspective, I don't believe it's a bug. It's pretty much possible that the rows are ordered in the column order in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.

E.g. on a MySQL 8.0 installation it seems to be ordered alphabetically by column name and 5.7 it seems to be by ordinal position which results in potential schema differences with 8.0

If it's a real issue, it should be reproducible with an integration test.

@AndreasA please feel free to send a patch though.

[AndreasA]

@morozov It is in so far reproducible that if I change the order of the join columns it tries to drop on a schema update, thene re-add the key again, etc. as the generated index name is different from the expected one due to different column order.

If I find the time I will create an integration test plus fix.

[AndreasA]

@morozov I have now created a PR #4978

If I remove this line https://github.com/doctrine/dbal/pull/4978/files#diff-f2c2d9a23d4985a19b3d2df3e3ff932fa5a506eae05cacbf084ff497362bf12aR199

then the created test https://github.com/doctrine/dbal/pull/4978/files#diff-6eb874abe74292a78033e7a9b66317b16aaed0ab8eb6ce1e36dd1801d8fe897dR547

fails in my MySQL 8.0.26 installation and it definitely shouldn't fail because doctrine DBAL thinks that the table it created itself differs from the created one but it doesn't.

I would actually also like to try that the test also fails in the CI with that adjustment first but the pipelines do not start for me there because maybe it is also related to some my.cnf configuration though I don't have anything special in there.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.