Doctrine Migration in upgrade to 5.1.0 fails

[benr77]

I upgrade to 5.1 with composer update and then I see the message to run the Doctrine migrations command.

When I run this command, it fails.

Details

Question	Answer
Relevant Bolt Version	5.0
Install type	Composer install
BC Break
PHP version	8.1
Web server	Nginx

Reproduction

bin/console doctrine:migrations:migrate

 WARNING! You are about to execute a migration in database "bolt" that could result in schema changes and data loss. Are you sure you wish to continue? (yes/no) [yes]:
 > 

[notice] Migrating up to Bolt\DoctrineMigrations\Version20211123103530
[error] Migration Bolt\DoctrineMigrations\Version20211123103530 failed during Execution. Error: "An exception occurred while executing 'DROP INDEX UNIQ_8B90D313A76ED395 ON bolt_user_auth_token':

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'UNIQ_8B90D313A76ED395': needed in a foreign key constraint"

In AbstractMySQLDriver.php line 128:
                                                                                                                    
  An exception occurred while executing 'DROP INDEX UNIQ_8B90D313A76ED395 ON bolt_user_auth_token':                 
                                                                                                                    
  SQLSTATE[HY000]: General error: 1553 Cannot drop index 'UNIQ_8B90D313A76ED395': needed in a foreign key constrai  
  nt                                                                                                                
                                                                                                                    

In Exception.php line 18:
                                                                                                                    
  SQLSTATE[HY000]: General error: 1553 Cannot drop index 'UNIQ_8B90D313A76ED395': needed in a foreign key constrai  
  nt                                                                                                                
                                                                                                                    

In PDOConnection.php line 141:
                                                                                                                    
  SQLSTATE[HY000]: General error: 1553 Cannot drop index 'UNIQ_8B90D313A76ED395': needed in a foreign key constrai  
  nt                                                                                                                
                                                                                                                    

doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration CONFIGURATION] [--em EM] [--conn CONN] [--] [<version>]

[bobdenotter]

Hmm, we'll take a look into this.. For now, you could try running:

bin/console doctrine:schema:update --force

(disclaimer: I've done this a million times, and never broke my DB, but i'm still obligated to tell you to make a backup first. ;-) )

[bobvandevijver]

I have the same migration issue.

You should make sure to check what is being done by the update command by running bin/console doctrine:schema:update --dump-sql first.

This is the query that is causing issues:

ALTER TABLE bolt_user_auth_token DROP INDEX UNIQ_8B90D313A76ED395, ADD INDEX IDX_8B90D313A76ED395 (user_id);

[benr77]

I can confirm that running doctrine:schema:update --force followed by doctrine:migrations:migrate works correctly.

[Sander-Hub]

When testing for a colleague today, ran in the same issue, can also confirm running doctrine:schema:update --force and after that doctrine:migrations:migrate fixes the issue

[bobvandevijver]

This actually means that the migrations are not used for their full potential. @bobdenotter is there a reason for this? Why not use the migration for the actual database schema changes of Bolt itself?

[bobdenotter]

@bobvandevijver Well, the intent was to have the migration take care of this. I think the problem we're facing is that Migrations can be generated only for specific platforms, which makes it hard to support MySQL, MariaDB, Sqlite and Postgres alike. If you have suggestions, i'm all ears. :-)

[bobvandevijver]

Alright, that is actually a good one! The issue here was (or at least in my case) that most people might have already been lagging behind: I got a couple of table updates when running the dump-sql version, more that described in the migration discussed here.

It should be possible to leverage $this->connection->getDatabasePlatform() in the migration to detect the platform being used, and then only add the migration relevant for that specific platform. Or, generate the migrations for each platform, place them in separate folder and register them dynamically based on the configured platform. Both will require the developer to generate all migrations for each platform though, but it should be possible to support them using pipeline checks that can validate the database state for the supported platform. We use the following sequence to test for example:

    - php bin/console doctrine:schema:validate --skip-sync
    - php bin/console doctrine:migrations:migrate
    - php bin/console doctrine:schema:update --dump-sql
    - php bin/console doctrine:schema:validate --skip-mapping

It

1. Validates the current mapping configuration
2. Executes all migrations to check if they work
3. Lists whatever might be missing from the database schema
4. Checks whether the database is in sync with the mapping, returning a non-zero exit code when that is not the case

[bobdenotter]

$this->connection->getDatabasePlatform()

That works, but be aware that it's getting removed for reasons i don't understand, and we've not been able to convey our usecase to the Doctrine people: https://github.com/doctrine/dbal/issues/5026