extractDatabaseMetaData and TX datasources

[jocsch]

I updated to the release candidate of spring 1.2 and now I get the following warning.

15172 [main] WARN  org.springframework.jdbc.support.SQLErrorCodesFactory  - Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: SQL operations are not allowed with no global transaction by default for XA drivers. If the XA driver supports performing SQL operations with no global transaction, explicitly allow it by setting "SupportsLocalTransaction" JDBC connection pool property to true. In this case, also remember to complete the local transaction before using the connection again for global transaction, else a XAER_OUTSIDE XAException may result. To complete a local transaction, you can either set auto commit to true or call Connection.commit() or Connection.rollback().
java.sql.SQLException: SQL operations are not allowed with no global transaction by default for XA drivers. If the XA driver supports performing SQL operations with no global transaction, explicitly allow it by setting "SupportsLocalTransaction" JDBC connection pool property to true. In this case, also remember to complete the local transaction before using the connection again for global transaction, else a XAER_OUTSIDE XAException may result. To complete a local transaction, you can either set auto commit to true or call Connection.commit() or Connection.rollback().
	at weblogic.rjvm.BasicOutboundRequest.sendReceive(BasicOutboundRequest.java:108)
	at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:137)
	at weblogic.jdbc.rmi.internal.ConnectionImpl_weblogic_jdbc_wrapper_JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection_814_WLStub.getMetaData(Unknown Source)
	at weblogic.jdbc.rmi.SerialConnection.getMetaData(SerialConnection.java:311)
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:138)
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:173)
	at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:185)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:113)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>&#40;SQLErrorCodeSQLExceptionTranslator.java&#58;92&#41;
	at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator&#40;JdbcAccessor.java&#58;81&#41;
	at org.springframework.jdbc.support.JdbcAccessor.afterPropertiesSet&#40;JdbcAccessor.java&#58;117&#41;
	at org.springframework.orm.ibatis.SqlMapClientTemplate.afterPropertiesSet&#40;SqlMapClientTemplate.java&#58;144&#41;
	at org.springframework.orm.ibatis.support.SqlMapClientDaoSupport.afterPropertiesSet&#40;SqlMapClientDaoSupport.java&#58;109&#41;
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods&#40;AbstractAutowireCapableBeanFactory.java&#58;1075&#41;
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean&#40;AbstractAutowireCapableBeanFactory.java&#58;349&#41;
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean&#40;AbstractAutowireCapableBeanFactory.java&#58;257&#41;
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean&#40;AbstractBeanFactory.java&#58;222&#41;
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean&#40;AbstractBeanFactory.java&#58;146&#41;
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons&#40;DefaultListableBeanFactory.java&#58;291&#41;
	at org.springframework.context.support.AbstractApplicationContext.refresh&#40;AbstractApplicationContext.java&#58;317&#41;
	at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh&#40;AbstractRefreshableWebApplicationContext.java&#58;131&#41;
	at org.springframework.web.context.ContextLoader.createWebApplicationContext&#40;ContextLoader.java&#58;224&#41;
	at org.springframework.web.context.ContextLoader.initWebApplicationContext&#40;ContextLoader.java&#58;150&#41;
	at org.springframework.web.context.ContextLoaderListener.contextInitialized&#40;ContextLoaderListener.java&#58;48&#41;
	at org.mortbay.jetty.servlet.WebApplicationContext.doStart&#40;WebApplicationContext.java&#58;498&#41;
	at org.mortbay.util.Container.start&#40;Container.java&#58;72&#41;
	at org.mortbay.http.HttpServer.doStart&#40;HttpServer.java&#58;695&#41;
	at org.mortbay.util.Container.start&#40;Container.java&#58;72&#41;
	at org.mortbay.jetty.Server.main&#40;Server.java&#58;433&#41;
	at sun.reflect.NativeMethodAccessorImpl.invoke0&#40;Native Method&#41;
	at sun.reflect.NativeMethodAccessorImpl.invoke&#40;NativeMethodAccessorImpl.java&#58;39&#41;
	at sun.reflect.DelegatingMethodAccessorImpl.invoke&#40;DelegatingMethodAccessorImpl.java&#58;25&#41;
	at java.lang.reflect.Method.invoke&#40;Method.java&#58;324&#41;
	at org.mortbay.start.Main.invokeMain&#40;Main.java&#58;151&#41;
	at org.mortbay.start.Main.start&#40;Main.java&#58;480&#41;
	at org.mortbay.start.Main.main&#40;Main.java&#58;94&#41;

The error occurs because the extractDatabaseMetaData directly access a TX enabled datasource (in weblogic) without being wrapped in a transaction .

Is there a possibility to avoid this error?

Thanks,
Markus

[trisberg]

I've been meaning to introduce a way of explicitly specifying the Database Product Name so we don't have to look it up in the meta data. Now seems like a good time

I'm thinking of introducing a DbNameProvidingDataSourceProxy that would wrap a DataSource and provide the SQLErrorCodeFactory with the specified database product name without a lookup. Should be easy to implement before the next release of 1.2RC2.

I'll let you know when it is available in the nightly builds.

[Juergen Hoeller]

Setting JdbcTemplate's "lazyInit" flag to "true" might help in such a scenario too: this effectively leads to the SQLExceptionTranslator only being initialized when needed, i.e. on the first SQLException thrown. The metadata access should work there even with a strict XA DataSource, as it will usually happen within a transaction (before the rollback).

Thomas, if we intend to provide a way to explicitly specify the database product name, we should discuss how to go about this. I'm not too keen on introducing another DataSource proxy. Maybe we can add this to LazyConnectionDataSourceProxy, which wouldn't be a bad fit? We could even try to return a lazy DatabaseMetaData proxy there, returning pre-specified info like the database product name without actually fetching a target Connection.

Juergen

[trisberg]

Jeurgen,

I looked at LazyConnectionDataSourceProxy but it seemed to introduce a LazyConnectionInvocationHandler as well, which might not be necessary if all we want to avoid is this initial meta data lookup.

We really only need a way of providing the database product name to the class using SQLErrorCodesFactory. There already is a "SQLErrorCodes getErrorCodes(String dbName)" method on the SQLErrorCodesFactory.

This could also be done via a SQLExceptionTranslatorBeanFactory that could be referenced in an applicationContext and used to set the translator on JdbcTemplate, HibernateTemplate etc.

A DataSourceProxy would apply everywhere it is used while the BeanFactory has to be explicitly wired to the XxxxTemplate that does the first database access.

Thomas

[Juergen Hoeller]

Thomas,

How would a separate dbName-aware DataSource proxy work? Would it simply carry a dbName bean property and delegate for everything else directly to the target DataSource? Making the dbName available through a special method in addition to the standard DataSource interface, with JdbcTemplate doing an instanceof check on the passed-in DataSource?

Actually, why don't we just simply change JdbcTemplate's default "lazyInit" flag to "true"? This would lead to autodetection on the first actual SQLException thrown, which I guess should always work - there is always an active Connection in that case (at least active enough to return DatabaseMetaData). If someone wants to enforce early initialization, "lazyInit" can still explicitly be switched to "false".

Juergen

[Juergen Hoeller]

Actually, such lazy initialization of JdbcTemplate's SQLExceptionTranslator has more than one benefit: not only that it works with strict XA DataSources, but also that it will work with a database that starts up after the application.

The latter scenario will currently lead to a default SQLErrorCodes instance being held, without vendor-code-specific exception translation for the entire running time of the application. This is actually a pretty bad effect, and arguably already warrants switching default "lazyInit" to "true".

I guess I'll do that switch, as it is probably the better default, and still gives proper detection without explicitly specifying the database product name anywhere. What do you think?

Juergen

[Juergen Hoeller]

I've just completed the switch to "lazyInit" with default "true", and also added "setDatabaseProductName" methods to SQLErrorCodesSQLExceptionTranslator and JdbcAccessor. Furthermore, I've changed all Hibernate and JDO accessors to lazily initialize the SQLExceptionTranslator too.

Juergen

[trisberg]

Juergen,

My idea for the DataSourceProxy is what you describe - an additional metthod that would return the database named. This could be used after an instanceof check.

The solutions you later discuss will work as well and I don't really see a need for providing this proxy anymore.

Did you comitt these changes? I have not seen anything in CVS yet.

[Juergen Hoeller]

I'm just about to commit everything, after refining the test cases too.

I think this will work sufficiently well: lazy initialization of the SQLExceptionTranslator is already used by numerous people, currently explicitly calling JdbcTemplate.setLazyInit(true). In addition to this, people can also call setDatabaseProductName now, to enforce eager initialization for a specific database.

In general, I think that setting up a shared JdbcTemplate (or HibernateTemplate etc) instance as bean in the application context can be a viable way, if there is more than just a DataSource (or SessionFactory) to configure. The template essentially serves as holder for multiple configuration settings then (in Hibernate, examples are cache settings and the JDBC exception translator as well). This is also the reason why JdbcDaoSupport and HibernateDaoSupport accept a template instance as alternative to a DataSource/SessionFactory reference.

On the other hand, the default behavior should be fine for most cases now: lazy initialization of the SQLExceptionTranslator on first encounter of a SQLException, with the metadata of the current Connection there. So there's no strong need for setting up a shared JdbcTemplate bean in the first place, and I guess no real need for a special DataSource proxy either.

Juergen

[trisberg]

The only thing that worries be a bit is the altered behavior in terms of the lazy initialization being the default. I vaguely remember trying this earlier and running into problems with some databases not being happy after an exception was thrown and not allowing us to get the meta data at this point. Maybe we should leave the default as eager initialization but make all the other changes.

[jocsch]

Wow. Amazing to see how fast you guys are. I still have a, maybe very simple, question. How can I set layzinit true if I fetch my datasource from JNDI? Doing so I deal with a JndIObjectFactoryBean and not a datasource directly.

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName"><value>jdbc/oracle</value></property>
    </bean>

Thanks,
Markus