Issue with getRenameIndexSQL being case-insensitive

[verganis]

We found out an issue with DB migration created through Doctrine/DBAL because an index created as "IDX_E0AEF1841F7B7B69" was later referred to as "idx_e0aef1841f7b7b69".

This works well on Windows systems but when the migration is performed on a Mac system it fails, simply because the index called "idx_e0aef1841f7b7b69" does not exist.

We are working on Symfony / Mysql stack and our DB collations are "latin1_swedish_ci" on both Windows and Mac machines.

We looked in the documentation trying to find some configuration that could be set to make Doctrine migration case-sensitive but we did not succeed, so this might be a bug.

First migration
$this->addSql('CREATE TABLE team_closure (id INT AUTO_INCREMENT NOT NULL, ancestor INT NOT NULL, descendant INT NOT NULL, depth INT NOT NULL, INDEX IDX_B77EFCE6B4465BB (ancestor), INDEX IDX_B77EFCE69A8FAD16 (descendant), INDEX IDX_E0AEF1841F7B7B69 (depth), UNIQUE INDEX IDX_5AD3014880E23D3F (ancestor, descendant), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');

Second migration
$this->addSql('ALTER TABLE team_closure RENAME INDEX idx_e0aef1841f7b7b69 TO IDX_8AFC573B1D9A3396');

We normally generate migrations running the following commands in the shell

php bin/console doctrine:migrations:diff
php bin/console doctrine:migrations:migrate

[morozov]

@verganis could you provide an self-contained use case to reproduce this issue? In MySQL, depending on the filesystem and the storage engine, object names can be case-sensitive or case-insensitive. Particularly, MyISAM table names are case-insensitive on Windows but are case-sensitive on Linux.

DBAL should use the same case everywhere and not rely on case insensitivity.

[Ocramius]

I think we implemented case-insensitive diffing for a lot of symbols due to `EXPLAIN` reporting them with different casing. Would need to dig up the ticket though... Marco Pivetta http://twitter.com/Ocramius http://ocramius.github.com/

…

On Wed, Jan 10, 2018 at 5:57 PM, Sergei Morozov ***@***.***> wrote: @verganis <https://github.com/verganis> could you provide an self-contained use case to reproduce this issue? In MySQL, depending on the filesystem and the storage engine, object names can be case-sensitive or case-insensitive. Particularly, MyISAM table names are case-insensitive on Windows but are sensitive on Linux. DBAL should use the same case everywhere and not rely on case insensitivity. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2974 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakPjd67bMKAQRvqzHaj_uR87HWvMfks5tJOv4gaJpZM4RZVMW> .

[verganis]

Hello, thanks for the quick reply.
We are facing this issue in a company project which is not open-source.
To create a self-contained example could take us some time.
We can see that the table name it's respected, it's the index which is suffering from a case change we can't really explain.
From a broader search we can see that each time an "ALTER INDEX" instruction is generated the same issue is occurring, please see attachment. The 4 occurrences in the attachment are present in the same migration file (2 for the up method and 2 for the down method).

[verganis]

Additional details: after further investigation the table "team_closure" it's the one used by the Doctrine Tree Bundle, here is the definition of the Team Entity

/**
 * Team
 *
 * @Gedmo\Tree(type="closure")
 * @Gedmo\TreeClosure(class="UserBundle\Entity\TeamClosure")
 * @ORM\Table(name="team")
 * @ORM\Entity(repositoryClass="UserBundle\Repository\TeamRepository")
 */
class Team implements EntityLifecycleInterface

At this point I am not sure if the issue comes from Doctrine/DBAL or from DoctrineExtensions

[teian]

I can confirm this problem also happens on Win 10 with postgres 11.2 very very frustrating!
Might be a problem with

dbal/lib/Doctrine/DBAL/Schema/AbstractAsset.php

Line 208 in 9a352c8

return strtoupper(substr($prefix . '_' . $hash, 0, $maxSize));

[kevinpapst]

This is quite an old ticket and maybe unrelated.
But I have an issue with index casing as well.

Creating a table with an index without name (relying on DBAL to auto-generate the index name):

 * @ORM\Table(name="demo",
 *     indexes={
 *          @ORM\Index(columns={"user_id"})
 *     }
 * )

could lead to an index name IDX_ADFA4DF78D93D649.

When I use a schema migration:

        $demo = $schema->createTable('demo');
        $demo->addColumn('user_id', 'integer', ['notnull' => true]);
        $demo->addIndex(['user_id'], 'IDX_ADFA4DF78D93D649');

The next call to bin/console doctrine:migrations:diff will generate

        $this->addSql('DROP INDEX idx_adfa4df78d93d649 ON demo');
        $this->addSql('CREATE INDEX IDX_ADFA4DF78D93D649 ON demo (user_id)');

That is caused by DBAL applying a strtolower to the given index name in Doctrine/DBAL/Schema/Table.php#L841

When relying on the auto-generated name instead (no index name given) everything works:

        $demo = $schema->createTable('demo');
        $demo->addColumn('user_id', 'integer', ['notnull' => true]);
        $demo->addIndex(['user_id']);

So DBAL generates uppercase index names, but lowercases given index names from user land.

That does NOT happen for foreign keys, so something like:

$expenses->addForeignKeyConstraint('demo_users', ['user_id'], ['id'], ['onDelete' => 'CASCADE'], 'FK_ADFA4DF7EF29BDD3');

behaves correctly, no matter if I give a name or leave it to use auto generated values.

Feels like a bug that DBAL is changing the case for the index name.