This week's book giveaway is in the Spring forum.
We're giving away four copies of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 and have Marten Deinum & Iuliana Cosmina on-line!
See this thread for details.
Win a copy of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

MySQL connections on Tomcat Shoutdown

 
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I would like to clear one doubt regarding tomcat restart. Suppose my web application code contains a complex query to MySQL.
I’m not sure what happens if tomcat got restarted while MySQL is running this query.
When I checked it, I could see the query in MySQL (show processlist) even after tomcat shoutdown.

Can anyone let me know if this process gets removed from MySQL processlist and when? I not
sure if frequent restarts will make MySQL busy on running these complex queries from previous sessions.

Please give me an advice, thanks in advance.
 
Saloon Keeper
Posts: 23703
161
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As far as I am aware, most DBMS's are going to do this. A JDBC query is an atomic operation with no option to abort processing mod-stream. It triggers processing in the DBMS, and the only real way the DBMS can tell that Tomcat has shut down is that Tomcat's network connections were all dropped. Since DBMS processing is fairly complex and modularized, it's unlikely that the inner workings of the query process in the DB server are going to be aware of the network connections. Not impossible, since loss of connection could trigger a cascade of events down into the DB internal processors, but not likely, since it's extra complexity and thus can make the processing buggier. Also, queries are one thing, but ACID updates and deletes are another - do you roll them back or finish them silently?

It's not really a good idea to run lengthy queries from a webserver, however. Not just because of the problems that you are anticipating, but because web actions are expected to happen interactively and quickly and it takes extra work to code something that can run long-term in a webapp. A better solution, if possible, would be do do the query offline and store the results pre-digested in a result table, materialized view, or something equally amenable to quick interrogation.
 
Jomy George
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Tim,

Is there any option to set querytimeout in connection pool configuration, or mysql settings?
I could set queryTimeout for a statement, and it worked fine (process got closed in mysql after specified time).

But setting this for all DB operations will be an overhead in my already deployed server.
Is there any single point to do this in Connection pool, or mysql?
 
Tim Holloway
Saloon Keeper
Posts: 23703
161
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't see that as an option for DBCP.

But it occurs to me that long-running queries aren't appropriate for web applications anyway. You're trying to solve a problem that shouldn't exist. Or at least, should be restricted to very unusual systems such as a free-form ad hoc query system where users have the ability to formulate SQL queries of unpredictable character.

Sometimes database operations will take a long time. I had one system where the worst-case scenario (about once a week) involved 10 hours of processing. Aside from the fact that this would timeout just about any web request, I couldn't even afford to put that in a background process, since the entire webserver - including unrelated webapps - would have been held hostage. I resolved that one by setting up an independent application that the webapp controlled via RMI.
 
Jomy George
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am facing the same problem,

In my webapp, we have options to query database.
Users can execute quires as they like.
Now if a user try some complex query, i can see it running in MYSQL for hours.
Normally user wont wait for result that much time, also will get a time out in browser, as it is taking too much time to get a response.

I just need to clear these unwanted processes (which is being executed for a long time like 1 hour) from MySQL.
 
"How many licks ..." - I think all of this dog's research starts with these words. Tasty tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic