I'm having an ongoing issue with the database connections timing out
after a long period of inactivity (i.e. no-one connecting to the tomcat
applicaton).

But first...

My system:
OS: Ubuntu 18.04.2 LTS (server)
Tomcat: 9.0.22 (installed from tomcat distribution, not via apt get)
Java: OpenJDK "11.0.3" 2019-04-16
Mysql: Ver 14.14 Distrib 5.7.26

I'm using the standard 8-hour timeout on mysql connections, and have the
set autoReconnect=true when I connect to the database:

jdbc:mysql://127.0.0.1:3306/mydb?autoReconnect=true

Everything works fine except for the 8-hour timeouts. If I click the
tomcat link again, the autoReconnect works and the applications is back
up instantly.

The only message in any log is this:

> SQL Problem: The last packet successfully received from the server was 
> 30,394,076 milliseconds ago.  The last packet sent successfully to the server 
> was 30,394,076 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.
> SQL State: 08S01
> Vendor Error: 0

Is there a way other than using a longer timeout value to stop the
connection from breaking? That is, is there a pre-emptive form of
autoReconnect?

Some other statistics: I have a 'watchdog' process (servlet + cron job)
that exercises the database on regular intervals. In spite of that, I
still get these SQL timeouts.

I've been tracking the timeouts since April 2019. All timeouts exceed 8
hours. The minimum between timeouts was 9.3 hours, maximum  was 166.1
hours with an average since April 2019 of 35 hours.

Thanks in advance.

-Richard

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

--
This communication is intended for the use of the recipient to whom it is 
addressed, and may contain confidential, personal, and or privileged 
information. Please contact us immediately if you are not the intended 
recipient of this communication, and do not copy, distribute, or take action 
relying on it. Any communications received in error, or subsequent reply, 
should be deleted or destroyed.
---

Reply via email to