Beefy Boxes and Bandwidth Generously Provided by pair Networks Bob
Do you know where your variables are?
 
PerlMonks  

timing out or interrupting a mysql query

by clueless newbie (Friar)
on Feb 01, 2011 at 21:48 UTC ( #885620=CUFP: print w/ replies, xml ) Need Help??

Greetings!

A recent post on PerlMonks "How to limit MySQL execution time?" along with a post on another forum "kill mysql query in perl" and my own attempts prompted this script which appears to actually work if the "K"ill option is used.

My tests indicate that the $sth->cancel approach does not terminate the query on the server-side (try "perl <scriptname> 10 C 2" five times in rapid succession) while the $dbh->clone()->do("KILL QUERY ".$dbh->{"mysql_thread_id"}) does ("perl <scriptname> 10 K 2" five times in rapid succession). While it appears "cancel" does allow the script to "time out", the subsequent "SHOW PROCESSLIST" indicates that the queries continue running. This doesn't seem to happen with the "KILL QUERY" approach.

Note that "KILL QUERY <>" was implemented in MySQL 5.0.

#!/usr/bin/perl # $ARGV[0] as in # "/* Test Query */ SELECT SLEEP($ARGV[0]) FROM lpn LIMIT 1" # $ARGV[1] as in # if ($ARGV[1] =~ /k/i) { # $dbh_o->clone()->do("KILL QUERY ".$dbh_o->{"mysql_thread_id" +}); # }; # # Cancel the statement # if ($ARGV[1] =~ /c/i) { # $sth_o->cancel(); # }; # $ARGV[2] as in # alarm($ARGV[2]); use Benchmark; use Data::Dumper; use DBI; use strict; use warnings; use Sys::SigAction; local $SIG{INT}='IGNORE'; # The following mySQL query will require $ARGV[0] seconds to compl +ete my $SQL_s="/* Test Query */ SELECT SLEEP($ARGV[0]) FROM lpn LIMIT +1"; my @Argument_a=(); my $t0=Benchmark->new(); # So we can do a SHOW PROCESSLIST in the future my $dbh_o=DBI->connect("DBI:mysql:host=<host>;database=<database>" +,"<user name>","<password>" ,{RaiseError=>1,PrintError=>1,AutoCommit=>1} ); my $sth_o=$dbh_o->prepare("SHOW FULL PROCESSLIST"); eval { # For our "Test Query" that we are hoping to timing out/interr +upting my $dbh_o=DBI->connect("DBI:mysql:host=<host>;database=<databa +se>","<useer name>","<password>" ,{RaiseError=>1,PrintError=>1,AutoCommit=>1,} ); print STDERR "Connecting to '$dbh_o->{Name}'!\n"; print STDERR "Connected to '$dbh_o->{mysql_hostinfo}'!\n"; print STDERR "Connected to '$dbh_o->{mysql_serverinfo}'!\n"; my $t0=Benchmark->new(); my $sth_o=$dbh_o->prepare($SQL_s); my @_a=(); my $rows_s; eval { # Abending the execute - fatal my $TimeOut=Sys::SigAction::set_sig_handler('ALRM',sub { # Clone a handle over which we will "do" a + "KILL QUERY ..." if ($ARGV[1] =~ /k/i) { warn "Attempting KILL QUERY."; $dbh_o->clone()->do("KILL QUERY ".$dbh +_o->{"mysql_thread_id"}); }; # Cancel the statement if ($ARGV[1] =~ /c/i) { warn "Attempting cancel."; $sth_o->cancel(); }; die "Timed Out!"; } ); my $ControlC=Sys::SigAction::set_sig_handler('INT',sub { # Clone a handle over which we will "do" a + "KILL QUERY ..." if ($ARGV[1] =~ /k/i) { warn "Attempting KILL QUERY."; $dbh_o->clone()->do("KILL QUERY ".$dbh +_o->{"mysql_thread_id"}); }; # Cancel the statement if ($ARGV[1] =~ /c/i) { warn "Attempting cancel."; $sth_o->cancel(); }; die "Ctrl-C'd!"; } ); # Set alarm alarm($ARGV[2]); $rows_s=$sth_o->execute(@Argument_a); # Clear alarm alarm(0); }; # Prevent race condition alarm(0); die if $@; my $row_s=0; my $Abend_f=0; eval { # Abending the fetch - not fatal $SIG{INT}=\&interrupt; sub interrupt { $SIG{INT}=\&interrupt; $Abend_f=1; }; # Preallocate $#_a=$rows_s-1; # Need field names to build the hash my $field_aref=$sth_o->{NAME}; my ($cache_aref,$row_aref); while (!$Abend_f && ($row_aref=shift(@$cache_aref) || shif +t@{$cache_aref=$sth_o->fetchall_arrayref(undef,10_000) || []})) { my $_href; @$_href{@$field_aref}=@$row_aref; $_a[$row_s++]=$_href; }; }; $sth_o->finish(); if (!$Abend_f) { # Not prematurely terminated } elsif ($row_s) { # At least one row was read - truncate $#_a=$row_s-1; } else { # No rows were read - truncate @_a=(); }; # display what was fetched ... }; print STDERR "Elapsed ".Benchmark::timestr(Benchmark::timediff(my +$t1=Benchmark->new(),$t0))."\n\n"; # See if "Test Query" is still running $sth_o->execute(); # Need field names to build the hash my $field_aref=$sth_o->{NAME}; my ($cache,$row); while ($row=shift(@$cache) || shift@{$cache=$sth_o->fetchall_array +ref(undef,10_000) || []}) { my %_h; @_h{@$field_aref}=@$row; if ($_h{Info} =~ m{Test Query}) { warn "'Test Query' (thread $_h{Id}:time $_h{Time}) found i +n processlist!\n"; }; }; $sth_o->finish(); die if $@; exit; __END__

Comment on timing out or interrupting a mysql query
Download Code
Re: timing out or interrupting a mysql query
by clueless newbie (Friar) on Feb 02, 2011 at 15:52 UTC

    The part relevant to implementing timing out and interrupting a mysql query can be condensed to:

    use strict; ... use warnings; use DBI; use Sys::SigAction; ... my $sth_o=$dbh_o->prepare($SQL_s); my $rows_s; eval { # Time out and interrupt work my $TimeOut=Sys::SigAction::set_sig_handler('ALRM',sub { $dbh_o->clone()->do("KILL QUERY ".$dbh_o->{"mysql_thre +ad_id"}); die "Timed Out!"; }); local $SIG{INT}=sub { $dbh_o->clone()->do("KILL QUERY ".$dbh_o->{"mysql_thre +ad_id"}); die "Ctrl-C'd!"; }; # Set alarm alarm(<max time allowed>); $rows_s=$sth_o->execute(@Argument_a); # Clear alarm alarm(0); }; # Prevent race condition alarm(0); die if $@;

    My attempts to use a local $SIG{ALRM} in place of the Sys::SigAction::set_sig_handler weren't successful. I'll freely admit to not trying too hard as Sys::SigAction::set_sig_handler works fine.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://885620]
Approved by GrandFather
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2014-04-21 12:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (495 votes), past polls