Client Variable Purge

[Libby_H]

I am at my wits end trying to fix what I think is a client variable problem. I am currently running CFMX 6.1 + updaters on a Windows 2003 R2 SP1standard edition server with IIS 6.0. Since implementing with this server (01/2007), we have installed all the necessary Windows patches for security every month. I have installed hot fixes for client variables (TN 19590) and a few others for COM and cfforms issues. I have experienced CF 'hanging'; not allowing anyone into the application. I shut down the CF MX Application service, the CF MX ADBC agent, and try to shut the CF MX ODBC server, but it remains in a 'stopping' state, with a reboot the only cure.

While trying to troubleshoot the problem, I have come across the following error:

10/16 15:12:24 error Operation failed on the data source named "Treasury".
coldfusion.runtime.ClientScopeDBException: Operation failed on the data source named "Treasury".
at coldfusion.runtime.JDBCHelper.Store(PersistenceFactory.java:261)
at coldfusion.runtime.ClientScopeServiceImpl.PersistClientVariables(ClientScopeServiceImpl.java:282)
at coldfusion.runtime.ClientScopeServiceImpl.PersistClientVariablesForRequest(ClientScopeServiceImpl.java:264)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:32)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.CfmServlet.service(CfmServlet.java:105)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:249)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

That's it - nothing else. I have searched high an low, finding similar errors, but they all have an additional error message like outOfBounds. This error appears up to 200 times per day, some within seconds of each other. (I never really looked at the logs, but it has been occurring since I brought up each server, test and production). I mentioned the client variable fix above because of the references in the error message like 'ClientScopeServiceImpl.PersistClientVariables'. I am using an Access DB to store the client variables, with a purge date of 90 days. Within the Access DB there are the CDATA and CGLOBAL tables, but with only 278 records in each. I inheritated the system in version 4.5, and have upgrade to 5 and the to 6.1, so I have never really looked at client variables before.

Any ideas? Could these errors be created every time someone logs onto the application? Your time and suggestions are greatly appreciated.

Libby

[Guest]

Libby, your wits should take you further. Your problem is your choice of databases more than a client variable issue. It is regularyly stated that ODBC and especially ODBC to a desktop database (access, paradox, etc.) is not recommended for real applications. More so, only use Access for low traffic, departmental applications or not at all. Storing client variables in access only compounds the bad choice. Client variables stored in a database means there is a db read at the start of every request and a write at the end of each request. That alone will over stress access.

And then you have your explicit database queries on top of that. With a datasource named "treasury", it seem likely you are storing application data there too. So you have a multithreaded application server going to a database that can only handle a single connection at a time where you are making multiple database queries for every page request.

Win2k3 was originally distributed without any desktop ODBC drivers, the msjet, etc. Microsoft indicated that an application server was not a place where desktop databases should be used. Unfortunately, MS let up and eventually put the destop ODBC drivers back in win2k3. But, their thinking is correct. Desktop db's cannot keep pace with a high speed appserver, including coldfusion and win2k3.

Path of a JDBC query to MS SQL server:
CFMX (java) - JDBC SQL server driver - over wire as TDS request straight to the database server. MSS DBMS engine does all work on back end.

[Path of a ODBC query to MS Access:
CFMX (java) - JDBC sequellink driver - sequelink server - sequelink client - windows ODBC - Access driver -msjet manipulates access file.

I will make the main points in this thread only, since they are all around these forums. ODBC is very indirect. File based desktop databases are typically not thread safe, multi-user or just capable enough for any appserver. Use a real DBMS, in short. Even mssql developer, desktop or what ever their new low cost database. Say good bye to Access for any real application's backend.

[Libby_H]

Ken - Thank you for your response. I have been trying for the last three years to get this application re-platformed to a real database. Our company standard is Oracle, but this application was deemed 'too small' for Oracle (currently 1/3 GB in size). My user base has almost tripled in the last five months, and three outages in the last six weeks is what I need to push this again.

In the meantime, I was thinking of separating off the CDATA and CGLOBAL tables into a separate Access DB - changing the code where the clientstorage is called and setting it up in the CF Administrator as the client store in the Data Access section. Does this sound OK for a short term fix? I figure that it will take me 6+ weeks before I can get funding and approval for a new database, and I need to do something to prevent the application from crashing. Thanks again.

[paross1]

Is there any reason why you can't use clientstorage="cookie" for this particular application? I have a couple of applications using Oracle and SQL Server databases that are using cookies for client variable storage with no problems, and have been doing so for years.

Phil

[Libby_H]

I was mistaken in thinking I could get this application replatformed in a six week time period. Looks like it will be moving to a MS Sql Server database (because we have a 'spare' license), but it cannot be started until February '08.

So, I have examined all of the code, and found 8 places where cflocation uses a 'clientstorage' parameter. I have gone ahead and changed these all to 'clientstorage='cookie'' (in test) and reset the clientstorage in the client variable area in the CF Administrator. So far, so good, but I really need volume testing to see if this will work. Then to production.

Thanks everyone for your help and support.