Could not create connection to database (ColdFusion)

Question

I'm creating a simple CRUD application via ColdFusion. I'm going to ColdFusion Administrator Panel at http://localhost:8600/CFIDE/administrator/index.cfm, and adding a "New Data Source". But I get the following error:

Connection verification failed for data source: dsnMyVariable
java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. 
The root cause was that: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

Answer 1

Update:

After installing MySQL 8, I got the exact same error message when creating a DSN. It seems to be a compatibility issue with MySQL 8. Checking the System Support Matrix for ColdFusion 2016 only shows MySQL 5.7, so MySQL 8 probably isn't supported or compatible with the built in drivers.

Installing a newer JDBC driver seemed to fix the problem and allow the DSN to verify.

1. Stop the ColdFusion 2016 Windows Service
2. Download the latest JDBC driver from https://dev.mysql.com/downloads/connector/j/. Currently, the latest version is v8.0.11.
3. Unzip and copy the new driver jar (mysql-connector-java-8.0.11.jar) into directory {cf2016_root}\wwwroot\WEB-INF\lib

4. Find the old MySQL driver jar and rename it so it doesn't have a .jar extension. Example, rename mysql-connector-java-5.1.39-bin.jar to mysql-connector-java-5.1.39-bin.jar.old. (The actual location and version number may vary, but it's usually located in {cf2016_root}\lib\)

5. Restart the ColdFusion 2016 Windows Service

Finally, create a new DSN using Driver Type = Other and enter the following. Just replace "YourDatasourceName" and "YourDatabaseName" with the correct values.

• CF Data Source Name: YourDatasourceName
• JDBC URL: jdbc:mysql://127.0.0.1:3306/YourDatabaseName?tinyInt1isBit=false&
• Driver Class: com.mysql.jdbc.Driver
• Driver Name: com.mysql.jdbc.Driver
• User name: root
• Password : (your password)

---

Without seeing your real DSN settings, this is just a guess, but... verify the Server setting is correct. Since MySQL is running on the same machine as CF, enter either localhost or 127.0.0.1

I confirmed that entering an invalid server name like NotARealServerName (no computer by that name on the network) produces the same error you're seeing when you try and verify the DSN in CF11.

Once you get it working, I'd strongly recommend creating a separate user account, granting the appropriate permissions, and using that with the CF DSN (instead of "root").

Answer 2

Problem was with version mysql connector java.
First time I used the latest version Connector/J 8.0.11 and need to use previous version Connector/J 5.1.46. Advice of Ageax's helped me a lot!
Work it both MySQL 5 and Other drivers.