devbox@COMPUTEC The Computec development blog

ColdFusion 8 and MySQL 5.1 via JDBC: Help needed! [Solved]

UPDATE: We have now found the cause of the issue. Please scroll to the end of the article for explanation and workaround.

In one of our latest projects we need to access a MySQL 5.1 server (5.1.45) from ColdFusion 8 (8,0,1,195765). Creating the datasource was no problem, the datasource does verify okay - but there obviously is some bug or incompatibility in ColdFusion that causes the connection to be anything but reliable.

Here's my testcode:

<cfsetting requesttimeout="120">
 
<cfset variables.iStart = getTickCount() />
<cfset variables.strMyDS = 'myMysqlDB' />
 
<cffunction name="doCheckQuery" output="yes" returntype="void">
	<cfset var qCheckDB = ''>
	<cftry>
		<cfquery name="qCheckDB" datasource="#variables.strMyDS#">
			SELECT now();
		</cfquery>
		<cfdump var="#qCheckDB#">
		<cfcatch type="any">
			<cfoutput>
			#getTickCount()-variables.iStart# ms
			</cfoutput>
			<br />
			<cfdump var="#CFCATCH#">
			<cfabort>
		</cfcatch>
	</cftry>
</cffunction>
 
<cfscript>
	doCheckQuery();
	sleep(10000);
	doCheckQuery();
</cfscript>

In a perfect world, there's just nothing here that could go wrong, right? Okay, as long as the sleep-statements remains at something like under 15 seconds, output is as expected:

As soon as the sleep time is longer than 15 seconds, the second query fails. The exact time is somewhat inconclusive, sometimes 15000ms does work, sometimes it doesn't. 10 seconds seems to always work, whereas 20 seconds seems to always fail.

The stack trace shows the cause of the error to be a java.lang.NullPointerException:

java.lang.NullPointerException
	at com.mysql.jdbc.Statement.setMaxRows(Statement.java:2178)
	at coldfusion.server.j2ee.sql.JRunStatement.setMaxRows(JRunStatement.java:214)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1276)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
	at coldfusion.sql.Executive.executeQuery(Executive.java:939)
	at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325)
	at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
	at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
...

Funny thing is that we already do use MySQL with ColdFusion - connecting to version 5.0.51a-19~bpo40+1-log doesn't seem to suffer from the same symptoms.

After spending some time with Google I found this Thread "[CF8] java.lang.NullPointerException" on the Adobe Support forums.

Unfortunately none of the suggestions in there has helped us with the issue. I tried to create the datasource with both the MySQL 4/5 option and the "other" option using a JDBC URL. I tried switching "Maintain Connections" on and off - neither does make a difference. I tried adding &autoReconnectForPools=true&zeroDateTimeBehavior=convertToNull to the JDBC-URL like this:

jdbc:mysql://123.123.123.123:3306/mytestdb?prepareThreshold=3&charSet=UTF-8&autoReconnectForPools=true&zeroDateTimeBehavior=convertToNull

Doesn't help either. I replaced the mysql-connector-java-commercial-5.0.5-bin.jar that came with CF8 with the latest mysql-connector-java-5.1.12-bin.jar, restarted the instance, checked the classpath and tried again - same outcome.

The wait_timeout on the MySQL server is currently set to 60 (i.e. 60 seconds), which leads me to believe that this isn't the cause of the issue either - as do some posts of people who have tried to increase the value without any positive effect.

Now upgrading all of our CF8 servers to CF9, though a compelling idea it might be, is just not an option, especially when the latest post in the aforementioned thread seems to suggest that the supposed bug fix in CF9 just reveals some underlying bug that might or might not be an issue for us, too.

Could anyone share their experiences with accessing a MySQL 5.1 DB from CF 8? What did you do, which version of the JDBC driver did you use, which version of MySQL are you using, which connection parameters do you set in your JDBC URL?

Any help on this would be greatly appreciated!

UPDATE: After some further experimenting on this issue, I stumbled over a misconception on my behalf; I thought I had checked the wait_timout setting on the affected MySQL server:

mysql>  show variables where variable_name like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 60    |
+---------------+-------+
1 row in set (0.00 sec)

So I couldn't exactly draw the line between the 60 seconds here and the 15 seconds problem found with my sample code. Now show variables in fact shows session variables, not the global setting. I don't know exactly what the "session variable" wait_timeout means exactly, I suspect nothing much, because the global variable actually wins, but here's how to determine the real setting:

mysql>  SELECT @@GLOBAL.wait_timeout;
+-----------------------+
| @@GLOBAL.wait_timeout |
+-----------------------+
|                    15 |
+-----------------------+

Ooookay - here are my 15 seconds. Now what I think happens here is that MySQL just closes the connection after 15 seconds of inactivity and ColdFusion/Jrun doesn't notice. Instead of removing the dead connection from the connection pool, it stays there zombified, ready to jump the next request with a null pointer exception.

The easiest workaround, and the one which we'll choose for now because I have actually tested it, is to set the value to something higher than 1200 seconds in your my.cnf like this:

[mysqld]
...
wait_timeout = 1300

... and restart the MySQL server. The default connection timeout in ColdFusion is 1200 seconds, so if you set the value higher, you should be safe. I couldn't reproduce the error with my 5.1.48-MySQL text server after setting it to 1500, so I assume that this does in fact do the trick.

If for some reason you cannot change the MySQL timeout setting, you may have some luck changing the setting on the ColdFusion server side. To do so, you need to find and manually edit your neo-datasource.xml where all the datasources are configured; in a multi-instance configuration this file would be located at {jrun-dir}/servers/{instance-name}/cfusion.ear/cfusion.war/WEB-INF/cfusion/lib/neo-datasource.xml, in a standalone install you'll find it under {cf-dir}/lib/neo-datasource.xml.

You'd better use an XML editor that doesn't mess with the code and make a backup of the file before fiddling with the settings in there!

When you open that file, search for the datasource element that contains the configuration for the affected datasource. There you will find a snippet that looks like this:

<var name='timeout'>
  <number>1200.0</number>
</var>

Stop the ColdFusion-server/instance, change the timeout-setting here to some value less than that of your MySQL server's wait_timeout, restart the server/the instance and hope for the best - because I haven't tried that one yet and I don't know if it would have some negative impact in performance or stability of ColdFusion.

What I do know though is that if you decide to edit this file manually, you cannot ever use the ColdFusion administrator to change any settings of the changed datasource without the risk that ColdFusion will then overwrite your custom setting with the built-in default. So as I said before: Changing the settings on the MySQL server side just makes more sense if that is an option for you.

Be Sociable, Share!

•