CF / IIS Service LockUp when using CFMX7, MS Access, and File Uploads
A client regularly uploads an Access database (I know, I know, not the best one for the job.. but the point is another blog/argument) which is then a CFMX7 datasource.
Occasionally and intermittently over the last few years, the .mdb file would be locked, showing the corresponding .ldb file and the uploads would fail. But these files weren't just locked. They were so tightly bound, that FTP deletes would not delete. Even the hosting support personnel could not delete. They had to shut down the CF server, and IIS to delete the files and then restart. Not good news for other accounts the shared hosting server.
Even more interesting is that all of a sudden, the problem became more frequent (locking 6 times yesterday, and 5 more today)... and thus, replicable. It appears as if CF Access datasource connection creates the lock (appropriately) when a query of any sort is performed, even SELECT statements. Attempting an upload while the file is "locked" however doesn't just reject the upload, it corrupts the files and the service.
We don't know what changed, but every single upload now locks. and I suspect the java method that creates the datasource link may lock the database at the application.cfc level. (Any CF/JAVA wizards, please feel free to chime in on this suspician if you know one way or the other). IIS is v6.
To correct this, have the CF Administrator UNcheck that the "maintain database connections" . Creating a "dummy" query is only for pre CF6.
With that said are you using <cflock /> in the code? or <cftransaction /> (not saying either of them is the answer but they are things that come to my mind in light of the issues. Also... do you have the current update of ColdFusion on that server? If not you could be running with a bug that may already have been eliminated. Lastly... when are you opening the query? You should not be doing logic processing to databases (design suggestion here) inside the Application.cfc file. (I doubt you are but wanted to make sure based on the description you were not.)
as for uploading updated db to the server, the way it used to be done on this site was the new db was uploaded to same dir as live one, but with a different filename. then late at night the live and old file was deleted and new one renamed to proper name... oh what a pain it was! i am SO happy NOT to have to deal with access as server db any more :)
Azadi
Why not? Moslty financial reasons. First, this is a non-profit organization and second, in the home building industry, so they have a double whammy to contend with on the economic front. The best solution is to get them online in a crisis, then when everyone has time to breathe, re approach them about planning on having a sustainable, reliable Internet capable database.
Also @John... We only had this access locking issue occuring a few times per year. We do not know what happened in the last month that made it a consistent replicatible problem. No cflocks, no logins... just queries. It even looks like the lock is getting set for any .cfm page (perhaps at the application.cfc level when the datasource is defined -- no db calls or processing there) regardless of whether or not that page has any queries on it.
Thanks for telling your experience. It is always helpful to realize I am not nuts and that others have bumped into similar issues :)
hth,
larry