Error accessing PooledConnection. Connection is invalid.

[Matic Petek]

Hi,

  When we run our app in production (around 5-10 current client connections), we randomly get a chunk of "Error accessing PooledConnection. Connection is invalid." errors. It happened between execution of different query sentences on some SqlSession or before commit is executed. Af corse we create new SqlSession for every client request and we call close() on the end. 

  When I check source code, this error is happened when connection is mark as "invalid" in connection pool (PooledConnection). This can happened only in this cases :

- PooledDataSource.forceCloseAll ; I know this is not happened, because log message "PooledDataSource forcefully closed/removed all connections." is only at application startup.

- PooledDataSource.popConnection(...) / pushConnection(...)

  Then I thought we have call close() somewhere between. But we probably didn't, because otherwise we would get an error "Executor was closed.". 

We are using 3.0.5 version with tomcat 6.x and without any DI framework. 

Any idea how to debug / solve this problem? Thank you.

 Regards,

     Matic

[Eduardo Macarron]

Yep, upgrade MyBatis. 3.0.x version can throw the connection is invalid if a log accesses a closed connection. That was fixed in 3.1 IIRC.

So. Go latest 3.2.x. MyBatis is backwards compatible. 

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Matic Petek]

Thank you Eduardo. 

  Yes, we will upgrade. But first we have to fix our app for this bug - https://groups.google.com/forum/#!topic/mybatis-user/OPvwdb59ufQ

Regards,

  Matic

[Matic Petek]

Hi,

  We are still getting this error from time to time - specially with long running jobs (job is open session and processing some data for 30 minutes ; meantime 10 other client requests are accessing some other services that open and close sessions). Frankly speaking we have now less trouble then with 3.0.5 version, but problem still exist. 

  Now we are working on setting test environment for simulating this trouble. Bet meantime - any advice how to approach to this problem? Thank you,

Regards,

   Matic

[Eduardo Macarron]

Can you please post the stacktrace of the exception?

[Matic Petek]

Hi,

  See below. We are using 3.2.6 version of myBatis and 2.3.4 of iBatis.

+++++ Example 1 : reading via MyBatis

### Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid. 

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) 

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154) 

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141) 

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51) 

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) 

at com.sun.proxy.$Proxy59.insertEShopSyncLog(Unknown Source) 

at com.myapp(EShopSync.java:499) 

[other our classes]

Caused by: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid. 

at org.apache.ibatis.datasource.pooled.PooledConnection.checkConnection(PooledConnection.java:255) 

at org.apache.ibatis.datasource.pooled.PooledConnection.invoke(PooledConnection.java:244) 

at com.sun.proxy.$Proxy0.prepareStatement(Unknown Source) 

at sun.reflect.GeneratedMethodAccessor64.invoke(Unknown Source) 

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 

at java.lang.reflect.Method.invoke(Method.java:606) 

at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:54) 

at com.sun.proxy.$Proxy0.prepareStatement(Unknown Source) 

at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:75) 

at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:85) 

at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:57) 

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:73) 

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:47) 

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105) 

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71) 

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152) 

+++++ Example 2 : we still have some old code in iBatis 2.4.x. So we create SqlMapSession from Connection from SqlSession. 

--- Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid. 

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201) 

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139) 

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567) 

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541) 

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118) 

at com.myapp(MyApp.java:3325) 

[other our classes]

Caused by: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid. 

at org.apache.ibatis.datasource.pooled.PooledConnection.checkConnection(PooledConnection.java:255) 

at org.apache.ibatis.datasource.pooled.PooledConnection.invoke(PooledConnection.java:244) 

at com.sun.proxy.$Proxy0.prepareStatement(Unknown Source) 

at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:505) 

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:176) 

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221) 

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189) 

[Eduardo Macarron]

I am afraid this problem is not the one I told you. This happens while calling  prepareStatement(), not toString().

Caused by: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid. 

at org.apache.ibatis.datasource.pooled.PooledConnection.checkConnection(PooledConnection.java:255) 

at org.apache.ibatis.datasource.pooled.PooledConnection.invoke(PooledConnection.java:244) 

at com.sun.proxy.$Proxy0.prepareStatement(Unknown Source) 

We appreciate that you investigate the problem but If I were you I would try 1st with another connection pool like c3p0 or dbcp. 

[Guy Rouillier]

30 minutes is a typical timeout for database connections. I know from
having worked with PostgreSQL, for example, that 30 minutes is the
default timeout for it. So, make sure that both your connection pool
and your database backend allow you to keep connections open for that long.

> <https://groups.google.com/d/optout>.

>
>
> --
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mybatis-user...@googlegroups.com

> <mailto:mybatis-user...@googlegroups.com>.

> For more options, visit https://groups.google.com/d/optout.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[Eduardo Macarron]

That makes a lot sense Guy. Thank you!

an email to mybatis-user+unsubscribe@googlegroups.com
<mailto:mybatis-user+unsub...@googlegroups.com>.

For more options, visit https://groups.google.com/d/optout.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

[Matic Petek]

Guy & Eduardo,

  Thank you for help. I have check timeout parameter on our MySQL and it's 8 hours. We also get this exceptions for relatively short running operation (couple of minutes). We have now try update to c0p3 and I will be back in day or two with results.

  Regards,

     Matic

an email to mybatis-user...@googlegroups.com
<mailto:mybatis-user+unsubscrib...@googlegroups.com>.

For more options, visit https://groups.google.com/d/optout.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jose María Zaragoza]

2014-04-15 9:18 GMT+02:00 Matic Petek <matic...@gmail.com>:
> Guy & Eduardo,
> Thank you for help. I have check timeout parameter on our MySQL and it's 8
> hours. We also get this exceptions for relatively short running operation
> (couple of minutes). We have now try update to c0p3 and I will be back in
> day or two with results.
> Regards,
> Matic

I guess that you are using Tomcat JDBC pool
Could you paste the settings ? I mean the <Context> element

I use Tomcat 6.x and I always set validationQuery = SELECT 1 ( or
whatever ) and testOnBorrow = true
And it works fine

Lately , I use Tomcat 7 JDBC pool implementation within my web application.
(Take care default values in Tomcat 7 JDBC pool because they are
different to Tomcat 6)

>>>> <mailto:mybatis-user...@googlegroups.com>.