AEM JDBC Connection issue

[smkswamy]

We are facing wait timeout exceptions while connecting to JDBC database and it happens intermittently. It is happening when only when there is lot of idle time in the night.

Exception details :

{​​​​"errorType":"SQL Exception Occured","appName":"Custom-Service","responseMessage":"The last packet successfully received from the server was 52,364,564 milliseconds ago.  The last packet sent successfully to the server was 52,364,564 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.","responseCode":500}​​​​  

JDBC config:

jdbc.driver.class="com.mysql.jdbc.Driver"
jdbc.connection.uri="jdbc:mysql://url"
jdbc.validation.query="SELECT\ 1\ FROM\ DUAL"
datasource.svc.properties=[""]
default.readonly=B"false"
default.autocommit=B"true"
pool.max.wait.msec=L"100000"
pool.size=L"100000"

We tried by increasing wait timeout but still for some users it is failing on higher environments. We also tried with 'autoReconnect=true', but that is also not fixed the issue. Thanks in advance. 

[vanegi · Accepted solution]

Please check this post https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/issue-while-creating-a-mys...

Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

1. IP address or hostname in JDBC URL is wrong.
2. Hostname in JDBC URL is not recognized by local DNS server.
3. Port number is missing or wrong in JDBC URL.
4. DB server is down.
5. DB server doesn't accept TCP/IP connections.
6. DB server has run out of connections.
7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
   
   

To solve the one or the other, follow the following advices:

1. Verify and test them with ping.
2. Refresh DNS or use IP address in JDBC URL instead.
3. Verify it based on my.cnf of MySQL DB.
4. Start the DB.
5. Verify if mysqld is started without the --skip-networking option.
6. Restart the DB and fix your code accordingly that it closes connections in finally.
7. Disable firewall and/or configure firewall/proxy to allow/forward the port.

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

Thanks!!