How to limit the sql execution time

mysql sql

To auto kill a query in MySQL after a long execution time:

Create a stored procedure as:

DECLARE CURSOR cur1 FOR SELECT ID                     FROM INFORMATION_SCHEMA.PROCESSLIST                     WHERE COMMAND = 'Query' AND TIME > 120;

then inside curosr's loop do:

FETCH ID INTO @var_kill_id;KILL QUERY @var_kill_id;

Create EVENT FOR EVERY 5 SECONDS and just CALL the above procedure inside it.

Note: KILL QUERY just kills the query and MySQL connection is not broken. see here.

mysql sql

Also if possible you can try Twitter's mysql fork that suport "max_statement_time" and kills a query exceding it, at a milisecond granularity.

Original source.

mysql sql

The only choice is that to open another session, use SHOW PROCESSLIST and then KILL QUERY the one you want to terminate.

You can use the mysqladmin command-line tool to issue these commands.

CodeHunter