Connection is not closed when releasing if connectionHandle is expired

[Geert Gerritsen]

Hello,

Last week, we observed some unexpected behavior using bonecp. Our max connections were set to 10 (5 max per partition, 2 partitions), but over the course of half an hour, the number of connections reached 40.
However, bonecp reported that it only had 10 connections. When garbage collection was performed, the 'extra' connections were cleaned up.

This seemed strange, so we started looking into it. Eventually, we found the culprit. In BoneCP.internalReleaseConnection(), the following happens:

{code}
protected void internalReleaseConnection(ConnectionHandle connectionHandle) throws SQLException {
  // snip

  if (connectionHandle.isExpired() || (!this.poolShuttingDown && connectionHandle.isPossiblyBroken()
    && !isConnectionHandleAlive(connectionHandle))){

   ConnectionPartition connectionPartition = connectionHandle.getOriginatingPartition();
   postDestroyConnection(connectionHandle);

   maybeSignalForMoreConnections(connectionPartition);
   connectionHandle.clearStatementCaches(true);
   return; // don't place back in queue - connection is broken or expired.
  }

  // snip
}
{code}

As you can see, this code does not actually _close_ the connection, it assumes it is already closed and therefore calls the postDestroyConnection() method.
However, this code might also be executed if the connection is not closed yet. This can happen if the connectionHandle is expired (i.e., the maxConnectionAge has passed), in which case the connection might still be open and will not be closed by here. Which leads such a connection to be released from the pool (i.e. BoneCP does not see it anymore), but still be open (which explains the increase in connections past the maxConnection level).

If you agree that this is a bug, we can supply a patch for it.

Regards, Geert

P.S. We use bonecp 0.7.1.RELEASE

[Eugene Gunichev (eugenegunichev)]

I encountered the same problem. Moreover, under certain conditions pool stucks forever in getConnection method because of this error.

[Geert Gerritsen (ggerritsen)]

Here's a patch for the bug for the 0.7.1.RELEASE branch.

[cristi p (prodan)]

Is there any plan to still apply this patch ?

[Geert Gerritsen (ggerritsen)]

It was applied here: http://github.com/wwadge/bonecp/commit/5694ef5e464a64eac6a489c900143d5023a92383, which was done approximately a year ago.

[selvakumar (selvakumarbecse)]

Hi, I am using BoneCP with hibernate.

I monitoring BoneCP connection pool status using JConsole. I have observed there is a discrepancy between connection count in connection and MY-SQL server.

For instance,

Connection pool total connection : 60
Free connection : 60

but,

MY-SQL shows : 85 connections (all are created by same application).

Jar version : bonecp-0.7.1.RELEASE.jar

I am getting exception in application error console :

19-12-2014 11:51:01 ERROR [BoneCP-pool-watch-thread] [com.jolbox.bonecp.PoolWatchThread] - Error in trying to obtain a connection. Retrying in 7000ms
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

please advise me If I have apply this patch, my problem goes away.

[Geert Gerritsen (ggerritsen)]

Hi Selvakumar,

Is the max_connections variable set to 85 in your mysql config?

mysql> show variables where Variable_name = "max_connections";

In any case, your situation shows that the amount of connections that bonecp holds is different than the amount of connections in mysql created by the application, which is wrong. The patch could fix this, but it's also possible you have a different problem.

Instead of applying the patch you could also consider updating to a later version of bonecp, such as bonecp-parent-0.8.0-beta1 or any newer version (see https://github.com/wwadge/bonecp/releases).

Hope this helps!

Kind regards,

Geert

[Wallace Wadge (wwadge)]

Please note: BoneCP is considered deprecated at this point (it's pretty old
now!). You are strongly encouraged to switch to HikariCP which offers
better performance and is more stable.

On Fri, Dec 19, 2014 at 1:42 PM, ggerritsen <email address hidden>
wrote:
>
> Hi Selvakumar,
>
> Is the max_connections variable set to 85 in your mysql config?
>
> mysql> show variables where Variable_name = "max_connections";
>
>
> In any case, your situation shows that the amount of connections that
> bonecp holds is different than the amount of connections in mysql created
> by the application, which is wrong. The patch could fix this, but it's also
> possible you have a different problem.
>
> Instead of applying the patch you could also consider updating to a
> later version of bonecp, such as bonecp-parent-0.8.0-beta1 or any newer
> version (see https://github.com/wwadge/bonecp/releases).
>
> Hope this helps!
>
> Kind regards,
>
> Geert
>
> --
> You received this bug notification because you are subscribed to BoneCP.
> https://bugs.launchpad.net/bugs/999114
>
> Title:
> Connection is not closed when releasing if connectionHandle is expired
>
> Status in BoneCP - Java Database Connection Pool:
> Fix Released
>
> Bug description:
> Hello,
>
> Last week, we observed some unexpected behavior using bonecp. Our max
> connections were set to 10 (5 max per partition, 2 partitions), but over
> the course of half an hour, the number of connections reached 40.
> However, bonecp reported that it only had 10 connections. When garbage
> collection was performed, the 'extra' connections were cleaned up.
>
> This seemed strange, so we started looking into it. Eventually, we
> found the culprit. In BoneCP.internalReleaseConnection(), the
> following happens:
>
> {code}
> protected void internalReleaseConnection(ConnectionHandle
> connectionHandle) throws SQLException {
> // snip
>
> if (connectionHandle.isExpired() || (!this.poolShuttingDown &&
> connectionHandle.isPossiblyBroken()
> && !isConnectionHandleAlive(connectionHandle))){
>
> ConnectionPartition connectionPartition =
> connectionHandle.getOriginatingPartition();
> postDestroyConnection(connectionHandle);
>
> maybeSignalForMoreConnections(connectionPartition);
> connectionHandle.clearStatementCaches(true);
> return; // don't place back in queue - connection is broken or
> expired.
> }
>
> // snip
> }
> {code}
>
> As you can see, this code does not actually _close_ the connection, it
> assumes it is already closed and therefore calls the
> postDestroyConnection() method.
> However, this code might also be executed if the connection is not
> closed yet. This can happen if the connectionHandle is expired (i.e., the
> maxConnectionAge has passed), in which case the connection might still be
> open and will not be closed by here. Which leads such a connection to be
> released from the pool (i.e. BoneCP does not see it anymore), but still be
> open (which explains the increase in connections past the maxConnection
> level).
>
> If you agree that this is a bug, we can supply a patch for it.
>
> Regards, Geert
>
> P.S. We use bonecp 0.7...

[selvakumar (selvakumarbecse)]

Hi Wallace Wadge (wwadge) and ggerritsen (ggerritsen) ,

I am getting into this issue when the application required more db connections.

For Instance, I am working an the application where I have schedule thread job.

Note: Each thread job required separate db connection.

No problem when I schedule only 20 jobs at the same time.

Issue when schedule 150 jobs at the same time.

I am getting this below state,

Connection pool total connection : 60
 Free connection : 60

but,

MY-SQL shows : 85 connections (all are created by same application).

[Geert Gerritsen (ggerritsen)]

Hi Selvakumar,

As I said, it is possible that the patch fixes your situation but it's also possible you have a different problem.

I'd say just try it and see if it works.

Also take note of Wallace's comment: Bonecp is more or less deprecated, you might fix your issue as well by moving to HikariCP (or another db connection pool library).

Let me know if this helps!

Kind regards,

Geert