Terminate MySQL Connections

Every so often I run into a situation when I need to terminate connections on MySQL server – for example, hundreds of instances of some bad query is running, making the server unusable. Many people have special scripts which can take the user, source host, or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:

In general, this is a very powerful approach which I use in a lot of cases to create a set of SQL statements by SQL query and then execute it.

It would be nice and clean if MySQL would have some way to “eval” – execute the result set of the query as SQL commands. This would avoid the requirement to use temporary file etc.

Share this post

Comments (35)

  • Ahmad Ali

    I am facing the problem
    Warning: mysql_connect() [function.mysql-connect]: Too many connections in /home/road2l/public_html/connect.php on line 15
    Error connecting to mysqlToo many connections
    Please help me out.

    May 21, 2009 at 12:00 am
  • http://krow.livejournal.com/

    You are not the only one. I’ve thought that this would be handy several times.

    Create a session variable and then make it so that it can be parsed by the parser?

    Not terribly hard.

    -Brian

    May 21, 2009 at 3:08 pm
  • peter

    Brian,

    Yes that is one possibility. Though I think it is nicer if result set can be executed line by line – the GROUP_CONCAT trick works but it is typically restricted in size.

    May 21, 2009 at 4:07 pm
  • Sergey

    Could you please update your post to point out that this feature is 5.1+?

    May 21, 2009 at 5:27 pm
  • Robert Wultsch

    I prefer the following as it will kill them in a multi threaded manner… (sometimes killing a single query can take a while)

    for i in $(mysql -uroot -pPASS -e ‘show processlist’ | grep ‘search_term’ | awk ‘{print $1}’); do
    mysql -uroot -pPASS -e “kill $i” &
    done

    May 21, 2009 at 6:27 pm
  • Robert Wultsch

    Peter,
    Couldn’t you use prepared statements as an eval?

    May 21, 2009 at 7:09 pm
  • Nils

    could also be a modifier to the client instead of a special sql construct (much like \G)

    May 21, 2009 at 9:52 pm
  • peter

    Sergey,

    PROCESSLIST table exists in MySQL 5.1 as well as Percona patches MySQL 5.0 I actually used later for this test.

    May 21, 2009 at 9:56 pm
  • Shlomi Noach

    Hi Peter,

    With INFORMATION_SCHEMA.PROCESSLIST one can create a stored procedure using server cursor to achieve this. The following procedure accepts a user name and kills all queries for that user.
    server cursors are a MySQL solution for ‘eval’.

    Shlomi

    DELIMITER $$

    DROP PROCEDURE IF EXISTS test.kill_user_queries$$
    CREATE PROCEDURE test.kill_user_queries (kill_user_name VARCHAR(16) CHARSET utf8)
    SQL SECURITY INVOKER
    BEGIN
    DECLARE query_id INT;
    DECLARE iteration_complete INT DEFAULT 0;
    DECLARE select_cursor CURSOR FOR SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user=kill_user_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET iteration_complete=1;

    OPEN select_cursor;
    cursor_loop: LOOP
    FETCH select_cursor INTO query_id;
    IF iteration_complete THEN
    LEAVE cursor_loop;
    END IF;
    KILL QUERY query_id;
    END LOOP;
    CLOSE select_cursor;

    END$$

    DELIMITER ;

    May 21, 2009 at 9:56 pm
  • peter

    Robert,

    I think you can use prepared statements and user variables to execute 1 statement. I’d like to execute many statements and it is best if they are returned as multiple rows in result set.

    May 21, 2009 at 9:58 pm
  • peter

    Shlomi,

    Thanks. Indeed I’d like some good set of useful Stored Procedures to be available for MySQL so we could just add them to any MySQL installation same as we do with Maatkit – there are many things which are relatively easy with stored procedures but you do not want to write it every time from scratch 🙂

    May 21, 2009 at 10:06 pm
  • Shlomi Noach

    Peter,

    I agree. I think there are some general-purpose stored procedures kits around. There are also many useful queries+views by Roland Bouman that I know of.
    Would be nice to gather them all into a “utilities” schema.

    PS a prepared statement will not allow “KILL” or “OPTIMIZE” or “ANALYZE” queries. Just the normal SELECT/INSERT/UPDATE/…

    May 21, 2009 at 10:10 pm
  • peter

    Shlomi,

    Yes… This is a design bug which is waiting to be fixed for years.

    May 21, 2009 at 10:24 pm
  • Chip Turner

    Check out mypgrep.py from the google mysql tools project on code.google.com. It basically is a supercharged process grep for mysql, including the ability to kill processes.

    May 21, 2009 at 11:59 pm
  • Peter Stuifzand

    A few years ago I wrote an article about how to use Vim with mysql to generate and execute SQL statements. It’s here: http://peterstuifzand.nl/external-programs-in-vim.html

    May 22, 2009 at 5:37 am
  • peter

    Thanks Chip,

    The good thing with having it done on SQL level is you do not need to install everything – not every server running MySQL has working perl python on php installed

    May 22, 2009 at 9:48 am
  • Bryan

    If information_schema.processlist doesn’t exist on your version of MySQL, this works in a linux script:

    #!/bin/bash
    for each in mysqladmin -u root -prootpwd processlist | awk '{print $2, $4, $8}' | grep $dbname | grep $dbuser | awk '{print $1}';
    do mysqladmin -u root -prootpwd kill $each;
    done

    May 27, 2009 at 8:24 am
  • peter

    Bryan,

    Which version of Percona patches are you running ? It exists in recent ones for sure.

    Thanks for command line version though.

    May 27, 2009 at 8:34 am
  • Bryan

    No Percona patches yet, unfortunately. We’re running the standard MySQL 5.0.77 on Centos.

    May 27, 2009 at 8:49 am
  • peter

    I see,

    Standard MySQL is not what we call “our” MySQL 🙂

    May 27, 2009 at 9:04 am
  • Glynn Durham

    How about a technique like this:

    . File optimizeworld.sfs:

    # Optimize tables in the World database.
    SELECT ‘OPTIMIZE TABLE ‘, CONCAT(TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = ‘World’;

    . To execute:

    shell> mysql –skip-column-names < optimizeworld.sfs | mysql

    May 29, 2009 at 3:39 am
  • Andrew Watson

    I do this:

    mysqladmin proc | grep Sleep | sort -r -n -k6 | awk {‘print $1; ‘} | xargs mysqladmin kill

    or something to that effect…

    May 29, 2009 at 9:38 am
  • Brent William Peterson

    I am wondering if there isn’t a symptom that is missing? I am running a high volume ecommerce site and we switched to Magento from custom app written in vb.net (.net 1.1). Shouldn’t MySql handle all of this? What I am wondering is why my old windows box running doesn’t crash with the same load as my high powered linux box?

    February 17, 2010 at 9:22 am
  • haberler

    I used the command and the query being run in mysql processlist still running for 28226 and l cant kill it with everything l did. what might else l should try ?
    thanks.

    April 10, 2012 at 12:53 pm
  • Kirill

    Thank you Peter for Percona and for this post!

    May 24, 2012 at 2:07 pm
  • Hitesh Agja

    Thanks Peter. Query is really very helpful to me.

    August 21, 2012 at 12:07 am
  • Jitender

    Hi, i am using mysql 5.0 which does not have processlist table in information_schema, now is there any way i can list all query which is taking more then x time. Any script will be really appriciated.

    September 11, 2012 at 3:53 pm
  • sheyda

    This may not directly related to this chain, but I landed here to kill massive connection due to the hung drop trigger – I have a dilemma with dropping a trigger on a percona 5.5.27 on a very large table ~ 68 Gig. I get the metadata lock and it hangs.

    I have tried everything. Is it safe to remove the trigger from DB.TRG file? Thanks a lot

    November 13, 2012 at 12:37 am
  • Alon

    ERROR 1146 (42S02): Table ‘infromation_schema.processlist’ doesn’t exist

    April 10, 2013 at 8:10 am
  • Shlomi Noach

    @Alon,
    You have a typo. Should be information_schema.processlist

    April 10, 2013 at 8:14 am
  • Aptitude

    Thank you.. very informative as all your articles.. keep it up.

    July 2, 2013 at 8:41 am
  • Aptitude

    Very informative as all your articles.. keep it up.

    July 2, 2013 at 8:42 am
  • scriptime

    i am also wiring about mysql administration on my blog

    November 19, 2013 at 3:39 am
  • Ben

    Great tip, thanks!

    June 27, 2018 at 10:10 am
  • Utpal

    Thanks for this post. Nice tips to kill mysql connections.

    October 13, 2018 at 4:30 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.