Problem with multiple datasources with different drivers/dialects

[zoladkow]

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

1. configure primary datasource with jndiName and Oracle dialect (dbCreate: '')
2. configure secondary datasource with peristent H2 (dbCreate: update)
3. configure primary override for development to use direct connection
4. configure secondary override for development to point to devDb file
5. configure secondary override for production to point to prodDb file

Expected Behaviour

1. should execute with datasources initialized as configured

Actual Behaviour

1. run-app -> works as expected
2. deploy to tomcat -> throws "Unable to build DatabaseInformation" exception

Environment Information

• Operating System: WinX
• Grails Version: 3.3.9
• JDK Version: 1.8.0_192
• Container Version (If Applicable): Tomcat 8.0.20 & 8.0.53
• Hibernate: 5.2.17.Final & 5.1.16.Final

Example Application

zipped: grails3-datasources.zip

• cannot share Oracle instance information, nor ojdbc7.jar

Description

The thing is, that when I'm deploying that application Hibernate mixes the dialect and driver for secondary datasource. Trying to read schema information using H2 dialect and ojdbc driver.

The only difference when deploying is that DEFAULT datasource is configured with jndiName

Similar problems found:

• https://github.com/grails/grails-core/issues/701 (similar, but resolved as 'cannot reproduce' but proof was given using both datasources with same driver/dialect)
• https://stackoverflow.com/questions/53351943/hibernate-error-when-hbm2ddl-auto-set-to-update (plain hibernate issue, suggesting this may be a problem there rather than Grails)
• https://stackoverflow.com/questions/33549958/multiple-data-sources-configuration (Grails 2.5.x specific, resolved by reversing primary/secondary, but why should I do that?!)

Relevant block from application.yml:

dataSource:
    jndiName: java:comp/env/jdbc/primary
    dialect: org.hibernate.dialect.Oracle10gDialect
    jmxExport: false
    dbCreate: ''

dataSources:
    secondary:
        pooled: true
        jmxExport: false
        driverClassName: org.h2.Driver
        dialect: org.hibernate.dialect.H2Dialect
        dbCreate: update
        username: sa
        password: ''

Relevant block from Hibernate initialization when deploying:

2018-12-07 13:33:41.688 [localhost-startStop-1] DEBUG [][] o.h.e.j.e.i.JdbcEnvironmentInitiator - Database ->
       name : Oracle
    version : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
      major : 12
      minor : 2
2018-12-07 13:33:41.688 [localhost-startStop-1] DEBUG [][] o.h.e.j.e.i.JdbcEnvironmentInitiator - Driver ->
       name : Oracle JDBC driver
    version : 12.1.0.2.0
      major : 12
      minor : 1
2018-12-07 13:33:41.688 [localhost-startStop-1] DEBUG [][] o.h.e.j.e.i.JdbcEnvironmentInitiator - JDBC version : 4.1
2018-12-07 13:33:41.688 [localhost-startStop-1] INFO  [][] org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.Oracle10gDialect

2018-12-07 13:33:46.387 [localhost-startStop-1] DEBUG [][] o.h.e.j.e.i.JdbcEnvironmentInitiator - Database ->
       name : Oracle
    version : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
      major : 12
      minor : 2
2018-12-07 13:33:46.387 [localhost-startStop-1] DEBUG [][] o.h.e.j.e.i.JdbcEnvironmentInitiator - Driver ->
       name : Oracle JDBC driver
    version : 12.1.0.2.0
      major : 12
      minor : 1
2018-12-07 13:33:46.387 [localhost-startStop-1] DEBUG [][] o.h.e.j.e.i.JdbcEnvironmentInitiator - JDBC version : 4.1
2018-12-07 13:33:46.387 [localhost-startStop-1] INFO  [][] org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.H2Dialect

And the stacktrace of execption caused by mixup of Dialect and Driver (not a result of application.yml):

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.grails.orm.hibernate.HibernateDatastore]: Constructor threw exception; nested exception is org.hibernate.exception.SQLGrammarException: Unable to build DatabaseInformation
	at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:154)
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:122)
	at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:271)
	... 58 common frames omitted

Caused by: org.hibernate.exception.SQLGrammarException: Unable to build DatabaseInformation
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:140)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:312)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
	at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:710)
	at org.grails.orm.hibernate.cfg.HibernateMappingContextConfiguration.buildSessionFactory(HibernateMappingContextConfiguration.java:276)
	at org.grails.orm.hibernate.connections.HibernateConnectionSourceFactory.create(HibernateConnectionSourceFactory.java:86)
	at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:39)
	at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:23)
	at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:64)
	at org.grails.datastore.mapping.core.connections.InMemoryConnectionSources.<init>(InMemoryConnectionSources.groovy:26)
	at org.grails.datastore.mapping.core.connections.ConnectionSourcesInitializer.create(ConnectionSourcesInitializer.groovy:28)
	at org.grails.orm.hibernate.HibernateDatastore.<init>(HibernateDatastore.java:201)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:142)
	... 60 common frames omitted

Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: brak wyraࠥnia
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
	at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:210)
	at com.sun.proxy.$Proxy83.executeQuery(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
	at com.sun.proxy.$Proxy83.executeQuery(Unknown Source)
	at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorH2DatabaseImpl.extractMetadata(SequenceInformationExtractorH2DatabaseImpl.java:37)
	at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)
	at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)
	at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:132)
	... 79 common frames omitted

Ona side note: If I don't provide a dialect for the H2 datasource another exception is raised, even when run locally:

Caused by: org.h2.jdbc.JdbcSQLException: Table "ALL_SEQUENCES" not found; SQL statement:
 select sequence_name from all_sequences  union select synonym_name   from all_synonyms us, all_sequences asq  where asq.sequence_name = us.table_name    and asq.sequence_owner = us.table_owner [42102-197]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
        at org.h2.message.DbException.get(DbException.java:179)
        at org.h2.message.DbException.get(DbException.java:155)
        at org.h2.command.Parser.readTableOrView(Parser.java:5920)
        at org.h2.command.Parser.readTableFilter(Parser.java:1430)
        at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:2138)
        at org.h2.command.Parser.parseSelectSimple(Parser.java:2287)
        at org.h2.command.Parser.parseSelectSub(Parser.java:2133)
        at org.h2.command.Parser.parseSelectUnion(Parser.java:1946)
        at org.h2.command.Parser.parseSelect(Parser.java:1919)
        at org.h2.command.Parser.parsePrepared(Parser.java:463)
        at org.h2.command.Parser.parse(Parser.java:335)
        at org.h2.command.Parser.parse(Parser.java:307)
        at org.h2.command.Parser.prepareCommand(Parser.java:278)
        at org.h2.engine.Session.prepareLocal(Session.java:611)
        at org.h2.engine.Session.prepareCommand(Session.java:549)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
        at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:78)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springsource.loaded.ri.ReflectiveInterceptor.jlrMethodInvoke(ReflectiveInterceptor.java:1427)
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:210)
        at com.sun.proxy.$Proxy68.executeQuery(Unknown Source)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springsource.loaded.ri.ReflectiveInterceptor.jlrMethodInvoke(ReflectiveInterceptor.java:1427)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy68.executeQuery(Unknown Source)
        at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:42)
        at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)
        at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)
        at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:130)
        ... 71 common frames omitted

[zoladkow]

Workaround: just configure primary directly to avoid using JNDI. Which kind of pinpoints where the problem lies.

I can use this workaround but only on nonproduction environments.

[amishpr]

@zoladkow Can you please explain your workaround in further detail? I am also having the same issue.

[zoladkow]

@javacoldbrew quite simply I drop the use of jndiName and define the datasource on deployment profile directly in application.yml - hence the "workaround" ;)

[amishpr]

@zoladkow Oh okay, thank you for getting back to me!