Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am experiencing issue.

Someone 'attacked' my server: simply by searching the same phrase with multiple requests.

As it is text search request and database indices are not used, the engine searches through every row. In general I will deal with myisam full-text indices, but I am curious, is there any way to block/disconnect/interrupt the query which is longer than x seconds?

P.S. I have wait_timeout set to 30, is there anything else I can set(I don't think interactive timeout will be useful).

share|improve this question

migrated from superuser.com Feb 27 '13 at 7:40

This question came from our site for computer enthusiasts and power users.

    
Thank you guys for all your question. I'v ended up with following answer: serverfault.com/a/401830/129360 –  Jevgeni Smirnov Feb 27 '13 at 10:41
add comment

2 Answers 2

up vote 2 down vote accepted

If you are using MySQL 5.1+, you can create a scheduled event in MySQL or a cronjob to poll the processlist from the information schema.

What queries do you look for in the processlist? Any DB Connection that ...

  • runs longer that 30 seconds
  • not used for replication
  • runs a SELECT query

Here is that query:

SELECT id FROM information_schema.processlist
WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

Here is a Stored Procedure to collect those IDs and terminate the query using the KILL command:

CREATE DATABASE myutil;
USE myutil
DELIMITER $$ 
DROP PROCEDURE IF EXISTS `Kill_Long_Queries` $$ 
CREATE PROCEDURE `Kill_Long_Queries` () 
BEGIN

    CREATE TABLE IF NOT EXISTS IDs_To_Kill;
    (ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL) ENGINE=MyISAM;
    TRUNCATE TABLE IDs_To_Kill;

    INSERT INTO IDs_To_Kill (ProcessID)
    SELECT id FROM information_schema.processlist
    WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

    SET @ndxnext = 0;
    SELECT MAX(ndx) INTO @ndxlast FROM IDs_To_Kill;
    WHILE @ndxnext < @ndxlast DO
        SET @ndxnext = @ndxnext + 1;
        SELECT id INTO @ProcessID from IDs_To_Kill WHERE ndx = @ndxnext;
        SET @SQL = CONCAT('KILL QUERY ',@ProcessID);
        PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$ 
DELIMITER ; 

If you want to go beyond terminating the query and just terminate the DB Connection, replace

SET @SQL = CONCAT('KILL QUERY ',@ProcessID);

with this

SET @SQL = CONCAT('KILL ',@ProcessID);

Launch Stored Procedure Using MySQL Event

USE myutil
DELIMITER $$
CREATE EVENT kill_query_event
    ON SCHEDULE EVERY 1 MINUTE
    STARTS (NOW() + INTERVAL 1 MINUTE + INTERVAL SECOND(NOW()) SECOND) DO
BEGIN
    CALL Kill_Long_Queries();
END $$ 
DELIMITER ; 

Launch Stored Procedure Using cron

In the OS, set up the crontab to go off every minute as follows

0 * * * * mysql -u... -p... -Dmyutil -ANe"CALL Kill_Long_Queries()"

CAVEAT for MySQL 5.0 Users

Since there are no events in the MySQL 5.0 and prior, you will have to do something like this:

for PROC_TO_KILL in `mysql -h... -u... -p... -ANe"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -ANe"KILL QUERY ${PROC_TO_KILL}" ; done

SPECIAL CAVEAT

Without a MySQL Event, you would have to connect to mysql each time and poll the processlist. In the event that there are too many connections or if the mysqld starts blocking connection due to connect errors, your only hope is to write a perl script whose sole job is to kill long running query and using only one live session at the same time.

I wrote a perl script two years to run flush hosts every 10 minutes (How do you tell which host is close to being blocked in MySQL?)

Here is that script

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;
}

$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
        {
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                {
                        my $uptime = $row->{Value};
                }
                $st_globalstatus->finish();
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        }
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
        {
                if ( -f $MYSQL_FLATLINE_FILE )
                {
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                }
                sleep 1;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

I am not a perl expert, but I suggest that you alter this script to collect and kill the process IDs. Save it to a file called killquery.pl. All you need to do is launch this once from the command line as a background process.

nohup perl killquery.pl 5 30 &

This makes the perl script heartbeat every 5 sec, and run the Stored Procedure every 30 second

Give it a Try !!!

share|improve this answer
    
I solved a problem with percona toolkit, but 'WOW'. Thanks for contribution. –  Jevgeni Smirnov Dec 26 '13 at 15:46
    
what about ALTER, UPDATE OR DELETE these queries will be killed to.. so you may want to check the thread states off the SHOW FULL PROCESSLIST/INFORMATION_SCHEMA.PROCESSLIST... an long running query with state sending data is most likely an query without indexes (well you cant be 100% sure). Better would be to use INFORMATION_SCHEMA.threads this VIEW/Table doenst need an thread mutex so the overall server performance is better when you need to fetch an PROCESSLIST with very short intervals like every second –  Raymond Nijland Dec 26 '13 at 16:02
    
@RaymondNijland Thank you, I will add to the criteria to screen for SELECTs only. –  RolandoMySQLDBA Dec 26 '13 at 16:07
    
@RolandoMySQLDBA no problem i see you added into the SP -> and LEFT(info,6)='SELECT'; also an very good filter this one makes sense but you may want to use an TRIM(info) and more REPLACE filters to clean up the query because the query can contain \n\r before the select part when sending it from an application.. iam not 100% sure if the MySQL filters newlines out i need to check this in the source code.. –  Raymond Nijland Dec 28 '13 at 15:23
add comment

Thinking a bit outside the box, you may be able to log query times and use FAIL2BAN to parse the log file and perform an action (like block the IP address, although that will only help prevent future queries)

share|improve this answer
    
this is not the best option. I am interested is there any kind of 'timeout' I can set? –  Jevgeni Smirnov Feb 27 '13 at 8:05
    
@davidgo this could be an triple lol moment.. if FAIL2BAN bans the correct client IP want needs to do the normal queries... feel the irony in this one because its like playing Russian Roulette .... –  Raymond Nijland Dec 26 '13 at 17:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.