Ever increasing number of database connections with JDBC and ODBC drivers - MSSQL

[tribule]

Hi,

We have a legacy CF application that has a number of Java classes as part of its API (we do not have easy access to these). A long-running issue has been that database connections keep rising and are never released. We have tried the JDBC and ODBC drivers for MSSQL with the same overall result. Changing lots of settings in the DSN settings has also yielded no different result. We have a lot of "IF @@TRANCOUNT > 0 COMMIT TRAN" commands when we look in the MSSQL sys.dm_exec_connections table. We wondered if autoCommit was an issue, but have not found a way to effectively use it in the DSN/driver settings. We use the latest MS JSBC driver, version 8.4.1.

We wondered if the Java classes were not properly closing the connections - this seems to be the concensus by many developers, however not even CF can close these "stale" connections and eventually the server crashes (web requests pile up and JDBC connections go to zero).

Any advice appreciated.

[Dave Watts]

Maybe a dumb question, but have you tried the JDBC drivers that come with CF? (DataDirect Connect for JDBC) There's a reason CF provides these instead of having you download the MS drivers.

Dave Watts, Eidolon LLC

[tribule]

Hi Dave, is the DataDirect driver the same as the MS JDBC driver version 8.4.1? If not, where is it? I checked in the CF Admin and the only thing mentioned for DataDirect is MySQL, which we don't use.

We are using a DSN with the com.microsoft.sqlserver.jdbc.SQLServerDriver class and a JDBC URL like "jdbc:sqlserver://our_listener:1433;databaseName=OURDB;SelectMethod=direct;". I tried the "normal" SQL Server driver as well. Both show rising connections. The non-JDBC MSSQL driver also shows a plateau of heap memory usage, which rises gradually and never goes down. The JDBC driver shows the classic jagged edge heap usage shape (up/down).

[Dave Watts]

No, DataDirect drivers are not the same as MS drivers or any other vendor-provided drivers. DataDirect is a company that makes their own JDBC drivers for a bunch of databases. You should be able to just pick the "MS SQL Server" option in the CF Administrator to use it. If you've selected that, you're NOT using the Microsoft-provided JDBC driver. To use the Microsoft-provided driver, you'd have to choose "Other" and fill out your class and JDBC URL like you appear to have done already.

Dave Watts, Eidolon LLC

[tribule]

We are on CF 2018 Enterprise. Do we need to download the driver from https://www.progress.com/jdbc/microsoft-sql-server 

[Charlie Arehart]

Mark, nope. The Progress/Data direct drivers are indeed built in, in std or enterprise. As Dave has said, just choose sql server as the dsn type.

Let us know if it helps. If it does not, your challenge sounds very interesting, and I'm sure frustrating for you. 

I'd be willing to offer my time at no charge to meet you in a shared desktop session to try to resolve it. I can see you've done a lot of digging already. I just wonder if I might help connect a dot. If not, it may cost you only an hour of your time. If interested, email me using the contact page on the link in my signature. 

[tribule]

Hi Charlie. Ok in that case we've already tried the DD driver. I've emailed you regarding your kind offer btw.

[BKBK]

tribule:  "We are using a DSN with the com.microsoft.sqlserver.jdbc.SQLServerDriver class and a JDBC URL like "jdbc:sqlserver://our_listener:1433;databaseName=OURDB;SelectMethod=direct;". I tried the "normal" SQL Server driver as well.

...

 It's like the driver and code are not in unison."

If I were you I would follow Dave's initial suggestion. That is, continue to test with the 'Microsoft SQL Server' driver option in the ColdFusion Administrator, thereby using the driver that ships with ColdFusion. At least, until you fix the problem.

The reason? Adobe engineers usually tweak drivers in specific ways to enable them to work optimally for ColdFusion.

The details of the MS SQL Server driver that ships with ColdFusion are:

class

    macromedia.jdbc.MacromediaDriver
port

    1433
url

    jdbc:macromedia:sqlserver://[host]:[port];databaseName=[database];SelectMethod=[selectmethod];sendStringParametersAsUnicode=[sendStringParametersAsUnicode];querytimeout=[qTimeout];applicationintent=[applicationintent];[args]

<!--- Login into Coldfusion Administrator. --->
 <cfset  createObject("component","cfide.adminapi.administrator").login("my_CF_Admin_password")>

<!--- Instantiate the data source object. --->
 <cfset  datasourceObject = createObject("component","cfide.adminapi.datasource")>
 
<!--- Get a structure containing all the database drivers --->
 <cfset databaseDrivers = datasourceObject.getDriverDetails()>
 <cfdump var="#databaseDrivers#" label="All available database drivers">

[tribule]

Ok, I am back on the "Microsoft SQL Server" driver. I am looking at connections now and I see no "IF @@TRANCOUNT > 0 COMMIT TRAN" entries at all this time, so that is one change that I can see immediately. I will watch again - we have used this driver before. I set the limit to 400 connections in the "Restrict connections to" field (and checked the "restrict connections" checkbox in the CF Admin).

[BKBK]

SQL Server allows a maximum of 32767 connections. Your application might require, say, at most 200 simultaneous database connections. You could therefore, as solution, use the settings of the database to limit the number of connections to 200.

[tribule]

I tried that and at 200 connections, the server fell over. FusionReactor showed JDBC connections fall to zero and web requests skyrocketed - assumed this was because the database could/would not serve pages.

[BKBK]

I would continue benchmarking. Try 400. If that, too, fails then 800. You're looking for a sweet spot where things work without excessive database connections.

[BKBK]

Do you suspect one or two of the datasources to be the cause? If so, here's another suggestion:

1) On the database, revert the connection-limit (the one we've been experimenting with) to its original value, 0.

2) In the ColdFusion Administrator, change the settings for the suspected datasources as follows:

Limit Connections: (check the checkbox)
Restrict connections to: 400

[tribule]

Hi, we've tried various values, 100, 500, 1000. At those limits the server stops responding. One a limit it reached, FR shows that the JDBC connections fall to zero and webrequests build up massively. It's like the server just never releases connections and then the systems cannot do anything more. As I typed this, connections went from 100 to 191, all sleeping and "AWAITING COMMAND". We also tried killing old SPIDs via job in SQL server and it still led to an eventual server crash.

[BKBK]

A headache indeed. Yet another test suggestion (because, as you say, it seems like the server doesn't release connections):

Maintain Connections: (uncheck the checkbox)

[tribule]

Have unticked it, will watch and come back to you. Hope it's that simple! 😂

[tribule]

Connections rose again and even when a limit was added it did not release. It's like the driver and code are not in unison.

[Charlie Arehart]

Javont, what?

(This was a response to a curious comment from someone named javont here, whose comment has since been deleted. I can't seem to delete my own comment, so just updating to provide context.) 

[BKBK]

Yet another suggestion @tribule :

1) Review every piece of Java code that creates a connection to the database.

2) In each case, ensure that the connection is closed. Example:

Connection conn = null;
try {
		String url = "jdbc:path-info";
		conn = DriverManager.getConnection(url);
		// etc.
		
} catch (SQLException e) {
	throw new Error("Problem connecting to the DB", e);
} finally {
	if (conn != null) {
         /******* Vital to close every connection! *******/
		conn.close();
	}
}

[tribule]

Hi. Now there's the issue. We don't have easy access to the Java classes. There are a lot of them - hundreds. I did try decompiling some using an online tool, but it's difficult to know where to look. Is there a specific piece of code for all database calls, for example? I decompiled one template that mentioned the "database" and there is a "conn.close()" in place, but there could be missing ones elsewhere. For <CFQUERY> I assume the close is included automatically? All of our own customised code is using CFML e.g. <CFQUERY> - the Java classes for the remainder of the site are an unknown, alas. I could not also find a tool to batch decompile all the Java classes - doing them one at a time takes ages.

I can attach one of the files here, but not sure if I should release code publically.

[BKBK]

Understood. OK, that was that. We just have to live in hope that the Java developers implemented conn.close().

Moving on, what about testing by reducing the following datasource setting:

Timeout(min)

This setting stands for the number of minutes that ColdFusion maintains an unused connection before destroying it. Test by reducing the value from 20, the default, to, say, 2.

[tribule]

Ok, have set to 2. Will continue to watch. Limit connections still set to 400.

[tribule]

Update: At 400 connections the server went haywire. Web requests rising massively and the site became unresponsive. Here's what FusionReactor shows.

[BKBK]

Thanks for sharing that. A picture does indeed speak louder than a thousand words.

The picture tells me that:

1) Queries, the database or database connections are unlikely to be the cause of your server problem. That is because there is no JDBC activity after 21:08:15 and there is no significant change in CPU or memory use in the interval between 21:08 and 21:09.

2) Requests are the likely cause of the server problem: specifically, an increasing number of non-terminating requests. Check Fusionreactor's Requests > History menu to find out which requests are running. Some process is generating them. Question is, which process?

3) The CPU graph suggests that some process outside the current ColdFusion instance is using the most CPU. Which process? For example, is some external process generating requests on the ColdFusion instance?

[tribule]

21:08:15 is when the 400 connection limit was reached. Requests are still coming in to the server for pages, but the database will not server them and so there is a massive backlog that cannot be serviced. If I look in the history I just see normal requests for pages on our site, nothing out of the ordinary. As for another process there is nothing special making requests 🤔