Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
EPUB - 366.2Kb
HTML Download (TGZ) - 289.0Kb
HTML Download (Zip) - 301.1Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you run a process every night to kill certain connections?

3.2.7 How do you run a process every night to kill certain connections?

Some database administrators use nightly routines to perform maintenance on their databases or servers. Sometimes these routines can be blocked by long running queries or applications that hang onto locks for longer than expected.

Naturally, priority is given to the application and maintenance routines are often canceled rather than interfere with an application. Should it happen that you subscribe to this notion and you have a routine that is still being blocked or for some reason hasn't completed by a certain time, you need a quick way to generate an event to kill the connection involved. This is where the mysqlprocgrep utility can help.

Objectives

The objective is to generate an event that will kill all connections based on a user login ('msaladin') but only if that connection is trying to run a custom administration script named 'my_admin_thingy'.

Example Execution

shell> mysqlprocgrep --sql-body \
          --match-command='my_admin_thingy%' --match-user='msaladin%'  --kill-connection
DECLARE kill_done INT;
DECLARE kill_cursor CURSOR FOR
  SELECT
        Id, User, Host, Db, Command, Time, State, Info
      FROM
        INFORMATION_SCHEMA.PROCESSLIST
      WHERE
          COMMAND LIKE 'my_admin_thingy%'
        AND
          USER LIKE 'msaladin%'
OPEN kill_cursor;
BEGIN
   DECLARE id BIGINT;
   DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
   kill_loop: LOOP
      FETCH kill_cursor INTO id;
      KILL CONNECTION id;
   END LOOP kill_loop;
END;
CLOSE kill_cursor;      

Discussion

Notice in the example above, we did not connect to any server to get this information. That is one of the great things about this utility - you can generate all manner of SQL statements for finding processes and try them out on a test system before incorporating them into your events, triggers, and routines.

We specified the user with the --match-user option using a wildcard in case the user is logged in from a different system. We also specified the name of the maintenance routine in the same manner in case it gets renamed with a version number or some such.

The output of this utility then is the SQL statement we need to use to find and kill the connections that meet these criteria. Armed with this, we can make a procedure we can call from an event and execute the SQL at a precise time every day.

Permissions Required

The user must have the SELECT permission on the mysql database.

Tips and Tricks

If you are familiar with using regular expressions, you can use the --regexp option to use regular expressions instead of database patterns.


User Comments
Sign Up Login You must be logged in to post a comment.