Killing MySQL Queries that exceed a timeout using PDO and PHP

Error message

Deprecated function: Methods with the same name as their class will not be constructors in a future version of PHP; GeSHi has a deprecated constructor in require_once() (line 915 of /home/digipiph/public_html/sites/all/modules/libraries/libraries.module).

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;