10

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?

wibeasley
  • 5,000
  • 3
  • 34
  • 62
d8aninja
  • 3,233
  • 4
  • 36
  • 60
  • 1
    There's a solution here: https://stackoverflow.com/questions/7891073/time-out-an-r-command-via-something-like-try . 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. – thc Jul 06 '17 at 23:16
  • 1
    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. – Rick James Jul 07 '17 at 00:18
  • d8aninja did the information provided help address your problem? I am just checking I understood your question correctly. – Technophobe01 Jul 10 '17 at 18:12
  • 1
    yes you did! @Technophobe01 – d8aninja Jul 19 '17 at 21:03
  • Oh, great. Always happy to help - just checking. Take care. – Technophobe01 Jul 19 '17 at 21:04

1 Answers1

4

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.

Community
  • 1
  • 1
Technophobe01
  • 8,212
  • 3
  • 32
  • 59