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.

Faster MySQL JDBC driver for ColdFusion

A new version of the MySQL driver - 5.0.7 - has been released that detects ColdFusion when it is running with Coldfusion MX 7 (and 8). This should result in much better performance for CF/MySQL customers.

Find it here: http://dev.mysql.com/downloads/connector/j/5.0.html

Here is the relevant info from the change list:

Setting "useDynamicCharsetInfo" to "false" now causes driver to use static lookups for collations as well (makes ResultSetMetadata.isCaseSensitive() much more efficient, which leads to performance increase for ColdFusion, which calls this method for every column on every table it sees, it appears).

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets "useDynamicCharsetInfo" to "false" (see previous entry), and sets useLocalSessionState and autoReconnect to true.

If you are using this driver with CFMX7, upgrading should give you a nice performance boost. Make sure you test your applications in a non-production setting first of course, as Adobe doesn't test with this driver.

Note: ColdFusion 8 ships with the "commercial" MySQL 5 driver (and we did test with this) and has a workaround in place to avoid calling isCaseSensitive() for MySQL. Configuring the built-in driver with the settings the MySQL team has done automatically (set useLocalSessionState and autoReconnect to true) might be a good idea depending on your application's use of the database. Once again, we haven't done any testing with these settings, your mileage may vary.

Sam Farmer on Flex features in ColdFusion 8

Sam Farmer blogs about some neat new features of ColdFusion 8 with the integrated LiveCycle Data Services (formerly Flex Data Services) install.

Be careful with the mxml JSP tag - it can really slow down your server!

CFUnited wrap up

Well, another CFUnited has come and gone. I think this years show was pretty good. My Flex/Data Services presentation seemed to fire at least a few people up and all the Scorpio/ColdFusion 8 sessions were successful.

I was really impressed with Rakshith's AJAX session - I think it hit just the right level of detail and Rakshith did a tremendous job presenting for his first time(!!).

Another highlight for me was Ray Camden's CFImage session, which did a good job of introducing this powerful new feature without overwhelming attendees with the huge number of functions we have added.

The special event to the National Zoo was pretty much a bust however, mostly due to weather and not very much food.

All-in-all a very good conference with tons of great info about ColdFusion. See you at MAX in October!