On 7/27/2019 9:43 PM, Christopher Schultz wrote: > -----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. Yes, that is what I understand from reading the documents. I was just hoping there might be another config item I missed. > >> 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.
<sigh> we do use a Java coded connection pool that's legacy code that I have not had a chance to replace. It does work, except for the timeout. The timeout itself is not unexpected. The client took a big hit in the economic downturn and there is almost no activity on the application, sometimes for weeks at a time. Basically, with no-one using the system it's behaving as one would expect. It's just that when you do go to log on, it *always* seems to be down, do to lack of activity. Click refresh and it's back up (again, as expected). I'd just like to have it not go down. >> 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. Again, I suspect this is the case. I have no idea why that should be, but when you remove all that's obvious, what is left must be suspected. Looks like I will be digging into / rebuilding this code. > >> 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----- > --- 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. --- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
