[Looking for Charlie's main web site?]

New for CF9 (and 9.0.1): a query timeout that may really work, with a caveat

This is a very interesting change in CF9 (and 9.0.1), which has slipped under the radar for the most part it seems.

Did you know there is now a setting in the DSN page of the CF Admin (for most of the Adobe-provided DB drivers) which allows you to set a maximum timeout for queries against that DSN?

It's a new feature enabled for the DataDirect drivers, as updated in CF 9. (You will not see it if you use an "other" datasource type, such as when using a downloaded JDBC driver that you implement on CF.)

The caveat? This timeout is ONLY settable there in the DSN definition, not in CFQUERY (or CFSTOREDPROC) itself, which is a shame. The existing TIMEOUT attribute for those (CF10 added it for CFSTOREDPROC) 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 Admin for CF 9 (or greater), 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 (more below), and it really does do the job (at least in that testing), which is huge.

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.

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.

(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 Advanced Settings page.)

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.

Beware impacting all queries

With this 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.

More to consider about the new setting

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, for example. It will not be there if you choose "other" for the datasource type, to point to some other JDBC driver.
  • 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.
  • 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 raise have 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).

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.

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.

Comments
CFQUERY has actually had a timeout attribute for a very long time, and as far as I can see it does the same thing as this settings.

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.
# Posted By Russ Michaels | 7/14/10 4:59 PM
Hey Russ, no, I wouldn't have gone on like that if I the old TIMEOUT attribute worked. :-)

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.
# Posted By Charlie Arehart | 7/14/10 5:28 PM
I have now filed the feature request for a new querytimeout (83592). Please consider voting it up. http://cfbugs.adobe....=83592. And note in the comments that I added that, of course, if they could just fix the current TIMEOUT to use this setting, that would be fine, but I said I realize there may be a reason it needs to do whatever it does, so that we may need a new attribute.

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.
# Posted By Charlie Arehart | 7/14/10 5:38 PM
OK well I can't say I have actually teste dthe timeout attibute for a long time, have not had any reason to as I was not aware it had broken. It certainly used to work.
# Posted By Russ Michaels | 7/15/10 6:58 AM
This would be *very* useful to me if it were a per-query setting. I'm having an issue on a production site right now that I could fix, or at least troubleshoot much better, if I could have one of the queries use a short timeout. I voted for it.
# Posted By Ryan Stille | 7/15/10 9:31 AM
I guess one work around if you needed a timeout for a just a couple queries would be to duplicate your datasource, use a timeout on it, and use that datasource in the specific queries you needed it on. I might try this on my issue.
# Posted By Ryan Stille | 7/15/10 9:32 AM
@Russ, thanks for the clarification.

@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 I did suggest that (see the end of the next to last section above).
# Posted By charlie arehart | 7/15/10 10:39 AM
This is a bit off topic, but the last time I checked, the "allowed SQL" setting only checks the first word of the query, which renders it pretty useless for protecting against SQL injection or even for preventing updates simple updates by malicious coders, since most databases support multiple statements per query. For example, in SQL Server, you could say " select 1 update pendingchecks set payee = 'Daryl' " and CF would let that pass. A much more effective tactic is to use multiple DSNs that authenticate with different database accounts that have different permissions.
# Posted By Daryl Banttari | 7/15/10 11:36 AM
Hi Charlie, thanks for bringing this to everyone's attention. I certainly overlooked this myself.

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.
# Posted By Steven Erat | 7/15/10 11:50 AM
Good find Charlie! +1, voted. Subscribing.. hopefully an engineer will elaborate on this: '"allowed SQL" setting only checks the first word of the query'
# Posted By Aaron Neff | 7/16/10 4:10 AM
Hi Charlie,

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.
# Posted By Gareth Cole | 11/30/10 2:33 PM
HI Charlie - do you know if there is a default value assigned?
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.
# Posted By Gavin Baumanis | 2/1/11 10:17 PM
I'm not aware of there being any default, no. Indeed, I believe the default is "no timeout". As for your observation, well, are you really sure it's timing out on the query? CF has a sad tendency to report an exceeded timeout on a tag AFTER a previous one has exceeded the timeout.

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....
# Posted By Charlie Arehart | 2/2/11 1:26 PM
Hi Charlie,

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
# Posted By Sami Barasi | 1/7/12 7:58 AM
@Sami, I wonder if it may act any differently if the activity were something other than just a sleep. Maybe that's interruptible (by CF passing to the DB server a signal to interrupt the DB request), in ways that a "real" query waiting for a "real" response from the DB would not be (such as if it was taking a long time doing a join, or the table was locked, or a backup was processing, etc.)

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.
# Posted By Charlie Arehart | 1/7/12 11:53 AM
I just had a problem with a hanging query and found this post. I found that the timeout attribute of the query tag did work for me, at least using a "WAITFOR" test. I tried two environments:
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.
# Posted By David Hammond | 1/14/12 2:54 PM
David, I said specifically (in the comment discussing a possible new querytimeout attribute) that "if they could just fix the current TIMEOUT to use this setting, that would be fine, but I said I realize there may be a reason it needs to do whatever it does, so that we may need a new attribute".
# Posted By Charlie Arehart | 1/14/12 9:31 PM
This is probably an old problem that everyone has fixed but this totally relevant for me right now! Try running a query that cross joins a table with a million records on itself and you'll find the TIMEOUT attribute of cfquery doesn't work. So for all those commenters using the SLEEP() or such function, I don't think that counts because it's not actually taxing the system. Any way, this totally helps us out. Thanks for the psot!
# Posted By Paul | 12/14/13 9:31 PM
@Paul, great to hear. Thanks for sharing your feedback and encouragement! :-)
# Posted By Charlie Arehart | 12/16/13 11:01 PM
Hi Charlie
I just stumbled across this blog post. Nice work documenting this. A coupla things:

The current ticket URL is https://bugbase.adob... (the old one dun't work any more).

That ticket is "closed" / "cannot reproduce". Which - as per my comment against it - strikes me as an odd reaction to a feature request! Do you understand what Adobe mean by this?

Cheers.

--
Adam
# Posted By Adam Cameron | 1/22/14 10:40 AM
@Adam, thanks for chiming in and looking into that. No, I really don't know. I was not contacted directly about this.

Maybe they didn't even read the blog entry (though I did point to it in the bug report), so didn't really understand what I was talking about. They may have some test where the old cfquery timeout attribute worked, and didn't go further to see/understand how the new DSN feature specifically added new functionality that addressed offering a better timeout.

Indeed, you can see from comments by others above (including long-time CFers) that there can be misunderstanding.

I fear that somehow some people just don't read carefully and bring presumptions into their quick conclusions about what I was trying to say here.

Not sure what we can do about Adobe's having closed the bug, though. I don't see any info to know who did it or when. I'll send an email to some contacts, but I don't know what may come of it.

I'll at least change the link to the bug report. Thanks. It's lamentable that in creating the new URLs they couldn't put in place a rewrite rule to pass old urls to the new one. :-(
# Posted By Charlie Arehart | 1/22/14 11:53 AM
Hi Charlie, Adam,

I checked the bug report and found it was closed January 25th of 2012. The bug description is two part. One part being the new "query timeout" setting at the datasource config level and that it would be helpful to have the equivalent "querytimeout" attribute on CFQuery. The second part of the description is the supporting argument that the reason "querytimeout" would be useful is because the CFQuery tag's "timeout" does not work.

The engineer assigned to review the issue started with the supporting argument that the CFQuery's "timeout" doesn't work and tried to reproduce that by testing it with Oracle 11g.

The problem with the supporting argument is that it was rather vague, and could have benefited by the qualification "timeout doesn't work for many of the drivers" rather that implying that it doesn't work at all. The engineer took a narrow interpretation of the supporting argument, tested one driver, found it worked, and then because the supporting argument *as written" was invalid. That appears to be why the bug was closed, never getting the the larger issue that this was an enhancement request for expanding the dsn level "query timeout" to the per query level usage with the suggested "querytimeout" attribute.

Please let me know if I didn't communicate that well.

I think the enchancement request is still valid and that it in fact the supporting argument for it is also valid once you consider that not all drivers support CFQuery's timeout attribute. (Surely one or more of your blog entries describes this in detail, but as additional reference here's my own: http://www.talkingtr... )

I would recommend either contacting Rakshith or Rupesh to request reopening the issue (pointing them to this discussion), or open a new enhancement request that is better worded than the original to avoid misinterpretations.

Hope that helps!
# Posted By Steven Erat | 1/22/14 12:58 PM
@Steven, thanks so much, and will do.
# Posted By Charlie Arehart | 1/26/14 11:31 PM
Any idea if there is an option for a way to use the timeout with wrapped datasources (ie for FusionReactor)? The Query Timeout is available with a MS SQL datasource at creation, but a wrapped datasource does not have the timeout field available. I imagine we need something for the connection string. You mentioned there was something "under the covers" but I didn't know if that translates to something we could interject in the connection string to accomplish the same thing with a wrapped DSN?
# Posted By JJ Blodgett | 2/6/14 3:24 PM
I just want to chime in because a lot of folks (including Adobe) seem to be saying "it works for me". In my case, the cfquery timeout attribute does not work. I just did a simple query (against a huge database) and gave the tag a timeout of 5. The query took 61 seconds and then returned the data. So the timeout did NOT work.

In our case, we are using SQL Server mirroring, and to get that to work we had to use the "Other" data source type and then we specify a custom JDBC URL. Our URL starts with jdbc:macromedia:sqlserver, so it appears we are indeed using the SQL Server JDBC driver, but we are passing custom parameters. I am not a DBA, nor am I a JDBC expert, so I am not enirely sure how this stuff works. But I just thought I'd pass along my experience of the timeout not working.
# Posted By Jake Munson | 6/11/14 6:21 PM
@Jake, so to confirm are you saying that the timeout I describe does not, though the timeout attribute on cfquery does not? Again, that was the point of my blog post, though some have tried to assert it seems not to be the case for them. Glad to hear if this did work for you.

@JJ, sorry I missed your note from Feb. Of course, by now you may know that FusionReactor 5 no longer requires "wrapping" of dsn's, so perhaps the concern is now moot?
# Posted By charlie arehart | 6/11/14 7:15 PM
@Charlie, No I suppose I was confusing the two issues. I did not try the DSN level timeout (that you set in CF Admin). I was referring the the tag level timeout that you set inside the cfquery tag. It seemed to me that some of your commenters were asserting that this tag level timeout (again, inside the cfquery tag) works for them, and always has worked. If they were not asserting this, then ignore my comment. If they WERE asserting that the cfquery tag's timeout attribute works, then I just wanted to add another voice to those who are saying that it does not always work.
# Posted By Jake Munson | 6/11/14 7:49 PM
@Jake, thanks. Folks were indeed discussing the timeout attribute. I just wasn't sure if you were you were saying you were using it AND the DSN timeout, or if your second paragraph was in reference to the DSN timeout. Thanks for the clarification.
# Posted By charlie arehart | 6/14/14 11:04 AM
Charlie, have you found that this setting (the timeout setting in the CF Admin) was removed in CF 10 (and thus in 11 as well)? We have ColdFusion 10 Enterprise, and we are evaluating CF 11 (again, Enterprise). In both cases I do not see the Query Timeout setting that you showed in the screenshot in your original post. I see the old Timeout, which affects inactive connections, but not this "new" query timeout setting. I looked at a SQL Server DSN, as well as some other DSN types. I can't see this setting anywhere.
# Posted By Jake Munson | 6/16/14 1:01 PM
@Jake, I do indeed see it in both CF 10 and 11. These are both developer editions. I suppose it could be Standard only, but I had not noticed that before, and you say you are using Enterprise anyway. I'll note that I'm on Windows machines, but I'd not expect that to be a factor. I am indeed using a SQL Server DSN (the default one), but again you say you are as well.

To be clear, the setting is labelled "Query Timeout" and appears just as I show in the screenshot above. But i hear you saying you don't see that. I don't understand why you wouldn't see it...

...unless you are perhaps using a CF Admin where your web server is pointing mistakenly at a version of the CFIDE code which is NOT from at least CF9 (when the feature was added). That's a more common problem than many realize, though I would of course (like you) expect that if you did it it would be showing you images (if not text also) mentioning the "wrong" release.

I've not heard anyone else here indicate any problem accessing the setting in CF 10 and 11, but then few seem to know about it and many may not have seen this entry to comment.

If you find something out, do let me know, but it sure seems you should have it.
# Posted By charlie arehart | 6/16/14 11:15 PM
@Charlie, I discovered why I don't see the Query Timeout setting. I was looking at SQL Server connections that use the "Other" data source type. This is because we use a SQL Server mirror, and we have to use a custom JDBC URL to connect to the mirror. It appears this query string attribute does not appear for the "Other" DSN type.

I decided to try to discover which DSN types have this Query Timeout setting. I found the setting in these DSN types:
DB2 Universal Database
Informix
Microsoft SQL Server
MySQL DataDirect (but not MySQL 5)
Oracle
Sybase

All of the other DSN types did not have this Query Timeout setting, at least on the CF 10 Enterprise server I was looking at. Oh, and the server is Windows.
# Posted By Jake Munson | 6/17/14 11:22 AM
@Yep, Jake. Sorry, I had heard that before but see now that no one else had mentioned it here. Since you said "I looked at a SQL Server DSN, as well as some other DSN types" I did assume you meant the built-in one, or I'd have thought to mention that. Glad it's here now to clarify for others. I have also modified the blog post to make that point more clear (that it is not available for use with "other" drivers.)
# Posted By charlie arehart | 6/18/14 12:04 AM
Just an update, related to prior comments here from Jake and JJ, asking about if one uses an "other" JDBC driver. I can report now that you would want to simply add to the JDBC URL a connection string with querytimeout=nn, for the number of seconds.

Folks could confirm this themselves: if you look at the CF Admin "settings summary" page you will see that even a DSN that is for a "normal" CF db type will get turned into a JDBC URL under the covers, as shown on that summary page. And if you set the timeout field (discussed in this blog post) for such "normal" CF DB types, you can then see on that page that CF sets a querytimeout connection string. I've even seen it set it to 0 if turned off, and maybe it's always done that but I'd not noticed before.

Sorry for the delay. I don't know why I didn't research and offer this here back during these discussions from 2014. Oh well, better late than never, for future readers I hope.
# Posted By Charlie Arehart | 2/24/17 10:05 PM
Copyright ©2018 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting