Connecting ColdFusion 2016 to MySQL 8

If you still happen to use ColdFusion 2016 and you want to connect it to MySQL 8 (or MariaDB 10), you will receive the following error:

Could not create connection to database server. Attempted reconnect 3 times. Giving up.

It is not telling much about the cause of the problem. In fact, when you try to connect any other modern tool with the same credentials, it will work just fine. So, where is the problem?

Problem: old connector version

ColdFusion 2016 is using MySQL connector version 5. In my case, it was mysql-connector-java-5.1.40-bin.jar which is not capable of connecting to MySQL 8. This is the root cause of the issue.

Solution: upgrade connector

Since ColdFusion is using Java connectors, we can make it use the new one. Be careful! I was not able to make “old” and “new” connectors work together, so once you will switch to the new one, you will have to stop using the old one. When you try to use the new one on MySQL 5.6 or 5.7, it will cause such an error: 

Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

So, you can switch all MySQL datasources to the new driver or stay with the old one.

How?

First, go to https://downloads.mysql.com/archives/c-j/ and select Product Version of 8.0.22 and Operating System: Platform Independent. Why the old version? I will get to this in a moment.

Once selected, you will see the download links below, download the ZIP archive (or TAR if you are a Linux user and prefer this one).

Once downloaded:

  1. Stop the ColdFusion service
  2. Unzip the connector and copy JAR file to the C:\ColdFusion2016\cfusion\lib (or, in other words: {CF home dir}\cfusion\lib)
  3. Rename the “old” connector (should be in the same folder) from .jar to .jar.bak or something else to make sure that it is no longer “jar” file. In my case it was from mysql-connector-java-5.1.40-bin.jar to mysql-connector-java-5.1.40-bin.jar.bak which prevents ColdFusion from loading it.
  4. Start the ColdFusion service
  5. Go go ColdFusion Admin and configure datasources. Please note, that in ColdFusion Admin, the driver is still named MySQL 5. Only the JAR file loaded in the background is replaced, not the name.

Why older driver?

Because the newer one will give you the error of:

The value class java.time.LocalDateTime cannot be converted to a date

This is due to the different types of objects being returned as a date time. This is true for connectors of version 8.0.23 – 8.0.28 (the current version on 2022-02-23, when this article was written).