DEV Community

Cover image for Dev Experience: `doctrine:schema:update` still outputs sql, despite changes applied upon db
Dimitrios Desyllas
Dimitrios Desyllas

Posted on • Edited on

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

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 
Enter fullscreen mode Exit fullscreen mode

That outputed 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;

.....
Enter fullscreen mode Exit fullscreen mode

Then I tried to apply them via:

$ php bin/console doctrine:schema:update --force
Enter fullscreen mode Exit fullscreen mode

And I re-checked for changes via:

$ php bin/console doctrine:schema:update --dump-sql 
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

As not notice same sql is outputed despite being applied.

The solution

Well, I asked for help from a coleague of mine. He tried to make a migration like this:

php bin/console doctrine:migrations:diff
Enter fullscreen mode Exit fullscreen mode

But that returned the error:

The metadata storage is not up to date, please run the sync-metadata-storage command to fix this issue.
Enter fullscreen mode Exit fullscreen mode

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 i got both upon migration generation an error and why also schema:update outputes same sql was, a serverVersion option upon .env:

DATABASE_URL=mysql://xxx:yyy@127.0.0.1:3306/testtest?serverVersion=10.4
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

And set the serverVersion into mariadb-10.4.1 (yours may differ). The value is comming from the mariadb version shown upon the command above prefixed with mariadb to indicate that it is mariadb instead of mysql.

The option we chose though, was to ditch completely the serverVersion argument upon DATABASE_URL, that fixed the issue as well.

Top comments (0)