Problem with doctrine diff

[baiiko]

trafficstars

Hi,

When i use the doctrine diff, doctrine create a file with this line :

<?php declare(strict_types = 1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20180129135625 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('ALTER TABLE address CHANGE complement complement VARCHAR(255) DEFAULT NULL');
    }

    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('ALTER TABLE address CHANGE complement complement VARCHAR(255) DEFAULT \'NULL\' COLLATE utf8_unicode_ci');
    }
}

I use Symfony 4, with php 7 and MariaDB from latest version.

My mapping is

App\Entity\Address:
  type: entity
  repositoryClass: App\Repository\AddressRepository
  indexes:
    uuid_index:
      columns: [ uuid ]
  id:
    id:
      type: integer
      generator: {strategy: AUTO}
  fields:
    uuid:
      type: uuid
      unique: true
    name:
      type: string
      length: 100
    address:
      type: string
      length: 255
    complement:
      type: string
      nullable: true
      length: 255
    postcode:
      type: string
      length: 100
    city:
      type: string
      length: 100
    deleted:
      type: boolean
    createdAt:
      type: datetime
      gedmo:
        timestampable:
          on: create
    updatedAt:
      type: datetime
      gedmo:
        timestampable:
          on: update

when i use very verbose on my command, doctrine create this request :

CREATE TABLE address (
id INT AUTO_INCREMENT NOT NULL, 
uuid CHAR(36) NOT NULL COMMENT '(DC2Type:uuid)', 
name VARCHAR(100) NOT NULL, 
address VARCHAR(255) NOT NULL, 
complement VARCHAR(255) DEFAULT NULL, 
postcode VARCHAR(100) NOT NULL, 
city VARCHAR(100) NOT NULL, 
deleted TINYINT(1) NOT NULL, 
created_at DATETIME NOT NULL, 
updated_at DATETIME NOT NULL, 
UNIQUE INDEX UNIQ_D4E6F81D17F50A6 (uuid),
 INDEX uuid_index (uuid), 
PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

When i use PMA to export my table :

DROP TABLE IF EXISTS `address`;
CREATE TABLE IF NOT EXISTS `address` (
`id` int(11) NOT NULL,
  `uuid` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:uuid)',
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `complement` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `postcode` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

So the problem is not on my server i think...

[baiiko]

up ?

[Ocramius]

Needs to be reproduced in isolation (in a test case)

[baiiko]

So, if you have a field who can be nullable on MariaDB we have the problem..

I reproduce the bug on the same project with an other field.

[Ocramius]

See if this is related: https://github.com/doctrine/dbal/pull/2825

[baiiko]

Oh, the problem is the point 6 of your list.

Will be fix soon ?

[Ocramius]

Please do try DBAL master and see if it solves the issue first. We will do a release in the next weeks.

[baiiko]

I try, and is not fixed. :(

I wait the next update to try again.

[samuelfa]

Hi!

I have the same error. I'm using Symfony 3 in some projects and Symfony 4 in another one. The reason for that problem is when the system is preparing the structure from the database, then it is running the following query:

SELECT COLUMN_NAME AS FIELD, COLUMN_TYPE AS TYPE, IS_NULLABLE AS Null, COLUMN_KEY AS Key, COLUMN_DEFAULT AS Default, EXTRA AS Extra, COLUMN_COMMENT AS COMMENT, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS COLLATION FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'some schema name' AND TABLE_NAME = 'some table name'

This query is giving the string NULL without any wrapping of double or single quotes when the column is configured with available NULL values and doesn't have any default value set, then is a NULL value. But for the system is a string value with the content "NULL".

But, If a user administrator wants to set the default value as "NULL" when the query is run, MariaDB is giving the value "'NULL'", check that the value is wrapped with singled quotes.

Temporary, I fixed the problem giving the following change in the file MySqlSchemaManager at path: vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/

Inside the protected method _getPortableTableColumnDefinition, at line 196, I added this three new lines:

 if($options['notnull'] == false && $options['default'] == 'NULL'){
            $options['default'] = null;
}

When the options allow to use null values and the system is giving the string value "NULL", but is different than "'NULL'", then the default value should be null.

[Ocramius]

@samuelfa please see #2825

[enricog84]

I do have the problem using doctrine/dbal: 2.8.0 and MariaDB version 10.2.14 While the issue with NULL seems to be fixed, the problem why the diff is complaining seems to be that mariadb is always adding the COLLATE to the column and it tries to remove it.

The create statement as doctrine migrations would create:

CREATE TABLE `my_test_table` (   
 `column1` longtext NOT NULL DEFAULT '' COMMENT '(DC2Type:json)',   
 `column2` varchar(32) NOT NULL COMMENT '(DC2Type:string)' ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Results in a SHOW CREATE TABLE from mariadb including the collate:

CREATE TABLE `my_test_table` (
  `column1` longtext COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '(DC2Type:json)',
  `column2` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:string)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

So next diff tries to remove the COLLATE again.

[siwymilek]

any update on this?

[BonBonSlick]

I have noticed same issue. New migration after diff creates table in up and down methods. Symfony 4.2, Pgsql 9 6

doctrine/annotations                   v1.6.0                           Docblock Annotations Parser
doctrine/cache                         v1.8.0                           Caching library offering an object-oriented API for many cache backends
doctrine/collections                   v1.5.0                           Collections Abstraction library
doctrine/common                        v2.10.0                          PHP Doctrine Common project is a library that provides additional functionality that other Doctrine projects depend on such as better reflection support, ...
doctrine/data-fixtures                 v1.3.1                           Data Fixtures for all Doctrine Object Managers
doctrine/dbal                          v2.9.2                           Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.
doctrine/doctrine-bundle               1.10.2                           Symfony DoctrineBundle
doctrine/doctrine-cache-bundle         1.3.5                            Symfony Bundle for Doctrine Cache
doctrine/doctrine-fixtures-bundle      3.1.0                            Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle    v2.0.0                           Symfony DoctrineMigrationsBundle
doctrine/event-manager                 v1.0.0                           Doctrine Event Manager component
doctrine/inflector                     v1.3.0                           Common String Manipulations with regard to casing and singular/plural rules.
doctrine/instantiator                  1.1.0                            A small, lightweight utility to instantiate objects in PHP without invoking their constructors
doctrine/lexer                         v1.0.1                           Base library for a lexer that can be used in Top-Down, Recursive Descent Parsers.
doctrine/migrations                    v2.0.0                           PHP Doctrine Migrations project offer additional functionality on top of the database abstraction layer (DBAL) for versioning your database schema and eas...
doctrine/orm                           v2.6.3                           Object-Relational-Mapper for PHP
doctrine/persistence                   v1.1.0                           The Doctrine Persistence project is a set of shared interfaces and functionality that the different Doctrine object mappers share.
doctrine/reflection                    v1.0.0                           Doctrine Reflection component

[SenseException]

A failing test case to reproduce this issue is still appreciated.

[decline]

I had a similar issue where my migration always mixed up NULL with 'NULL' - found out that I did not set the correct server version for MariaDB in the doctrine.yaml config: https://github.com/doctrine/dbal/issues/3321#issuecomment-447642346

[bocharsky-bw]

@decline Hm, what was the exact server_version for you? Because I have the same problem and server_version does not help me

[decline]

@decline Hm, what was the exact server_version for you? Because I have the same problem and server_version does not help me

@bocharsky-bw Since my MariaDB version was 10.3.13 my exact value for the server_version was mariadb-10.3.13

[bocharsky-bw]

@decline Thanks for replying on it! I found the problem, I specified server version incorrectly, now it works for me with 10.2.7 or higher

[smtlab]

I noticed doctrine:diff generates migrations every time for nullable columns.

[HCrane]

Leaving this Link for future searchers of this problem. Completely fixed my problem with :diff command.

[baptgb]

Hi, still having the issue, I'm working with MariaDB 10.2.31, and I tried with 10.4.12. Here is an example :

php bin/console doctrine:schema:update --dump-sql

The following SQL statements will be executed:
   ALTER TABLE survey CHANGE ended_at ended_at DATETIME DEFAULT NULL;

Then : php bin/console doctrine:schema:update --force

[...] 1 query was executed

Then again : php bin/console doctrine:schema:update --dump-sql

 [...] ALTER TABLE survey CHANGE ended_at ended_at DATETIME DEFAULT NULL;

I can confirm that the fix proposed by @samuelfa does the trick, is there any chance this could be merged ?

@HCrane I think your link is dead but I was able to access it from Google's cache : https://webcache.googleusercontent.com/search?q=cache:nVTDyKNHiLwJ:https://marenkay.com/post/symfony-doctrine-migrations/+&cd=1&hl=fr&ct=clnk&gl=fr

[greg0ire]

I can confirm that the fix proposed by @samuelfa does the trick, is there any chance this could be merged ?

If it comes with tests that prove that it fixes what it says it fix, without breaking anything on other platforms, I suppose it would get merged.

[baptgb]

I'm really not sure what's the best way to address this problem : It seems to be an old issue that should be fixed at this time : https://github.com/doctrine/dbal/pull/2825 However this comment helped me fix it : https://github.com/doctrine/dbal/pull/2825#issuecomment-351388309 But removing the server_version parameter feels like a bad idea

[glanes]

I am having the same problem after updating from symfony 4.4 to 5.4.

My configuration is:

Symfony 5.4.7 PHP 7.3.15

doctrine: dbal: server_version: 'mariadb-10.4.11'

"doctrine/orm": "2.10.5", "doctrine/doctrine-bundle": "2.6.3", "doctrine/doctrine-migrations-bundle": "3.2.2",

Every time That I run diff on my migrations it keeps coming all the date fields changing for notnull.

The sql bellow is just a representation, but it taking all my tables (172 tables)

    $this->addSql('ALTER TABLE admin_action CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
    $this->addSql('ALTER TABLE assessment_action CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
    $this->addSql('ALTER TABLE assessment_criteria CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
    $this->addSql('ALTER TABLE assessment_qualification CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');

I have researched a lot and it is a recurrent problem with different dbal package versions, However could not find a way to lock my package in a previous version.

[baiiko]

I am having the same problem after updating from symfony 4.4 to 5.4.

My configuration is:

Symfony 5.4.7 PHP 7.3.15

doctrine: dbal: server_version: 'mariadb-10.4.11'

"doctrine/orm": "2.10.5", "doctrine/doctrine-bundle": "2.6.3", "doctrine/doctrine-migrations-bundle": "3.2.2",

Every time That I run diff on my migrations it keeps coming all the date fields changing for notnull.

The sql bellow is just a representation, but it taking all my tables (172 tables)

    $this->addSql('ALTER TABLE admin_action CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
    $this->addSql('ALTER TABLE assessment_action CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
    $this->addSql('ALTER TABLE assessment_criteria CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');
    $this->addSql('ALTER TABLE assessment_qualification CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL');

I have researched a lot and it is a recurrent problem with different dbal package versions, However could not find a way to lock my package in a previous version.

Set your server version and its OK for me its work

[glanes]

Hi there,

Didn't work for me setting for a older version.

The trait in TimestampableEntity namespace Gedmo\Timestampable\Traits datetime fields are not nullable anymore, that's why all the changes on diff.

In my case I fixed the problem duplicating the file Gedmo\Timestampable\Traits\TimestampableEntity and changing for nullable again: Instead of using the gedmo one, I use the one in my application

= 5.4 * * @author Gediminas Morkevicius ***@***.***> */ trait TimestampableEntity { /** * @var \DateTime * @Gedmo\Timestampable(on="create") * @ORM\Column(type="datetime", nullable=true) * */ #[Gedmo\Timestampable(on: 'create')] #[ORM\Column(type: Types::DATETIME_MUTABLE)] protected $createdAt; /** * @var \DateTime * @Gedmo\Timestampable(on="update") * @ORM\Column(type="datetime", nullable=true) */ #[Gedmo\Timestampable(on: 'update')] #[ORM\Column(type: Types::DATETIME_MUTABLE)] protected $updatedAt; /** * Sets createdAt. * * @return $this */ public function setCreatedAt(\DateTime $createdAt) { $this->createdAt = $createdAt; return $this; } /** * Returns createdAt. * * @return \DateTime */ public function getCreatedAt() { return $this->createdAt; } /** * Sets updatedAt. * * @return $this */ public function setUpdatedAt(\DateTime $updatedAt) { $this->updatedAt = $updatedAt; return $this; } /** * Returns updatedAt. * * @return \DateTime */ public function getUpdatedAt() { return $this->updatedAt; } } In the Entity Class: class CletConsultationRegisterSector { use \AppBundle\Traits\TimestampableEntity; On Mon, Jul 18, 2022 at 7:44 AM Guillaume D. ***@***.***> wrote: > I am having the same problem after updating from symfony 4.4 to 5.4. > > My configuration is: > > Symfony 5.4.7 > PHP 7.3.15 > > doctrine: > dbal: > server_version: 'mariadb-10.4.11' > > "doctrine/orm": "2.10.5", > "doctrine/doctrine-bundle": "2.6.3", > "doctrine/doctrine-migrations-bundle": "3.2.2", > > Every time That I run diff on my migrations it keeps coming all the date > fields changing for notnull. > > The sql bellow is just a representation, but it taking all my tables (172 > tables) > > $this->addSql('ALTER TABLE admin_action CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL'); > $this->addSql('ALTER TABLE assessment_action CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL'); > $this->addSql('ALTER TABLE assessment_criteria CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL'); > $this->addSql('ALTER TABLE assessment_qualification CHANGE created_at created_at DATETIME NOT NULL, CHANGE updated_at updated_at DATETIME NOT NULL'); > > I have researched a lot and it is a recurrent problem with different dbal > package versions, However could not find a way to lock my package in a > previous version. > > Set your server version and its OK for me its work > > — > Reply to this email directly, view it on GitHub > , > or unsubscribe > > . > You are receiving this because you commented.Message ID: > ***@***.***> >

[baiiko]

Please share your configuration

[morozov]

Closing as non-reproducible. If you are still affected, please file a new issue with the details to reproduce.

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.