How can I stop a MySQL query if it takes too long?

by

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.

More Related Contents:

  1. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  2. Terminating idle mysql connections
  3. How to Prevent Connection Timeouts for Large MySQL Imports
  4. 1052: Column ‘id’ in field list is ambiguous
  5. Codeigniter – multiple database connections
  6. How to add levenshtein function in mysql?
  7. How to search JSON data in MySQL?
  8. MySQL > Table doesn’t exist. But it does (or it should)
  9. #1071 – Specified key was too long; max key length is 1000 bytes
  10. Is the primary key automatically indexed in MySQL?
  11. Is there ANY_VALUE capability for mysql 5.6?
  12. Cumulative sum over a set of rows in mysql
  13. MySql Error 150 – Foreign keys
  14. Set NOW() as Default Value for datetime datatype?
  15. MySQL server has gone away – in exactly 60 seconds
  16. No connection could be made because the target machine actively refused it (PHP / WAMP)
  17. Add Auto-Increment ID to existing table?
  18. MySQL SELECT DISTINCT multiple columns
  19. SQL DELETE with JOIN another table for WHERE condition
  20. How to connect to a MySQL Data Source in Visual Studio
  21. DOs and DONTs for Indexes [closed]
  22. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  23. How to insert images from database?
  24. MySQL – ORDER BY values within IN()
  25. MySQL convert timediff output to day, hour, minute, second format
  26. How do I rename a MySQL database (change schema name)?
  27. MySQL my.cnf file – Found option without preceding group
  28. Returning records from the last 3 months only in MySQL
  29. MySQL errorno 121
  30. Are there any functions in MySQL like dense_rank() and row_number() like Oracle?

Leave a Comment