Friday, July 20, 2007

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.

7 comments:

Anonymous said...

you said: "might be a good idea depending on your application's use of the database"

Can you elaborate on this and in what cases this would be of benefit? If the MySql team is recommending it as a default why wouldnt CF8 just ship with that as the default config?

Unknown said...

In what situations is CF calling this "ResultSetMetadata.isCaseSensitive()" method on "every column on every table it sees"? And why?

--
Adam

Anonymous said...

Seems like the fix is based on a bug report I filed a while back.

Great news ;)

Anonymous said...

Gah, here's the link:

http://bugs.mysql.com/bug.php?id=25978

Tom said...

*anonymous* - I don't know why the MySQL team changed the settings, we don't 'configure' the MySQL driver at all.

*adam* - We call this API so that we can keep this info about each column of the result set. We have done this for all queries since CFMX 6, and MySQL was the only one that did a round trip to the server for every call.

Julian said...

Just a warning that we have had problems with the new driver when using ON DUPLICATE KEY UPDATE syntax. Seems that the sql cache doesn't get reset on either 5.0.x or the 5.1.x beta, so updates are happening in the DB but not being shown in subsequent selects via CF. Reverting to the 3.1.12 driver, it works fine again.

Anonymous said...

Do you know how to set CF to use this driver by default when creating a MYSQL DSN instead of having to create a DSN with type OTHER.