Case sensitive string comparison in Nextras\Dbal\Platforms\MySqlPlatform

[longshadowdev]

Describe the bug
When setting up a 'many to many' relationship the foreign keys were not being detected. After some analysis, I found this issue is caused by nextras/orm doing a case sensitive string comparison in Nextras\Orm\Mapper\Dbal\Conventions\Conventions::findManyhasManyPrimaryColumns(). Technically MySQL is supposed to be case insensitive for schema, table and column names. The reflection API performs an SQL query that uses information_schema.KEY_COLUMN_USAGE and in my version of MySQL (8.0.28) this using the real case for TABLE_SCHEMA, but lowercases REFERENCED_TABLE_SCHEMA. Because Orm does a string === comparison, they don't match.

In my case it was "TitanAdminPROJ09-2649-mark" === "titanadminproj09-2649-mark".

I realise that MySQL is being pretty bad to lowercase its columns inconsistently, but technically the ORM should also do a case insensitive comparison according to MySQL. MySQL even has its own bug reports on this https://bugs.mysql.com/bug.php?id=88718

The if/elseif in the findManyHasManyPrimaryColumns function should be changed to something like the following to fix this.

if (strcasecmp($refTable, $sourceTable) === 0 && $sourceId === null) {
    $sourceId = $column;
} elseif (strcasecmp($refTable, $targetTable) === 0) {
    $targetId = $column;
}

To Reproduce

1. Use a MySQL schema name that has uppercase letters
2. Have a many to many table relationship set up the way Orm expects
3. Execute your code. It will throw an error like "[error] No primary keys detected for many has many 'xxx' join table."

Expected behavior
The expected behaviour is normal execution where the schema names match with a case insensitive check. I implemented the fix proposed above to test this and it corrected the issue for me.

Note, of course I could make the schema name lowercase. I almost always do this. In this case, it is an auto generated schema name as part of our dev/testing process and it would be harder to change.

Versions::

• Database: [MySQL 8.0.28]
• Orm: [Orm 4.0.4]
• Dbal: [Dbal 4.0.4]

[hrach]

Thank you for reporting this. Fixed in main and cherry-picked to v4.0 - please test this branch using composer composer update nextras/orm@v4.0.x-dev
I will tag next patch version after that.

[longshadowdev]

Hello @hrach , I have done this and confirmed it fixed my issue. Thank you!