Sarcastic Summary: The Complete Migrate Disaster
Today, I was attempting to deploy a web application on a team that utilizes diffs instead of migrations. As a result, I encountered a series of unexpected queries and errors during the schema update process. The initial output of the php bin/console doctrine:schema:update --dump-sql command was ~93 queries, which seemed excessive given the nature of the changes being applied to the database tables.
Upon further investigation, I discovered that the original schema update command used the same logic for both doctrine:schema:update and doctrine:migrations:diff to generate migrations. This led to the same error message being returned when attempting to run the php bin/console doctrine:migrations:diff command, as the metadata storage was not up to date.
The migration generation process exposed this issue, as both commands use the same logic for generating migrations. To fix this, I decided to investigate the DATABASE_URL environment variable, which contained the database server version. The team had set this variable to mariadb-10.4.1, as
The problem
Today I was deploying an app, team uses diffs instead of migration.
Therefore, I saw what changes upon db needs to be applied via:
$ php bin/console doctrine:schema:update --dump-sql
That ouitputed me ~93 queries (well lots of changes needed to be applied upon db):
ALTER TABLE xxx1 CHANGE expires\_at expires\_at DATETIME DEFAULT NULL, CHANGE created\_at created\_at DATETIME DEFAULT NULL, CHANGE updated\_at updated\_at DATETIME DEFAULT NULL;
ALTER TABLE xx2 CHANGE name name VARCHAR(255) DEFAULT NULL, CHANGE created\_at created\_at DATETIME DEFAULT NULL, CHANGE updated\_at updated\_at DATETIME DEFAULT NULL, CHANGE unique\_identifier unique\_identifier VARCHAR(255) DEFAULT NULL;
ALTER TABLE xx3 CHANGE description description VARCHAR(255) DEFAULT NULL, CHANGE created\_at created\_at DATETIME DEFAULT NULL, CHANGE updated\_at updated\_at DATETIME DEFAULT NULL, CHANGE deleted\_at deleted\_at DATETIME DEFAULT NULL, CHANGE unique\_identifier unique\_identifier VARCHAR(255) DEFAULT NULL;
.....
Then I tried to apply them via:
$ php bin/console doctrine:schema:update --force
And I re-checked for changes via:
$ php bin/console doctrine:schema:update --dump-sql
And still outputed:
ALTER TABLE xxx1 CHANGE expires\_at expires\_at DATETIME DEFAULT NULL, CHANGE created\_at created\_at DATETIME DEFAULT NULL, CHANGE updated\_at updated\_at DATETIME DEFAULT NULL;
ALTER TABLE xx2 CHANGE name name VARCHAR(255) DEFAULT NULL, CHANGE created\_at created\_at DATETIME DEFAULT NULL, CHANGE updated\_at updated\_at DATETIME DEFAULT NULL, CHANGE unique\_identifier unique\_identifier VARCHAR(255) DEFAULT NULL;
ALTER TABLE xx3 CHANGE description description VARCHAR(255) DEFAULT NULL, CHANGE created\_at created\_at DATETIME DEFAULT NULL, CHANGE updated\_at updated\_at DATETIME DEFAULT NULL, CHANGE deleted\_at deleted\_at DATETIME DEFAULT NULL, CHANGE unique\_identifier unique\_identifier VARCHAR(255) DEFAULT NULL;
As not notice same sql is outputed to be applied.
The solution
Well, I asked for helpo from a coleague of mine. He tried to make a migration like this:
php bin/console doctrine:migrations:diff
But that returned the error:
The metadata storage is not up to date, please run the sync-metadata-storage command to fix this issue.
The rationale behind this is because both doctrine:migrations:diff and doctrine:schema:update use same logic for diffs. Migration generation though also exposes any error as well.
The reason why is because upon .env I had:
DATABASE_URL=mysql://xxx:yyy@127.0.0.1:3306/testtest?serverVersion=10.4
We use mariadb for db and as you notice above serverVersion contains the mariadb version. The soplution is either get the correct version (incl minor one) from:
mysql --version
and set the serverVersion into mariadb-10.4.1.
The option we chose was to ditch completely the serverVersion argument upon DATABASE_URL, that fixed the issue.
