Connection timeout, seems connection don't go back to the pool

[Pedro Larroy]

Hi there

I'm using a small rest server writen in Xitrum where I have a class to access my data.

I have the problem that after a number of requests equal to ConnectionPoolSettings.maxSize  I get exceptions due to not being able to get connections from the pool. I tried increasing the timeout etc, closing the connection explicitly after each query but doesn't seem to work.

Does anybody have any idea that what I could be doing wrong?

I have a connection pool 'rg  that I'm accessing with the following pattern:

  def readOnly[A](name: scala.Any)(execution: scala.Function1[scalikejdbc.DBSession, A]): A = {

    using(ConnectionPool.borrow(name)) { conn => DB(conn) readOnly execution

    }

  }

 def getSomething... {

    readOnly('rg) { implicit session =>

      val res = sql"""SELECT....

   }

But I always get:

org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114) ~[commons-dbcp-1.4.jar:1.4]

        at scalikejdbc.CommonsConnectionPool.borrow(CommonsConnectionPool.scala:58) ~[scalikejdbc-core_2.10-2.2.2.jar:2.2.2]

        at scalikejdbc.ConnectionPool$.borrow(ConnectionPool.scala:225) ~[scalikejdbc-core_2.10-2.2.2.jar:2.2.2]

        at com.here.routing.RGStorePostgreSQL.readOnly(RGStorePostgreSQL.scala:63) ~[classes/:na]

        at com.here.routing.RGStorePostgreSQL.getSimilarity(RGStorePostgreSQL.scala:401) ~[classes/:na]

        at com.here.vadis.server.action.Tiles.execute(Tiles.scala:18) [classes/:na]

        at xitrum.action.Filter$$anonfun$1.apply$mcV$sp(Filter.scala:59) ~[xitrum_2.10-3.21.jar:3.21]

        at xitrum.action.Filter$class.callExecuteWrappedInAroundFilters(Filter.scala:63) ~[xitrum_2.10-3.21.jar:3.21]

        at com.here.vadis.server.action.Tiles.callExecuteWrappedInAroundFilters(Tiles.scala:12) [classes/:na]

        at xitrum.Action$class.xitrum$Action$$callExecuteWrappedInAroundFiltersThenAfterFilters(Action.scala:183) ~[xitrum_2.10-3.21.jar:3.21]

        at xitrum.Action$class.dispatchWithFailsafe(Action.scala:108) ~[xitrum_2.10-3.21.jar:3.21]

        at com.here.vadis.server.action.Tiles.dispatchWithFailsafe(Tiles.scala:12) [classes/:na]

        at xitrum.handler.inbound.Dispatcher$.dispatch(Dispatcher.scala:45) [xitrum_2.10-3.21.jar:3.21]

        at xitrum.handler.inbound.Dispatcher.channelRead0(Dispatcher.scala:87) [xitrum_2.10-3.21.jar:3.21]

        at xitrum.handler.inbound.Dispatcher.channelRead0(Dispatcher.scala:65) [xitrum_2.10-3.21.jar:3.21]

        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:333) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:319) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at xitrum.handler.inbound.MethodOverrider.channelRead0(MethodOverrider.scala:45) [xitrum_2.10-3.21.jar:3.21]

        at xitrum.handler.inbound.MethodOverrider.channelRead0(MethodOverrider.scala:19) [xitrum_2.10-3.21.jar:3.21]

        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:333) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:319) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at xitrum.handler.inbound.UriParser.channelRead0(UriParser.scala:33) [xitrum_2.10-3.21.jar:3.21]

        at xitrum.handler.inbound.UriParser.channelRead0(UriParser.scala:16) [xitrum_2.10-3.21.jar:3.21]

        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:333) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:319) [netty-all-4.0.24.Final.jar:4.0.24.Final]

        at xitrum.handler.inbound.WebJarsServer.channelRead0(WebJarsServer.scala:33) [xitrum_2.10-3.21.jar:3.21]

        at xitrum.handler.inbound.WebJarsServer.channelRead0(WebJarsServer.scala:23) [xitrum_2.10-3.21.jar:3.21]

        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105) [netty-all-4.0.24.Final.jar:4.0.24.Final]

Does anybody have some idea that might help me? 

Thanks a lot!

Pedro.

[Kazuhiro Sera]

Hi,

Cool> using Xitrum

If you don't always forget using `LoarnPattern.using` when borrowing
connections, connections should be returned to the pool.

I've not figured out your issue completely yet but I'm afraid that
your connection pool max size may be too small for your application.
Or if your connection pool has some damaged connections, setting
ConnectionPools.validationQuery may help you.

commons-dbcp basically works but the library is considered as its
performance is not very good and the library is not actively
maintained for several years.
How about trying other connection pool implementation? Switching
connection pool is pretty easy. I recommend you HikariCP or
commons-dbcp2.

Thanks,
-Kaz

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

[Pedro Larroy]

Hi Kaz

Thanks for your reply and this great library.

I've narrowed it down to some strange problem triggered in xitrum / netty: https://github.com/xitrum-framework/xitrum/issues/502   probably a race condition of some sort.

Why did it interact with the connection pooling of scalikejdbc I have no idea, AFAIK postgres jdbc nor scalikejdbc use netty.

But after triggering this bug I was seeing very strange behaviour, one of them the problem with the connection pool I still have no idea why. I have tested without triggering this bug and everything works as expected even with a pool of size 1.

Pedro.

> email to scalikejdbc-users-group+unsub...@googlegroups.com.

[Pedro Larroy]

Actually I was wrong, I'm still struggling with this issue after solving the problem that I had with xitrum.

I did a very small test on the console and monitoring PostgreSQL connections, and I can't figure out how to return connections to the pool. I thought the problem is that autoclose by default is false, but I tried setting it up manually to true and I'm debugging through scalikejdbc and close is being called by the LoanPattern. And I think the connection should return to the pool.

I'm using something like this:

    using(ConnectionPool.borrow(name)) { conn => DB(conn).autoClose(true).readOnly(execution)
    }

Any ideas of what am I doing wrong or some direction that might help me?  I will try the suggestion of using the other pool (hikari) to see if that solves my problem, but stil.

Thanks a lot.

[Pedro Larroy]

Hi

In the attached backtrace, close is reaching Apache commons pool, still I don't know why the connection is not returned to the pool


Pedro.

[Pedro Larroy]

Hi all

I'm thinking that the problem might be on the jdbc driver, closeForNextExecution is not really closing the connection as far as I can see.

I tried with dbcp2 and the problem is the same. Is this working for somebody using PostgreSQL?


Pedro.

[Pedro Larroy]

Found the problem, my mistake as I was leaking connections by a member variable to support batch statement executions!!


Pedro.