KILL QUERY_ID IN (SELECT QUERY_ID FROM information_schema.PROCESSLIST WHERE ...)

or

KILL THREAD_ID IN (SELECT id FROM information_schema.PROCESSLIST WHERE ...)

or

KILL USER IN (SELECT user FROM information_schema.PROCESSLIST WHERE ...)

Eric Bergen

eric.bergen@gmail.com

TiVo

If you're going to add support for killing queries in a specific state

then why not just add the ability to pass in a state? I can see some

use cases for killing queries in a specific state that are more useful

than sleep.

Off the top of my head:

Killing a query while it is waiting for table lock. This would be

especially handy for things like alter table or a big update where you

want to kill it only if it is waiting on a lock but before it has done

any modifications. This may be because it is holding the write lock

and causing other queries to pileup and you know the rollback will be

fast.

It may also be useful to be able to kill queries in opening tables

state in a load shedder to try to quiet down table cache trashing. Or

maybe killing queries in unauthenticated state that are holding up the

accept thread waiting on a bad dns request.

I'm sure there are more state specific kill commands that would come in handy.

static const char *thread_state_info(THD *tmp)

{

#ifndef EMBEDDED_LIBRARY

  if (tmp->net.reading_or_writing)

  {

    if (tmp->net.reading_or_writing == 2)

      return "Writing to net";

    else if (tmp->get_command() == COM_SLEEP)

      return "";

    else

      return "Reading from net";

  }

  else

#endif

  {

    if (tmp->proc_info)

      return tmp->proc_info;

    else if (tmp->mysys_var && tmp->mysys_var->current_cond)

      return "Waiting on cond";

    else

      return NULL;

  }

}

plugin/feedback/sender_thread.cc:  thd->proc_info="feedback";

sql/sql_table.cc:    thd_proc_info(thd, "Writing to binlog");

sql/sql_table.cc:    thd_proc_info(thd, 0);

sql/event_scheduler.cc:  thd->proc_info= "Clearing";

sql/event_scheduler.cc:  thd->proc_info= "Initialized";

sql/event_scheduler.cc:    new_thd->proc_info= "Clearing";

sql/event_scheduler.cc:    new_thd->proc_info= "Clearing";

sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Waiting for ndbcluster binlog update to "

sql/ha_ndbcluster_binlog.cc:  const char *proc_info= "<no info>";

sql/ha_ndbcluster_binlog.cc:    proc_info= injector_thd->proc_info;

sql/ha_ndbcluster_binlog.cc:                        "  injector proc_info: %s"

sql/ha_ndbcluster_binlog.cc:                        ,proc_info

sql/ha_ndbcluster_binlog.cc:  thd->proc_info= "Opening " NDB_REP_DB "." NDB_REP_TABLE;

sql/ha_ndbcluster_binlog.cc:  thd->proc_info= "Syncing ndb table schema operation and binlog";

sql/ha_ndbcluster_binlog.cc:    thd->proc_info= "Waiting for ndbcluster to start";

sql/ha_ndbcluster_binlog.cc:    thd->proc_info= "Waiting for first event from ndbcluster";

sql/ha_ndbcluster_binlog.cc:    thd->proc_info= "Waiting for event from ndbcluster";

sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Waiting for schema epoch";

sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Processing events from schema table";

sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Processing events";

sql/ha_ndbcluster_binlog.cc:          thd->proc_info= "Committing events to binlog";

sql/ha_ndbcluster_binlog.cc:  thd->proc_info= "Shutting down";

sql/sql_partition.cc:  thd->proc_info="end";

sql/sql_base.cc:                  thd->proc_info= "DBUG sleep";

sql/sql_load.cc:  thd_proc_info(thd, "reading file");

sql/sql_load.cc:    thd_proc_info(thd, "End bulk insert");

sql/slave.cc:  thd_proc_info(thd, "Loading slave GTID position from table");

sql/slave.cc:  thd->proc_info= messages[SLAVE_RECON_MSG_WAIT];

sql/slave.cc:  thd->proc_info = messages[SLAVE_RECON_MSG_AFTER];

sql/sp_head.cc:    thd_proc_info(thd, "closing tables");

sql/lock.cc:  thd_proc_info(thd, "Table lock");

sql/sql_show.cc:        thd_info->proc_info= (char*) (tmp->killed >= KILL_QUERY ?

sql/sql_partition_admin.cc:  thd_proc_info(thd, "verifying data with partition");

sql/sql_class.cc:    proc_info= msg;

sql/sql_class.cc:  proc_info="login";

sql/sql_parse.cc:  thd_proc_info(thd, "updating status");

sql/sql_select.cc:  thd_proc_info(thd, "show_explain_trap");

sql/sql_select.cc:  thd_proc_info(thd, save_proc_info);

sql/sql_select.cc:    thd_proc_info(thd, (!strcmp(save_proc_info,"Copying to tmp table") ?

sql/sql_select.cc:                  "Copying to tmp table on disk" : save_proc_info));

storage/csv/ha_tina.cc:  old_proc_info= thd_proc_info(thd, "Checking table");

storage/myisam/ha_myisam.cc:  thd_proc_info(thd, "Checking table");

storage/myisam/ha_myisam.cc:        thd_proc_info(thd, "Repair done"); // to reset proc_info, as

storage/myisam/ha_myisam.cc:        thd_proc_info(thd, "Repair by sorting");

storage/myisam/ha_myisam.cc:      thd_proc_info(thd, "Repair with keycache");

storage/myisam/ha_myisam.cc:      thd_proc_info(thd, "Sorting index");

storage/myisam/ha_myisam.cc:    thd_proc_info(thd, "Analyzing");

storage/myisam/ha_myisam.cc:  thd_proc_info(thd, "Saving state");

storage/myisam/ha_myisam.cc:    thd_proc_info(thd, "Creating index");

storage/pbxt/src/discover_xt.cc:  session->set_proc_info("creating table");

storage/pbxt/src/discover_xt.cc:  session->set_proc_info("After create");

storage/pbxt/src/discover_xt.cc:  thd_proc_info(thd, "creating table");

storage/pbxt/src/discover_xt.cc:  thd_proc_info(thd, "After create");

storage/spider/spd_trx.cc:  thd->proc_info = "";

storage/archive/ha_archive.cc:  old_proc_info= thd_proc_info(thd, "Checking table");

storage/maria/ha_maria.cc:  old_proc_info= thd_proc_info(thd, "Checking status");

storage/maria/ha_maria.cc:  thd_proc_info(thd, "Checking keys");

storage/maria/ha_maria.cc:  thd_proc_info(thd, "Checking data");

storage/maria/ha_maria.cc:  old_proc_info= thd_proc_info(thd, "Scanning");

storage/maria/ha_maria.cc:  old_proc_info= thd_proc_info(thd, "Checking table");

storage/maria/ha_maria.cc:        thd_proc_info(thd, "Repair done");

storage/maria/ha_maria.cc:        thd_proc_info(thd, "Repair by sorting");

storage/maria/ha_maria.cc:      thd_proc_info(thd, "Repair with keycache");

storage/maria/ha_maria.cc:      thd_proc_info(thd, "Sorting index");

storage/maria/ha_maria.cc:        thd_proc_info(thd, "Analyzing");

storage/maria/ha_maria.cc:  thd_proc_info(thd, "Saving state");

storage/maria/ha_maria.cc:    const char *save_proc_info= thd_proc_info(thd, "Creating index");