Cannot connect Coldfusion 9 to MySql 5 in Apache (Ubuntu)

[PeteComcar]

When trying to verify my datasource int he coldfusion admin panel I get the following error:

Connection verification failed for data source: dsnallcars
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
The root cause was that: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

I can serve phpMyAdmin and play around with the tables in there so I know Apache and MySql are working. I've got the coldfusion administrator and also a normal coldfusion error serving this message:

The following information is meant for the website developer for debugging purposes.

Error Occurred While Processing Request

Error Executing Database Query. Could not create connection to database server. Attempted reconnect 3 times. Giving up.

So I know that's working as well. My settings in the administrator are:

JDBC URL: jdbc:mysql://localhost:3306/allcars

Driver class: com.mysql.jdbc.Driver

I know the user name and password are correct, as I use them in phpMyAdmin.

I've got the mysql-connector-java-5.1.12.bin.jar file pasted into /opt/coldfusion9/wwwroot/WEB-INF/lib

I'm at a complete loss as to what to do next. Anyone have any ideas? I can provide the stack trace from the error if it helps or any other info if needs be.

[BreakawayPaul]

Go back into the CF Administrator and click on Data Sources and make sure your data source is still listed in there.

I have your exact same setup at home, and for some reason, when I stop and restart the ColdFusion server, I randomly lose datasources.  I'm not sure what's causing it, but it's not a huge deal to me since I just use it for development purposes.

[PeteComcar]

My Data source is still there and still can't communicate with CF, after Apache, Coldfusion and system reboots.

[Guest]

I've had a same error before with the similar setup except for it was running on debian which is almost the same thing, and the way i fixed it was by changing the ip it connects to, so instead of using localhost to connect to the db, try using the physical ip even if its on the same box. I don't know why but it fixed it for me.

[PeteComcar]

Hmm no that didn't change anything. I assume you meant something like the following

jdbc:mysql://192.168.1.19:3306/dbname

[Adam Cameron.]

From the CF server, can you telnet onto the IP & port that the MySQL server is listening on?

--

Adam

[PeteComcar]

From putty, if I type telnet 192.168.1.19, which is the ip of the cf server I'm logged into, I get

Unable to connect to remote host: Connection refused

From putty, if I type telnet 192.168.1.19 3306, I get

Trying 192.168.1.19...

Connected to 192.168.1.19.

Escape character is '^]'.

EHost '192.168.1.19' is not allowed to connect to this MySQL serverConnection closed by foreign host.

And I have no idea what that means

[Adam Cameron.]

It means that the CF server can make a network connection to the MySQL server.

Does MySQL log anything when these connections fail?

--

Adam

[PeteComcar]

The /var/log files has mysql.err and mysql.log, both of which are empty.

The my.cnf file does not have any of the log lines commented out.

[PeteComcar]

Anyone else got any ideas?

I can access MySql through the command line and with phpMyAdmin

[PeteComcar]

It's a permissions problem. The IP needed to be 192.168.1.19 in the DSN setup in the administrator, but that didn't fix it alone.

I also created a 'temp' MySQL user based on a post found here: http://www.linuxquestions.org/questions/showthread.php?p=4039359#post4039359

And used that instead, it worked.