Add support for query timeout for datasource and/or globally for an application

Description

Setting a query timeout is considered good practice to prevent DoS attacks, and in general can protect your app from hanging threads when a query does not return. I believe this has been supported in ACF since CF9, and is specified in the datasource settings.

Currently in Lucee, there is no way to specify a query timeout value on a per-datasource level, and is only partially possible on a per-application level. The only thing that comes close would be to add: this.tag.query.timeout = 60; to application.cfc, but this setting is ignored when calling queryExecute().

Ideally, you should be able to do both. One use-case that's interesting for setting it on a per-datasource level is when you setup two separate datasources that point to the same database, each with a different query timeout setting... the DSN with a longer timeout is used for admin/reporting features that have heavier queries, and the other is used for end users.

Here's an article that was written a long time ago, but is still relevant. http://www.carehart.org/blog/client/index.cfm/2010/7/14/hidden_gem_in_cf9_admin_querytimeout

In it, Charlie points out:

Why us this an important development? It's been a long-time issue that if a CFQUERY got hung up waiting for a response, that request thread (doing the CFQUERY) is then hung until the query finishes, which can sometimes be many minutes, or even hours or days, due to some odd situations. More important, a thread waiting for a query with no timeout can't be terminated (by the JVM, or CF, or the monitoring tools) because the thread was in a native thread state.

This is exactly the issue I'm currently experiencing with an mssql datasource. I've get hanging threads that are waiting for a query to finish executing. The requests never timeout even though my application timeout is set to 50 seconds. I'm seeing that some queries get "suspended" and are waiting for a resource to become available, which in some cases never happens. See my bug report here: https://luceeserver.atlassian.net/browse/LDEV-1622

Activity

Show:
Brad Wood
December 21, 2017, 6:21 PM

Thanks for adding this ticket

Brad Wood
August 29, 2019, 5:03 PM

Just ran into this again today. A shame it's still not possible.

JP
August 29, 2019, 5:10 PM

At the very least, queryExecute() needs to respect this.tag.query.timeout global setting in application.cfc

Brad Wood
August 29, 2019, 5:14 PM

That would be a very simple approach and probably easy to implement since it has a struct of options that basically mirror the cfquery attributes.

Assignee

Unassigned

Reporter

JP

Labels

Affects versions

Priority

New
Configure