Dev Experience: `doctrine:schema:update` still outputs sql, despite changes applied upon db

Avatar photoByAdil

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.

Similar Posts