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

[Rod Simpson]

Hello all,

I am seeing this kind of error in our application when under modest loads:

"org.apache.ibatis.exceptions.PersistenceException: ",

    "### Error querying database.  Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.",

    "### The error may exist in com/api/resources/menutype/MenuTypeMapper.java (best guess)",

    "### The error may involve com.resources.menutype.MenuTypeMapper.getAll",

    "### The error occurred while executing a query",

    "### SQL: SELECT * from menu_types where site_id = ?",

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

    "\tat org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)",

    "\tat org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)",

    "\tat org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)",

    "\tat org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)",

    "\tat org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)",

    "\tat org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)",

    "\tat com.sun.proxy.$Proxy104.getAll(Unknown Source)",

..."

The specific SQL statement/module varies.  It happens against RDS Postgres in AWS,  as well as locally (postgres). No firewall in the way.  Can reproduce locally and in production with a simple load test. have the pool configured like so (and have tried playing around with different settings:

<dataSource type="POOLED">
    <property name="driver" value="${driver}"/>
    <property name="url" value="${url}"/>
    <property name="username" value="${username}"/>
    <property name="password" value="${password}"/>
    <property name="poolMaximumActiveConnections" value="50"/> <== have also tried 10, 20,and 30
    <property name="poolMaximumIdleConnections" value="20"/> <== have also tried 5, 10
    <property name="poolMaximumCheckoutTime" value="5000"/> <== have also tried 20000
</dataSource>

Hoping someone can point me in the right direction.  Thanks!

[Iwao AVE!]

Hi Rod,

If you haven't tried, setting poolPingEnabled and poolPingQuery might help.

Also, you should use the latest version 3.4.6.

There was a possibly related fix a while ago.

https://github.com/mybatis/mybatis-3/issues/748

Regards,

Iwao

--
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.

[Rod Simpson]

Thank you for the response, Iwao!  

I actually figured out what the problem was.  In my case,  the code allocates one session per request, and closes the session at the end of the call.  This has generally been working quite well - once the pool is exhausted, requests simply queue until others complete.  But, I found a couple of methods that try to allocate a new session.  I was using gatling to hit the server, which exhausted the connection pool, so when these methods tried to allocate the session, they would block until connections started to time out (and threw the error I mentioned in the subject).  I just changed the logic in these methods to reuse the existing session and not allocate new sessions.  Everything is working great now.

Rod