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

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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!





--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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.

Regards,
Iwao


On Thu, Aug 2, 2018 at 6:51 AM 'Rod Simpson' via mybatis-user <[hidden email]> wrote:
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!





--
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 [hidden email].
For more options, visit 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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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


On Friday, August 3, 2018 at 4:07:14 AM UTC-6, Iwao AVE! wrote:
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.
<a href="https://github.com/mybatis/mybatis-3/issues/748" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F748\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEwuew8xLNFqufL5b7q4HdvkLby1w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F748\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEwuew8xLNFqufL5b7q4HdvkLby1w&#39;;return true;">https://github.com/mybatis/mybatis-3/issues/748

Regards,
Iwao


On Thu, Aug 2, 2018 at 6:51 AM 'Rod Simpson' via mybatis-user <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="Kp0i5mNDAgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@...> wrote:
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!





--
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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="Kp0i5mNDAgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">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 [hidden email].
For more options, visit https://groups.google.com/d/optout.