Connection is not closed when releasing if connectionHandle is expired

Bug #999114 reported by Geert Gerritsen on 2012-05-14
24
This bug affects 5 people
Affects Status Importance Assigned to Milestone
BoneCP
Fix Released
Undecided
Unassigned

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

description: updated
description: updated
description: updated
description: updated

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

Geert Gerritsen (ggerritsen) wrote :

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

Wallace Wadge (wwadge) on 2012-08-06
Changed in bonecp:
status: New → Fix Committed
cristi p (prodan) wrote :

Is there any plan to still apply this patch ?

Geert Gerritsen (ggerritsen) wrote :

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

Irioth (irioth) on 2014-08-12
Changed in bonecp:
status: Fix Committed → Fix Released
selvakumar (selvakumarbecse) wrote :

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

Download full text (3.2 KiB)

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

Read more...

selvakumar (selvakumarbecse) wrote :

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) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers