[DoctrineMesseger] Incompatibility with dbal 4 on Mysql #62298
Description
Symfony version(s) affected
7.3
Description
Doctrine messenger uses the type DATETIME_IMMUTABLE for the columns created_at, available_at, delivered_at on table messenger_messages.
From Symfony\Component\Messenger\Bridge\Doctrine\Transport\Connection.php in method addTableToSchema
$table->addColumn('created_at', Types::DATETIME_IMMUTABLE)
->setNotnull(true);
$table->addColumn('available_at', Types::DATETIME_IMMUTABLE)
->setNotnull(true);
$table->addColumn('delivered_at', Types::DATETIME_IMMUTABLE)This leads to the creation of the columns with a comment DC2Type to map back
After migrating to DBAL 4+, the comments are not possible anymore and are ignored, with the consequence of the command php bin/console make:migration creating this migration:
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE messenger_messages CHANGE created_at created_at DATETIME NOT NULL, CHANGE available_at available_at DATETIME NOT NULL, CHANGE delivered_at delivered_at DATETIME DEFAULT NULL');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE messenger_messages CHANGE created_at created_at TIMESTAMP NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', CHANGE available_at available_at TIMESTAMP NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', CHANGE delivered_at delivered_at TIMESTAMP DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\'');
}The first interesting thing is that in the down method there is TIMESTAMP, but my column is of type DATETIME.
The second problem is that executing the same command after will then create this over and over:
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE messenger_messages CHANGE created_at created_at DATETIME NOT NULL, CHANGE available_at available_at DATETIME NOT NULL, CHANGE delivered_at delivered_at DATETIME DEFAULT NULL');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE messenger_messages CHANGE created_at created_at TIMESTAMP NOT NULL, CHANGE available_at available_at TIMESTAMP NOT NULL, CHANGE delivered_at delivered_at TIMESTAMP DEFAULT NULL');
}In the down method there is still TIMESTAMP, while my column is of type DATETIME
How to reproduce
Just install the package with mysql database and DBAL 3. Then migrate to DBAL 4
Possible Solution
As a workaround, in my doctrine.yaml, I put the following: doctrine.dbal.schema_filter: ~^(?!messenger_messages)~
It will work for the migration:
> php bin/console make:migration
[WARNING] No database changes were detected.
The database schema and the application mapping information are already in sync
But won't work for other commands like:
> php bin/console doctrine:schema:validate
Mapping
-------
[OK] The mapping files are correct.
Database
--------
[ERROR] The database schema is not in sync with the current mapping file.
> php bin/console doctrine:schema:update --dump-sql
ALTER TABLE messenger_messages CHANGE created_at created_at DATETIME NOT NULL, CHANGE available_at available_at DATETIME NOT NULL, CHANGE delivered_at delivered_at DATETIME DEFAULT NULL;
Additional Context
No response