Having just recently decided to start supporting MySQL, I went ahead and setup a new database server and installed MySQL 4.1.13, all seemed to be fine until I actually came to connect ColdFusion to a MySQL database, then Oops. I got an error something like this 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? After a fair bit of investigation I discovered that MySQL4.x has changed the way passwords are encrypted. So anything that is using the old method will not connect to your database. This includes the MySQL drivers that come with CFMX as they are 3.x drivers. You now have two options.

1. Set your MySQL server to use the old 3.x style passwords.
2. Install a newer MySQL JDBC driver.

I in fact chose to do both. You can in fact set the server to use new or old password globally and then set individual databases to use the opposite. I have set the MySQL to use old style passwords, as this appears to be the most common requirement, and if a client then has problems, I change their database to use the new style passwords. Set your MySQL Server to use old 3.x style passwords Open your my.ini file on your MySQL Server and find the [mysqld] section, now just add the parameter "old_passwords". You can now use the standard MySQL driver that comes with CFMX for your DSN. E.G. [mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Use old password encryption method
old_passwords Steps to install a new MySQL 4.x JDBC driver

• 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.

Adding a DSN using the new driver

• Add new datasource selecting "other" as the driver
• Add the JDBC URL jdbc:mysql://[server]:[port]/[database]
• Add the Driver Class com.mysql.jdbc.Driver
• Fill in the username/password, and adjust other dsn settings if needed.
• Submit datasource for verification
• As long as the DSN verifies, don't forget to remove the username and password, as you should be passing these in your code.
Views: 439