47

Is it possible to timeout a query in MySQL?

That is, if any query exceeds the time I specify, it will be killed by MySQL and it will return an error instead of waiting for eternity.

Michael Currie
  • 13,721
  • 9
  • 42
  • 58
acheruns
  • 681
  • 1
  • 7
  • 14
  • To help us answer the question - Are you trying to do this in, or outside, your code? And if you would like an answer for a specific language, which one? – David Jan 26 '10 at 01:58
  • I don't know any ANSI SQL for that, but I know how to do it in a few consuming applications -- how are they connecting? There is the `join-limit` syntax associated per connection and quite a bit you can do in the application. – Travis Gockel Jan 26 '10 at 02:01
  • i'm sorry, i'm stupid, i forgot to specify things. i'm not trying to do this in my code, i wanna know, if it's possible to do this query timeout in mysql self, something like: query runs for 120 seconds, limit is 120 so mysql will kill this query and return some error or something. i've issue with 3rd party code which is nasty and i don't have permission to edit it. i need to kill that query in mysql, not in code. – acheruns Jan 26 '10 at 10:37
  • this works: http://serverfault.com/a/402076/280625 – sactiw May 12 '16 at 17:19

9 Answers9

10

There is a nice Perl script on CPAN to do just this: http://search.cpan.org/~rsoliv/mysql-genocide-0.03/mysql-genocide

One only needs to schedule it to run with the proper parameters. Create a CRONtab file /etc/cron.d/mysql_query_timeout to schedule it to run every minute:

* * * * * root /path/to/mysql-genocide -t 7200 -s -K

Where 7200 is the maxiumum allowed execution time in seconds. The -s switch filters out all except SELECT queries. The -K switch instructs the script to kill the matching processes.

The root user should be able to run local mysql tools without authentication otherwise you will need to provide credentials on the command line.

Erik
  • 101
  • 1
  • 3
  • These days, a more up-to-date/maintained version of that code lives at https://github.com/dailymotion/mysql-genocide – dland Oct 01 '15 at 14:20
9

I thought it has been around a little longer, but according to this,

MySQL 5.7.4 introduces the ability to set server side execution time limits, specified in milliseconds, for top level read-only SELECT statements.

SELECT 
MAX_STATEMENT_TIME = 1000 --in milliseconds
* 
FROM table;

Note that this only works for read-only SELECT statements.

Westy92
  • 19,087
  • 4
  • 72
  • 54
9

I just set up the following bash script as a cron job to accomplish this with MySQL 5.0 (kills any query that has been executing for more than 30 seconds). Sharing it here in case it proves useful to anyone (apologies if my bash scripting style is inefficient or atrocious, it is not my primary development language):

#!/bin/bash
linecount=0
processes=$(echo "show processlist" | mysql -uroot -ppassword)
oldIfs=$IFS
IFS='
'
echo "Checking for slow MySQL queries..."
for line in $processes
do
    if [ "$linecount" -gt 0 ]
        then
            pid=$(echo "$line" | cut -f1)
            length=$(echo "$line" | cut -f6)
            query=$(echo "$line" | cut -f8)
            #Id User    Host    db  Command Time    State   Info
            if [ "$length" -gt 30 ]
                then
                    #echo "$pid = $length"
                    echo "WARNING:  Killing query with pid=$pid with total execution time of $length seconds! (query=$query)"
                    killoutput=$(echo "kill query $pid" | mysql -uroot -ppassword)
                    echo "Result of killing $pid:  $killoutput"
            fi
    fi
    linecount=`expr $linecount + 1`
done
IFS=$oldIfs
aroth
  • 54,026
  • 20
  • 135
  • 176
8

Starting with MySQL 5.1 you can create a stored procedure to query the information_schmea.PROCESSLIST table for all queries that match your criteria for "long running" then iterate over a cursor to kill them. Then setup that procedure to execute on a recurring basis in the event scheduler.

See: http://forge.mysql.com/tools/tool.php?id=106

Matthew Montgomery
  • 81
  • 1
  • 2
7

The MySQL forum has some threads about this.

This post details how to set up timeouts on the server using innodb_lock_wait_timeout.

Here's a way to do it programmatically, assuming you're using JDBC.

David
  • 1,187
  • 1
  • 10
  • 22
5

I think this old question needs an updated answer.

You can set a GLOBAL timeout for all your read-only SELECT queries like this:

SET GLOBAL MAX_EXECUTION_TIME=1000;

The time specified is in milliseconds.

If you want the timeout only for a specific query, you can set it inline like this:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ my_column FROM my_table WHERE ...

MySQL returns an error instead of waiting for eternity.

Note that this method only works for read-only SELECTs. If a SELECT statement is determined not to be read-only, then any timer set for it is cancelled and the following NOTE message is reported to the user:

Note 1908 Select is not a read only statement, disabling timer

For statements with subqueries, it limits the top SELECT only. It does not apply to SELECT statements within stored programs. Using the MAX_EXECUTION_TIME hint in SELECT statements within a stored program will be ignored.

Ali Hashemi
  • 3,158
  • 3
  • 34
  • 48
3

I don't think the egrep above would find "2000".
Why not try just selecting the id as well, and avoiding all of that posh shell stuff:

mysql -e 'select id from information_schema.processlist where info is not null and time > 30;'
bool.dev
  • 17,508
  • 5
  • 69
  • 93
fred
  • 31
  • 1
3

Since MySQL 5.7.8 there is max_execution_time option that defines the execution timeout for SELECT statements.

Alexander Ushakov
  • 5,139
  • 3
  • 27
  • 50
2

Here is my script :

mysql -e 'show processlist\G' |\
egrep -b5 'Time: [6-9]{3,10}' |\
grep 'Id:' |\
cut -d':' -f2 |\
grep -v '155' |\ ## Binary Log PID
sed 's/^ //' |\
while read id
do
    mysql -e "kill $id;"
done
RoToRa
  • 37,635
  • 12
  • 69
  • 105
Srinivas Mutyala
  • 21
  • 1