Troubleshooting CFMX 6.1 Database Connection Pooling
Posted by David Chandler on August 21, 2004
I recently upgraded from CFMX Updater 3 to CFMX 6.1. While the upgrade resolved many issues pertaining to CFCs, I have been having many problems with SQL Server datasources. My datasource is configured to limit connections to 3 so as not to run out of SQL Server licenses. Every morning, the application could not connect to SQL Server at all. All database pages would hang until I restarted CF. The exception log shows what appears to be an infinite loop involving JDBCPool exceptions.
This issue is supposedly resolved in a connection pooling hotfix (http://www.macromedia.com/support/coldfusion/ts/documents/conn_pool_hotfix.htm); however, it did not solve the problem for me. Pages no longer hang forever waiting for a db connection, but instead you get a ConnectionCreationException immediately. The workaround is to uncheck the “Limit Connections” setting for the data source. I do not think this is a big deal since CFMX times out unused db connections, but if load is so heavy that I run out of SQL Server licenses, then I will have to uncheck “Maintain Connections” as well. Then, at any given time, the app will use only as many connections as the number of simultaneous queries being run.
There was some discussion on Macromedia forums suggesting that going back to the CFMX Updater 3 drivers (pre-MX61)(http://download.macromedia.com/pub/coldfusion/drivers/3.1/cfmx60_u3.zip) may resolve the issue. This makes sense to me, as I never had any DB issues until 6.1, but I haven’t tried it yet. See the forum thread http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=684306&highlight_key=y&keyword1=connection%20pooling for details. The messages posted by Macromedia staff member “sdupre” are gold mines.