MySQL Datasources - One thing leads to another ..

After installing CFEclipse, my next step was to create a few datasources. I have several databases lying around but for now I started with MySQL.

It turns out that setting up the MySQL datasource was an interesting experience for me. Not only because it was my first exposure to installing JDBC drivers for ColdFusion. I also learned a bit about MD5 checksums and cryptographic signatures in the process.

Since I am using Colfusion MX 7 and MySQL 5, I first read the instructions on the Adobe site Configuring a JDBC Driver for MySQL 4.1 or 5 for use with ColdFusion MX . Knowing I needed to download an updated JDBC driver first, I went the MySQL downloads section. There were several versions of the MySQL Connector J driver available. I decided to download a version 5.x driver, since the TechNote mentioned an incompatibility with MX7 and some of the 3.1.1x versions.

MD5 checksums and Signatures oh my!

On the download page I noticed two things: an MD5 value and Signature. Now I had heard of both, but must admit I had never used either. Thankfully the MySQL site provides a good description of how you can use the MD5 checksums and GnuPG signatures to verify the integrity of the packages you download. Since I am a curious type I decided to check both.

First I downloaded the GNU Core Utilities package for Windows after reading a helpful article on Peter Leung's blog mentioning the md5sum utility included in that package. Using the command line utility, I quickly verified the MD5 checksum of the MySQL download

  c:\download> md5sum c:\download\mysql-connector-java-5.0.7.zip \b7c9d9a274aa93af3d9111be567a5696 *c:\\download\\mysql-connector-java-5.0.7.zip

GnuPG (Oh great.. another acronym)

Next up was downloading GnuPG (GNU Privacy Guard) so I could verify the cryptographic signature. I started downloading the file when I noticed the checksum value listed was SHA-1 (not MD5). Fortunately the GNU Core Utilities package includes a utility for this too. Using sha1sum.exe I was able to verify the GnuPG file checksum.

   c:\download>sha1sum c:\download\gnupg-w32cli-1.4.7.exe\b806e8789c93dc6d08b129170d6beb9e1a5ae68f *c:\\download\\gnupg-w32cli-1.4.7.exe

After installing GnuPG, I tried to run gpg.exe from the command line, but got the infamous "not recognized as a command" error.

  c:\download> gpg
'gpg' is not recognized as an internal or external command, operable program or batch file.

I checked the README-W32.txt file and it confirmed the installer does not change the PATH environment variable. As I am lazy, I manually added the installation directory (c:\program files\GNU\GNUPG) to my PATH environment variable. That way I can run gpg.exe from the command line without having to type out the full path to the .exe every time I use it.

Next I copied the MySQL build key from the download site and saved it to a file: c:\download\mysql_pubkey.asc. Then I fired up another command prompt window and imported the key

  c:\download> gpg --import mysql_pubkey.asc

Next I downloaded the driver jar signature and saved it to: c:\mysql-connector-java-5.0.7.zip.asc. Then verified the signature with gpg. As you can see the verification was successful.

  c:\download> gpg --verify mysql-connector-java-5.0.7.zip.asc
    gpg: Signature made 07/18/07 14:31:37 using DSA key ID 5072E1F5
    gpg: Good signature from "MySQL Package signing key (www.mysql.com) <build@mysql com="">

Wow, I have learned all this great stuff and have not even created the datasource yet.

Creating a MySQL Datasource (Finally!)

Now that I had verified all of my download files, it was time to install the driver. I unzipped the archive and immediately noticed more files than I was expecting



But I did not panic. The TechNote said to look for a file name like: mysql-connector-java-3.{n}-bin.jar. Since I had downloaded a version 5.x driver, my jar happened to be named mysql-connector-java-5.0.7-bin.jar. So I copied it into the cf_root/WEB-INF/lib directory and restarted ColdFusion. After the server restarted, I created my datasource according to the instructions. Here are the settings I used

Datasource Name:     MySQL5DSN 
Driver:              Other
JDBC URL:            jdbc:mysql://127.0.0.1:3306/sakila
Driver Class:        com.mysql.jdbc.Driver
Driver Name:         com.mysql.jdbc.Driver
User Name:           testUser
Password:            ********

Note, my database is running on port number 3306 and the database name is "sakila"

After entering the settings, I clicked the submit button and received this lovely error



Turns out the MySQL service was not running (duh!). After starting the service, and configuring my firewall to allow communication between CF and MySQL, the datasource verified successfully.

The only thing left to do was create a test page in CFEclipse. I ran it and eureka, it works!



I know some of this has nothing to do with either ColdFusion or MySQL, but I found it interesting and hope some of you might as well. As always, comments/suggestions/corrections are welcome.

... we now return you to your regularly scheduled ColdFusion programming.