Dbal: DBAL-1062: upgrade from v2.4.3 to v2.5.0 is forcing recreating Indexes making Doctrine unusable

[doctrinebot]

Jira issue originally created by user gondo:

after executing 'composer update' i was upgraded to dbal v2.5.0
(im using "doctrine/orm": "~2.2,>=2.2.3" in composer.json)
im using Symfony 2.6.*

now when i try 'app/console doctrine:schema:update --dump-sql' i see that doctrine wants to recreate indexes on some tables for no practical reason
nothing changed in the code.

example:
DROP INDEX idx_a604da13a76ed395 ON table1;
CREATE INDEX IDX_B7E704F0A76ED395 ON table1 (user_id);
DROP INDEX uniq_b3319c7d77153098 ON table2;
CREATE UNIQUE INDEX UNIQ_C984F95777153098 ON table2 (code);

however when i try to execute this update, im getting this error:
General error: 1553 Cannot drop index 'IDX_A604DA13A76ED395': needed in a foreign key constraint

this essentially prevents me from using automatic doctrine database mapping or using migration tools.

[doctrinebot]

• is referenced by DBAL-1063: Exceptions from SchemaTool when running with DBAL 2.5.0
• is referenced by DBAL-1092: [GH-756] [DBAL-1062] Fix renaming indexes used by foreign key constraints

[doctrinebot]

Comment created by @ocramius:

Downgraded to "Major", as this doesn't prevent usage of the DBAL at runtime.

You can still upgrade those indexes manually after having removed the FKs (to re-add them later on).

Seems like a case sensitivity issue of your setup: consider adding environment details.

[doctrinebot]

Comment created by @deeky666:

Which database vendor are you using? Also interestingly the indexes get recreated with a different name. Thought of case sensitivity, too. I think the comparator nevertheless has to be adjusted to compare with identical case on index names.

[doctrinebot]

Comment created by gondo:

well it IS preventing me from using doctrine as it is.
i just downgraded dbal to v2.4.3 by adding "doctrine/dbal": "2.4.*", to my composer.js and everything is working fine. by that i mean, no commands to drop and create indexes.

im using mysql Ver 14.14 Distrib 5.6.21, for osx10.10 (x86_64) using EditLine wrapper

im developoing on OSX 10.10 and production is running CentOS.
i know that OSX is using case insensitive file system (i had to deal with it in the past when i was deploying to production)
but this time there is NO change in my code. zero. nothing.

if there was case sensitivity changes in dbal itself, that might be the problem, however that is nothing i can fix.

i would love to upgrade those indexes manualy, however i have no idea how to.
should i change something in the code? indexes were created and named by doctrine, not by me.
if you recommend updating database, that seems to be failing. i assume dropping indexes on live data might cause problems.
or is it safe to just drop and create these indexes in mysql cmd?

[doctrinebot]

Comment created by gondo:

i can not drop those indexes directly in mysql, im getting erros:
ERROR 1553 (HY000): Cannot drop index 'IDX_A604DA13A76ED395': needed in a foreign key constraint

[doctrinebot]

Comment created by @deeky666:

okay just checked, casing is not a problem as identifiers will be lowercased automatically in Doctrine\DBAL\Schema\Table for comparison.
Need further information about the underlying database being used...

[doctrinebot]

Comment created by @deeky666:

[~gondo] please for now don't try to solve the issue automatically because it looks like a real issue we need to figure out. Otherwise there will be little chance we get to know the real cause of the issue...

[doctrinebot]

Comment created by gondo:

@Steve Müller for now i've solved it by downgrading dbal to v2.4.*
do you need some more information from me?
unfortunately i can't give you all the code, company policy + its quite big. but i can dump you database schemas and entity declarations of affected tables if that will help. please let me know.

[doctrinebot]

Comment created by @deeky666:

Hmm cannot reproduce the problem. What I did:

composer create-project symfony/framework-standard-edition path/

then added 'doctrine/dbal": "2.4.*"' to composer.json

composer install

then created entity with unique column name (to force auto index generation)

php app/console doctrine:database:create
php app/console doctrine:schema:create

then changed 'doctrine/dbal": "2.4."' to 'doctrine/dbal": "2.5."' in composer.json

php app/console doctrine:schema:update --force
Nothing to update - your database is already in sync with the current entity metadata.

Tried that with pdo_mysql on ubuntu 14.04 x86_64.

[doctrinebot]

Comment created by gondo:

were you creating some tables with foreign keys?

[doctrinebot]

Comment created by gondo:

here is the list of all indexes what tries to be recreated:
http://pastebin.com/nFYp6pnp

here are the definitions of some entities + their schemas and indexes from mysql:
notification_channel
http://pastebin.com/EfkcyUnf
http://pastebin.com/Ngd1Lkbe

online_payment_option
http://pastebin.com/R5waCF35
http://pastebin.com/ti4TzyKX

user_settings
http://pastebin.com/gxed5kjY
http://pastebin.com/EiCBWKNQ

---

i've also tried to specify index with custom name as per http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#annref-index
to prevent this index recreation but without no luck, it was ignored.
i've tried to change the definition of online*payment*option table like this:
@ORM\Table(name="online_payment_option", options={"collate"="utf8_unicode_ci", "charset"="utf8"}, indexes={@ORM\Index(name="TEST", columns={"code"})})
but after trying schema:update im still getting the same output http://pastebin.com/nFYp6pnp

[doctrinebot]

Comment created by @deeky666:

Okay I think I get the problem now. I don't get any suggested update statements when upgrading from a DBAL 2.4 created schema to DBAL 2.5.
I assume this is because even in DBAL 2.4 the indexes are created with the name your update command suggests. For example:

DROP INDEX idx*a604da13a76ed395 ON notification*channel;
CREATE INDEX IDX*B7E704F0A76ED395 ON notification_channel (user*id);

You have an index named idx_a604da13a76ed395 in your database, the index name DBAL generates is IDX_B7E704F0A76ED395. Even in 2.4 this is the name that is generated by DBAL. The reason why you get those update statements is because index names are compared since DBAL 2.5 as part of the new index renaming feature.
I guess that the index name generation has changed in an earlier version of DBAL (can't prove that right now). So you probably created the index with a much earlier DBAL version back then and now it wants to rename it. This should be a one time "upgrade" step.
The reason why manually defining an index in your entity with a custom name has no effect is because ORM's schema tool prefers auto generated indexes over custom indexes if both fullfill the same criteria. This is something to be fixed in ORM then.
The foreign key problem is indeed something we have to deal with in DBAL. The update schema command should create SQL to first drop FKs, then rename indexes and afterwards recreate FKs again.
Hope that helps for now. Sorry for the upgrade circumstances...

[doctrinebot]

Comment created by gondo:

thank you very much for looking into this and spending time on it!
it is very likely that those indexes were created in older version, however I'm 100% that it is not older than 1 year.

if i understand correctly, there are several things what needs to be fixed (manual overwriting of indexes and generating proper update schema command)

is this something what will be fixed? or does this fall into "edge case" bucket and will be left until more people experience same problem?
so far I'm fine staying on 2.4.3 but eventually i would like to upgrade. if the fix is planned, i can wait. if not, than i can create manual update now.

thanks one more time

[doctrinebot]

Comment created by @deeky666:

I am already on that foreign key issue. As soon as that is fixed, the generated update SQL should be valid so that it can safely be run and you don't need to update your index names manually then. As what the manual index preference is concerned that needs to be fixed in ORM. I might also have a look into this issue afterwards. I think it won't take long until DBAL 2.5.1 as there is another critical issue that needs to be adressed sonner than later.
With 2.5.1 you should be safe to update your schema automatically.

[doctrinebot]

Comment created by gondo:

perfect!
thats much sooner than i expected :)
thanks again

[doctrinebot]

Comment created by tgabi333:

This is a deal breaker for us. We cannot use 2.5 branch until it is not fixed. :(

[doctrinebot]

Comment created by @ocramius:

[~tgabi333] you are not forced to upgrade for now: consider sending a patch if it is that critical to you.

[doctrinebot]

Comment created by @deeky666:

[~tgabi333] I provide a patch here: https://github.com/doctrine/dbal/pull/756

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-756] was closed:
https://github.com/doctrine/dbal/pull/756

[doctrinebot]

Comment created by gondo:

i've just updated to 2.5.1 and the problem remains

[doctrinebot]

Comment created by @deeky666:

[~gondo] can you please post the SQL created by the schema tool and the error you get? Thanks.

[doctrinebot]

Comment created by gondo:

same as on the beginning, basically nothing changed for me :/
http://pastebin.com/NqP9aEae

[doctrinebot]

Comment created by @deeky666:

Do you get an error when executing the SQL? I can see that foreign keys are now dropped before dropping and recreating the index which is part of the patch. The reason why the schema manager is still outputting upgrade SQL was discussed here before (index name mismatch). However this should only happen once now and it should work without an error.

[doctrinebot]

Comment created by gondo:

ah i see.
i was expecting that there will be no SQL update needed after this patch, but now i understand what you mean.
i was only doing app/console doctrine:schema:update --dump-sql
after trying app/console doctrine:schema:update --force (on localhost only) everything seems to be fine

perfect! i will do some more testing, hopefully i ll not destroy production database with this :)

[doctrinebot]

Comment created by @deeky666:

Unfortunately we cannot prevent SQL generation for users that have different index names in their database than those created by the mapping. Those users will have to "resync" index names once and should be fine afterwards. If we would not compare index names, the index renaming feature would not be possible.
You should be safe to run the SQL in production as it is just dropping and recreating indexes / foreign keys.

[doctrinebot]

Issue was closed with resolution "Fixed"