Symfony Migrations Bundle creating the same migration over and over again

Question

I added an auto-update timestamp to an entity like in this blog post and it works just fine.

Here is the code snippet:

#[Entity]
class Article
{
    #[Column(type: "datetime",
        columnDefinition: "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        insertable: false,
        updatable: false,
        generated: "ALWAYS")]
    public $created;
}

The first time I run php bin/console make:migration the correct migration is generated:

$this->addSql('ALTER TABLE article ADD created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');

This works just fine and any DB update now updates created. However, this is also where the problems begin. Whenever I make another migration now, it tries to apply the same changes again:

$this->addSql('ALTER TABLE article CHANGE created created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');

How can this be, and does anyone know how to fix this?

Thank you very much for your input, I really appreciate it.

Answer 1

So I didn't figure out why this happens. But the solution to this problem is to check if the property in question already exists, and then ignore it when diffing: https://www.liip.ch/en/blog/doctrine-and-generated-columns

Edit:

So, the classes used in the blog article above are deprecated and I had to dig a little deeper into Doctrine on how to exclude specific table columns.

I performed the following steps, and it works like a charm:

• Read the official documentation on schema managers: https://www.doctrine-project.org/projects/doctrine-dbal/en/current/reference/schema-manager.html
• Implement a custom SchemaManagerFactory
• Implement a custom custom MySQLSchemaManager (at least if you use MySQL, that is)
• Make the schema manager use a custom Comparator
• Add schema_manager_factory: doctrine.dbal.default_schema_manager_factory to my entries in connections in doctrine.yaml
• Alias doctrine.dbal.default_schema_manager_factory in services.yaml

Here is some example code to make this more clear.

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                server_version: 8.0.25
                schema_manager_factory: doctrine.dbal.default_schema_manager_factory

services:
    doctrine.dbal.default_schema_manager_factory:
        class: App\Service\Doctrine\CustomSchemaManagerFactory

<?php

namespace App\Service\Doctrine;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception;
use Doctrine\DBAL\Platforms\MySQL80Platform;
use Doctrine\DBAL\Schema\AbstractSchemaManager;
use Doctrine\DBAL\Schema\DefaultSchemaManagerFactory;
use Doctrine\DBAL\Schema\SchemaManagerFactory;

final class CustomSchemaManagerFactory implements SchemaManagerFactory
{
    private readonly SchemaManagerFactory $defaultFactory;

    public function __construct()
    {
        $this->defaultFactory = new DefaultSchemaManagerFactory();
    }

    /**
     * @throws Exception
     */
    public function createSchemaManager(Connection $connection): AbstractSchemaManager
    {
        $platform = $connection->getDatabasePlatform();
        if ($platform instanceof MySQL80Platform) {
            return new CustomMySQLSchemaManager($connection, $platform);
        }

        return $this->defaultFactory->createSchemaManager($connection);
    }
}

<?php

namespace App\Service\Doctrine;

use Doctrine\DBAL\Platforms\MySQL\CollationMetadataProvider\CachingCollationMetadataProvider;
use Doctrine\DBAL\Platforms\MySQL\CollationMetadataProvider\ConnectionCollationMetadataProvider;
use Doctrine\DBAL\Schema\Comparator;
use Doctrine\DBAL\Schema\MySQLSchemaManager;

final class CustomMySQLSchemaManager extends MySQLSchemaManager
{
    public function createComparator(): Comparator
    {
        return new CustomComparator(
            $this->_platform,
            new CachingCollationMetadataProvider(
                new ConnectionCollationMetadataProvider($this->_conn),
            ),
        );
    }
}

<?php

namespace App\Service\Doctrine;

use Doctrine\DBAL\Platforms\MySQL\Comparator;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Schema\TableDiff;

final class CustomComparator extends Comparator
{
    public function compareTables(Table $fromTable, Table $toTable): TableDiff
    {
        $diff = parent::compareTables($fromTable, $toTable);
        
        # Your custom logic here!

        return $diff;
    }
}

Answer 2

Doctrine sees that your actual column has a default value while none is defined in its mapping's options (ignoring your columnDefinition). Thus, it decides it needs to generate an ALTER migration, and when doing so it does take into account your columnDefinition.

Set options: ["default" => "CURRENT_TIMESTAMP"] in the column attributes:

#[Entity]
class Article
{
    #[Column(type: "datetime",
        columnDefinition: "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        insertable: false,
        updatable: false,
        generated: "ALWAYS",
        options: ["default" => "CURRENT_TIMESTAMP"])]
    public $created;
}

---

Alternatively, if you're only changing entries in this table through Doctrine, you can use Doctrine lifecycle events to keep created/updated timestamp fields up-to-date.