MySQL – can I limit the maximum time allowed for a query to run?

by

Update

As of MySQL 5.7, you can include a MAX_EXECUTION_TIME optimizer hint in your SELECT queries to instruct the server to terminate it after the specified time.

As far as I know, if you want to enforce a server-wide timeout, or if you care about queries besides SELECTs, the original answer is still your only option.

Original answer

There is no way to specify a maximum run time when sending a query to the server to run.

However, it is not uncommon to have a cron job that runs every second on your database server, connecting and doing something like this:

  1. SHOW PROCESSLIST
  2. Find all connections with a query time larger than your maximum desired time
  3. Run KILL [process id] for each of those processes

More Related Contents:

  1. How to use GROUP_CONCAT in a CONCAT in MySQL
  2. MySQL query / clause execution order
  3. How can I tell when a MySQL table was last updated?
  4. Operand Should Contain 1 Column – MySQL NOT IN
  5. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  6. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  7. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  8. How to use LOAD_FILE to load a file into a MySQL blob?
  9. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  10. On duplicate key ignore? [duplicate]
  11. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  12. MyIsam engine transaction support
  13. SQL Query to get column values that correspond with MAX value of another column?
  14. MySQL : transaction within a stored procedure
  15. When to use SELECT … FOR UPDATE?
  16. BULK INSERT in MYSQL
  17. Setting column values as column names in the SQL query result
  18. MYSQL sum() for distinct rows
  19. Implementing a hierarchical data structure in a database
  20. MySQL date format
  21. How to get current date & time in MySQL?
  22. Laravel join with 3 Tables
  23. phpMyAdmin – can’t connect – invalid setings – ever since I added a root password – locked out
  24. How do I split the output from mysqldump into smaller files?
  25. How to regex in a MySQL query
  26. How to remove duplicate entries from a mysql db?
  27. Syntax error on MERGE statement
  28. Strip last two characters of a column in MySQL
  29. mysql double-quoted table names
  30. Speeding up mysql dumps and imports [closed]

Leave a Comment