New for CF9 (and 9.0.1): a query timeout that really works, with a caveat
Did you know there is now a setting in the DSN page of the CF Admin (for most DB drivers) that allows you to set a maximum timeout for queries against that DSN? It's a new feature enabled for the DataDirect drivers udpated in CF 9. The caveat? It is ONLY settable there, not in CFQUERY itself, which is a shame (the existing TIMEOUT attribute is not the same and generally does not work). Still, the value of this even at the DSN level is too important to ignore for some challenges. More on that (and some other thoughts) in a moment.
As for the setting, it's in the "Advanced Settings" section for a DSN in the CF 9 Admin, and it's called "Query Timeout". This should not to be confused with the older settings, "Timeout" (which is about inactive connections) or "Login Timeout" (which is about logging in to the connection). The screenshot at right shows all 3. (This blog entry continues, with more information below it.)
I've run a test, and it really does do the job, which is huge. Why? Because 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.
With this new option specified, if the request exceeds the timeout, the request does now fail, with a JDBC error, "Execution timeout expired." The same test does NOT timeout with the older cfquery TIMEOUT attribute.
Here are some other notes on the new feature:
- It works with most of the database drivers. I have confirmed that the setting appears in the DSN settings page for SQL Server, Oracle, MySQL (datadirect), DB2, Informix, Sybase) in CF 9 Enterprise, and in both SQL Server and MySQL (Datadirect) on CF 9 Standard.
- The timeout's specified in seconds.
- You can learn more in the CF Admin guide, in this specific page.
- Oddly, the Admin manual page above only references this new setting in that CF Admin manual is in the the MySQL settings, but again it does appear in all the drivers above.
- The manual page does even reference the other DBMSs by name in its naming the methods of the Admin API (for other DBMSs) which you can use as well, which can be used to set this default setting in the DSN programmatically.
- That said, again there is sadly no new QueryTimeout (or QTimeout) attribute for CFQUERY, so for now we can only set this at the datasource level, not per query.
- I've raised the concerns above on the CF Admin livedocs page (or whatever we are to call it now.)
- If you look under the covers (in [cf]\lib\neo-datasource.xml), there is in fact a querytimeout connectionstring that this setting controls. If only there was a way we could pass connectionstring values to the CFQUERY, we'd be golden. Some may recall use used to have just such an attribute (ConnectString), but sadly it was deprecated in CF 6. I did try it, to no avail.
- I
will raisehave raised a bug with Adobe to get a new attribute for CFQUERY related to this.When I do that, I'll report it here.It's bug 83592. Please add your vote of support for it. - There's another page on this Admin setting, in the CF Dev Guide, for those who may be interested in following any other possible places where this feature may be discussed (in the comments there).
- If you want some code to use to test a request waiting a long time for the DB to return, most databases offer a statement that tells the DB to wait for some time. In SQL Server, that's WAITFOR DELAY 'hrs:mins:secs'. Just use that in a CFQUERY, assuming your DSN definition doesn't limit what SQL statements you can use, in the 'Allowed SQL' section of the Adcanced Settings page.
Why is this setting important?
I think this is a very important setting, and though it has been a hidden gem, it seems, it's one that people should consider. That's why I've flagged this in my CF911 category.If you're suffering situations where requests are hanging due to long-running queries, and you have not been able to solve the real root cause for why they are hanging (which I always recommend first and foremost), then at least this option can help avoid a situation where queries can run without time limit. An Admin can decide that no queries should be allowed to run more than x seconds.
With that power comes responsibility, of course, and caution. You wouldn't want to preclude someone being able to run a query that really needed to take a long time. That's why it's really better if this was settable on a per-query basis. (And no, the CF page timeout settings are NOT the solution here, because again as I said above, they cannot timeout some kinds of long-running tags, like CFQUERY, CFHTTP, CFINVOKE of a web service, etc.)
What one could do, though (for now), is create different DSNs, where one could be used for most query processing, and another could be used for long-running requests. Yes, it's ok to have 2 DSNs point to the same DB. This same technique has been used when wanting to have most queries run against one DSN with a limited set of "allowed SQL" (per the DSN advanced settings) while another DSN has unfettered SQL access.
Hope this is helpful to some. Let me know what you think, whether this was helpful or if you feel I left something out. Especially please let me know if you may know of a way that we can indeed pass querystring values on a per-query basis.
from the cfdocs
Maximum number of seconds that each action of a query is permitted to execute before returning an error. The cumulative time may exceed this value.
For JDBC statements, ColdFusion sets this attribute. For other drivers, see the driver documentation.
Both this attribute and the the new admin setting will result in the query timing out and generating a jdbc error.
I did fail to clarify in the entry that it does not work, but the whole point in identifying that this was new functionality was to clarify that it added something that was in fact new.
So no, to be clear, the same tests I did with the old TIMEOUT did NOT work (did not timeout), but when I added that new querytimeout DSN setting, they DID timeout. I have added clarifications of this in the entry above for future readers.
So is the last line of your comment quoting the docs, or your own experience? I thought most people had already concluded long ago (like me, and my recent testing) that the CFQUERY TIMEOUT did not do the job.
That's why this seems to be big news. Care you clarify? I'd hate people to think that your comment has dismissed the value of my entry above. Indeed, this is why I'm asking people for feedback.
I'll also add a comment here (and will update the entry above for future readers) that this new "query timeout" is indeed a new DataDirect driver feature, for the new release of the drivers that were implemented in CF9. So this really is a new feature, not just some curious Admin interface change.
@Ryan, thanks for the validation.
And for the suggested workaround (using two DSNs, one with the setting and one without), I'll just clarify that it was my final suggestion (next to last paragraph above).
My understanding has been that the query level timeout would work only when using the SQL Server driver, and otherwise has no effect. I decided to test it quickly and found that indeed that a tag level cfquery timeout did work. Here's my sample code and the exception that it threw, using CF 9.0 on Windows 7 and SQL Server Express 2008.
[cfquery name="TOTest" datasource="MySQLServer" timeout="10"]
WAITFOR DELAY '000:00:30'
select getdate() as now
[/cfquery]
[cfdump var="#TOTest#"]
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Execution timeout expired.
Then I added the Query Timeout for the datasource in the CFAdmin, set to 20 seconds. I found that the tag level timeout has priority since the query again timed out in 10 seconds. Removing the tag level timeout and leaving the CF Admin Query Timeout, the query then timed out at 20 seconds.
Presently I have MySQL also running, so I'll try that out too. I expect that the tag level won't work there, and hope that the new Query Timeout that you've described here does.
Thanks for this little gem - it's been a great quick fix!
Just to add my own findings, the timeout only seems to apply to individual queries - not a cftransaction of queries.
I had a big batch of queries all wrapped in a transaction. Unfortunately, as this batch took several minutes to run, page requests were building up as they waited to access the db.
With this setting, I can now timeout those page requests after 30 seconds and give the user a nice friendly error message telling them to come back later. The big batch cftransaction keeps running without problems.
We use PostgreSQL and there is not a query timeout option in CFAdmin for that driver.
And despite not setting a timeout attribute in our cfquery tags we still get query timeout error messages for long running SQL tasks.
On our test server the request timeout is set to 15 minutes and the query timeouts well within this value - so we're a little lost with working out where the timeout is set. We didn;t seem to ever have this issue with CF7 (we skipped 8) - and we have always had long running SQL for reports.
I'd certainly apprciate - anything you might think of.
Gavin.
Now, I realize you're saying it's all timing out before what you expect, and of course besides the CF Admin timeout setting, there is also the possibility that some other template in the request (an included file or called CFC, or in the application cfc/cfm) may have used CFSETTING REQUESTTIMEOUT.
Back to the issue of CF reporting the timeout on the wrong line, see a blog entry I did in the past on this:
http://www.carehart....
Let is know what you find out.
PS If you remain really stuck, note as well that I'm available for consultation to help solve throny CF problems (with no minimum time commitment, and a satisfaction guarantee). More at http://www.carehart....
after reading your post and all the comments. I tried cfquery with timeout and it worked like it should be. See below:
Error Executing Database Query.
Statement cancelled due to timeout or client request
The error occurred in
1 : <cfquery datasource="xx" timeout="10">
2 : SELECT SLEEP(20);
3 : </cfquery>
Coldfusion 8.0.1 Enterprise and MySQL 5.0.51a
I don't know why, but it doesn't seems to be the case in my environment. Just so you know, maybe it's not a general problem.
take care,
Sami
I'd go further and say that the CFQUERY TIMEOUT attribute might work also, even when a query DOES take a long time to execute, but it is feeding query response data back to CF. Perhaps CF could interrupt that, even if the query would otherwise have taken a long time to finish completely, if CF got sent data from the DB after the timeout was reached.
All that said, I do appreciate that I made the assertion myself, in the blog entry, about using the SQL Server equivalent (waitfor delay) to test things, so I understand why you may have used that for your own testing.
I just wonder if perhaps besides these distinctions about whether the query is doing real work, or is returning data, it may be also that things could work differently depending on one's version of CF, the OS it's running on, the db driver, the version of that driver, the db server, the version of that DB, the OS it's running on, and so on.
In that respect, I appreciate that some have offered here the specifics of the platforms they are testing things on. Perhaps over time we may find some pattern. :-) But until then, I'll just hope that if someone finds that the CFQUERY TIMEOUT attribute does NOT work for them, that perhaps the timeout in the DSN *may* work for them. That was my real goal here, to point that out. :-)
Cheers.
Windows 7 + CF9.0.1 + SQL Server 2008
Windows Server 2008 + CF8.0.1 + SQL Server 2005
On the subject of adding a new queryTimeout attribute for the cfquery tag: Why add a new attribute when we already have a "timeout" attribute? If it's possible to make it work, then it should work. The only reason to add a new parameter would be if it had a different meaning than the old one, and as far as I can tell it wouldn't in this case. In other words, the focus should be entirely on making the existing timeout attribute work in as many environments as possible.