Dbal: Unsigned numeric columns not generated correctly

[succinct]

I am using Doctrine 2.5.4 (under a Symfony 2.8.4 project) and when I create the database and schema, then run a schema validation, it is out of sync due to unsigned declarations. For example, I have the following columns defined in my Place entity:

    /**
     * @var float
     *
     * @ORM\Column(name="score", type="decimal", precision=6, scale=5, nullable=true, options={"unsigned":true})
     */
    protected $score;

    /**
     * @var float
     *
     * @ORM\Column(name="debatability", type="decimal", precision=12, scale=2, nullable=true, options={"unsigned":true})
     */
    protected $debatability;

Immediately after generating the schema, I run doctrine:schema:update --dump-sql from the Symfony console, and receive the following output:

ALTER TABLE place CHANGE score score NUMERIC(6, 5) DEFAULT NULL, CHANGE debatability debatability NUMERIC(12, 2) DEFAULT NULL;

It seems that the schema update recognizes that it is out of sync - the only difference being that it should be unsigned, but is not - and yet the query generated does not include the UNSIGNED keyword. As should probably be expected, running the ALTER TABLE query and then checking the schema again results in the exact same query.

What am I missing here?

EDIT: This problem is also occurring on BOOLEAN columns, but that's mainly a stylistic complaint on my part and wouldn't impact me overall.

[Ocramius]

@succinct what DB is this affecting? Can you reproduce this in the functional test suite of the DBAL?

[succinct]

This is in a MySQL DB. I can't find any documentation on creating a functional test for this, but I'd be happy to if you can point me to a good starting place.

[sakaristenudd]

According to https://github.com/doctrine/doctrine2/issues/4554 this is fixed but seems not to be in the 2.5.4 release.

[succinct]

@sakaristenudd thanks for the heads-up, good to know that a fix is potentially soon to come. I didn't mention in the original post, but we are also having this problem on boolean fields.

[succinct]

I submitted a pull request to fix the issue (if you want to call it that) with unsigned booleans: doctrine/dbal#2385

After applying this patch and the one mentioned by @sakaristenudd to my environment, all schema generation is working as expected!

[succinct]

It appears that v2.5.4 does not include the fix, but master does, as seen in https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php

    /**
     * {@inheritdoc}
     */
    public function getFloatDeclarationSQL(array $field)
    {
        return 'DOUBLE PRECISION' . $this->getUnsignedDeclaration($field);
    }
    /**
     * {@inheritdoc}
     */
    public function getDecimalTypeDeclarationSQL(array $columnDef)
    {
        return parent::getDecimalTypeDeclarationSQL($columnDef) . $this->getUnsignedDeclaration($columnDef);
    }
    /**
     * Get unsigned declaration for a column.
     *
     * @param array $columnDef
     *
     * @return string
     */
    private function getUnsignedDeclaration(array $columnDef)
    {
        return ! empty($columnDef['unsigned']) ? ' UNSIGNED' : '';
    }
    /**
     * {@inheritDoc}
     */
    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
    {
        $autoinc = '';
        if ( ! empty($columnDef['autoincrement'])) {
            $autoinc = ' AUTO_INCREMENT';
        }
        return $this->getUnsignedDeclaration($columnDef) . $autoinc;
    }

Do we just need a new version tag?

[succinct]

This is still marked as "Requires Feedback" - are there still unanswered questions? It seems like this is pretty straight-forward.

[Ocramius]

@succinct is the fix also in the 2.5 branch? If that's the case, then this issue will be fixed when tagging 2.5.5

[deeky666]

Nope it was not backported it seems.

[carlo1138]

Hi guys, do we have a chance to get this fix in 2.5.6?

[Ocramius]

@carlo1138 as per comment above, it will be fixed in 2.6.0

[brunowowk]

was it indeed fixed in 2.6.0?
if so, this should be closed

[xthiago]

@succinct Did you have upgraded to 2.6.* and the issue has been fixed? Please let us know.

[pfeiferbit]

Wouldn't it be possible to have it backported to 2.5.* ?

Doctrine 2.6.* has a dependency on PHP >= 7.1 which my hoster cannot fulfill - but with 2.5.* the schema will never be validated successfully due to this bug.

[Ocramius]

2.5.* is only going to get security fixes.

[gnumoksha]

I'm experiencing this issue with Doctrine 2.6.3.

[Ocramius]

Closing as per https://github.com/doctrine/dbal/issues/2380#issuecomment-363819528 - new issue reports will require a regression test case.