Connecting to MySQL 4.1 using Connector J version 3.1.12

For quite some time now I've been using MySQL (http://www.mysql.com/) as my database of choice for building web applications on top of. Why? Well the server software and the GUIs are free. Its a million times better than using MS Access. Its stable (for the most part) and generally accepted in the web development community as a good solution.

Until recently though I've been having problems using the latest MySQL J Connector with CFMX. I would always get a "Connection Failed" error. This has meant that I've been stuck with the 3.1.10 J Connector, which while they work are less than ideal.

Well recently I found out how to get the latest J Connector (3.1.12 at time of 'print') to allow connections!

If you try connecting using the latest connector with a JRun install of CF, then you get a completely different error regarding a Log4 error. A friend of mine Tim Blair had had this problem and found solution to this, so I thought I'd give his solution a try on my bog-standard Professional Server installation of CFMX 7.

It turns out that there isn't actually a connection problem at all! The actual problem is some kind of clash between CFMX/Jrun and the logger in the newer connector. Its the same problem that Tim was encountering, just not giving sufficient or correct information in the error!

So whats the solution you ask? Simple, the solution is to add "logger=com.mysql.jdbc.log.StandardLogger" to the end of the connection string. This tells the J Connector to use the standard logger rather than the Log4 log class.

For example :

jdbc:mysql://localhost:3306/mydatabase?logger=com.mysql.jdbc.log.StandardLogger

Other useful additions to the connection string are useUnicode=true and characterEncoding=UTF8 for internationalisation, giving you :

jdbc:mysql://localhost:3306/mydatabase?logger=com.mysql.jdbc.log.StandardLogger&useUnicode=true&characterEncoding=UTF8

One thing to note : Don't use the connection string box in the datasource form as this doesn't work for "other" type datasources.

TweetBacks
Comments
Other entries in the "other" dsn form.

Driver Class : com.mysql.jdbc.Driver
Driver Name : mysql J Connector

Driver Name can be anything you like, but it helps if its descriptive....
# Posted By StephenM | 3/13/08 9:39 PM
Thank you very much for your post! It helped me a lot! I have been looking for days for the answer!!!
# Posted By Jeremie | 3/13/08 9:39 PM
Just to note that the 3.1.12 connector works with MySQL 5 without the logging string.
# Posted By Julian Halliwell | 3/13/08 9:39 PM