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 cancelled 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.
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'.
mysqlprocgrep --sql-body \
--match-command='my_admin_thingy%' --match-user='msaladin%' --kill-connectionDECLARE 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;
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
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.
If you are familiar with using regular expressions, you can
to use regular expressions instead of database patterns.