Fix broken migrations by “resetting” to current schema

We have taken over an old (3 years) Symfony 3.3 project using the doctrine-migrations 1.0 bundle for handling migrations. Unfortunately, the migration scripts have been broken for some time without anyone noticing, so you cannot make a fresh build from source without checking out an existing database.

I would like to fix the situation so that running composer doctrine:migrate would essentially just run a dummy script that creates what the current schema looks like. How would I do this so that when this is run on existing schemas it will not break?

I am thinking I would do something along these lines:

1. Check out a clean project (with the faulty old migrations)
2. Get hold of a production db dump
3. Dump the production db schema to a SQL file
4. Delete the old migrations
5. Create a migration file with the same version number as what is the “current” / highest number in the faulty migrations
6. Run migrate

As a last step, verify that this actually works by dropping the database, importing the production db dump and then running migrate to verify that nothing breaks. I am just not sure how to do this in context of the Doctrine package (I am new to PHP), even after consulting the docs (that does not seem to cover this situation).

Answer

I did exactly like I sketched out above, and that worked perfectly. For context, I had this set of metadata for the migrations scripts:

"SELECT * FROM migration_versions" 
┌────────────────┐
│    version     │
├────────────────┤
│ 20161112122228 │
│ 20161113143631 │
│ 20161122223420 │
│ 20161124162611 │
│ 20161128151448 │
│ 20161207194257 │
│ 20161208114104 │
│ 20161208114105 │
│ 20170123074229 │
│ 20170125081729 │
│ 20170130080734 │
│ 20170130080820 │
│ 20170131082751 │
│ 20170201074705 │
│ 20170208092040 │
│ 20170208092917 │
│ 20170208103930 │
│ 20170608042313 │
│ 20170628044258 │
│ 20170930061118 │
└────────────────┘

This means I would also have a corresponding set of files in app/DoctrineMigrations/, called stuff like app/DoctrineMigrations/Version20170930061118.php. Only the last file will end up with actual content, the rest will be empty dummies.

Git log of my changes

Create a dummy template to replace contents of existing migrations

At some point the existing migrations have stopped working. This was discovered around March 1 2020.

This means it is not possible to start developing from a fresh source, using the doctrine:migrations:migrate command, as it fails early on.

At the same time, existing databases contain metadata about which migrations have been performed in the past, so any fix would need to signal to the migrations package that these are/have been performed.

The proposed fix is to create simple “marker classes” that just fills the need for signalling that migrations have been done/exist, and then replace the contents of the last migrations class with a script that simply creates a schema matching whatever the current state of the production database is.

This is step 1.

Replace all migration script contents with empty contents

This is step 2 in the fix. Basically do a loop for every file in app/DoctrineMigrations/*.php and replace that with the dummy template. This is the full script

$ git show 773ccebee20425d7025152b338282f0a0034556f:app/DoctrineMigrations/create-dummy-migrations.sh
#!/bin/bash

for file in Version*.php; do 
    CLASS=$(basename $file .php)
    sed -e "s/CLASSNAME/$CLASS/" template.php > $file
done

This is the full template

git show 773ccebee20425d7025152b338282f0a0034556f:app/DoctrineMigrations/template.php
<?php

namespace ApplicationMigrations;

use DoctrineDBALMigrationsAbstractMigration;
use DoctrineDBALSchemaSchema;

/**
 * Dummy migration to fix faulty migration scripts issue 
 * discovered in March 2020.
 */
class CLASSNAME extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
         $this->addSql('COMMENT ON table migration_versions IS 'migrations from 2016-2017 are stubs'');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
    }
}

Remove helpers

Dump of schema from prod

Cleaned up schema using script

grep -v -- '--' production-schema-2020.sql  
  | awk 1 ORS=' ' 
  | sed -r -e 's/;s+/; /g' > cleaned.sql

Generate Doctrine PHP statements from SQL script

Script: sed -e "s/^(.*);/$this->addSql('1');/" cleaned.sql > cleaned-sql-to-php-statements.txt

Move schema statements into last migration Basically copy-pasting the contents from cleaned-sql-to-php-statements.txt into

Remove temporary files

Minor adjustments and cleanups to the generated PHP SQL statements