CF DSN 'loses track' of tables and views

[Guest]

... but only in one database and only at a certain time (around 8am). We have a manual solution, Open the DSN and switch the server name to Network alias or vice versa and save - but why does this work? Ae we refreshing the connection pool? I've increased the connection pool from 100 - 200, decreased the timeout to 10 minutess and checking to 3 minutes and, t.urned off autogenerate keys        We've completed SQL traces, CF DSN logs, and tested different DNS entries, nothing notable yet.

We checked with networking folks, they are not backing up at this time... anyone have something we haven't tried or should have tried a different way or can suggest a way to test to pinpoint the problem?

Our infrastructure is WinServ 2008 w/ SQL2008r2 and a Winserv2008 and CF9 (it doesn't look like we have the right version to use cold fusion monitor)

Thanks in advance,

Chris

[ilssac]

Any chance you have SELECT * code in your application?

Any chance the structure of the database somehow changes (new tables and|or columns added and|or removed)?

There is a very hard to track problem with SELECT * code where the database driver will cache table and column structure.  Then if the structure is changed in the database, the cahced version IS NOT updated.  Then the next time the SELECT * code runs it is using an outdated layout of the database and can throw errors similar to what you described.

[Guest]

I am currently reviewing code for any Select * calls, but your point about the underlying table structure is notable - the main tables are updated via .Net processes each night. The structures are not changed that I am aware of, but the data is refreshed (in whole or part) each night. It seems to be similar to the type of situation you've described.

But, what is the effect of changing the DSN server/alias? Does that refresh the connection pool? Is there some function that I can replicate the manual DSN switch?

Thanks again for responding ilssac. Beyond the manual work around, I think I'll try changing the table specifc calls to views.

[ilssac]

Yes, I beleive you are refreshing the Cached Connections, causing everthing to be read fresh.

IF that is the problem.  AND IF you can live with the preformance trade offs you can set the DSN to NOT cache the connection.  Thus every time the <cfquery...> is run new connections are made.  For all but the highest traffic, large data consuming sites this may not be to bad.

You could also do this programmatically with a schedulded task that does the DSN change using the ColdFusion Administrator API to refresh the connection every morning.

[Guest]

Fast answer: Whatever causes this error, the resolution was to change the ColdFusion DSN (CFDSN) to an IP address instead of Server name or Server Alias.

Details: The first occurrence of the error is March 15^th, 8:28 AM (It is probably worth asking network folk if anything changed around this time). From this point, the error started everyday between 8 and 8:05AM and would seemingly randomly continue until the CFDSN was refreshed by changing Server name to Server Alias or vice versa (we had not tested IP address at this point). The first error (yes the table does exist):

"Error","jrpp-2175","03/15/11","08:28:59","wxxx","Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'Cxxxxxl'. The specific sequence of files included or processed is: C:\xxxxx.cfm, line: 302 " coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query.

Research: In the URL’s below most center around bad caches of DNS. The one suggestion of refreshed tables and Select * statements, seems feasible, but wasn’t supported by any other articles and I could not find any Select all statements on the website… but if this is purely a DNS caching issue, why didn’t the other testing CFDSN’s cause errors? Is the DNS cache specific to a CFDSN?

Testing: I set up three DSN’s to match the only DSN failing, JGData. The only difference being Server name, Server Alias and IP were the sources in the test CFDSN’s. I set up test.cfm to cycle through the CFDSN’s with cftry/catch so we could record any errors every 5 minutes…. But no errors.  I added the live CFDSN, JGData, and we started picking up failures which is how we pinpointed the 8-8:05 window.

From there it was just trying different permutations till one stuck. If time allowed and the work around was somehow problematic, I’d want to figure out how to replicate the error then experiment with different permutations (Would ODBC Socket eliminate the problem, the Java solution provided in couple of the articles below, etc). FWIW, I wouldn’t rule out some kind of anomaly with our virtual environment just b/c I know so little about it.

N E Way. It’s fixed and brain dumped. Marching on.

Researched posts/articles/blogs

http://forums.adobe.com/message/3396333 - points a finger at DSN configuration.

http://forums.adobe.com/message/3109318 Points a finger at incorrectly created DNS entries on the Server

http://forums.adobe.com/message/3622433 – possible ‘refreshed’ tables could be causing the problem.

http://tjordahl.blogspot.com/2004/10/cfmx-and-dns-caching.html points a finger at using cfhttp and its use of stored DNS cache – in theory, if we specify IP address this shouldn’t happen.

http://russ.michaels.me.uk/index.cfm/2010/4/16/Fix-the-DNS-caching-in-ColdFusion  - similar article about DNS caching and potential problems.