Bug #69229 'opt-out' mechanism for Connection Tracking/Abandoned Connections
Submitted: 14 May 2013 15:22 Modified: 31 May 2013 10:31
Reporter: Dominic Tootell (OCA) Email Updates:
Status: In progress Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.25 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any

[14 May 2013 15:22] Dominic Tootell
Description:
Hi there,

Apologies, this feature may or may not exist in the form of "closeAbandondedConnections" on the jdbc url, but may not yet be released/pushed to the source repo: 

- http://forums.mysql.com/read.php?39,586107,586281#msg-586281
-- http://forums.mysql.com/read.php?39,586107,586281#msg-586281

I am just raising in case the feature hasn't yet been recorded anywhere.  

- http://forums.mysql.com/read.php?39,586107,586289#msg-586289

If the feature has already been created somewhere, then do please close it; and accept my apologies for duplicating the request.

Request details:
-----------------

Currently when a Connection (com.mysql.jdbc.ConnectionImpl) is created, the connection is added to a ConcurrentHashMap in com.mysql.jdbc.NonRegisteringDriver, with a PhantomReference (ConnectionPhantomReference).  The call that does this is at the end of the ConnectionImpl constructor.

NonRegisteringDriver.trackConnection(this);

The purpose of the above is to monitor for Connections that have been un-referenced by client applications, which have not called .close() on the connection (i.e. connection = null ).  In the case where an application,  hasn't called .close() on the connection; the thread that monitors a ReferenceQueue obtains the ConnectionPhantomReference and closes the connection on behalf of the mis-behaving application.

Howerver, if the application developer knows that the application calls Connection.close() in all circumstances,  then the management of the PhantomReference's is just overhead for the GC of the client application; with more live objects for it to manage.  

For those applications where the connections are managed by the application server or a similar connection pool (tomcat pool, bonecp, etc); then connection.close() is usually called by that external component, and in theory no connection tracking should be needed to make sure connection.close() is called.  As a result the overhead associated with the connection tracking wouldn't be needed, such as the the abandoned thread, and the ConnectionPhantomReferences.

The request is therefore, for an 'opt-out' mechanism for Connection Tracking i.e. connectionTracking=true|false on the jdbc url perhaps.  As previously mentioned this may already have been implemented with a property named "closeAbandondedConnections" (Mark Matthews may have more information relating to this)

Apologies if this feature request if already available.

cheers
/dom

How to repeat:
The feature request related to the forum post:

- http://forums.mysql.com/read.php?39,586107,586107#msg-586107

The steps for replication to see connection tracking in operation would be:

- Create JDBC Connection (this puts the Connection, wrapped in a PhantomReference on the map)
- Issue a select query
- null or close out the reference to the JDBC Connection.
- The AbandonedConnectionCleanupThread recieves the PhantomReference and closes the network resources on the connection; in case the client application didn't

Connection connection=null;
        Statement statement = null;
        ResultSet resultSet=null;
        try {

            //sudo ngrep -d lo0 -q -W byline port 3306//
            String url = System.getProperty("jdbc.url","jdbc:mysql://localhost:3306/testingconn?useCompression=true");
            connection = DriverManager.getConnection(url);

            statement = connection.createStatement();
            resultSet = statement.executeQuery("select /* a very large string so that we can see that compression is still enabled on the driver and that all things are equal with the WeakReference added to the CompressedInputStream */ count(*) from items");

            while (resultSet.next()) {
                int rows = resultSet.getInt(1);
                System.out.println(rows);
            }

        } catch(Exception sqe) {
            sqe.printStackTrace();
        }
        finally {
            try {
                if (resultSet!=null) resultSet.close();
            }catch(Exception e){}

            try {
                if(statement!=null) statement.close();
            }catch(Exception e){}

            try{
                if(connection!=null) connection = null; // this would usually be connection.close()
            }catch(Exception e){}
        }

The AbandonedConnectionCleanupThread will call the close() on the mysqlInput assocatiate with the connection that was not closed

Suggested fix:
Implementation of a property to only add the connection onto the map (only call NonRegisteringDriver.trackConnection(this) ), when the property is enable.

Apologies, I have not investigated an actual potential implementation, so I don't have an patch files to supply (I shall look at this if required).
[22 May 2013 7:39] Alexander Soklakov
Thanks Dominic!

Verified.
[27 Dec 2017 2:35] Hassan Adeel
I came across same memory issue in my application. 

I am wondering that this issue has status "In Progress"... when this issue is expected to be resolved?

Thanks!