Connecting CF 2021 to AWS RDS MySQL Community Server 8.0.35 fails

[default8vn6shtsmayn]

Howdy,

We have a MySQL Community server packaged as an AWS RDS instance. We had been running MySQL 5.7, but AWS is forcing an upgrade to 8.0 unless you pay an extra charge.

We have a datasource that works fine for 5.7, using the "MySQL" driver from the dropdown, but when we try to connect to an upgraded snapshot running MySQL 8.0.35, we get:

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

Stack Trace:
java.lang.NullPointerException at com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionImpl.java:3307) at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1985) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1911) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1288) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2506) at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2344) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2326) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344) at coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(JDBCPool.java:666) at coldfusion.server.j2ee.sql.pool.ConnectionRunner$RunnableConnection.run(ConnectionRunner.java:67) at java.base/java.lang.Thread.run(Thread.java:834)

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

The datasource that worked for 5.7 but not for 8.0 also had connection string
useUnicode=true&characterEncoding=utf8

Keeping that or removing it when trying to conenct to 8.0 appears to have no effect- same error.

Any ideas? Thanks for your time and attention.

[Charlie Arehart · Accepted solution]

I'm willing to bet you just need to replace your mysql 5 jdbc driver in cf with a mysql 8 one. It may surprise some to hear this is "not Adobe's job" to do for us. Let me explain (for any who need to hear this). 

When it comes to connecting to mysql Community edition, Adobe does NOT provide the needed jdbc driver (because Oracle would make them pay an arm and a leg to "distribute" it with a product like CF). They offer that mysql option in the cf admin, but (as documented) it won't work until WE as users of CF take the needed steps of a) getting that driver (from Oracle, which is free for US to get) then b) implementing that driver in cf. 

So first, look in the coldfusion2021/cfusion/lib folder. You will likely see a jar file whose name starts with mysql. It will likely indicate being for version 5. If so, this is what you need to replace.

You can't leave the old one there, not even renamed. But don't delete it, in case you want to restore it. Instead, stop cf and move it out to some non-cf folder, for backup purposes. 

Then Google for downloading the mysql 8 jdbc driver and get that file--which should be a single jar with a filename again starting with mysql but indicating a version of 8. As of now (the url and site interface could change over time), go to https://dev.mysql.com/downloads/connector/j/, and use the "platform independent" option. Choose either the zip or tar.gz, depending on your preference, and extract that zip to find that needed jar.) 

Put THAT jar file into the cfusion/lib folder, and restart cf, then verify your failing datasource in the cf admin (or just test your app). Does it work now?

If not, revert things by swapping out the old jar for the new (again, don't leave the new one there, even renamed), and restart cf. At least then any existing mysql 5 datasources should still work for you.

Let us know if this helps or not. 

[default8vn6shtsmayn]

Howdy,

That worked the charm- went to the URL,
downloaded the platform independent zip file,
unzipped,
found the mysql-connector-j-8.3.0.jar file,
stopped CF,
moved the cfusion/lib/mysql-connector-java-5.1.30.bin.jar file to a safe location outside the cfusion tree,,
swapped in the mysql-connector-j-8.3.0.jar file into cfusion/lib,
restarted CF,
the datasource verified,
and the template with the query using that source ran successfully.

Thank you once more for your able assistance!

[Charlie Arehart]

Wonderful to hear. Thanks for the update and the kind regards. Enjoy. 🙂