Description
Symfony version(s) affected: 5.1.0
Description
The doctrine messenger creates a schema that can't be created in a mysql 5.6.
An exception occurred while executing 'CREATE TABLE messenger_messages (id
BIGINT AUTO_INCREMENT NOT NULL, body LONGTEXT NOT NULL, headers LONGTEXT NO
T NULL, queue_name VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, ava
ilable_at DATETIME NOT NULL, delivered_at DATETIME DEFAULT NULL, INDEX IDX_
75EA56E0FB7336F0 (queue_name), INDEX IDX_75EA56E0E3BD61CE (available_at), I
NDEX IDX_75EA56E016BA31DB (delivered_at), PRIMARY KEY(id)) DEFAULT CHARACTE
R SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB':
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
oo long; max key length is 767 bytes
As you can see, an index (specifically the queue_name index) is too large since mysql uses 4 bytes per character (in utf8 mode) and mysql 5.6 is limited to 767 bytes per index. This limit was increased in mysql 5.7.
This error only appears if you use the STRICT_TRANS_TABLES sql mode. If you don't do that, the index is silently truncated which will make the index comparison update the index on every compare.
How to reproduce
Use the messenger with a mysql 5.6. I recommend the docker image with mysqld --sql-mode=STRICT_TRANS_TABLES as startup command.
Possible Solution
The simplest solution is to limit the field size of the queue_name field to something like 100 or 150 characters.
The obvious better solution would be if doctrine/dbal#3419 would be fixed although that isn't really a fix but a workaround so no specific schema is needed.
Additional context
The reason mysql 5.6 is still important for modern projects is because AWS Aurora only supports mysql 5.6 compatibility in all but the classic mode.
I would not recommend to use the doctrine messenger in an aws context directly but I use it for local testing and since I need to use mysql 5.6 for the rest of the application, the messenger uses it too in affect.