GORM Integration Tests with 3 or more DataSources

Question

My integration test, MultipleDbsITSpec, is failing with the error, "org.hibernate.HibernateException: No Session found for current thread" when you attempt to interact with more than 2 datasources. This issue does not occur when running the application, please see BootStrap.groovy.

What is the proper way to set this up?

I want to avoid the workaround I find which requires wrapping the GORM query with a withNewTransaction closure. This would ruin the readability of the code.

Source Code

Environment:

host:TestingMultDbs user$ ./grailsw -version
| Grails Version: 3.3.6
| Groovy Version: 2.4.7
| JVM Version: 1.8.0_181

Database Config:

    development:
    dataSource:
        dbCreate: create-drop
        url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        logSql: true
    dataSources:
        db2:
          dbCreate: create-drop
          url: jdbc:h2:mem:devDb2;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        db3:
          dbCreate: create-drop
          url: jdbc:h2:mem:devDb3;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
test:
    dataSource:
        dbCreate: create-drop
        url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        logSql: true
    dataSources:
        db2:
          dbCreate: create-drop
          url: jdbc:h2:mem:devDb2;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        db3:
          dbCreate: create-drop
          url: jdbc:h2:mem:devDb3;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

Failing Test:

// Can only declare 1 @Transactional
//    @Transactional("db2") -- causes db3 to fail
//    @Transactional("db3") -- causes db2 to fail
//    @Transactional(["db2","db3"]) -- doesn't exist
@Transactional
void "Test interaction with all 3 databases"() {
    given:
    new DefaultDb(f1: "test").save()
    new Db2Example(f1: "test").save()
    new Db3Example(f1: "test").save()

    when:
    def value1 = DefaultDb.findByF1("test")
    def value2 = Db2Example.findByF1("test")
    def value3 = Db3Example.findByF1("test")

    then:
    value1
    value2
    value3
}

Passing Tests:

void "Test default db"() {
    given:
    new DefaultDb(f1: "test").save()

    when:
    def value = DefaultDb.findByF1("test")

    then:
    value
}

@Transactional("db2")
void "Test default db2"() {
    given:
    new Db2Example(f1: "test").save()

    when:
    def value = Db2Example.findByF1("test")

    then:
    value
}

@Transactional("db3")
void "Test default db3"() {
    given:
    new Db3Example(f1: "test").save()

    when:
    def value = Db3Example.findByF1("test")

    then:
    value
}

Answer 1

zyro23 explains the solution here: https://github.com/grails/grails-core/issues/10383

check the changes reg. the ChainedTransactionManagerPostProcessor (disabled by default since 3.3.0): http://docs.grails.org/3.3.0/guide/upgrading.html and try re-enabling via application.yml..

grails:
  transaction:
    chainedTransactionManagerPostProcessor:
      enabled: true

with that config, omitting the connection attribute should work (or rather not make a difference).

but make sure you understand the implications of chained transaction handling, i.e. the best-effort two-phase commit ("be2pc") approach.