Monday, July 23, 2007

MySQL and ColdFusion 8

I just recently posted about a new MySQL database driver that contains code that improves performance for ColdFusion MX 7. I wanted to clarify why this driver isn't needed for the about-to-be-released ColdFusion 8.

First ColdFusion 8 will contain the "commercial" version of the JDBC driver for MySQL 4 & 5. This driver integrates in to CF just like all the other databases we support and you no longer need to manually download and configure a driver if you are using a recent version of MySQL. This commercial driver has been run through our regression suite and verified on all of the ColdFusion 8 supported platforms. This is very much goodness for our customers using MySQL.

Second, ColdFusion 8 contains a workaround that prevents it from calling the isCaseSensitive API when using MySQL. It does this by examining the result set meta-data Java class and if that class is "com.mysql.jdbc.ResultSetMetaData" we avoid calling the isCaseSensitive() API on it, we just hard code the value for each column to false (the column is not case sensitive). Why do we call this api at all? Because ColdFusion keeps its own local copy of the meta-data attached to the Query object. This is one of the APIs on the JDBC meta-data object, so we collected it. As far as I know MySQL is the only database to perform a server round trip for each column to collect this information. So what is done with this information? It is returned by the GetMetaData() CFML function, which can be called on a Query long after the JDBC result set has been released. In general,
ColdFusion does not use this information internally at all. The one exception to this is for Query-of-Queries, where this meta-data can affect the processing of the column data. But this is extremely unlikely.

You can turn this workaround off by setting the Java System property coldfusion.mysql.enableiscasesensitive=true. You would only need to do this if your application made some use of the GetMetaData() function, you were using MySQL and you had case sensitive columns in your table. I can't foresee anyone needing to so this, but we gave you an escape hatch
just in case.

1 comment:

Sam Farmer said...

Thanks for clarifying and providing more details.