UPDATE: This article has been published as a technote, and later updated for MySQL 4 and 5.

Having not gone to Macromedia MAX this year, I'm glued to the tube this evening watching the 2004 presidential election results. Dan Rather's pontifications, prognostications, and random musings are starting my make my eyes glaze over, so to remedy that, I'll take a quick break here to share some information about how to connect to MySQL 4.1 from ColdFusion MX server.

First, does ColdFusion MX 6.1 server officially support MySQL 4.1? Good question! The System Requirements state support for MySQL without reference to a specific version, whereas the other database vendors are listed with supported versions. Second, ColdFusion MX 6.1 has not yet been tested for MySQL 4.1 given that just last week MySQL 4.1 was officially certified as production ready. Third, the MySQL driver option listed in the ColdFusion Administrator is hard-coded in neo-query.xml to use the MySQL 3.2 JDBC driver, org.gjt.mm.mysql.Driver.

There are two significant steps to configure ColdFusion MX to use MySQL 4.1: Including the MySQL Connector J in the ColdFusion server classpath, and using the Other driver type from the drop down list to specify the driver class com.mysql.jdbc.Driver.

Here's the detailed list:
Steps to Configure ColdFusion MX for MySQL 4.1

  • Download the MySQL Connector J JDBC driver
  • Make the JDBC driver available to CFMX through the classpath by saving mysql-connector-java-3.0-bin.jar in either $CFMX_HOME/runtime/servers/lib/ for CFMX Server Configuration or $JRUN_HOME/servers/lib/ for CFMX on JRun Configuration. This makes the driver available to all JRun server instances.
  • Restart CFMX server.
  • Add new datasource using the driver option Other
  • Add the JDBC URL jdbc:mysql://[host]:[port]/[database]
  • Add the Driver Class com.mysql.jdbc.Driver
  • Complete username/password, and adjust other dsn settings if needed.
  • Submit datasource for verification

If you select the existing MySQL driver option in CFMX to connect to a MySQL 4.1 database, then it will fail with the error message below since neo-query.xml is configured to use the "old" JDBC driver class.

The error is:
Connection verification failed for data source: mysql_bad java.sql.SQLException: Communication failure during handshake.Is there a server running on localhost:3306?
The root cause was that: java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306?

You'll probably want to set up a MySQL user account for use with your application, so check out the MySQL documentation for adding new user accounts, section 5.6.2 Adding New User Accounts to MySQL. Be sure to set the GRANT statements properly to permit the user to connect to the desired databases from the host that CFMX resides on.

To administer your MySQL database, I highly recommend the free application from Digital Crew called CFMyAdmin, comparable to PhpMyAdmin. Out of the box, CFMyAdmin 1.0 (Beta 3) will not work with MySQL 4.1. To correct this, just find the wddx config file CFMyAdmin/modules/MySQLDSWDDX.z, then change the driver CLASS from org.gjt.mm.mysql.Driver to com.mysql.jdbc.Driver. Once that's done, just launch the CFMyAdmin application in a browser and create a new connection using the new mysql user account that you've just set up.

I'll post back when I get better information on the official ColdFusion MX support for MySQL 4.1 database. Incidentally, I successfully tested with Blackstone Beta 2 as well.