CFMX 6.1 and MySQL 5.0.27 JDBC issues

Question

Hi, I am currently trying to get CFMX 6.1 to talk to MySQL
5.0.27 on Redhat 3.0. I was able to get it working on our
development environment using SuSE, but not on our live servers.
 
We have been using MySQL 4.1.15 with JDBC 5.0.4 for awhile
now with no problems, as long as we use this connections string for
backward compatibility with our MySQL 4 scripts:
jdbc:mysql://xxx.xxx.xxx.xxx:3306/mydb?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull&dumpMetadataOnColumnNotFound=true&jdbcCompliantTruncation=false&autoReconnect=true
 
But as soon as I try switching to 5.0.27, CF gives me the
following message (I tried this with and without a password and
using an ip and the % wildcard in mysql.users):
 
Attempted reconnect 3 times. Giving up.
Server connection failure during transaction. Due to
underlying exception: 'java.sql.SQLException: Access denied for
user 'anybodycf'@'xxx.xxx.xxx.xxx' (using password: NO)'.
 
** BEGIN NESTED EXCEPTION **
 
java.sql.SQLException
MESSAGE: Access denied for user 'anybodycf'@'xxx.xxx.xxx.xxx'
(using password: NO)
 
STACKTRACE:
 
java.sql.SQLException: Access denied for user
'anybodycf'@'xxx.xxx.xxx.xxx' (using password: NO)
 at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
 at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812)
 at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3269)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182)
 at
com.mysql.jdbc.Connection.createNewIO(Connection.java:2818)
 at
com.mysql.jdbc.Connection.<init>(Connection.java:1531)
 at
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
 at
coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(JDBCPool.java:559)
 at
coldfusion.server.j2ee.sql.pool.ConnectionRunner$RunnableConnection.run(ConnectionRunner.java:64)
 at java.lang.Thread.run(Thread.java:534)

** END NESTED EXCEPTION **

Attempted reconnect 3 times. Giving up.

CF and MysQL are running on separate boxes. I was also able
to communicate to MySQL using Query Browser and Administrator from
a remote location.
 
Is there maybe something wrong with my connection string or
is it something else? I want to try testing this with perl or php,
but i am too tired at the moment. Thanks for any insight you can
give me.

Anonymous · Answer

I figured out what was happening, but I'm still not sure why.

It turned out CF was trying to read the databases in /usr/local/mysql5/data, when I had them installed in /var/lib/mysql. I find this weird, because other clients I used (mysql command line, Query Browser and Adminsitrator), had no problems connecting to the same exact databases. I created symbolic links to each of the databases from /usr/local/mysql5/data to /var/lib/mysql and it started working. I have a theory right now that it has to do with the /tmp/mysql.sock file, because i change it to use /tmp/mysql5.sock and i think CF is trying to use the former, but i don't care testing this at the moment.

Anyway, this was 2 days ago. Today I started running into long running query problems that were timing out my scripts. This actually happened yesterday too, but they went away after I shut off query cache and innodb. I couldn't figure out what was wrong today, so I decided to revert back to an older driver mysql-connector-java-3.0.17-ga-bin.jar and now everything seems to be running ok again.

I'm going to try using the 3.1 drivers when there is less traffic on the servers tonight, and hopefully they will be fine. Then i'll try turning the other stuff back on i turned off in mysql.

I wish I had some more luck with the 5.0.4 drivers. I'll try to followup later on with my findings.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded