-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Richard,
On 7/25/19 21:44, Richard Huntrods wrote: > 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. This is the documented behavior of MySQL Connector/J: after a disconnect, it will fail the first subsequent connection usage and mark the connection as failed. Only after that will the connection become available the reconnect. > 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? You should be using a connection pool with a connection validation-check. If you enable those, you shouldn't even have the "SQL Problem" after a timeout. > 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. Maybe it's not doing what you think it is. > 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. - -chris -----BEGIN PGP SIGNATURE----- Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/ iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl09J/IACgkQHPApP6U8 pFjjqA//ULfwUhS4r0NWVxIljVck9uUHKtzJW5Wk2fzCnjr0MQh3h4bNJSZj7Myt aFUWt3Cw+KmJ1zSOoAkDpDIvfvJsszCJhE5NP7DGi7iZMcA9Ln4JIpVEFpIbOj+J 9dV9+yHom60vLefwhl8v+Rh5PYsA6Sr87T6PXj8wkIrPvdr5LGnz+YzONFaCZKok R9YujGVoDiA3mI+FXX3V6BwyOw2w7zwJYJHRJ6kdB/bVjzcZ0DDsW1OOo5iifAeX IevbR7pa+K0GuCZrvzje/6MefI3Lm6giXFReMIU4PpwLL+oITM6ImbYuUJxA7Lqk kWb79SQAcw5MAlbeNXVuYL/1eGuyG1Vf5wkAZj4sf8XPMWeyWbstLOk6WN7Mwtm3 0ALPQgSs1Dhb8BUVOgCC4AtfvbBfE3/47dP6ZDumU8DNfV78SZdKcaaWvFSXobZu m0qk8raDdAhRxQ8FwJlzgZLWU7sqTjxw9D8F5mD9VPiTxD/IuVxGV9fOZDKN9vP4 q69km58evlFew0KIkHQE7MCKhDL7+oQ9Q7i3/dJmxHXxwWLpZDLTAGWANes/ksD6 GAsWkpFHejNu/cNJYOJ/B2Yl1FvPPqq1k0QBVQTYleJ+FXThOzJOyShd38tMdWgW bcy7ZeUgglI6B1VNlxp7ROQhA3fj5xOexL/sqi5kWICNiAsaQwU= =0TT3 -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
