MySQL Communication link failure

[ManojK5]

Hello,

We have few internal tasks that try to query our SMA(Systems Management Appliance) to fetch some data on our CF 2023 stack.

However, we have been getting  a specific error mentioned below. Surprisingly, the error seems to be intermittent. 

It works sometimes and fails later after trying consecutively

Error is: Error Executing Database Query.: Communications link failure The last packet successfully received from the server was 48 milliseconds ago. The last packet sent successfully to the server was 63 milliseconds ago

we are using mysql data source type as other(connection string mentioned below) and the connection verification says "OK" but the query fails  intermittently. 

jdbc:mysql://xx..xx.com:3306/XXX?connectTimeout=5000&failOverReadOnly=false&enabledTLSProtocols=TLSv1.2&trustCertificateKeyStoreUrl=file:/opt/mysql.keystore&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=XXX&serverTimezone=EST5EDT

Here are few other details :

mysql connector 8.4

OS type: RHEL 9 

Any inputs/suggestions are highly appreciated.

Thanks.

[BKBK]

Error is: Error Executing Database Query.: Communications link failure The last packet successfully received from the server was 48 milliseconds ago. The last packet sent successfully to the server was 63 milliseconds ago

we are using mysql data source type as other(connection string mentioned below) and the connection verification says "OK" but the query fails  intermittently. 

jdbc:mysql://xx..xx.com:3306/XXX?connectTimeout=5000&failOverReadOnly=false&enabledTLSProtocols=TLSv1.2&trustCertificateKeyStoreUrl=file:/opt/mysql.keystore&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=XXX&serverTimezone=EST5EDT

Here are few other details :

mysql connector 8.4

OS type: RHEL 9 

Any inputs/suggestions are highly appreciated.

By @ManojK5

I have the following suggestions:

1.    In the ColdFusion Administrator, choose 'MySQL' as datasource instead of 'Other'.
2.    Delete the setting "connectTimeout=5000" from the connection-string. That should enable ColdFusion (not MySQL) to determine when the database connection will time out.
3.    Replace the setting "enabledTLSProtocols=TLSv1.2" in the connection-string with "tlsVersions=TLSv1.2,TLSv1.3" (Remark: from MySQL 8.0.28 onwards, the property "tlsVersions" has replaced the property "enabledTLSProtocols" ).
4.    When you make the changes in steps 1, 2 and 3, the connection-string should then be something like:
   

   failOverReadOnly=false&tlsVersions=TLSv1.2,TLSv1.3&trustCertificateKeyStoreUrl=file:/opt/mysql.keystore&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=XXX&serverTimezone=EST5EDT​

   
   Now, take a look at the java.args property in ColdFusion's configuration file jvm.config. Make sure ColdFusion's time-zone and TLS settings don't clash with MySQL's.  For example, ensure that java.args contains the settings, 
   

   -Duser.timezone=EST5EDT -Djdk.tls.client.protocols=TLSv1.2,TLSv1.3 -Dhttps.protocols=TLSv1.2,TLSv1.3​

   
   (Remark: I am assuming that the ColdFusion and MySQL servers are in the same time-zone).
   
   
5.  Restart ColdFusion for the changes to take effect.

[ManojK5]

@BKBK  Thanks for the response.  We have identified that the issue is outside  of CF2023. We have done few unit tests and determined that we were having issues with the communication from the server to application(SMA) MySQL DB. I will update here once we identify the root cause.

[BKBK]

@ManojK5 , Thanks for the update.  As you look further into the issue, it might help to take my earlier suggestion into account: 'Delete the setting "connectTimeout=5000" from the connection-string. That should enable ColdFusion (not MySQL) to determine when the database connection will time out.'

[ManojK5]

@BKBK  I tried removing the setting connectTimeout=5000 from the connection string. but,   that didn't change anything with the output I'm getting.

" Error is: Error Executing Database Query.: Communications link failure The last packet successfully received from the server was 50 milliseconds ago. The last packet sent successfully to the server was 65 milliseconds ago."

Anyways, I think we may have to tweak my.cnf file  and see if that would change anything. 

[BKBK]

OK. That was that then.

Should you be looking for more suggestions, here are some: 

https://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-... 

https://stackoverflow.com/questions/6865538/solving-a-communications-link-failure-with-jdbc-and-mysq... 

[BKBK]

There is yet another indication that this issue is likely caused on the MySQL side. Two days ago, someone published a similar MySQL connection failure error on Lucee.  

[BKBK]

Yet another idea, attributed to Steven Erat by Pete Freitag: use a "Validation Query". 

(Reference: https://stackoverflow.com/questions/6681443/communications-failure-link-in-cfc-with-query )

Steps:

1.  Click on the button to edit the datasource;
2.  Click on "Show Advanced Settings";
3.  Check the checkbox "Enable connection validation";
4.  Add the line SELECT 1 in the field "Validation Query"

5.  Press the Submit button.

[BKBK · Accepted solution]

@ManojK5 , what is the database brand? I ask because someone who reported a similar MySQL Communication Link Failure on Lucee discovered that they were in fact connected to MariaDB, and not,  as they thought, to MySQL.

They solved the problem by reverting to a MariaDB-specific JDBC driver.

[ManojK5]

@BKBK  It seems like changing to MariaDB driver did the trick.. I tried multiple times and got NO errors so far. 

and yeah we are trying to connect to MySQL DB in our case.  

Thanks much for pointing me in the right direction.