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

Question

I understand 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 (eg, very large result sets) this can be a critical step to avoid exhausting resources (memory, file descriptors, etc.)

But my team just ran into a locked table that we sent to MySQL to kill pid , and I wonder if there is a way to timeout a query submitted using a DBI package?

I search and cannot find an equivalent

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

I tried this and profiled a function with a set of parameters and without it, and it does not appear, it does nothing; why if dbClearResult always in the game?

Answer 1

If I read your question correctly, I believe that you need to rely on your MySQL server to implement the requested request timeout. What for? dbQuery sends a client request to the server on which you want the server to execute the request and timeout.

Proposed Solution:

Include a query execution prompt in the query that you send to your MySQL database.

nb. The returned query data may be too large for you, but this is another problem.

MySql example:

The hint MAX_EXECUTION_TIME is only allowed for SELECT statements . It sets a limit of N (timeout value in milliseconds) on how long the SQL query is allowed to execute before the server completes 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 hint MAX_EXECUTION_TIME (N) sets the timeout for executing the instruction for N milliseconds. If this option is absent or N is 0, the execution timeout set by the max_execution_time system variable is applied.

The hint MAX_EXECUTION_TIME 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 should appear after the first SELECT strong>.

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 you in the right direction.