I wrote my code in CF 2018 on my local dev environment. I had no issues with it in 2018. When I load the same code into CF 2021 Standard I'm getting an error when I try to move a file from one directory to another after reading it.
Basic set up is I read the excel file and then move it to a different directory. The file gets moved but errors out on deletion. When I try to manually do anything to the file in Windows, I get a message that the file is being used by coldfusion.exe and cannot be deleted or modified.
The only way I've been able to release this lock on the file is to restart the ColdFusion service. I tried waiting to see if it gets release but it didn't after 24 hours.
Any ideas on how to get the file moved to a new directory and deleted from the original after I'm done reading it?
Following a file operation - especially, following a file-read - use fileClose() to close the file stream and release the server resources.
I added fileClose() before posting and didn't help. I forgot to mention it but it wasn't even an issue in 2018 without FileClose().
>> I added fileClose() before posting and didn't help
Add fileClose(the_file_object); directly after reading the file.
If that still doesn't work, could you please share the code you're using?
If you have moved it, there is nothing to delete, right? If you copied it, yes it would still be there
technically, yes if it works. However, it seems that actual processing of "moving" is broken down into pieces which consist of copying the file and then deleting it from the source.
I have the same issue with just deleting the file and not moving it.
Do you face these issues only when you copy/delete Excel files? Try various other file-types, using the same code, file-location, and so forth. What happens then?
Another idea. Rather than restart ColdFusion, download and use the LockHunter tool. For example:
Yet another idea.
Forget for a moment about "Basic set up is I read the excel file". Do the following test.
Manually copy a random Excel file to the initial location. What happens when you use the same code to copy or delete the file (without reading it)?
Feels like it could be a permission error, as if cf has read but not write priveleges on the file. You mention running cf as a service. Is this Windows? And does the services panel show the cf service running as user "system" or something else?
Finally, in case anyone may want to try to recreate this, is the file (that's to be deleted) under the cfusion/wwwroot? Or is it under a webroot of iis or apache? Or is it still somewhere else? If so, is it a local file or some sort of share? A unc path? A drive mapping?
And did you install 2021 with the normal gui installer or the new zip installer?
Thanks for your reply, Charlie.
CF is running on a Windows Server. I tried this from cfusion/wwwroot and accessing the app through localhost when I made the post before. Since then, I set it up in IIS and a public domain name and I'm getting the same results. The code is in inetpub/wwwroot folder
The file that I'm reading that's getting locked by coldfusion is located inside inetpub/wwwroot/myapp folder. I tried having it in C:\import before and had the same issue.
I'm not sure how Coldfusion 2021 was installed.
Here's the code that should enou
<cfset path = "C:/inetpub/wwwroot/myApp/import/records"/>
<cfset movePath = "C:/inetpub/wwwroot/myApp/import/records/Processed"/>
<cfset todayDate = dateFormat(Now(), 'mmddyyyyhhmmss')/>
<cfsetting requesttimeout="99000" />
<cfdirectory name="files" action="list" directory="#path#" sort="datelastmodified" />
<cfif files.recordcount GT 1>
we have files <br>
<cfif files.Type EQ "File">
<cfset filePath = "#path#/#files.Name#"/>
<cfset myFile = FileOpen("#filePath#", "read")/>
<!---- read the excel file ----->
headerrow = "1"
query = "myData"/>
<!---- <cfdump var="#myData#" /> --->
<!--- Import Data --->
<!---- <cfloop query="myData"> ---->
<!---Insert data into the Product database table --->
<!--- </cfloop> --->
<!----- move processed file ---->
The file-open and file-close actions are unnecessary. Here is the rest of my feedback:
<cfset path = "C:/inetpub/wwwroot/myApp/import/records"/> <cfset movePath = "C:/inetpub/wwwroot/myApp/import/records/Processed"/> <cfset todayDate = dateFormat(Now(), 'mmddyyyyhhmmss')/> <cfset fileMovePath = movepath & "/" & todayDate & "_" & files.Name /> <!--- Timeout is in seconds, not milliseconds. ---> <cfsetting requesttimeout="99" /> <!--- No cfoutput tag necessary here ---> <cfdirectory name="files" action="list" directory="#path#" sort="datelastmodified" /> <cfdump var="#files#"> <!--- You want, presumably, 1 or more records. Hence, GTE ---> <cfif files.recordcount GTE 1> we have files <br> <!--- Since you wish to output within the loop, it is more efficient to use cfoutput instead of cfloop. ---> <cfoutput query="files"> <cfif files.Type EQ "File"> #files.Name# <br> <cfset filePath = path & "/" & files.Name /> FilePath: #filePath#<br> <!--- No need to call fileOpen(). Cfspreadsheet will open the file before reading it. ---> <!---<cfset myFile = FileOpen(filePath, "read")/>---> <!---- read the excel file -----> <cfspreadsheet action="read" src="#filePath#" columnnames="Buying_Group,Item_Number,Effect_Date,Price" headerrow = "1" excludeHeaderRow="true" query = "myData"/> <!--- There is no fileOpen(), plus cfspreadsheet will try to close file after reading it. So, no need to call fileClose(). ---> <!--- <cfscript> //FileClose(myfile); </cfscript> ---> <!----- move processed file ----> <cffile action="move" source="#filePath#" destination="#fileMovePath#"> </cfif> </cfoutput> </cfif>
One more remark: as far as I can see, the cfspreadsheet read-action has no relevance to the file-move.
My original code that works on CF 2018 didn't have fileClose. I only added it here to see if it will help unlock the file. I have another solution that will not require moving the file for now.
It makes sense why you would use fileClose.
In any case, have you tested the code I suggested? If it solves the file-locking problem then please mark this thread as answered. In doing so, you will help fellow developers who have the same problem.
We have some people experiencing the same problem. Is there any significance, related to this file in use message, to whether we used the normal gui installer or the new zip installer?
Steve, I'll say first I'm not aware of there being any reason why it may depend on how CF was installed. (I had asked about this back in December, just in case it may prove diagnostically useful.) I have also not heard of anyone else experiencing this issue, so I don't think it's a known CF2021 issue.
FWIW, there was an update 1 for CF2021 since this thread was first started by Omega back in Dec 2020. Though I see no mention of any obvious connection to this issue in the update's technotes, it still may be useful to hear from you (or Omega) whether you've applied that update or not.
As for being able to recreate the problem, the example offered by Omega above does not stand alone, but if you (Steve) could somehow offer an example that does demonstrate the problem, perhaps we could at least confirm if the problem happens for "anyone".
One last thought (especially if you also can't or don't create a standalone example), here's something to at least try: I wonder if there may be a difference if you did the move or delete operation in a separate thread or request (using cfthread, cflocation, or their script equivalents). I realize Omega had reported needing to restart CF to "unlock the file", but it's not clear if any other request was run to try to delete the file. If it works, I'm not suggesting that's "how it should be". I just offer it as a work-around.
I have installed the update since this post but by that time I already found a workaround. I just moved the file before reading it and it worked fine my situation. However, I did notice that after the update the files were getting unlocked after some time without having to restart CF. The time before they got unlocked seemed to be random. From 24 to 48 hours.
Good points. Thanks for sharing them and hope they may help Steve or future readers.
I just added a ticket for this issue with Adobe. Only seems to happen when you read excel files with cfspreadsheet it seems. The file gets locked and no firther operations are possible thereafter
And now it seems that Adobe has confirmed that the fix for this is coming in update 2. See a comment from them in the bug report that harkirats indicates in a comment below here.
And to be clear, that update 2 (of CF2021) is currently in prerelase, available at prerelease.adobe.com (as of late July 2021), if any may want to test it out before the update is released as "final" to the public.