SQLSTATE[HY000]: General error: 1 table messenger_messages already exists

[lsv]

After an upgrade from symfony 4.4 to symfony 5.1 with all the doctrine dependencies, and also an remove of liip/functional-test-bundle and instead install of liip/test-fixtures-bundle

Some dependencies that have been upgraded is

doctrine/doctrine-migrations-bundle
doctrine/doctrine-fixtures-bundle
symfony/messenger

My tests began to output a lot of SQLSTATE[HY000]: General error: 1 table messenger_messages already exists all over the place.

Now for my migrations to not add messenger_messages table I have the following doctrine config

# config/packages/doctrine.yaml
doctrine:
    dbal:
        connections:
            default:
                schema_filter: ~^(?!messenger_messages)~

So to fix my table already exists I had to add the following to my doctrine test yaml file

# config/packages/test/doctrine.yaml

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                schema_filter: ~

And now my tests are running fine.

Im not actually sure this is a bug in either doctrine or liip, but just wanted to add it somewhere, so it can be googled - because I had no relevant google results coming up.

[alexislefebvre]

You can use that setting to disable the automatic creation of tables:

liip_test_fixtures:
    keep_database_and_schema: true

[lsv]

I actually thinks this is the reason.
symfony/symfony#40336

Ill try and test it at a later point.

[alexislefebvre]

@lsv Did the fix in Symfony solved your problem?

[kolotov]

@alexislefebvre I have same problem if I use automatic creation of tables.

[alexislefebvre]

@kolotov I don't have any other solution right one than using keep_database_and_schema: true.

[WubbleWobble]

Here's my research into this.

I have a set of tests, the first test runs fine, and then the next test blows up trying to create messenger_messages, which already exists.

Ultimate cause:

ORMSqliteDatabaseTool - lines 92-94 - dropDatabase and createSchema are not symmetrical
(createSchema has a "create table messenger_messages", but dropDatabase doesn't have a corresponding drop table line)

Problem overview

• First test starts...
  • createSchema creates schema
  • Test runs (success)
• Next test starts...
  • dropDatabase gets run but does not remove "cache_items" or "messenger_messages" table
  • createSchema blows up whilst creating schema as messenger_messages already exists...

Cause

• ORMSqliteDatabaseTool calls $schemaTool->createSchema($this->getMetadatas());
• AbstractDatabaseTool->getMetadatas() is used to generate a list of entity classes (doesn't include messenger or cache)
• createSchema calls getSchemaFromMetadata($classlist)
• But during this, SchemaTool->getSchemaFromMetadata() generates a ToolEvents::postGenerateSchema event
• MessagerTransportDoctrineSchemaSubscriber is subscribed to this and adds more SQL for table creation (i.e. it adds the CREATE TABLE messenger_messages SQL)
• DoctrineDbalCacheAdapterSchemaSubscriber is also subscribed and likely does the same

Looks like these SchemaSubscribers were added to symfony/doctrine-bridge in 5.1 (SchemaListener directory appears at 5.1)
https://github.com/symfony/doctrine-bridge/tree/4.4
https://github.com/symfony/doctrine-bridge/tree/5.1

Thoughts

• It's a bit weird that getSchemaFromMetadata($classes) returns schema that doesn't relate to the list of $classes passed
• In general it doesn't seem completely terrible that it creates the messenger table for us...
• ...but the lack of a counterpart drop table SQL mechanism breaks it all

Conclusion

Not much I can do to workaround - this is caused by code in symfony/doctrine-bridge, and behaviour could be adjusted if I could hook into SchemaTool, but that's in doctrine/orm.

I'm going to have to do the whole keep_databases_and_schema thing, right? :/

[alexislefebvre]

@WubbleWobble thanks for the detailed report!

Calling setExcludedDoctrineTables(['messenger_messages']) may help here, please see the doc: https://github.com/liip/LiipTestFixturesBundle/blob/2.x/doc/database.md#exclude-some-tables-

Update: actually it looks like it's only used when purging the tables, it may not help here.

[WubbleWobble]

@alexislefebvre Thanks - I found that method, but as you say - it didn't actually help :)

I would open a bug report with doctrine and/or symfony, but honestly I'm quite confused by many aspects of it, and where the "blame" (for lack of a better word) lies. It's going to take some more investigation I think!

I ended up using keep_databases_and_schema: true, and used a phpunit bootstrap file to run the various Symfony console commands needed to set up the test database.

[Ceriusz]

You can use that setting to disable the automatic creation of tables:

liip_test_fixtures:
    keep_database_and_schema: true

Sorry but I'm not sure where should I put this yaml configuration - should I create a new file config/packages/liip.yaml?
Because I don't see any yaml regarding liip configuration under config/packages.

[alexislefebvre]

@Ceriusz yes, there is an example of path here: https://github.com/liip/LiipTestFixturesBundle/blob/2.x/doc/configuration.md

[glaplace]

Hello,

I have the same problem.

As a workaround, I deleted the global transport configuration and added it only to the dev and prod environments.

For testing I used test://.

example

## Configuration messenger
## Commun environment configuration
framework:
  messenger:
    routing:
      'App\Message\AsyncMessageInterface': async
    buses:
      command_bus:
        middleware:
          - router_context
    transports:
      async:
        options:
          queue_name: aqueuename

# prod environment
when@prod:
  framework:
    messenger:
      transports:
        async: 'doctrine://default'

# Test environment
when@test:
  framework:
    messenger:
      transports:
        async: 'test://'

I hope this can help someone.