Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MariaDB 10.2.7 BC break #6565

Closed
bilhackmac opened this issue Jul 19, 2017 · 40 comments
Closed

MariaDB 10.2.7 BC break #6565

bilhackmac opened this issue Jul 19, 2017 · 40 comments
Assignees

Comments

@bilhackmac
Copy link

@bilhackmac bilhackmac commented Jul 19, 2017

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
Copy link
Member

@Ocramius Ocramius commented Jul 19, 2017

Needs a test case though...

@sagaraspl
Copy link

@sagaraspl sagaraspl commented Aug 24, 2017

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

@belgattitude
Copy link

@belgattitude belgattitude commented Aug 26, 2017

Same issue too.

@baumannsven
Copy link

@baumannsven baumannsven commented Aug 26, 2017

I have the same problem.

@lcobucci
Copy link
Member

@lcobucci lcobucci commented Aug 26, 2017

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

@sabzeta
Copy link
Contributor

@sabzeta sabzeta commented Aug 26, 2017

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
Copy link

@belgattitude belgattitude commented Aug 26, 2017

just published a P/R on doctrine/dbal#2825 with the third approach... Feel free to comment.

@belgattitude belgattitude mentioned this issue Aug 26, 2017
8 of 8 tasks complete
@mdeboer
Copy link

@mdeboer mdeboer commented Sep 6, 2017

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
Copy link
Member

@lcobucci lcobucci commented Nov 19, 2017

doctrine/dbal#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.

@lcobucci lcobucci self-assigned this Nov 19, 2017
samuelvogel pushed a commit to pickware/shopware that referenced this issue Nov 22, 2017
Attribute default models are broken otherwise, see: doctrine/orm#6565
samuelvogel pushed a commit to pickware/shopware that referenced this issue Nov 22, 2017
Attribute default models are broken otherwise, see: doctrine/orm#6565
@Bukashk0zzz
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 3, 2017

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

@belgattitude
Copy link

@belgattitude belgattitude commented Dec 3, 2017

@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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 3, 2017

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
Copy link

@belgattitude belgattitude commented Dec 3, 2017

@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
Copy link

@belgattitude belgattitude commented Dec 3, 2017

@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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 4, 2017

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

screen shot 2017-12-04 at 12 57 10

@Bukashk0zzz
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 4, 2017

@belgattitude
Copy link

@belgattitude belgattitude commented Dec 4, 2017

@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
Copy link

@belgattitude belgattitude commented Dec 4, 2017

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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 4, 2017

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
Copy link

@mdeboer mdeboer commented Dec 4, 2017

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

@belgattitude
Copy link

@belgattitude belgattitude commented Dec 4, 2017

@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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 5, 2017

@belgattitude Bukashk0zzz/dbal-test#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
Copy link

@belgattitude belgattitude commented Dec 5, 2017

@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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 5, 2017

@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
Copy link

@belgattitude belgattitude commented Dec 5, 2017

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

image

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
Copy link

@belgattitude belgattitude commented Dec 5, 2017

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 doctrine/dbal#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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 8, 2017

@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
Copy link

@mdeboer mdeboer commented Dec 8, 2017

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
Copy link

@Bukashk0zzz Bukashk0zzz commented Dec 9, 2017

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
Copy link
Member

@lcobucci lcobucci commented Dec 17, 2017

@Bukashk0zzz awesome, closing then!

@lcobucci lcobucci closed this Dec 17, 2017
shopwareBot pushed a commit to shopware/shopware that referenced this issue Jan 24, 2018
Attribute default models are broken otherwise, see: doctrine/orm#6565
fixes 1396
pascalheidmann-bedarf added a commit to pascalheidmann-bedarf/shopware that referenced this issue Feb 19, 2018
Attribute default models are broken otherwise, see: doctrine/orm#6565
fixes 1396
@musicjerm
Copy link

@musicjerm musicjerm commented Apr 6, 2018

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
Copy link

@remoteclient remoteclient commented Jun 11, 2018

I opened a new [issue]#7258 for MariaDB 10.2.14 as this issue reappears there.

@belgattitude
Copy link

@belgattitude belgattitude commented Jun 11, 2018

@remoteclient I'll reply on #7258 7258

flackovic added a commit to flackovic/pongz that referenced this issue Sep 19, 2018
Since there was server version inside config set to 5.7, migration:diss was always generating difference for some fields.
Known bug in dbal/doctrine - doctrine/orm#6565
@chrisg123
Copy link

@chrisg123 chrisg123 commented Feb 3, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
You can’t perform that action at this time.