How to limit sql runtime

Question

Some sql are spelled incorrectly. Sometimes a search takes time in applications. When an application (possibly a website) sends a request that takes a long time, I have to restart mysql. How can I limit the execution time of sql query in the database side?

Answer 1

To automatically kill a query in MySQL after a long run time:

• Create a stored procedure as:

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

  then inside the curosr do loop:

   FETCH ID INTO @var_kill_id; KILL QUERY @var_kill_id; 

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

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

Answer 2

Also, if possible, you can try mysql's Twitter-tubuq which supports "max_statement_time" and kills a query that goes beyond it, in milisecond granularity.

See http://engineering.twitter.com/2012/04/mysql-at-twitter.html and https://github.com/twitter/mysql/wiki/Statement-Timeout

The original source .

Answer 3

The only choice is to open another session, use SHOW PROCESSLIST , and then KILL QUERY , which you want to end.

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

Answer 4

It may not be the exact solution to limit SQL runtime, but I think it will help. You do not need to restart the MySQL server at all.

Try using some GUI tool for MySQL, such as SQLyog . There you can manually stop the executed request (check the X button in the screenshot) if you think it takes longer.

And besides, you can also kill the request process by accessing the list of processes. Check out the other two images in the screenshot with the steps to kill the processes ...

Hope this helps ....