Re: Re: Database timeout
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. 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
Re: Re: Database timeout
On 7/27/2019 7:18 AM, Mark Thomas wrote: > On 26/07/2019 02: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 > Full configuration please. The database is run entirely from within the servlets and supporting Java code, no configuration at all in server.xml. Unfortunately it's a legacy part of the system that I have not had the time to rewrite. Basically the only change to the default server.xml is to add the 443 enabling block and point it to our certificates. I don't change anything else from what is supplied as the standard tomcat 9.0.22 tar.gz file aside from clearing out the default webapps directory. I'll see what I can grab in the way of database configuration from the code. > > Mark > > >> 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. >> --- >> >> - >> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org >> For additional commands, e-mail: users-h...@tomcat.apache.org >> > -- 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. ---
Re: Database timeout
-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
Re: Database timeout
On 26/07/2019 02: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 Full configuration please. Mark > > 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. > --- > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Database timeout
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. ---
