Doctrine-messenger crashes on acknowledgement

Symfony version(s) affected

6.0.8

Description

I just implemented a messenger to send my emails asynchronously with a doctrine messenger.
The table was created correctly

CREATE TABLE `messenger_messages` (
  `id` bigint NOT NULL,
  `body` longtext NOT NULL,
  `headers` longtext NOT NULL,
  `queue_name` varchar(190) NOT NULL,
  `created_at` timestamp NOT NULL,
  `available_at` timestamp NOT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `messenger_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_75EA56E0FB7336F0` (`queue_name`),
  ADD KEY `IDX_75EA56E0E3BD61CE` (`available_at`),
  ADD KEY `IDX_75EA56E016BA31DB` (`delivered_at`);

When I start the async consumer, everything works fine, the delivered_at field is set correctly and the email is sent. But after a few seconds, I receive an exception:

  [Symfony\Component\Messenger\Exception\TransportException]                                                                                                               
  An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '9999-12-31' for column 'delivered_at' at row 1

and the script crashes. I don't understand why trying to put this value and not simply deleting the row like on other platforms.

How to reproduce

$ bin/console messenger:consume async --time-limit=3600 -vv 

12:39:25 INFO      [messenger] Received message Symfony\Component\Mailer\Messenger\SendEmailMessage ["message" => Symfony\Component\Mailer\Messenger\SendEmailMessage { …},"class" => "Symfony\Component\Mailer\Messenger\SendEmailMessage"] ["token" => null,"request_id" => "707b7758-8d85-4372-9732-074c180c6c8d"]
12:39:40 INFO      [messenger] Message Symfony\Component\Mailer\Messenger\SendEmailMessage handled by Symfony\Component\Mailer\Messenger\MessageHandler::__invoke ["message" => Symfony\Component\Mailer\Messenger\SendEmailMessage { …},"class" => "Symfony\Component\Mailer\Messenger\SendEmailMessage","handler" => "Symfony\Component\Mailer\Messenger\MessageHandler::__invoke"] ["token" => null,"request_id" => "707b7758-8d85-4372-9732-074c180c6c8d"]
12:39:40 INFO      [messenger] Symfony\Component\Mailer\Messenger\SendEmailMessage was handled successfully (acknowledging to transport). ["message" => Symfony\Component\Mailer\Messenger\SendEmailMessage { …},"class" => "Symfony\Component\Mailer\Messenger\SendEmailMessage"] ["token" => null,"request_id" => "707b7758-8d85-4372-9732-074c180c6c8d"]

In Connection.php line 259:
                                                                                                                                                                           
  [Symfony\Component\Messenger\Exception\TransportException]                                                                                                               
  An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '9999-12-31' for column 'delivered_at' at row 1  
                                                                                                                                                                           

Exception trace:
  at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/Connection.php:259
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\Connection->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineReceiver.php:78
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineReceiver->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineTransport.php:53
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineTransport->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:217
 Symfony\Component\Messenger\Worker->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:170
 Symfony\Component\Messenger\Worker->handleMessage() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:106
 Symfony\Component\Messenger\Worker->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Command/ConsumeMessagesCommand.php:223
 Symfony\Component\Messenger\Command\ConsumeMessagesCommand->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Command/Command.php:291
 Symfony\Component\Console\Command\Command->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:1007
 Symfony\Component\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:94
 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:299
 Symfony\Component\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:80
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:171
 Symfony\Component\Console\Application->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:54
 Symfony\Component\Runtime\Runner\Symfony\ConsoleApplicationRunner->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/autoload_runtime.php:29
 require_once() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/bin/console:11

In ExceptionConverter.php line 119:
                                                                                                                                                                           
  [Doctrine\DBAL\Exception\DriverException (1292)]                                                                                                                         
  An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '9999-12-31' for column 'delivered_at' at row 1  
                                                                                                                                                                           

Exception trace:
  at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:119
 Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:1814
 Doctrine\DBAL\Connection->handleDriverException() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:1749
 Doctrine\DBAL\Connection->convertExceptionDuringQuery() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:1163
 Doctrine\DBAL\Connection->executeStatement() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:724
 Doctrine\DBAL\Connection->update() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/Connection.php:254
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\Connection->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineReceiver.php:78
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineReceiver->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineTransport.php:53
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineTransport->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:217
 Symfony\Component\Messenger\Worker->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:170
 Symfony\Component\Messenger\Worker->handleMessage() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:106
 Symfony\Component\Messenger\Worker->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Command/ConsumeMessagesCommand.php:223
 Symfony\Component\Messenger\Command\ConsumeMessagesCommand->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Command/Command.php:291
 Symfony\Component\Console\Command\Command->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:1007
 Symfony\Component\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:94
 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:299
 Symfony\Component\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:80
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:171
 Symfony\Component\Console\Application->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:54
 Symfony\Component\Runtime\Runner\Symfony\ConsoleApplicationRunner->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/autoload_runtime.php:29
 require_once() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/bin/console:11

In Exception.php line 30:
                                                                                                                            
  [Doctrine\DBAL\Driver\PDO\Exception (1292)]                                                                               
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '9999-12-31' for column 'delivered_at' at row 1  
                                                                                                                            

Exception trace:
  at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:30
 Doctrine\DBAL\Driver\PDO\Exception::new() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:103
 Doctrine\DBAL\Driver\PDO\Statement->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/Middleware/AbstractStatementMiddleware.php:40
 Doctrine\DBAL\Driver\Middleware\AbstractStatementMiddleware->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Logging/Statement.php:74
 Doctrine\DBAL\Logging\Statement->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:1155
 Doctrine\DBAL\Connection->executeStatement() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:724
 Doctrine\DBAL\Connection->update() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/Connection.php:254
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\Connection->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineReceiver.php:78
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineReceiver->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineTransport.php:53
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineTransport->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:217
 Symfony\Component\Messenger\Worker->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:170
 Symfony\Component\Messenger\Worker->handleMessage() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:106
 Symfony\Component\Messenger\Worker->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Command/ConsumeMessagesCommand.php:223
 Symfony\Component\Messenger\Command\ConsumeMessagesCommand->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Command/Command.php:291
 Symfony\Component\Console\Command\Command->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:1007
 Symfony\Component\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:94
 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:299
 Symfony\Component\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:80
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:171
 Symfony\Component\Console\Application->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:54
 Symfony\Component\Runtime\Runner\Symfony\ConsoleApplicationRunner->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/autoload_runtime.php:29
 require_once() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/bin/console:11

In Statement.php line 101:
                                                                                                                            
  [PDOException (22007)]                                                                                                    
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '9999-12-31' for column 'delivered_at' at row 1  
                                                                                                                            

Exception trace:
  at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:101
 PDOStatement->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:101
 Doctrine\DBAL\Driver\PDO\Statement->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Driver/Middleware/AbstractStatementMiddleware.php:40
 Doctrine\DBAL\Driver\Middleware\AbstractStatementMiddleware->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Logging/Statement.php:74
 Doctrine\DBAL\Logging\Statement->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:1155
 Doctrine\DBAL\Connection->executeStatement() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/doctrine/dbal/src/Connection.php:724
 Doctrine\DBAL\Connection->update() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/Connection.php:254
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\Connection->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineReceiver.php:78
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineReceiver->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/doctrine-messenger/Transport/DoctrineTransport.php:53
 Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineTransport->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:217
 Symfony\Component\Messenger\Worker->ack() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:170
 Symfony\Component\Messenger\Worker->handleMessage() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Worker.php:106
 Symfony\Component\Messenger\Worker->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/messenger/Command/ConsumeMessagesCommand.php:223
 Symfony\Component\Messenger\Command\ConsumeMessagesCommand->execute() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Command/Command.php:291
 Symfony\Component\Console\Command\Command->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:1007
 Symfony\Component\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:94
 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:299
 Symfony\Component\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/framework-bundle/Console/Application.php:80
 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/console/Application.php:171
 Symfony\Component\Console\Application->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:54
 Symfony\Component\Runtime\Runner\Symfony\ConsoleApplicationRunner->run() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/vendor/autoload_runtime.php:29
 require_once() at /home/yyy/Desktop/xxx/programs/php/xxxBackend/bin/console:11

Here is the log from Doctrine for the attempted SQL request:

[2022-05-14T12:39:40.811332+00:00] doctrine.DEBUG: Executing statement: UPDATE messenger_messages SET delivered_at = ? WHERE id = ? (parameters: array["9999-12-31","1"], types: array[]) {"sql":"UPDATE messenger_messages SET delivered_at = ? WHERE id = ?","params":["9999-12-31","1"],"types":[]} {"token":null,"request_id":"707b7758-8d85-4372-9732-074c180c6c8d"}

Possible Solution

No response

Additional Context

Here is the part of the code showing the problem (vendor/symfony/doctrine-messenger/Transport/Connection.php):

    public function ack(string $id): bool
    {
        try {
            if ($this->driverConnection->getDatabasePlatform() instanceof MySQLPlatform) {
                return $this->driverConnection->update($this->configuration['table_name'], ['delivered_at' => '9999-12-31'], ['id' => $id]) > 0;
            }

            return $this->driverConnection->delete($this->configuration['table_name'], ['id' => $id]) > 0;
        } catch (DBALException $exception) {
            throw new TransportException($exception->getMessage(), 0, $exception);
        }
    }

    public function reject(string $id): bool
    {
        try {
            if ($this->driverConnection->getDatabasePlatform() instanceof MySQLPlatform) {
                return $this->driverConnection->update($this->configuration['table_name'], ['delivered_at' => '9999-12-31'], ['id' => $id]) > 0;
            }

            return $this->driverConnection->delete($this->configuration['table_name'], ['id' => $id]) > 0;
        } catch (DBALException $exception) {
            throw new TransportException($exception->getMessage(), 0, $exception);
        }
    }

It can't work since TIMESTAMP is limited to the size of an int.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Doctrine-messenger crashes on acknowledgement #46351

Symfony version(s) affected

Description

How to reproduce

Possible Solution

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Search code, repositories, users, issues, pull requests...

Uh oh!

Doctrine-messenger crashes on acknowledgement #46351

Description

Symfony version(s) affected

Description

How to reproduce

Possible Solution

Additional Context

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions