You are here

Home » Blogs » d.cochran's blog

Killing MySQL Queries that exceed a timeout using PDO and PHP

published by d.cochran on Fri, 10/18/2013 - 08:15

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;

Tags: