Orm: MariaDB 10.2.7 BC break

[bilhackmac]

MariaDB 10.2.7 and future releases (5.5, 10.0 and 10.1 branches are not affected) just resolve an old bug in SQL standards implementation and introduce a BC break

Ticket that explains changes https://jira.mariadb.org/browse/MDEV-13132
Ticket that confirms (in comments) only >= v10.2.7 are affected https://jira.mariadb.org/browse/MDEV-13341

Concretely, for Doctrine, schema update just gone crazy and want to update every column with _NULL_ default because 'NULL' is return by MariaDB instead of _NULL_.
I don't find any other issues… for now.

For the moment, MySQL don't have the same issue.

[Ocramius]

Needs a test case though...

[sagaraspl]

Hi @bilhackmac and @Ocramius I also faced the same issue when I updated with MariaDB 10.2.7

[belgattitude]

Same issue too.

[baumannsven]

I have the same problem.

[lcobucci]

@sabzeta @belgattitude @baumannsven as @Ocramius said, please send us a PR with tests. That helps A LOT 😉

[sabzeta]

I haven't realy ran into this problem, but it's good to be aware. I'll try to look into it.
Meanwhile, perhaps @sagaraspl could send a PR with tests?

[belgattitude]

just published a P/R on https://github.com/doctrine/dbal/pull/2825 with the third approach... Feel free to comment.

[mdeboer]

Same problem here, narrowed it down to it being a NULL problem of some sort as removing all nullable relations and fields solved it. That combined with me recently pulling the newest mariadb image for Docker explains it all.

There went another 3/4 hours of my day trying to find an error in my code even though the mapping files validated fine 😭 Using the mysql:5 image now which should fix things, fingers crossed 🤞 .

Hopefully a fix will be merged soon!

[lcobucci]

https://github.com/doctrine/dbal/pull/2825 has just been merged and it will be released on DBAL v2.7.0, please test if your issue is fixed when using "doctrine/dbal": "2.7.x-dev@dev" and let us know.

[Bukashk0zzz]

With "doctrine/dbal": "2.7.x-dev@dev" issue steel exist

[belgattitude]

@Bukashk0zzz

Can you confirm, I don't have it anymore: My composer:

    "require": {
        "php": "^7.1",
        "doctrine/dbal": "2.7.x-dev as 2.6.4",
        "doctrine/common": "^2.7.0",
        "doctrine/orm": "^2.5.0",
        "gedmo/doctrine-extensions": "^2.4.28",
        "doctrine/data-fixtures": "^1.2.2",
        "symfony/console": "~2.6|~3.0|~4.0"
},

[Bukashk0zzz]

Tested with mariadb 10.2.11 and 10.3.1 official docker image.

in composer

        "symfony/orm-pack": "^1.0",
        "doctrine/dbal": "2.7.x-dev as 2.6.4",

Always get diff with null on every field.
On percona 5.7 and standard mysql 5.7 all works correct.

[belgattitude]

@Bukashk0zzz ,

Weird, in my case, testing on 10.2.11-MariaDB-10.2.11+maria~xenial-log mariadb.org binary distribution works. As a simple reproducible test, I've just run a create update create/update based on openstore-schema-core project. (you can clone it if you like to test)

$ ./vendor/bin/doctrine orm:schema-tool:create 
ATTENTION: This operation should not be executed in a production environment.
Creating database schema...
Database schema created successfully!

Followed by an update:

$ ./vendor/bin/doctrine orm:schema-tool:update --dump-sql
Nothing to update - your database is already in sync with the current entity metadata.

So it's working fine.

I'm not aware of symfony/orm-pack but I guess it's not the problem. Could you confirm the latest 2.7 master have been fetched.... You can easily see by doing

$ ls -la vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/Keywords

If there's a file named MariaDb102Keywords.php you should have the latest one. Otherwise it might be a composer problem.

Let me know. Thanks

[belgattitude]

@Bukashk0zzz,

Have you recreated the database before testing ? It's possible you've already applied the migration with 'NULLs' prior to the patch (so your default values have been broken). Can be an possibility too

[Bukashk0zzz]

Made clear demo example https://github.com/Bukashk0zzz/dbal-test

With code in repo above I every time get diff. You can check it by yourself

[Bukashk0zzz]

I have one entity https://github.com/Bukashk0zzz/dbal-test/blob/master/src/Entity/Admin.php

And one init migration https://github.com/Bukashk0zzz/dbal-test/blob/master/src/Migrations/Version20171204100535.php

How I run project https://github.com/Bukashk0zzz/dbal-test/blob/master/up.sh

And after running I execute doctrine:migrations:diff or doctrine:schema:update

[belgattitude]

@Bukashk0zzz

Can you check by commenting server_version: '5.7' in config/packages/doctrine.yaml ?

Like this:

doctrine:
    dbal:
        # configure these for your database server
        driver: 'pdo_mysql'
        # server_version: '5.7'
        charset: utf8mb4

        # With Symfony 3.3, remove the `resolve:` prefix
        url: '%env(resolve:DATABASE_URL)%'

[belgattitude]

With the commented server_version, everything looks good so far. Am I missing something ?

[Bukashk0zzz]

Still has problem with commented server_version: '5.7'

At your screenshot you just run migrations. Try run now for getting diff:

bin/console doctrine:migrations:diff
or
bin/console doctrine:schema:update --dump-sql

[belgattitude]

@Bukashk0zzz ...

Thanks, I can reproduce now... Could you help me to determine where the problem is ? I've just checked your entity, could you remove unsigned if you're using booleans ?

    /**
     * @var bool
     *
     * @ORM\Column(name="status", type="boolean", nullable=false, options={"default": true})
     */
    private $status = true;
 ```

And test with doctrine commands:

```shell
$ php bin/console doctrine:database:drop -n --force; php bin/console doctrine:database:create -n; php bin/console doctrine:schema:create -n
$ php bin/console doctrine:schema:update --dump-sql

It looks working... Either you change the status to integer or if you're using boolean remove "unsigned"="true".

Should be the same using migrations commands too.

Could you confirm you don't have migrations using mysql-5.7 ? So I can look deeper

[belgattitude]

Oups, just installed mysql-5.7... So with your entity, looks there's a diff generated as well:

$ rm -f src/Migrations/*.php
$ php bin/console doctrine:database:drop -n --force; php bin/console doctrine:database:create; php bin/console doctrine:schema:create -n
$ php bin/console doctrine:migrations:diff
# Generated new migration class to "/web/www/dbal-test/src/Migrations/Version20171204125419.php" from schema differences.

So I guess the same behaviour is for MySQL 5.7 too. So the way to fix is to fix your entity. Is there anything I missed ?

[Bukashk0zzz]

Updated repo. Now boolean not unsigned.

Still get diff:

ALTER TABLE admins CHANGE login login VARCHAR(255) DEFAULT NULL, CHANGE password password VARCHAR(255) DEFAULT NULL;

Also on percona and official mysql all works correct.

[belgattitude]

@Bukashk0zzz

Looks you've forgot to remove 'server_version' ? I've made a p/r https://github.com/Bukashk0zzz/dbal-test/pull/1, have a look:

Could you test by doing (exactly) this:

$ composer update
$ rm -f src/Migrations/*.php
$ php bin/console doctrine:database:drop -n --force; php bin/console doctrine:database:create; php bin/console doctrine:schema:create -n
$ php bin/console doctrine:migrations:diff

What I have is

# No changes detected in your mapping information.

[belgattitude]

Also note in your symfony.lock:

    "doctrine/dbal": {
        "version": "v2.6.3"
    },

Not sure exactly how it can affect.... but it could be the problem. To be sure check:

$ ls -la vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/Keywords

If you're on the good doctrine version, you should have a MariaDb102Keywords.php file in it.

[Bukashk0zzz]

Commenting 5.7 helped
Now all works correct. So there was two problems:

• server_version: '5.7' in config
• unsigned bool in mapping

Add travis check for this case https://travis-ci.org/Bukashk0zzz/dbal-test/builds/311377116

[mdeboer]

Glad it's fixed (by the looks of it), when will this make it to master?

[belgattitude]

@Bukashk0zzz, happy for you. server_version will bypass database detection, that explains. For the other one: unsigned bool in mapping, unsigned boolean (really tinyint(4)) does not play well with schema manager (same applies for Mysql 5.7, could you confirm ?).

@mdeboer Don't know when they are able to release, but in the meantime, please be welcome to test ;)

[Bukashk0zzz]

@belgattitude https://github.com/Bukashk0zzz/dbal-test/pull/2 as you can see mysql 5.7 have not any issue with unsigned bool. There are problems only with mariaDB

Check travis buid https://travis-ci.org/Bukashk0zzz/dbal-test/builds/311772602

You can create pull-request to this repo and test any cases you want.

[belgattitude]

@Bukashk0zzz

as you can see mysql 5.7 have not any issue with unsigned bool

Nope ! Your travis build show there's no migration, right ! But your entity does not have unsigned:true. So it's meaningless... (see https://github.com/Bukashk0zzz/dbal-test/blob/905ad233d52747e269ea76658e786a974ff971fc/src/Entity/Admin.php#L41)

To figure out, please test with 'unsigned:true' AND mysql5-7 (both conditions, don't forget to remove existing migrations rm -f src/Migrations/*.php if needed).

Anyway I just did the test on my laptop with mysql-5.7. And I can confirm, there's a migration happening with unsigned bool on mysql5.7 too.

Thanks

[Bukashk0zzz]

@belgattitude You look at master not at PR and branch mysql

See PR files
https://github.com/Bukashk0zzz/dbal-test/pull/2/files

There are UNSIGNED in entity and in migration.

[belgattitude]

Ok, I still don't get it. Cannot reproduce but we are using 2 different ways to tests. I cannot help with doctrine:migrations... we need to start from dbal. Can you modify your travis scripts:

https://github.com/Bukashk0zzz/dbal-test/blob/b4dadcaa4672a314a40205368e798f523849b123/.travis.yml#L21

And only test with doctrine/dbal commands:

  - php bin/console doctrine:database:drop -n --force
  - php bin/console doctrine:database:create -n
  - php bin/console doctrine:schema:create -n
  - php bin/console doctrine:schema:update --dump-sql
  - php bin/console doctrine:schema:validate -n

This is what I have both unsigned:true and mysql 5.7

I'm not using doctrine:migrations, that why I've always removed rm -f src/Migrations/*.php just to be sure we work on the same schema specs. If the test above works for you, can you track it down to see where the migration process fails ?

[belgattitude]

For extended info, look what doctrine creates with unsigned: bools on mysql-5.7 or mariadb-10.2:

> php bin/console doctrine:schema:create --dump-sql

Here's the output (removed some columns for clarity):

CREATE TABLE admins (id INT UNSIGNED AUTO_INCREMENT NOT NULL,  status TINYINT(1) DEFAULT '1' NOT NULL, PRIMARY KEY(id))

It looks it didn't set the unsigned while creating...

But from schema introspection (schema:update) it always trigger a diff if using unsigned. DBAL seems to alias BOOL to TINYINT(1) and seems to remove unsigned property (mysql or mariadb). I don't think https://github.com/doctrine/dbal/pull/2825/ have changed that.

My question now is more about how 'doctrine:migrations' handles that. Can you eventually see if there's differences in generated migrations for both databases ? It can help me to track down the problem

Thanks

[Bukashk0zzz]

@belgattitude You correct and on mysql and on mariadb with unsigned bool without my init migration always getting diff. Migrations generated by mysql and mariadb identical.

Answering on question how I get this migration:
I have already old project on ZF 1 and rewiring it to symfony 4 I just copy create table syntax from existing mysql db and set it to initial migration.

I agree with you that this not related to issue #2825

But what you think this are issue that dbal not correct handle unsigned bool ?
If yes I will create new issue. If no ok. Anyway I already fixed problem on my project.

travis test for mariadb https://travis-ci.org/Bukashk0zzz/dbal-test/builds/313452048

travis test for mysql https://travis-ci.org/Bukashk0zzz/dbal-test/builds/313450766

[mdeboer]

Why on earth would you use an unsigned bool anyway? Unless it's part of a bigger issue I don't see why this has priority to get fixed.

[Bukashk0zzz]

Why on earth would you use an unsigned bool anyway?

@mdeboer But why not? Near 6-7 years ago I always put unsigned for every integer that will be only with +
And that was good practice. Now I am using doctrine for a while and don't really look what created in db because all this things hided.

Anyway I agree that this not an issue for now. Thats why I asked and not created issue for that.
Also I think this issue can be closed @belgattitude tested everything.

[lcobucci]

@Bukashk0zzz awesome, closing then!

[musicjerm]

This issue has resurfaced for me with a clean symfony/website-skeleton install. Running MariaDB 10.2.14 and doctrine/dbal v2.7.0. I compared against a fresh symfony-standard-edition connected to the same database. Verified doctrine/* versions are the same. Set up a simple entity to map with one field nullable=true and I get the issue on the symfony 4.0 install but not the 3.4. Anyone else able to confirm my insanity?

[remoteclient]

I opened a new [issue]https://github.com/doctrine/doctrine2/issues/7258 for MariaDB 10.2.14 as this issue reappears there.

[belgattitude]

@remoteclient I'll reply on #7258 7258

[chrisg123]

Faced the same issue. Adding comment for searchability.
doctrine:schema:validate fails

The database schema is not in sync with the current mapping file.

Symfony Version 4.2.2
using:

"symfony/orm-pack": "^1.0"

Fix:

composer remove symfony/orm-pack
composer require doctrine
composer update

This solution worked for me. I suspect the problem had to do with a custom doctrine type I am using but cannot confirm. Only the entities that used this custom type were causing the issue, hence my suspicion.

First I tried updating doctrine/dbal. That did not work. I had to remove symfony/orm-pack then install doctrine to resolve.