dpcasady opened a new issue, #14579: URL: https://github.com/apache/grails-core/issues/14579
### Task List - [x] Steps to reproduce provided - [x] Stacktrace (if present) provided - [x] Example that reproduces the problem uploaded to Github - [x] Full description of the issue provided (see below) ### Steps to Reproduce After following http://guides.grails.org/grails-dynamic-multiple-datasources/guide/index.html, I found that tenants added at runtime have some odd behavior in regards to connection pooling. It seems as though the new dataSource's connection pool is closed too early for some reason. Take the following controller and corresponding service: ``` @CurrentTenant class DeviceController { DeviceService deviceService def index() { deviceService.list() render Device.list() // causes an exception to be thrown } } ``` ``` @Transactional(readOnly = true) class DeviceService { def list() { Device.createCriteria().list { } } } ``` This works fine when the current tenant is the default dataSource, or a secondary dataSource that is defined in application.yml. If, however, the current tenant is a dataSource that is added at runtime, the call to `Device.list()` in the controller will throw an exception. This is obviously not a real-world example but the second query is arbitrary, the same exception is thrown if making another call to a transactional service method as well. There are no configuration differences between the dataSources in application.yml and the one added at runtime (apart from the db name). I do understand that [as the GORM docs state](http://gorm.grails.org/6.1.x/hibernate/manual/index.html#_adding_tenants_at_runtime), adding tenants at runtime is beyond the scope of what GORM offers, but I'm hoping that you might be able to give me some guidance as to what the problem might be. ### Expected Behaviour I would expect that a tenant added at runtime via `hibernateDatastore.connectionSources.addConnectionSource()` would function identically to a tenant that is defined as an additional dataSource in the dataSources section of application.yml. ### Actual Behaviour Instead, dataSources added at runtime seem to have a problem with connection pooling. The example application noted below shows that while the new tenant can be used successfully for one connection, any connections after the initial one throw the following exception: ``` org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not prepare statement; uncategorized SQLException for SQL [select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.name as name3_0_0_ from device this_]; SQL state [null]; error code [0]; PooledConnection has already been closed.; nested exception is java.sql.SQLException: PooledConnection has already been closed. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.grails.orm.hibernate.GrailsHibernateTemplate.convertJdbcAccessException(GrailsHibernateTemplate.java:731) at org.grails.orm.hibernate.GrailsHibernateTemplate.convertHibernateAccessException(GrailsHibernateTemplate.java:719) at org.grails.orm.hibernate.GrailsHibernateTemplate.doExecute(GrailsHibernateTemplate.java:303) at org.grails.orm.hibernate.GrailsHibernateTemplate.execute(GrailsHibernateTemplate.java:243) at org.grails.orm.hibernate.GrailsHibernateTemplate.execute(GrailsHibernateTemplate.java:117) at org.grails.orm.hibernate.HibernateGormStaticApi.list(HibernateGormStaticApi.groovy:74) at org.grails.orm.hibernate.HibernateGormStaticApi.list(HibernateGormStaticApi.groovy:73) at org.grails.datastore.gorm.GormEntity$Trait$Helper.list(GormEntity.groovy:664) at org.grails.DeviceController.$mt__index(DeviceController.groovy:12) at grails.gorm.multitenancy.Tenants$_withId_closure2$_closure6.doCall(Tenants.groovy:265) at org.grails.orm.hibernate.GrailsHibernateTemplate$1.doInHibernate(GrailsHibernateTemplate.java:153) at org.grails.orm.hibernate.GrailsHibernateTemplate.doExecute(GrailsHibernateTemplate.java:299) at org.grails.orm.hibernate.GrailsHibernateTemplate.execute(GrailsHibernateTemplate.java:243) at org.grails.orm.hibernate.GrailsHibernateTemplate.executeWithNewSession(GrailsHibernateTemplate.java:150) at org.grails.orm.hibernate.GrailsHibernateTemplate.executeWithExistingOrCreateNewSession(GrailsHibernateTemplate.java:209) at org.grails.orm.hibernate.AbstractHibernateDatastore.withNewSession(AbstractHibernateDatastore.java:361) at grails.gorm.multitenancy.Tenants$_withId_closure2.doCall(Tenants.groovy:258) at grails.gorm.multitenancy.Tenants$CurrentTenant.withTenant(Tenants.groovy:358) at grails.gorm.multitenancy.Tenants.withId(Tenants.groovy:236) at org.grails.datastore.gorm.services.DefaultTenantService.withCurrent(DefaultTenantService.groovy:71) at org.grails.core.DefaultGrailsControllerClass$MethodHandleInvoker.invoke(DefaultGrailsControllerClass.java:223) at org.grails.core.DefaultGrailsControllerClass.invoke(DefaultGrailsControllerClass.java:188) at org.grails.web.mapping.mvc.UrlMappingsInfoHandlerAdapter.handle(UrlMappingsInfoHandlerAdapter.groovy:90) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55) at org.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:77) at org.grails.web.filters.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:67) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: PooledConnection has already been closed. at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:376) at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:240) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1934) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1903) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881) at org.hibernate.loader.Loader.doQuery(Loader.java:925) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342) at org.hibernate.loader.Loader.doList(Loader.java:2622) at org.hibernate.loader.Loader.doList(Loader.java:2605) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434) at org.hibernate.loader.Loader.list(Loader.java:2429) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1787) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:363) at org.grails.orm.hibernate.query.AbstractHibernateQuery.listForCriteria(AbstractHibernateQuery.java:719) at org.grails.orm.hibernate.HibernateGormStaticApi$_list_closure1.doCall(HibernateGormStaticApi.groovy:88) at org.grails.orm.hibernate.GrailsHibernateTemplate.doExecute(GrailsHibernateTemplate.java:299) ... 31 common frames omitted ``` I can't see anything that sticks out to me as incorrect about the dataSource added at runtime. It is an instance of a pooled dataSource and the connection pool does seem to be initialized ok. ### Environment Information - **Operating System**: MacOS 10.13.3 - **GORM Version:** 6.1.9.RELEASE - **Grails Version (if using Grails):** 3.2.11 - **JDK Version:** 1.8 ### Example Application This example application, https://github.com/dpcasady/multi-tenant-runtime, provides a functional test that hits a controller action three times: once for the default dataSource, once for an additional dataSource defined in application.yml, and once for a dataSource added in BootStrap.groovy on startup. Although the same controller action is hit each time, an exception is thrown only when using the tenant added at runtime. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
