You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Gilles Schlienger <s_...@yahoo.com> on 2008/08/19 12:11:11 UTC
Re : Question: "Connection.close has already been closed"
Actually, if you use the Spring way of plugging into iBATIS, Spring is setting for you the transaction manager to the "External" one.
The javadoc of the Spring classes is well documented and the source code is not too complicated.
However, I discovered yesterday (funny :) that the datasource that you inject into you ibatis factory (SqlMapClientFactoryBean) needs to be "transactional aware"
In the class: org.springframework.orm.ibatis.SqlMapClientTemplate
you will see in the "execute" method:
boolean transactionAware = (dataSource instanceof TransactionAwareDataSourceProxy);
[...]
springCon = (transactionAware ?
dataSource.getConnection() : DataSourceUtils.doGetConnection(dataSource));
So my understanding would be that you need to encapsulate the datasource in a TransactionAwareDataSourceProxy and inject it in the SqlMapClientFactoryBean.
I hope this helps!
Regards
Gilles
----- Message d'origine ----
De : Brian Parkinson <pa...@avaning.com>
À : user-java@ibatis.apache.org
Envoyé le : Lundi, 18 Août 2008, 22h54mn 43s
Objet : RE: Question: "Connection.close has already been closed"
Clinton writes:
"Make
sure the iBATIS TransactionManager is set to EXTERNAL (in SqlMapConfig.xml or
equivalent). "
Aha. I had no TransactionManager element defined in my
SqlMapConfig. Whoops?
I was using Spring to configure the
datasource:
<bean id="mapConfig"
class="org.springframework.core.io.ClassPathResource">
<constructor-arg>
<value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value>
</constructor-arg>
</bean>
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName"
value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql:///ecobee" />
<property name="username"
value="XXX" />
<property name="password" value="YYY"
/>
<property name="initialSize" value="10"
/>
<property name="maxActive" value="64"
/>
<property name="maxIdle" value="16"
/>
<property name="maxWait" value="1000"
/>
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property
name="dataSource" ref="dataSource" />
<property
name="configLocation" ref="mapConfig"
/>
</bean>
but I
had no TransactionManager defined in my SqlMapConfig.xml file.
Am I
correct in assuming that I just need to add:
<transactionManager
type="EXTERNAL">
<dataSource
type="DBCP">
</dataSource>
</transactionManager>
to my
SqlMapConfig file?
If so,
should I move the properly declarations from the spring bean (id="dataSource")
into the SqlMapConfig file (under the transactionManager)
element?
Any
advice on best practices appreciated - the configuraiton of the datasource,
spring, transaction manager, etc. isn't completely clear to
me.
Cheers,
Brian
Parkinson...
________________________________
From: Clinton Begin
[mailto:clinton.begin@gmail.com]
Sent: Monday, August 18, 2008 4:19
PM
To: user-java@ibatis.apache.org
Subject: Re: Question:
"Connection.close has already been closed"
Based on the stack trace, it doesn't look like iBATIS ever even
gets the connection... so this looks like a Spring/DBCP related issue.
That said, if iBATIS prematurely closed the connection and DBCP didn't
check the connection for validity, that would cause this problem.
But
the root cause would be why iBATIS is closing the connection. Make sure the
iBATIS TransactionManager is set to EXTERNAL (in SqlMapConfig.xml or
equivalent).
Clinton
On Mon, Aug 18, 2008 at 2:13 PM, Brian Parkinson <pa...@avaning.com> wrote:
Hi all:
I'm using iBatis 2.3.3.720 and Spring and dbcp 1.2.2. All seems to be
well, but sometimes I see an exception related to "Connection.close()
has already been called".
When this happens, the server is pretty much borked - any new database
access results in the exception.
I never call close() in my code - unsure what's going on here. A quick
inspection of my logs didn't reveal anything...
Does anyone have any clues?
Any help is appreciated.
<bean id="mapConfig"
class="org.springframework.core.io.ClassPathResource">
<constructor-arg>
<value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value>
</constructor-arg>
</bean>
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"
value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///ecobee" />
<property name="username" value="ecobee" />
<property name="password" value="ecobee" />
<property name="initialSize" value="10" />
<property name="maxActive" value="64" />
<property name="maxIdle" value="16" />
<property name="maxWait" value="1000" />
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" ref="mapConfig" />
</bean>
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
>
<property name="dataSource" ref="dataSource"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="get*" read-only="true" />
<tx:method name="*" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="daoServiceOperation"
expression="execution(* com.ecobee.foundation.dao.ibatis.*.*(..))" />
<aop:advisor advice-ref="txAdvice"
pointcut-ref="daoServiceOperation" />
</aop:config>
Thanks.
Brian Parkinson
----------- x8 snip
ERROR 15:45:16:428 Error selecting user from dataabase.
{foundation.schedule.DbConnectionPing.executeTask}
Message: Could not open JDBC Connection for transaction; nested
exception is
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:
Connection.close() has already been called. Invalid operation in this
state.
Trace org.springframework.transaction.CannotCreateTransactionException:
org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin
(DataSourceTransactionManager.java:238)
org.springframework.transaction.support.AbstractPlatformTransactionManag
er.getTransaction(AbstractPlatformTransactionManager.java:377)
org.springframework.transaction.interceptor.TransactionAspectSupport.cre
ateTransactionIfNecessary(TransactionAspectSupport.java:261)
org.springframework.transaction.interceptor.TransactionInterceptor.invok
e(TransactionInterceptor.java:101)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(E
xposeInvocationInterceptor.java:89)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAo
pProxy.java:204)
$Proxy5.getUser(Unknown Source)
>>>
com.ecobee.foundation.schedule.DbConnectionPing.executeTask(DbConnection
Ping.java:27)
com.ecobee.foundation.schedule.ScheduledService.executeSchedule(Schedule
dService.java:38)
sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:275)
org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean
$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.ja
va:272)
org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBea
n.java:86)
org.quartz.core.JobRunShell.run(JobRunShell.java:202)
org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java
:529)
Message: Connection.close() has already been called. Invalid operation
in this state.
Nested exception trace
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
com.mysql.jdbc.Connection.getMutex(Connection.java:3728)
com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5365)
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon
nection.java:331)
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.set
AutoCommit(PoolingDataSource.java:317)
org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin
(DataSourceTransactionManager.java:221)
org.springframework.transaction.support.AbstractPlatformTransactionManag
er.getTransaction(AbstractPlatformTransactionManager.java:377)
org.springframework.transaction.interceptor.TransactionAspectSupport.cre
ateTransactionIfNecessary(TransactionAspectSupport.java:261)
org.springframework.transaction.interceptor.TransactionInterceptor.invok
e(TransactionInterceptor.java:101)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(E
xposeInvocationInterceptor.java:89)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAo
pProxy.java:204)
$Proxy5.getUser(Unknown Source)
>>>
com.ecobee.foundation.schedule.DbConnectionPing.executeTask(DbConnection
Ping.java:27)
com.ecobee.foundation.schedule.ScheduledService.executeSchedule(Schedule
dService.java:38)
sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:275)
org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean
$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.ja
va:272)
org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBea
n.java:86)
org.quartz.core.JobRunShell.run(JobRunShell.java:202)
org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java
:529)
_____________________________________________________________________________
Envoyez avec Yahoo! Mail. Une boite mail plus intelligente http://mail.yahoo.fr
RE: Re : Question: "Connection.close has already been closed"
Posted by Brian Parkinson <pa...@avaning.com>.
Thanks Gilles (and Clinton):
Here is the config in gory detail - looks like there might be some use in posting it for others - using Spring for transactions (external aop) as well as DBCP for connection pooling.
Hopefully this is all correct now. :)
SqlMapConfig contains:
<transactionManager type="EXTERNAL">
<dataSource type="DBCP">
</dataSource>
</transactionManager>
Spring transactions:
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="get*" read-only="true" />
<tx:method name="*" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="daoServiceOperation" expression="execution(* com.ecobee.foundation.dao.ibatis.*.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="daoServiceOperation" />
</aop:config>
Spring datasource:
<bean id="mapConfig" class="org.springframework.core.io.ClassPathResource">
<constructor-arg>
<value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value>
</constructor-arg>
</bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
<property name="targetDataSource">
<ref local="dataSourceImpl"/>
</property>
</bean>
<bean id="dataSourceImpl" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///ecobee" />
<property name="username" value="XXX" />
<property name="password" value="YYY" />
<property name="initialSize" value="10" />
<property name="maxActive" value="100" />
<property name="maxIdle" value="16" />
<property name="maxWait" value="2000" />
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" ref="mapConfig" />
</bean>
Regards,
Brian...
________________________________
From: Gilles Schlienger [mailto:s_gilou@yahoo.com]
Sent: Tuesday, August 19, 2008 6:11 AM
To: user-java@ibatis.apache.org
Subject: Re : Question: "Connection.close has already been closed"
Actually, if you use the Spring way of plugging into iBATIS, Spring is setting for you the transaction manager to the "External" one.
The javadoc of the Spring classes is well documented and the source code is not too complicated.
However, I discovered yesterday (funny :) that the datasource that you inject into you ibatis factory (SqlMapClientFactoryBean) needs to be "transactional aware"
In the class: org.springframework.orm.ibatis.SqlMapClientTemplate
you will see in the "execute" method:
boolean transactionAware = (dataSource instanceof TransactionAwareDataSourceProxy);
[...]
springCon = (transactionAware ?
dataSource.getConnection() : DataSourceUtils.doGetConnection(dataSource));
So my understanding would be that you need to encapsulate the datasource in a TransactionAwareDataSourceProxy and inject it in the SqlMapClientFactoryBean.
I hope this helps!
Regards
Gilles
----- Message d'origine ----
De : Brian Parkinson <pa...@avaning.com>
À : user-java@ibatis.apache.org
Envoyé le : Lundi, 18 Août 2008, 22h54mn 43s
Objet : RE: Question: "Connection.close has already been closed"
Clinton writes:
"Make sure the iBATIS TransactionManager is set to EXTERNAL (in SqlMapConfig.xml or equivalent). "
Aha. I had no TransactionManager element defined in my SqlMapConfig. Whoops?
I was using Spring to configure the datasource:
<bean id="mapConfig" class="org.springframework.core.io.ClassPathResource">
<constructor-arg>
<value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value>
</constructor-arg>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///ecobee" />
<property name="username" value="XXX" />
<property name="password" value="YYY" />
<property name="initialSize" value="10" />
<property name="maxActive" value="64" />
<property name="maxIdle" value="16" />
<property name="maxWait" value="1000" />
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" ref="mapConfig" />
</bean>
but I had no TransactionManager defined in my SqlMapConfig.xml file.
Am I correct in assuming that I just need to add:
<transactionManager type="EXTERNAL">
<dataSource type="DBCP">
</dataSource>
</transactionManager>
to my SqlMapConfig file?
If so, should I move the properly declarations from the spring bean (id="dataSource") into the SqlMapConfig file (under the transactionManager) element?
Any advice on best practices appreciated - the configuraiton of the datasource, spring, transaction manager, etc. isn't completely clear to me.
Cheers,
Brian Parkinson...
________________________________
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: Monday, August 18, 2008 4:19 PM
To: user-java@ibatis.apache.org
Subject: Re: Question: "Connection.close has already been closed"
Based on the stack trace, it doesn't look like iBATIS ever even gets the connection... so this looks like a Spring/DBCP related issue.
That said, if iBATIS prematurely closed the connection and DBCP didn't check the connection for validity, that would cause this problem.
But the root cause would be why iBATIS is closing the connection. Make sure the iBATIS TransactionManager is set to EXTERNAL (in SqlMapConfig.xml or equivalent).
Clinton
On Mon, Aug 18, 2008 at 2:13 PM, Brian Parkinson <pa...@avaning.com> wrote:
Hi all:
I'm using iBatis 2.3.3.720 and Spring and dbcp 1.2.2. All seems to be
well, but sometimes I see an exception related to "Connection.close()
has already been called".
When this happens, the server is pretty much borked - any new database
access results in the exception.
I never call close() in my code - unsure what's going on here. A quick
inspection of my logs didn't reveal anything...
Does anyone have any clues?
Any help is appreciated.
<bean id="mapConfig"
class="org.springframework.core.io.ClassPathResource">
<constructor-arg>
<value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value>
</constructor-arg>
</bean>
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"
value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///ecobee" />
<property name="username" value="ecobee" />
<property name="password" value="ecobee" />
<property name="initialSize" value="10" />
<property name="maxActive" value="64" />
<property name="maxIdle" value="16" />
<property name="maxWait" value="1000" />
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" ref="mapConfig" />
</bean>
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
>
<property name="dataSource" ref="dataSource"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="get*" read-only="true" />
<tx:method name="*" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="daoServiceOperation"
expression="execution(* com.ecobee.foundation.dao.ibatis.*.*(..))" />
<aop:advisor advice-ref="txAdvice"
pointcut-ref="daoServiceOperation" />
</aop:config>
Thanks.
Brian Parkinson
----------- x8 snip
ERROR 15:45:16:428 Error selecting user from dataabase.
{foundation.schedule.DbConnectionPing.executeTask}
Message: Could not open JDBC Connection for transaction; nested
exception is
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:
Connection.close() has already been called. Invalid operation in this
state.
Trace org.springframework.transaction.CannotCreateTransactionException:
org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin
(DataSourceTransactionManager.java:238)
org.springframework.transaction.support.AbstractPlatformTransactionManag
er.getTransaction(AbstractPlatformTransactionManager.java:377)
org.springframework.transaction.interceptor.TransactionAspectSupport.cre
ateTransactionIfNecessary(TransactionAspectSupport.java:261)
org.springframework.transaction.interceptor.TransactionInterceptor.invok
e(TransactionInterceptor.java:101)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(E
xposeInvocationInterceptor.java:89)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.framework.JdkDyna micAopProxy.invoke(JdkDynamicAo
pProxy.java:204)
$Proxy5.getUser(Unknown Source)
>>>
com.ecobee.foundation.schedule.DbConnectionPing.executeTask(DbConnection
Ping.java:27)
com.ecobee.foundation.schedule.ScheduledService.executeSchedule(Schedule
dService.java:38)
sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:275)
org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean
$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.ja
va:272)
org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBea
n.java:86)
org.quartz.core.JobRunShell.run(JobRunShell.java:202)
org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java
:529)
Message: Connection.close() has already been called. Invalid operation
in this state.
Nested exception trace
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
com.mysql.jdbc.Connection.getMutex(Connection.java:3728)
com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5365)
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon
nection.java:331)
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.set
AutoCommit(PoolingDataSource.java:317)
org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin
(DataSourceTransactionManager.java:221)
org.springframework.transaction.support.AbstractPlatformTransactionManag
er.getTransaction(AbstractPlatformTransactionManager.java:377)
org.springframework.transaction.interceptor.TransactionAspectSupport.cre
ateTransactionIfNecessary(TransactionAspectSupport.java:261)
org.springframework.transaction.interceptor.TransactionInterceptor.invok
e(Transa ctionInterceptor.java:101)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(E
xposeInvocationInterceptor.java:89)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref
lectiveMethodInvocation.java:171)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAo
pProxy.java:204)
$Proxy5.getUser(Unknown Source)
>>>
com.ecobee.foundation.schedule.DbConnectionPing.executeTask(DbConnection
Ping.java:27)
com.ecobee.foundation.schedule.ScheduledService.executeSchedule(Schedule
dService.java:38)
sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:275)
org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean
$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.ja
va:272)
org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBea
n.java:86)
org.quartz.core.JobRunShell.run(JobRunShell.java:202)
org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java
:529)
________________________________
Envoyé avec Yahoo! Mail <http://us.rd.yahoo.com/mailuk/taglines/isp/control/*http://us.rd.yahoo.com/evt=52423/*http://fr.docs.yahoo.com/mail/overview/index.html> .
Une boite mail plus intelligente.