Cancel DBI Mysql request in a CGI script called with XMLHTTPREQUEST

joe_kool has asked for the wisdom of the Perl Monks concerning the following question:

A javascript application is calling, with XMLHTTPREQUEST, a perl script to run MySQL SELECT queries using DBI. Those sql queries can take a while to execute, like 20-30 seconds. It sometimes happens that the XMLHTTPREQUEST request is interrupted by the user. Example: The user changes his mind about a search criterion and restarts the search.

Is there any way to:

1- Trap the fact that the Perl script was interrupted in the middle
2- At that moment, cancel the ongoing MySQL query?

Thank you!

Comment on Cancel DBI Mysql request in a CGI script called with XMLHTTPREQUEST

Replies are listed 'Best First'.
Re: Cancel DBI Mysql request in a CGI script called with XMLHTTPREQUEST by Devanchya (Beadle) on Dec 15, 2006 at 15:34 UTC
Sounds like you need to look at AJAX. The reason being is you can make a progress bar to explain to the user it is executing the search However, I would be more inclined to look at your select statement and figure out why it takes 20 seconds to return. One of the EASIEST ways to cut down the return time on an SQL statement is using Limit. With my suggested AJAX usage (CGI::AJAX), you can then make additional calls to the database in the background to load up the rest of the data while the user looks at the smaller result set. If you can prevent it, there is really NO reason to send a user 550 results when he really just cares about the top five lines. Oh, and did I mention, no one likes reading through huge datasets. To kill the select statement, you need to know the thread_id in mysql, and have the proper permissions to issue a kill command. You can then send a command through MYSQL to kill the select. Most times this requires PROCESS privileges that most shared db owners will not give you. If it's dedicated do what you want, it's your data to kill. As of MySQL 5, you can use the KILL QUERY command to kill the current query that is running for your active connection. Kill first showed up in MySQL 4.1, and something similar is in 3.23 under "zapp" but I wouldn't touch that one... -- Even smart people are dumb in most things...	[reply]
Re: Cancel DBI Mysql request in a CGI script called with XMLHTTPREQUEST by samtregar (Abbot) on Dec 15, 2006 at 18:44 UTC
This may not be directly applicable, but you might want to look at DBIx::Timeout. It kills a running MySQL query when a timeout is reached - you could use the same technique but have it trigger off an AJAX request. -sam	[reply]
Re: Cancel DBI Mysql request in a CGI script called with XMLHTTPREQUEST by stonecolddevin (Parson) on Dec 16, 2006 at 08:00 UTC
Take a look at CGI::Ajax. I'm not sure what kind of stuff it has as far as progress bars and such, I actually think they may have something like that you can use, but if not, you can code it in yourself. It will save you a lot of time, allowing you to focus on coding in perl and not worrying so much about the JS. meh.	[reply]