Safely timing out DBI queries

Greetings all. I'm working on an app which allows users to construct
queries using a web UI in a moderately free-form fashion. There's
plenty of data and hence plenty of rope. I need to save my users from
themselves by timing-out long-running queries and killing the MySQL
thread.

Our first attempt used alarm() and $SIG{ALRM}. For reasons described
in the DBI docs, this didn't work - the alarm fires, but only after
the database is done with the query. I did try the POSIX sigaction()
recipe from the docs, and it worked. However, I'm very nervous about
the possibility of random instability inherent in unsafe signals. I'm
hoping to find a better way.

At present I'm imagining something like:

- Parent forks a child before starting long-running query.

- Child sleeps for $timeout seconds.

- If parent finishes first, kills child and proceeds.

- If child wakes up, kills parent's MySQL thread ID and exits.
Parent gets "lost connection during query" and assumes that's a
timeout.

I am concerned about a race condition here. What happens when the
parent finishes just before the child wakes up? Seems to me the child
could kill the parent's connection and the parent might not notice.
In my case I think I can get away with this - when the parent finishes
it's about the exit() itself, and doesn't need its DB handle anymore.
I'd like to have a recipe that didn't have this potential problem
though, since I forsee needing this elsewhere in the future.

Ideas? Am I better off giving in to darkside of POSIX?

-sam

RE: Safely timing out DBI queries

I've done something similar to what you did...

It went something like this...

- Run query in a separate thread.
- Display dialog box to allow user to abort (instead of having fixed timeout).
- If query finishes first, close dialog box, and query thread is done.
- If user presses abort, cancel the query which then brings control back to the thread and the thread is done.

Yes, I ran into race conditions occasionally, but that was fixed by adding locks around critical sections of code. Now, by all appearances, it works like a charm.

Now the problem is I did not use Perl and did not use DBI**. Plus I am not familiar with using Threads in Perl and with DBI so I can't tell how well a design like this translates to Perl. I'll leave that for you to evaluate if you wish.

** I used the latest language and development platform from a large monopoly company that shall remain nameless.

-----Original Message-----
From: Sam Tregar [mailto:sam@tregar.com]
Sent: Friday, September 15, 2006 3:15 PM
To: dbi-users@perl.org
Subject: Safely timing out DBI queries


Greetings all. I'm working on an app which allows users to construct
queries using a web UI in a moderately free-form fashion. There's
plenty of data and hence plenty of rope. I need to save my users from
themselves by timing-out long-running queries and killing the MySQL
thread.

Our first attempt used alarm() and $SIG{ALRM}. For reasons described
in the DBI docs, this didn't work - the alarm fires, but only after
the database is done with the query. I did try the POSIX sigaction()
recipe from the docs, and it worked. However, I'm very nervous about
the possibility of random instability inherent in unsafe signals. I'm
hoping to find a better way.

At present I'm imagining something like:

- Parent forks a child before starting long-running query.

- Child sleeps for $timeout seconds.

- If parent finishes first, kills child and proceeds.

- If child wakes up, kills parent's MySQL thread ID and exits.
Parent gets "lost connection during query" and assumes that's a
timeout.

I am concerned about a race condition here. What happens when the
parent finishes just before the child wakes up? Seems to me the child
could kill the parent's connection and the parent might not notice.
In my case I think I can get away with this - when the parent finishes
it's about the exit() itself, and doesn't need its DB handle anymore.
I'd like to have a recipe that didn't have this potential problem
though, since I forsee needing this elsewhere in the future.

Ideas? Am I better off giving in to darkside of POSIX?

-sam

Re: Safely timing out DBI queries

On Sat, 16 Sep 2006, Henri Asseily wrote:

> Use the great Sys::SigAction by Lincoln Baxter.

That's just a wrapper around POSIX sigaction(), right? As I
understand it that's equivalent to the old unsafe signals in Perl
before 5.8. I'd rather avoid that if it all possible!

-sam

Re: Safely timing out DBI queries

On Sun, 17 Sep 2006, Henri Asseily wrote:

> You've got a catch-22: If you ALWAYS want the signal to fire on time, then
> you should expect unsafe signals.
> Just clean up after yourself.

It's hard to clean up after the kind of problems unsafe signals can
cause. For example, imagine the DB client code is in the middle of
updating some global state when the signal fires. That state variable
or structure will be left in an inconsistent state, just waiting for
the next usage to explode. This kind of corruption can be very hard
to recognize - it might not trigger a problem until that state is used
again, likely in a completely different area of your program.

Memory corruption bugs are the very worst kind, in my opinion - I'll
do whatever I have to do to avoid them. Yes, it's unlikely, but
that's actually worse! I'd rather have a bug that happens every time
than one that only happens once a week, or once a month.

-sam

Re: Safely timing out DBI queries

Use the great Sys::SigAction by Lincoln Baxter.
It has never failed me.
Take a look at DBIx::HA for sample usage.

On Sep 16, 2006, at 2:27 AM, CAMPBELL, BRIAN D (BRIAN) wrote:

> I've done something similar to what you did...
>
> It went something like this...
>
> - Run query in a separate thread.
> - Display dialog box to allow user to abort (instead of having
> fixed timeout).
> - If query finishes first, close dialog box, and query thread is done.
> - If user presses abort, cancel the query which then brings control
> back to the thread and the thread is done.
>
> Yes, I ran into race conditions occasionally, but that was fixed by
> adding locks around critical sections of code. Now, by all
> appearances, it works like a charm.
>
> Now the problem is I did not use Perl and did not use DBI**. Plus
> I am not familiar with using Threads in Perl and with DBI so I
> can't tell how well a design like this translates to Perl. I'll
> leave that for you to evaluate if you wish.
>
> ** I used the latest language and development platform from a large
> monopoly company that shall remain nameless.
>
> -----Original Message-----
> From: Sam Tregar [mailto:sam@tregar.com]
> Sent: Friday, September 15, 2006 3:15 PM
> To: dbi-users@perl.org
> Subject: Safely timing out DBI queries
>
>
> Greetings all. I'm working on an app which allows users to construct
> queries using a web UI in a moderately free-form fashion. There's
> plenty of data and hence plenty of rope. I need to save my users from
> themselves by timing-out long-running queries and killing the MySQL
> thread.
>
> Our first attempt used alarm() and $SIG{ALRM}. For reasons described
> in the DBI docs, this didn't work - the alarm fires, but only after
> the database is done with the query. I did try the POSIX sigaction()
> recipe from the docs, and it worked. However, I'm very nervous about
> the possibility of random instability inherent in unsafe signals. I'm
> hoping to find a better way.
>
> At present I'm imagining something like:
>
> - Parent forks a child before starting long-running query.
>
> - Child sleeps for $timeout seconds.
>
> - If parent finishes first, kills child and proceeds.
>
> - If child wakes up, kills parent's MySQL thread ID and exits.
> Parent gets "lost connection during query" and assumes that's a
> timeout.
>
> I am concerned about a race condition here. What happens when the
> parent finishes just before the child wakes up? Seems to me the child
> could kill the parent's connection and the parent might not notice.
> In my case I think I can get away with this - when the parent finishes
> it's about the exit() itself, and doesn't need its DB handle anymore.
> I'd like to have a recipe that didn't have this potential problem
> though, since I forsee needing this elsewhere in the future.
>
> Ideas? Am I better off giving in to darkside of POSIX?
>
> -sam

Re: Safely timing out DBI queries

On Sep 16, 2006, at 8:58 PM, Sam Tregar wrote:

> On Sat, 16 Sep 2006, Henri Asseily wrote:
>
>> Use the great Sys::SigAction by Lincoln Baxter.
>
> That's just a wrapper around POSIX sigaction(), right? As I
> understand it that's equivalent to the old unsafe signals in Perl
> before 5.8. I'd rather avoid that if it all possible!

Yes, when you're under 5.8+, it's the POSIX routines.

From the POD:

Unfortunately, at least with perl 5.8.0, the result is that
doing this
effectively reverts to the 'unsafe' signals behavior. It is
not clear
whether this would be the case in perl 5.8.2, since safe flag
can be
used to ask for safe signal handling. I suspect this
separates the the
logic of which "sa_flags" are used install the handler, and
whether
deferred signal is used.


You've got a catch-22: If you ALWAYS want the signal to fire on time,
then you should expect unsafe signals.
Just clean up after yourself.

Personally, when coding DBIx::HA I couldn't find another way.
H

Re: Safely timing out DBI queries

On Sep 17, 2006, at 12:05 AM, Henri Asseily wrote:

>
> On Sep 16, 2006, at 8:58 PM, Sam Tregar wrote:
>
>> On Sat, 16 Sep 2006, Henri Asseily wrote:
>>
>>> Use the great Sys::SigAction by Lincoln Baxter.
>>
>> That's just a wrapper around POSIX sigaction(), right? As I
>> understand it that's equivalent to the old unsafe signals in Perl
>> before 5.8. I'd rather avoid that if it all possible!

One more thing: my experience with Sys::SigAction (i.e. POSIX
sigaction) is very good. I have yet to run into issues as long as I
properly wipe the sth/dbh/... variables after the signal fires. Of
course the strategy of spawning a child process and wiping the whole
thing if the signal fires also works, but it's not really necessary.
It's like using a nuke on a cockroach. But YMMV...

H

Re: Safely timing out DBI queries

I'm not really qualified to comment on DBD::mysql, but...

I just saw your note on mysql internals, and suspect your
request there is unlikely to be given serious consideration.

I think your best bet might be to work with the DBD::mysql maintainers
to implement some driver-specific nonblocking versions of
execute/prepare (and maybe fetch), as well as support for
'out of band' cancel.

I've implemented a similar capability for DBD::Teradata
with some success, and IIRC there has been some mention
of a general async i/f for DBI for awhile. I'm not
terribly familiar w/ MySQL's client libs, but I'd hope
they provide support for such capability. Or alternately,
I believe ODBC provides an async i/f which might be wired into
DBD::ODBC.

HTH,
Dean Arnold
Presicient Corp.

Re: Safely timing out DBI queries

On Sat, 16 Sep 2006, Dean Arnold wrote:

> I just saw your note on mysql internals, and suspect your
> request there is unlikely to be given serious consideration.

Always a possibility. Maybe they'd be more interested in a patch.
(For the curious, I wrote a message to the MySQL internals
mailing-list proposing a new feature:

SELECT ... WITH TIMEOUT 10;

)

> I think your best bet might be to work with the DBD::mysql maintainers
> to implement some driver-specific nonblocking versions of
> execute/prepare (and maybe fetch), as well as support for
> 'out of band' cancel.

That's an interesting idea. Right now I'm putting together
DBIx::Timeout which implements my fork()-based timeout in a reusable
package. Seems to work, although I've learned to expect forking code
to have unexpected bugs which take time to shake out.

-sam

Re: Safely timing out DBI queries

On Sat, Sep 16, 2006 at 08:31:54PM -0400, Sam Tregar wrote:
> On Sat, 16 Sep 2006, Dean Arnold wrote:
>
> >I think your best bet might be to work with the DBD::mysql maintainers
> >to implement some driver-specific nonblocking versions of
> >execute/prepare (and maybe fetch), as well as support for
> >'out of band' cancel.
>
> That's an interesting idea.

For any driver that uses a network socket to connect you could close()
the socket in the signal handler to (relatively) safely timeout.

Should be fairly clean/safe for the db client library state, though
unsafe signals means there's still a chance perl's internal state could
be corrupted.

On the server-side the query may be left running on databases that
don't detect disconnects (which I think still includes mysql).

A minor problem with this approach is how to determine the socket file
descriptor. For drivers that don't/can't make the socket fd available,
it requires checking which fd's are open before and after the connect.
Kludgy but effective.


> Right now I'm putting together
> DBIx::Timeout which implements my fork()-based timeout in a reusable
> package. Seems to work, although I've learned to expect forking code
> to have unexpected bugs which take time to shake out.

It's mysql specific currently, using $id=$dbh->{thread_id} and do("KILL $id").
(And shouldn't thread_id be mysql_thread_id?)

Would be nice to generalise it. There was talk sometime ago (perhaps on
dbi-dev) of extending the DBI api along these lines:

$id = $dbh->{SessionId};

$dbh->kill_session($id);

Would certainly be simple for me to add to the DBI. Then it's just a
simple matter of getting the drivers to implement it :)


Also, rather than fork each time you need a timeout it would be nice to
be able to have a single 'watchdog' process and use some form of fast
IPC to tell it when to be active (passing the timeout to use and
relevant session id) and when to be inactive. A pipe would be fine.
But perhaps that could be a new DBIx::Watchdog module. Just a thought.

Tim.

Re: Safely timing out DBI queries

>
> Also, rather than fork each time you need a timeout it would be
> nice to
> be able to have a single 'watchdog' process and use some form of fast
> IPC to tell it when to be active (passing the timeout to use and
> relevant session id) and when to be inactive. A pipe would be fine.
> But perhaps that could be a new DBIx::Watchdog module. Just a thought.

This is a good way for handling a few potentially long-running
queries, but forking each time means that you create a new dbh each
time, correct? That's impossibly slow for environments like high-
volume webservers.
The watchdog process concept is good, but if you have a watchdog for
each regular process, you end up with either double the connect on
the database server, or the watchdog process having to open-close
every time.
Better yet is a pool of watchdog processes, which can then also be
used in an Apache/modperl environment. You specify how many processes
are allowed to connect to the db, and you use those exclusively. You
can also kill any one of them any time you want, effectively having
safe signals.

Some kind of a DBI::Pool on the process level, basically. With a
parent process that manages the pool of DBI processes, which can be
killed by a calling process when the query takes too long.

H

Re: Safely timing out DBI queries

Read The Fine Mail headers which tell you how to do so!

On 17.09.2006 13:47, Martin Gainty wrote:
> unsubscribe immediately
>
> ************************************************************ *********
> This email message and any files transmitted with it contain confidential
> information intended only for the person(s) to whom this email message is
> addressed. If you have received this email message in error, please notify
> the sender immediately by telephone or email and destroy the original
> message without making a copy. Thank you.
>
>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: Safely timing out DBI queries

On 17.9.2006 16:21, Martin Gainty wrote:
> where ?
>
> Martin--
> ************************************************************ *********
> This email message and any files transmitted with it contain confidential
> information intended only for the person(s) to whom this email message is
> addressed. If you have received this email message in error, please notify
> the sender immediately by telephone or email and destroy the original
> message without making a copy. Thank you.
>
>
>
> ----- Original Message -----
> From: "Alexander Foken"
> To: "Martin Gainty"
> Cc: "dbi-users"
> Sent: Sunday, September 17, 2006 8:31 AM
> Subject: Re: Safely timing out DBI queries
>
> > Read The Fine Mail headers which tell you how to do so!
> >
> > On 17.09.2006 13:47, Martin Gainty wrote:
> >> unsubscribe immediately
> >>
> >> ************************************************************ *********
> >> This email message and any files transmitted with it contain
> >> confidential information intended only for the person(s) to whom this
> >> email message is addressed. If you have received this email message in
> >> error, please notify the sender immediately by telephone or email and
> >> destroy the original message without making a copy. Thank you.
> >
> > --
> > Alexander Foken
> > mailto:alexander@foken.de http://www.foken.de/alexander/



List-Unsubscribe:


--
Zhivko Duchev
===================================================
Institute for Animal Breeding
Mariensee 31535 Neustadt Germany
Tel : (+49)(0)5034 871127 Fax : (+49)(0)5034 871239
e-mail: duchev@tzv.fal.de
===================================================

Re: Safely timing out DBI queries

On Sun, 17 Sep 2006, Tim Bunce wrote:

> For any driver that uses a network socket to connect you could close()
> the socket in the signal handler to (relatively) safely timeout.
>
> Should be fairly clean/safe for the db client library state, though
> unsafe signals means there's still a chance perl's internal state could
> be corrupted.

That doesn't sound quite safe enough to me, frankly. I hate memory
corruption bugs...

>> Right now I'm putting together
>> DBIx::Timeout which implements my fork()-based timeout in a reusable
>> package. Seems to work, although I've learned to expect forking code
>> to have unexpected bugs which take time to shake out.
>
> It's mysql specific currently, using $id=$dbh->{thread_id} and
> do("KILL $id"). (And shouldn't thread_id be mysql_thread_id?)

Yup, I figured other people could send me patches implementing that
for their DBs if they were so inclined, but a DBI solution would be
even better!

> Also, rather than fork each time you need a timeout it would be nice to
> be able to have a single 'watchdog' process and use some form of fast
> IPC to tell it when to be active (passing the timeout to use and
> relevant session id) and when to be inactive. A pipe would be fine.
> But perhaps that could be a new DBIx::Watchdog module. Just a thought.

That's an interesting idea, and worthwhile if DBIx::Timeout was a
bottleneck. That's unlikely in my application since I'm using it in a
case where the query is very likely to be a long one.

-sam

Re: Safely timing out DBI queries

On Sun, 17 Sep 2006, Henri Asseily wrote:

> This is a good way for handling a few potentially long-running queries, but
> forking each time means that you create a new dbh each time, correct? That's
> impossibly slow for environments like high-volume webservers.

Right. And a few long-running queries is exactly what I plan to use
it for! It would be nice to have something that could prevent any
query from running long, but what I really need is to have a way to
time-out the few obvious problem-children.

> Some kind of a DBI::Pool on the process level, basically. With a
> parent process that manages the pool of DBI processes, which can be
> killed by a calling process when the query takes too long.

Patches welcome. It's overkill for my app, but I can see the value of
it.

-sam

Re: Safely timing out DBI queries

On Sun, 17 Sep 2006 10:59:48 -0700, Dean Arnold wrote:

Hi Dean

> just swallow the signal. (I'll note that signals are conclusive
> proof that UNIX
> was developed in an era when recreational narcotics were readily
> available and inexpensive.)

The more things change, the more things stay the same...

And haven't you heard about the buildings (sic) full of marijuana smoke when=
the
Burroughs OSes were developed? And they were, I believe, the most powerful=
of
all OSes. Makes me think I might have missed out on something by not taking=

drugs ;-).

--
Cheers
Ron Savage, ron@savage.net.au on 18/09/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: Safely timing out DBI queries

--=_alternative 004D9EAFC12571EE_=
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="us-ascii"

And some drivers have a "timeout" parameter that handles this issue at the
vendor API level (e.g. DBD::Sybase's "timeout" parameter that is handled
internally by OpenClient).

Michael





Extranet
chuckfox2@aol.com - 19.09.2006 15:37


To: henri
cc: tyler, darnold, Tim.Bunce, dbi-users, dbi-dev
Subject: Re: Safely timing out DBI queries


I realize that this is very specific to the database, however, it may be
possible to set a resource limit at the database level that will prevent
the queries from consuming too much time.

Chuck

henri@shopzilla.com wrote:
> On Sep 18, 2006, at 6:18 PM, Tyler MacDonald wrote:
>
>> Dean Arnold wrote:
>>>>> Which brings me back to the notion of non-blocking requests.
>>>>> Assuming
>>>>> many/most client libs do support an async capability, and a OOB
>>>>> cancel, then it should be possible to standardize the behavior
>>>>> externally.
>>>>
>>>> Attempting to standardize, let alone implement, non-blocking requests
>>>> for the current DBI is a far bigger task than the above.
>>>>
>>>> On the other hand, I'd be *delighted* if you, or anyone else, would
>>>> like
>>>> to champion the work.
>>
>>
>> Start up a thread to handle the request, which sets a state
>> variable on
>> the statement handle then the request has been processed?
>>
>
> The problem is not to know when a request is done processing.
> The problem is killing requests that are processing for too long.
> If you want kill them safely, you may not be able to kill them until
> they're done, which defeats the purpose.
> If you kill them "unsafely", then the Perl interpreter might be in a
> dirty state, forcing you to thoroughly dispose of it if you want to be
> 100% safe.
>
> To kill the requests safely and when you want to, you need
> asynchronous support from the database client APIs and drivers, and
> quite a bit of standardized support code from DBI.
> H



This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.

---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.


--=_alternative 004D9EAFC12571EE_=--