Doctrine schema update command shows always few queries to be executed

[jigarpancholi]

I am using doctrine with symfony and run php bin/console doctrine:schema:update --force command. But it executes few queries always as following:

ALTER TABLE company CHANGE current_subscription_id current_subscription_id INT DEFAULT NULL, CHANGE city city VARCHAR(255) DEFAULT NULL, CHANGE state state VARCHAR(255) DEFAULT NULL, CHANGE zip zip VARCHAR(255) DEFAULT NULL, CHANGE country country VARCHAR(255) DEFAULT NULL, CHANGE phone phone VARCHAR(255) DEFAULT NULL, CHANGE subscription_start_date subscription_start_date DATE DEFAULT NULL, CHANGE subscription_end_date subscription_end_date DATE DEFAULT NULL;

[Ocramius]

This needs a test case to validate why this is happening.

[jigarpancholi]

@Ocramius do I need to create test case for that?
Because I am using this bundle and this happens only in one project of symfony.

[Ocramius]

@jigarpancholi yeah, it needs to be reproduced in isolation. You will need to isolate it via debugging and then either find if the problem is on your side, or whether it is a bug in the ORM.

Start by writing a test in your own application.

[jigarpancholi]

@Ocramius this problem occurs only in my local. There is no problem in server. Is there any caching issue in local or anything else?

Or can you please give me an example for test case? So that I can proceed further in debugging.

[Ocramius]

See https://github.com/doctrine/doctrine2/tree/master/tests/Doctrine/Tests/ORM/Functional/Ticket for examples on how to construct tests inside the context of the ORM project.

[jigarpancholi]

I just found same issue what I am facing here everlutionsk/email-bundle-2#13.

[apoorva-shah]

Yes @jigarpancholi I also have same issue. Did not understand what to do. @Ocramius I think thats bug in doctrine.

[Ocramius]

I think thats bug in doctrine.

That's exactly the difference between opinion and proof. Without a test, nobody can really verify nor work on the issue at all.

[stephenheron]

I think this might be the same issue as: #6565

[jigarpancholi]

Yes, this issue is only for MriaDB not for MySql.

[belgattitude]

Should be fixed with doctrine/dbal#2825

[lcobucci]

Closing as Invalid since it's DBAL issue and should be already fixed when using v2.7.x-dev version of doctrine/dbal.

[Glideh]

Should be fixed with doctrine/dbal#2825

Seems not. I was having this issue with a brand new SF4 installation (and MariaDB 10.2.14).
I upgraded dbal:

$ composer require doctrine/dbal:^2.7.1
[...]
Package operations: 0 installs, 1 update, 0 removals
  - Updating doctrine/dbal (v2.6.3 => v2.7.1): Loading from cache
[...]
Executing script cache:clear [OK]
[...]

$ composer show doctrine/dbal
[...]
versions : * 2.7.1
[...]

Checking the diffs:

$ console d:s:u --dump-sql

 The following SQL statements will be executed:

     ALTER TABLE table1 CHANGE field1 field1 INT DEFAULT NULL, CHANGE field2 field2 VARCHAR(255) DEFAULT NULL;
     ALTER TABLE table2 CHANGE field1 field1 VARCHAR(255) DEFAULT NULL, CHANGE field2 field2 SMALLINT DEFAULT NULL, CHANGE field3 field3 SMALLINT DEFAULT NULL;
     ALTER TABLE table3 CHANGE field1 field1 INT DEFAULT NULL;

Executing them:

$ console d:s:u --force

 Updating database schema...

     3 queries were executed

                                                                                                                        
 [OK] Database schema updated successfully!

Re-checking:

$ console d:s:u --dump-sql

 The following SQL statements will be executed:

     ALTER TABLE table1 CHANGE field1 field1 INT DEFAULT NULL, CHANGE field2 field2 VARCHAR(255) DEFAULT NULL;
     ALTER TABLE table2 CHANGE field1 field1 VARCHAR(255) DEFAULT NULL, CHANGE field2 field2 SMALLINT DEFAULT NULL, CHANGE field3 field3 SMALLINT DEFAULT NULL;
     ALTER TABLE table3 CHANGE field1 field1 INT DEFAULT NULL;

Those fields are indeed defined as nullable=true and NULL is indeed already allowed in the database.

[strawburrypokki]

I don't know if it is related or not, but try checking #6565.
It seems the issues is still there (#6565 (comment))

[Glideh]

Well that was my +1 on this comment you are linking :)

[strawburrypokki]

Oh, sorry! I didn't notice :)

[belgattitude]

@Glideh

Don't remember exactly but from dbal perspective I'm pretty sure it's working as intended...

In other words, using the dbal command should work

php bin/console doctrine:migrations:diff

For the symfony part, I cannot tell... But one thing I can think of, is:

If you are NOT relying on server version autodetection, and have server_version param set somewhere in your symfonoy app. THEN be sure you set it greater than 10.2.7 (please include the bugfix number '.14' in your example or greater) or remove the key from your config.

This should do the trick.... but I cannot help from symfony side, never had the chance to use it.

PS: The information change have appeared in 10.2.7 (10.2.6 was already GA)... I've made the mariadb 10.2: doctrine/dbal#2825, but from the discussion it was important to make it 10.2.7 instead of 10.2... Not that I'm happy with it

[Glideh]

I'm actually using migrations but giving the example with doctrine:schema:update (d:s:u) to simplify (which is used by migration:diff behind the scenes isn't it ?).

Each doctrine:migrations:diff will always add the DEFAULT NULL alterations.

My server version was initially set to 10.2, I had tried removing it before without any effect, I just tried setting it to 10.2.14 with no more effect (container rebuilt to make sure it is not a cache issue)

[belgattitude]

@Glideh

just to be se sure... I didn't understand:

I just tried setting it to 10.2.14 with no more effect (container rebuilt to make sure it is not a cache issue)

Means the issue is fixed or not ?

[belgattitude]

@Glideh, if the issue is not fixed with the server version...

Can you copy/paste your example entities ? So I can try too... because I still cannot reproduce with mine.

Thank you

[belgattitude]

@Glideh

I'm actually using migrations but giving the example with doctrine:schema:update (d:s:u) to simplify (which is used by migration:diff behind the scenes isn't it ?).

I think yes but I don't know details, I'm using:

 ./vendor/bin/doctrine orm:schema-tool:update --dump-sql

[Glideh]

Ok I'll try to make a reproducible example

[belgattitude]

@Glideh don't worry about giving something perfect, I'll handle... you can just provide what you used for your examples

ALTER TABLE table1 CHANGE field1 field1 INT DEFAULT NULL, CHANGE field2 field2 VARCHAR(255) DEFAULT NULL;
     ALTER TABLE table2 CHANGE field1 field1 VARCHAR(255) DEFAULT NULL, CHANGE field2 field2 SMALLINT DEFAULT NULL, CHANGE field3 field3 SMALLINT DEFAULT NULL;
     ALTER TABLE table3 CHANGE field1 field1 INT DEFAULT NULL;

It's fine for me

[Glideh]

I wanted to give you a nice docker setup but I didn't manage to do it quickly.
So here is an entity that causes a systematic diff.

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 */
class Question
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     */
    private $label;

    /**
     * @ORM\Column(type="string", nullable=true)
     */
    private $complement;
}

The diff is only in the nullable field

ALTER TABLE question CHANGE complement complement VARCHAR(255) DEFAULT NULL;

For info my doctrine versions requirements:

"doctrine/doctrine-bundle": "^1.9",
"doctrine/doctrine-migrations-bundle": "^1.3",
"doctrine/orm": "^2.6",

[belgattitude]

Still cannot reproduce... but I feel we can sort things out ;)

On my setup, running ./vendor/bin/doctrine orm:schema-tool:update --dump-sql, gives

CREATE TABLE Question (id INT AUTO_INCREMENT NOT NULL, label VARCHAR(255) NOT NULL, complement VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

Then I run ./vendor/bin/doctrine orm:schema-tool:update --force... All ok, from here I have a schema with the latests changes.

Running migrations gives nothing (my schema is up to date as expected), for example:

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

To be exactly sure... I've setup a travis build to check the behaviour.. See https://travis-ci.org/belgattitude/openstore-schema-core and the used travis file https://github.com/belgattitude/openstore-schema-core/blob/master/.travis.yml.

So on my side, I cannot reproduce with my setup. So to exclude more case, can you run the following sql

SELECT `TABLE_SCHEMA`, 
       `TABLE_NAME`, 
       `COLUMN_NAME`, 
       `COLUMN_DEFAULT`, 
       `IS_NULLABLE`, 
       `DATA_TYPE`, 
       `CHARACTER_MAXIMUM_LENGTH`,
       `CHARACTER_SET_NAME`, 
       `COLLATION_NAME` 
FROM `information_schema`.`COLUMNS` 
WHERE `TABLE_NAME` = 'Question'
ORDER BY `ORDINAL_POSITION`  ASC

For my setup it gives

| mariadb_doctrine_test | Question | id | NULL | NO | int | NULL | NULL | NULL |
| mariadb_doctrine_test | Question | label | NULL | NO | varchar | 255 | utf8mb4 | utf8mb4_unicode_ci |
| mariadb_doctrine_test | Question | complement | NULL | YES | varchar | 255 | utf8mb4 | utf8mb4_unicode_ci |

What's your output ?

PS: if you're wondering I've put Question.php entity in https://github.com/belgattitude/openstore-schema-core/tree/master/tests/entity

[Glideh]

Already done in my first comment

I upgraded dbal:

$ composer require doctrine/dbal:^2.7.1

Same result

[belgattitude]

Hey @Glideh,

just noticed from your sql output that 'question' table is without uppercase:

| mytest | question | id | NULL | NO | int | NULL | NULL | NULL |

Not sure, but can you try to add with capitalization:

/**
 * @ORM\Entity
 * @ORM\Table(
 *   name="Question"
 * )
 */

[belgattitude]

Alos noticed a difference, probably not important: but let's try...

Looking at your output:

| mytest | question | id_________| NULL | NO_________| int___ | NULL | NULL | NULL |
| mytest | question | label______ | NULL | NO___| NULL | varchar | 255_ | utf8_ | utf8_unicode_ci |
| mytest | question | complement | NULL | NULL_| YES_ | varchar | 255_ | utf8_ | utf8_unicode_ci |

The fifth column seems weird to me, the first row have 9 columns, the next ones have 10 cols... Can you recheck the output of the SQL ?

[Glideh]

Ho but I'm noticing something, when I copy/pasted the query result from my db client, I reworked it manually (which also explains the missing pipe) to replace the empty spaces by NULL but I didn't see there was already some NULL values. Let's do a screenshot to illustrate.

I added a nullable int theme_id to compare. The different NULL and <null> values seems especially weird to me on this field, isn't it ?

[belgattitude]

no it's the way new Mariadb treats null. got to go now but I'll look deeper on Sunday our Monday. thanks for the screenshot

[belgattitude]

maybe try to set the charset in the entity. check an example in the openstore-schema-core repo. I'm on mobile., ill let you find the link 😀I'm not sure the problem comes from null default

[Glideh]

My Doctrine diffs always concern exclusively nullable fields.
My whole model currently contains about 50 fields, I have 12 nullable, they are all appearing in the diff.
I'll try to set the charset on an entity.

[belgattitude]

yes please try. even if it's not working I can look somewhere else.

[belgattitude]

@Glideh I'm back on it... Sorry for redundancy but I try to put the pieces together. So as far as I found , here's some related issues that I'll link here:

• Stylus had fixed it, by removing server_version (Problem with migration (doctrine:migrations:diff not work!) Sylius/Sylius#8537 (comment)). That's what I was expecting.
• @musicjerm still reports the same issue than yours (MariaDB 10.2.7 BC break #6565 (comment))
• Just added some explanations about server_version in mariadb: Don't use the server_version option to define the vendor name dbal#3110 (comment) and saw an update on symfony doc (https://github.com/symfony/symfony-docs/pull/9547/files) that might help about defining server_version

For now I'm still unable to reproduce your case... for reference see
https://github.com/belgattitude/openstore-schema-core/blob/master/.travis.yml
https://github.com/belgattitude/openstore-schema-core/tree/master/tests/entity
https://travis-ci.org/belgattitude/openstore-schema-core

Would be really nice when you have time to retest with this updated entity:

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(
 *   name="question",
 *   options={
 *     "charset"="utf8mb4",
 *     "collate"="utf8mb4_unicode_ci"
 *   }
 * )
 */
class Question
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;
    /**
     * @ORM\Column(type="string")
     */
    private $label;
    /**
     * @ORM\Column(type="string", nullable=true)
     */
    private $complement;
}

I would like to exclude possible charsets or mariadb configuration differences... It might not help but good for me.

In the meantime, I'll try to test with symfony with the test repo created by @Bukashk0zzz. As I'm not used to symfony any help is appreciated (https://github.com/belgattitude/dbal-test). /ping @musicgerm

Also would be nice to have your doctrine.yaml config

[belgattitude]

@Glideh sorry for long texts :)

Just tested with symfony:
https://github.com/belgattitude/dbal-test/tree/doctrine2/6790
And seems it works too:
https://travis-ci.org/belgattitude/dbal-test

If you see anything in the config that can explain let me know.

[Glideh]

Thank you for your time @belgattitude, I'll check that and keep you in touch as soon as I find some.

[belgattitude]

@Glideh , just a free thought... do you use/have migrations classes ? I can imagine problems with it... If it's that, I think I can look into this... All the best

[Glideh]

I have a migration class, but I also tried without, same result.

[belgattitude]

It would have been nice ;) Anyway, we'll figure it out. In case, you can always test with https://github.com/belgattitude/dbal-test/tree/doctrine2/6790... You don't need docker (./up.sh), just clone and checkout branch (git checkout -b doctrine2/6790), composer install and set the correct db credentials in .env... From there you can add new entities and use the doctrine commands as usual.

[Glideh]

Nice setup, thank you.
I always use composer from within a container so I don't have it installed in my computers (same for php).
I have Docker on all of them though (If I could have it in my phone and tablets I would be happy :), so I'm using your up.sh.
jakubsacha/symfony-docker image is taking some time (I have a bad connection), I used the mariadb:10.2 I already had though (10.2.14) instead of 10.3 which you had.

[Glideh]

Composer seems included into the image but not git, so I can't install the dependencies with it.
Also up.sh complains about /var/www/html/var not existing when running chmod.

[belgattitude]

Possible, I haven't tested with docker... but if you have a working docker image running, just clone into it for a test.

[belgattitude]

@Glideh I know it's long time... But I got a possible answer.

It should work if you set server_version: 'mariadb-10.2.15' (with mariadb- prefix) or if you don't specify server_version at all (in this case it will be autodetected)

See #7258 (comment)

[Glideh]

Thx, I'll try that

[justinas-kazanavicius]

@belgattitude It works for my application! Thanks!

[4cc355-d3n13d]

Seen the same on mysql db, few projects!

[4cc355-d3n13d]

One of the queries, executes every run of schema update:
ALTER TABLE meta_currency CHANGE code code VARCHAR(6) NOT NULL;

<?php

namespace AD\Doctrine\Model;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="currency", options={"collate"="utf8mb4_general_ci", "charset"="utf8mb4"})
 *
 * @method string getCode()
 * @method string getTitle()
 * @method string getSymbol()
 * @method string getNative()
 * @method string getThousandsSep()
 * @method string getDecimalSep()
 * @method string getSymbolLeft()
 * @method string getSpaceBetween()
 * @method string getExp()
 * @method string getMinAmount()
 * @method string getMaxAmount()
 * @method string getAvailable()
 */
class Currency extends AbstractModel
{
    /**
     * @var string
     * @ORM\Id
     * @ORM\Column(name="code", type="string", length=6)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $code;

    /**
     * @var string|null
     * @ORM\Column(name="title", type="string", length=255, nullable=true)
     */
    protected $title;

    /**
     * @var string|null
     * @ORM\Column(name="symbol", type="string", length=6, nullable=true)
     */
    protected $symbol;

    /**
     * @var string|null
     * @ORM\Column(name="native", type="string", length=12, nullable=true)
     */
    protected $native;

    /**
     * @var string|null
     * @ORM\Column(name="thousands_sep", type="string", length=3, nullable=true)
     */
    protected $thousandsSep;

    /**
     * @var string|null
     * @ORM\Column(name="decimal_sep", type="string", length=3, nullable=true)
     */
    protected $decimalSep;

    /**
     * @var bool|null
     * @ORM\Column(name="symbol_left", type="boolean", nullable=true)
     */
    protected $symbolLeft;

    /**
     * @var bool|null
     * @ORM\Column(name="space_between", type="boolean", nullable=true)
     */
    protected $spaceBetween;

    /**
     * @var int|null
     * @ORM\Column(name="exp", type="integer", nullable=true)
     */
    protected $exp;

    /**
     * @var int|null
     * @ORM\Column(name="min_amount", type="integer", nullable=true, options={"unsigned"=true})
     */
    protected $minAmount;

    /**
     * @var int|null
     * @ORM\Column(name="max_amount", type="integer", nullable=true, options={"unsigned"=true})
     */
    protected $maxAmount;

    /**
     * @var bool|null
     * @ORM\Column(name="available", type="boolean", nullable=true)
     */
    protected $available = '0';
}

[JKetelaar]

Just had the same issue, my way to solve this was setting the server version in config/packages/doctrine.yml.

From

        server_version: '5.7'

To

        server_version: 'mariadb-10.2.7'