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 :
Other useful additions to the connection string are useUnicode=true and characterEncoding=UTF8 for internationalisation, giving you :
One thing to note : Don't use the connection string box in the datasource form as this doesn't work for "other" type datasources.