Skip to main content
M
Manoj36229141q88d
Inspiring
August 22, 2024
Answered

MySQL Communication link failure

  • August 22, 2024
  • 1 reply
  • 2923 views

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.

    This topic has been closed for replies.
    Correct answer 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.

     


    @Manoj36229141q88d , 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.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 23, 2024
    quote
    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 @Manoj36229141q88d

     

    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.
    M
    Manoj36229141q88dAuthor
    Inspiring
    August 28, 2024

    @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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 28, 2024

    @Manoj36229141q88d , 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.'

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices