5

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.

[![DSN SQLNonTransientConnectionException Error][1]][1]

Roman Kharitonov
  • 131
  • 1
  • 9
  • 1
    Possible duplicate of [Connecting Coldfusion 9 with MySql 5 on Ubuntu 9.10](https://stackoverflow.com/questions/3257081/connecting-coldfusion-9-with-mysql-5-on-ubuntu-9-10) – Terrabits Apr 30 '18 at 19:07
  • 3
    What settings did you use for the DSN (Database, Server, Port, Username, ...). Is the database on the same server as CF? – SOS Apr 30 '18 at 19:14
  • I use standard settings (MySQL server, localhost, 3306, root). Same IP addresses, but different ports. CF on the http://localhost:8600 and DB on the http://localhost:3306. – Roman Kharitonov May 01 '18 at 00:06
  • Did you set up a user to connect to MySQL? Are the permissions correct? And is your CF DSN setup to look for MySQL on Port 3306? Using the correct JDBC string? – Shawn May 01 '18 at 03:48
  • No, I didn't set up a user, I use already existing 'root', I suppose 'root' has permissions for my problem. My CF DSN setup to look for MySQL on Port 3306. Where I can to find jdbc string? – Roman Kharitonov May 01 '18 at 12:33
  • Are you able to connect to your database with MySql Workbench with the same username, password and port? – Twillen May 01 '18 at 13:30
  • It's okay for testing, but normally you'd create a separate user, instead of using "root". Anyway, 1) can you log into mysql from the command line? `mysql -u root -p`? 2) Is port 3306 open? You can check using something like `telnet 127.0.0.1 3306`. – SOS May 01 '18 at 13:32
  • Yes, Twillen, I'm able to connect to my database with MySql Workbench and that set of params which I try to use in CF Administrator.
    Ageax, Yes, I can log into mysql from comman line. I wrote `mysql -u root -p -h 127.0.0.1` then I entered the password and I successfully connected. When I wrote "telnet 127.0.0.1 3306" I saw: `J 8.0.11&Giw2>     – Roman Kharitonov May 01 '18 at 17:29
  • @RomanKharitonov - It's open. Otherwise, you'd have gotten a connection failed message like `Could not open connection to the host, on port 3306...`. Hmm... when you say *I use standard settings (MySQL server, localhost....)* I'm not sure the values entered are correct. One cause of the NonTransient... error is an incorrect "Server" name, like setting it to "SomeInvalidName" instead of "localhost". It should be set to: `Database: YourDatabaseName | Server: 127.0.0.1 | Port: 3306 | UserName: root | Password: (YourPassword)`. – SOS May 01 '18 at 18:21

2 Answers2

10

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

Server Name Localhost

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.

Invalid Server Name

Verify DSN SQLNonTransientConnectionException

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").

SOS
  • 6,430
  • 2
  • 11
  • 29
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.

enter image description here

Roman Kharitonov
  • 131
  • 1
  • 9
  • Glad you finally got things working =) Weird that v8.0.11 didn't work for you with MySQL 8. I didn't try 5.1.46, only 5.1.39 which didn't work MySQL8. The right driver makes all the difference! Just be aware the "MySQL5" label is misleading. It's just a hard coded label, so CF'll uses whatever version of the driver it finds in the CF class path 5.x, 8.x, whatever ;-) – SOS May 03 '18 at 15:40
  • Yep, you're right. For anyone reading, I can confirm it works with 5.1.46 too. (On my install, it also works with 8.0.11 too, but use whichever of the two works for you). – SOS May 05 '18 at 11:58