Two web pages with identical queries, different results.

[rlinhart68]

How can two pages give different results from the same exact query?

I have pages that are part of my application.  Immediately after an Update query, the results of the update are not displayed on the applicable page of my application via a Select query. 

When I subsequently execute the identical Select query in a page that is not part of my application (or in SQL Server MMC), it shows the results of the update correctly every time.

I have turned off query caching (When I do a CFDUMP of the query, it always shows Cached=false) and do not keep any browser history in Firefox.  I also set the Expires header value in all my pages, and added a dummy clause to end of my Where clause so that the query is always different.

The other issue is that it seems to be happening randomly.

What could be causing this?  It is driving me crazy.

Richard

[Miguel-F]

Can you share your queries and the results?

[rlinhart68]

I would like to first present a disclaimer that I inherited this database and have nothing to do with its current design...

I have a page that is displaying a list of 10 steps with the following query:

SELECT rx.* ,co.cont_firstname,co.cont_lastname

FROM ProcessRxIntake RX

INNER JOIN Contacts CO

ON RX.Contacts_ID = CO.Contacts_ID

WHERE RX.Contacts_ID = #URL.ID#

The output from this page displays a list of 10 steps (with links to the page for that step) with its status as either Validated or Not Validated.  The validation status is stored in the table ProcessRxIntake in the fields Valid1, Valid2, Valid3, ..., Valid10.

Each step page contains three buttons added via a cfinclude page: Mark as Not Valid, Mark as Valid, and Cancel.  The first two these buttons results in a form submission whose action page executes the following query:

UPDATE ProcessRXIntake SET #updatefield# = #valid# WHERE contacts_id = #form.id#

Where updatefield is valid1 or valid2, etc. and valid is 0 or 1, which are set from values received from the form.

After executing the query, it redirects back to the step page.

Occasionally, the step page will not display the updated value of the validation, while in SQL Server MMC or a test page that executes the same above query displays the values correctly.

I added this to the WHERE clause in the above query to force it to always be a different query:

or (1 = #second(now())#)

but it still occasionally doesn't work.

I have cachedwithin="#CreateTimeSpan(0, 0, 0, 1)#" as a cfquery attribute.  I have also tried setting the maximum number of cached queries in the coldfusion administrator to 0.  It still intermittently doesn't work.

That is the worst part...it is intermittent!

When I cfdump the above query, it always shows cached=false.

I have also tried qualifying my tables with (NOLOCK), but that still hasn't had an affect.

[Dan_Bracuk]

It could be that all the processing is taking place in less than a second, in which case your original select query is still cached.  Since your intent is to not cache the query, the cachedwithin attribute is not doing anything useful for you anyway.  I suggest getting rid of it.

[rlinhart68]

How could my original select query still be cached if I have disabled all caching?

What if I put a sleep(1000) after the query to delay the redirect?

[Miguel-F]

By specifying the cachedwithin attribute you are actually telling CF to cache your query, albeit for only 1 second.  I would remove that attribute as Dan suggested.

Are you sure it is your query being cached and not the page?  Can you turn on debug output for this page?  Or just add a timestamp to your page like <cfoutput>#Now()#</cfoutput>. Take note of the timestamp when the issue occurs.  Is the page refreshing or not?  Also when it happens what does your query information tell you in the debug output?  Was the query cached and how many records did it return (what you expected)?  I guess since it is intermittent you really need to focus on the debug output when the issue happens.  Save off the html source when it works and then compare that to the html source when it does not work.  Maybe that will shed some light on it.

[rlinhart68]

I have removed the cachedwithin.

Regarding my pages being cached:

I do not save any history in my browsers, and have set the headers in my <head> to always expire my pages with

<meta http-equiv="Expires" content="Mon, 06 Jan 1990 00:00:01 GMT">

I have tried this in Chrome, Firefox, and IE and it occurs in all three of them.

I also have the full debug information being displayed, and the cfdump of my query.  The query always returns only one record, as it should, and the cfdump always shows cached as false.

I have noticed that if I hit refresh like a minute later, that the correct result finally is displayed.  Why is the result being held up when I am not caching?  And then only on this page and not the other pages?  It must be caching somehow.

[rlinhart68]

I am displaying the timestamp on the screen as you suggested, and it is only changing once per minute or so...occasionally.

Of course when it changes, the page displays correctly.

What am I not doing (or doing wrong) that is causing this?

Is it a browser issue or a CF issue?

[rlinhart68]

I think I got it to work consistently now with the following:

<cfoutput>

<cfheader name="expires" value="#now()#">

<cfheader name="pragma" value="no-cache">

<cfheader name="cache-control" value="no-cache, no-store, must-revalidate">

</cfoutput>

Thank you for all your assistance.

[Miguel-F]

Excellent!  Glad you got it working.  Yes, I always use those 3 headers when fighting a browser caching issue.

[Dan_Bracuk]

Regarding:

How could my original select query still be cached if I have disabled all caching?

Your cfquery tag has a cachedwithin attibute.  Perhaps you should check the documentation and see what that does.