467,859 Members | 1,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,859 developers. It's quick & easy.

query timeout

It looks like mysql doesn't have the ability to set a query timeout. Is
this true? So in the mean time I created the following perl DBI script
to do it for me.

#!perl

# DBI service name
use constant DSN => "dbi:mysql:ABC:localhost";

# database username
use constant DBUSER => "root";

# database password
use constant DBPASS => "XYZ";

# seconds application should wait before checking again
use constant SLEEP => 5;

# maximum seconds before script kills a query
use constant TIMEOUT => 10;

# hash of usernames that will be checked
my %USERS = ( foo => 1 );

# END OF CONFIG
use strict;
use DBI;
my ($dbh, $read_sth, $kill_sth);
$dbh = DBI->connect(DSN,DBUSER,DBPASS,
{ PrintError => 1, RaiseError => 1, AutoCommit => 1 }) or die
$DBI::errstr;
$read_sth = $dbh->prepare("SHOW FULL PROCESSLIST");
$kill_sth = $dbh->prepare("KILL ?");
print STDERR "Killed Statements:\n";
while (1) {
$read_sth->execute();
while (my ($id, $user, $host, $db, $cmd, $time, $state, $info)
= $read_sth->fetchrow_array()) {
if (exists $USERS{$user} && $time > TIMEOUT &&
$cmd eq 'Query' && $state eq 'Sending data') {
print STDERR "Id: $id\nUser: $user\nHost: $host\nDB:
$db\nCommand: $cmd\nTime: $time\nState:
$state\nInfo:\n$info\n---------------------------------------------------------------------\n";
$kill_sth->execute($id);
}
}
sleep SLEEP;
}

Jul 28 '05 #1
  • viewed: 8775
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Stefano | last post: by
reply views Thread by Abram Friesen | last post: by
3 posts views Thread by Stijn Vanroye | last post: by
1 post views Thread by traceable1 | last post: by
reply views Thread by laurenquantrell | last post: by
2 posts views Thread by laurenquantrell | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.