Dbal: Prevent duplicate DROP FOREIGN KEY

[ghost]

This issue is related to the schema comparator. We run it to create migration scripts.

I have often seen that a generated migration script (we use Doctrine Migrations) tries to do the same SQL query multiple times, for example:

$this->addSql('ALTER TABLE news_newsletter_mailing DROP FOREIGN KEY FK_FB48B08B22DB1917');

I cannot give you any working example how to regenerate it but I have seen this multiple times in different applications. The script itself works fine until it tries to run the same SQL query again in the same migration script:

Migration 20160509214556 failed during Execution. Error An exception occurred while executing 'ALTER TABLE news_newsletter_mailing DROP FOREIGN KEY FK_FB48B08B22DB1917':
SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'FK_FB48B08B22DB1917'; check that column/key exists

There must be any issue that result into duplicate SQL queries for DROP FOREIGN KEY in the generated migration code.

See https://github.com/doctrine/migrations/issues/460

[ghost]

This issue occurs while we had this issue: https://github.com/doctrine/dbal/issues/2395
I am not 100% sure that it's really related to each other.

[deeky666]

We need something to replicate the issue otherwise it is nearly impossible to tell what's going wrong. Also more information about your setup is necessary. DBAL version, database vendor and version...

[ghost]

I am not 100% sure anymore when this error occurs. I thought it was while I am renaming tables or column and generating migration scripts after that. I will write it here if I can reproduce it. The issue really occurs sometimes - maybe anybody else can reproduce it faster then me.

[Merk87]

Hello.

I'm experiencing a similar issue.

I have three databases, and I've configured DoctrineORMBundle to manage all, so I can do cross db foreign keys.

The issue is, this three databases already exists, so this is kinda a migration, and in the first run, I'm getting duplicated DROP queries...

Any change on this?

[mergeMarc]

I have the same issue.

DBAL: v2.5.12
doctrine/migrations: v1.5.0
PHP: 7.0.18
MySQL Server: 5.6.33

I was running into this issue when i was simply trying to rename a table name.
(From table name app_countrys to app_countries, refrenced in table app_users_profiles)

Here is the output (commented) of the migration tool for the table name change:

// Drops foreign key for the first time.
$this->addSql('ALTER TABLE app_users_profiles DROP FOREIGN KEY FK_2F8B3C14F92F3E70');
$this->addSql('CREATE TABLE app_countries (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(191) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
$this->addSql('DROP TABLE app_countrys');
// Drops it again
$this->addSql('ALTER TABLE app_users_profiles DROP FOREIGN KEY FK_2F8B3C14F92F3E70');
// Adds it to the new table
$this->addSql('ALTER TABLE app_users_profiles ADD CONSTRAINT FK_2F8B3C14F92F3E70 FOREIGN KEY (country_id) REFERENCES app_countries (id)');

Here is the output (commented) to reverse this change:

// Drop foreign key
$this->addSql('ALTER TABLE app_users_profiles DROP FOREIGN KEY FK_2F8B3C14F92F3E70');
$this->addSql('CREATE TABLE app_countrys (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(191) NOT NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
$this->addSql('DROP TABLE app_countries');
// Drop it again
$this->addSql('ALTER TABLE app_users_profiles DROP FOREIGN KEY FK_2F8B3C14F92F3E70');
// Add it to the table
$this->addSql('ALTER TABLE app_users_profiles ADD CONSTRAINT FK_2F8B3C14F92F3E70 FOREIGN KEY (country_id) REFERENCES app_countrys (id)');

[antode]

some work in progress in #2576