grails-core: Grails 3.2.4: multiple Datasources "No Session found for current thread" exception when accessing secondary datasource

I’ve followed the instructions reported on point “4.4.5 Multiple Datasources” of http://docs.grails.org/latest/guide/single.html#configGORM.

This is the datasource section of my application.yml


dataSources:
  dataSource:
      #dbCreate: create-drop
      #url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
      driverClassName: com.mysql.cj.jdbc.Driver
      pooled: true
      dialect: org.hibernate.dialect.MySQL5InnoDBDialect
      url: **********

      username: **********
      password: **********

      logSql: true

      properties:
        testOnBorrow: true
        testWhileIdle: true
        testOnReturn: true
        validationQuery: SELECT 1 as dbcp_connection_test

  dataSource2:
      #dbCreate: create-drop
      #url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
      driverClassName: com.mysql.cj.jdbc.Driver
      pooled: true
      dialect: org.hibernate.dialect.MySQL5InnoDBDialect
      url: **********

      username: **********
      password: **********

      logSql: true

      properties:
        testOnBorrow: true
        testWhileIdle: true
        testOnReturn: true
        validationQuery: SELECT 1 as dbcp_connection_test

These are my domain classes


package multidb3

class User {
  Long id

  String name
  String lastname

  static constraints = {
  }

  static mapping = {
    version false
  }
}


package multidb3

class Message {
  Long id

  String subject
  String body

  static constraints = {
  }

  static mapping = {
    datasource 'dataSource2'

    version false
  }
}

Message is a table in secondary datasource (dataSource2)

When I try to access Message for example

List<Message> messages = Message.list()

I get the exception:

Class: org.hibernate.HibernateException Message: null Caused by: No Session found for current thread

I’ve attached an example application, see TestController->index

Environment Information

Operating System: Linux Mint 18.1 64 bit
Grails Version: 3.2.4
JDK Version: 1.8.0_111
Container Version (If Applicable):

multidb3.zip

About this issue

Original URL
State: closed
Created 8 years ago
Reactions: 1
Comments: 27 (11 by maintainers)

Commits related to this issue

sample app for the follow-up discussion on grails/grails-core#10383 — committed to zyro23/grails-core-10383 by zyro23 7 years ago
Recreate grails/grails-core#10383 — committed to ch0wnag3/grails-core-10383 by ch0wnag3 7 years ago

Most upvoted comments

I’m still having issues with this on 3.3.1. Standard @Transactional service is getting me No session found for current thread when I access a domain class from the second datasource. Using Transactional(connection = 'secondDSName') doesn't do the trick either, as my service needs to access domain classes from both data sources. Any ideas? The only way I can get this to work is by adding withTransaction` manually.

borgiani on Oct 3, 2017

our current approach:

put all persistence logic into @Transactional services. or use gorm data services.

grails-3.2.4 will use the ChainedTransactionManager by default, so all DataSources will have their sessions opened “inside” the transactional methods.

important is that if your dataSource is configured readOnly = true, you have to configure it transactional = true to include it in the ChainedTransactionManager.

if you need the open sessions for not hitting LazyLoadingExceptions (e.g. caused by iterating over associations in a view), i suggest you check this hibernate setting:

hibernate.enable_lazy_load_no_trans: true

(ref. https://jira.spring.io/browse/SPR-12823?focusedCommentId=113801&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-113801)

zyro23 on May 13, 2017

Thank you very much for your example app, @zyro23, but unfortunately, I was still unable to get my secondary data source working.

I’ve created my own sample app and recreated the issue using a file-based H2 database. I’m hoping you can easily identify where I’ve gone wrong.

ch0wnag3 on Oct 3, 2017

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.

zyro23 on Oct 3, 2017

See upgrade notes http://docs.grails.org/latest/guide/upgrading.html

Section “TransactionManager Chaining for Multiple Data Sources Disabled by Default”

graemerocher on Aug 14, 2017

point taken. so if we want to have auto-opened sessions for the non-primary datasources as well, the way to go would be to define additional OpenSessionInViewInterceptors?, e.g.

openSessionInViewInterceptor_mySecondDataSourceName(GrailsOpenSessionInViewInterceptor) {
    hibernateDatastore = ref("hibernateDatastore_mySecondDataSourceName")
}

zyro23 on Jan 18, 2017