Is there a way to timeout a MySql query when using DBI and dbGetQuery?

Question

I realize that

dbGetQuery comes with a default implementation that calls dbSendQuery, then dbFetch, ensuring that the result is always freed by dbClearResult.

and

dbClearResult frees all resources (local and remote) associated with a result set. In some cases (e.g., very large result sets) this can be a critical step to avoid exhausting resources (memory, file descriptors, etc.)

But my team just experienced a locked table that we went into MySQL to kill pid and I'm wondering - is there a way to timeout a query submitted using the DBI package?

I'm looking for and can't find the equivalent of

dbGetQuery(conn = connection, 'select stuff from that_table', timeout = 90)

I tried this, and profiled the function with and without the parameter set and it doesn't appear it does anything; why would it, if dbClearResult is always in play?

Cowan

There's a solution here: #7891573 . General idea is to fork the command and use a Sys.time() in the main thread followed by tools::pskill() if it takes too long. – Durban

Which Engine are you using (MyISAM or InnoDB)? Does the connector wait for the entire resultset? Or does it provide the data incrementally? This choice, if possible, has a direct impact on your question. – Casserole

d8aninja did the information provided help address your problem? I am just checking I understood your question correctly. – Toothed

yes you did! @Toothed – Cowan

Oh, great. Always happy to help - just checking. Take care. – Toothed

Answer 1

If I am reading your question correctly, my sense is you need to rely on your MySQL server to implement your required query timeout. Why? dbQuery is sending a client request to the server you want the server to run the query and timeout.

Proposed Solution:

Include a statement execution hint in the query you submit to your MySQL database.

nb. The query data returned may be too large for you to consume but that is a different problem.

MySql Example:

The MAX_EXECUTION_TIME hint is permitted only for SELECT statements. It places a limit N (a timeout value in milliseconds) on how long an SQL statement is permitted to execute before the server terminates it.

---

MAX_EXECUTION_TIME(N)

Example with a timeout of 1 second (1000 milliseconds):

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies.

The MAX_EXECUTION_TIME hint is applicable as follows:

For statements with multiple SELECT keywords, such as unions or statements with subqueries, MAX_EXECUTION_TIME applies to the entire statement and must appear after the first SELECT.

It applies to read-only SELECT statements. Statements that are not read-only are those that invoke a stored function that modifies data as a side effect.

It does not apply to SELECT statements in stored programs and is ignored.

---

I hope the above approach helps move you in the right direction.

Toothed