Killing MySQL Queries that exceed a timeout using PDO and PHP

Here is an easy way to kill queries using PDO connection method with MySQL. Note that you can NOT kill queries using a "prepared" string. It must be done using "query".

The first step is to grab the PROCESSLIST.

$q = $dbh->query("SHOW FULL PROCESSLIST");
$results = $q->fetchAll();
 
$error = $q->errorInfo();
if ($error[1]) print_r($error);

Now that all the MySQL processes have been aquired in our $results variable, loop through them, killing the queries that exceed your timeout limit.

In this below example, I will kill all queries that exceed 30 seconds.

$killedList = '';
 
foreach($results as $row => $field) {
 
  //make sure we only kill queries
  if ($field['Command'] != 'Query') continue;
 
  //if the Time exceeds 30 seconds, kill it
  if ($field['Time'] > 30) {
 
    $q = $dbh->query("KILL ".$field['Id']);
 
    $error = $q->errorInfo();
    if ($error[1]) print_r($error);
 
    $killedList .= '<strong>Query Killed:</strong> '.$field['Id'].'<br>';
  } 
}
 
if ($killedList) print $killedList;

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
If you have questions about anything on this site, you can find me on Google+: