33 Replies Latest reply: Dec 16, 2016 10:36 AM by Xinzhen Wang RSS

    Failed to open file in write mode for file - QS Server 2.0.6.0

    Mark Costa

      I'm getting an intermittent problem during an incremental load.

       

      Basically I load my new and updated records from a Database and concatenate it with the Historical records stored in a QVD file.

      The problem happens when I store the updated set of records in the previous loaded historical QVD - this is pretty much the standard Incremental Load Process.

       

      So I get this error:

      "Failed to open file in write mode for file"

       

      Again, this is an intermittent problem and I'm not able to reproduce it.

       

      1 - Is there anyone else having this issue?

      2 - Is there a way to avoid this problem?

       

      I'm thinking this may be a Qlik Sense bug.

       

      Thank you in advance,

      Mark Costa

        • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
          Hugo Andrade

          Is another resource hitting the QVDs you are trying to save? It seems like you are trying to store the QVD at the same moment someone is loading it into an App.

          You may want to enable audit logging and verify the activity on the server during the issue times.

          • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
            Sarah Lamb

            Hi Mark,

             

            I'm having the same problem.  I'm trying to reload an App that saves a table to a QVD but it failed with the error message: Failed to open file in write mode for file.  I tried to delete the QVD file in Windows Explorer but it said: The action can't be completed because the file is open in Qlik Sense Engine Service.

             

            There is one app that loads from this QVD but I'm the only person with access to it and it was not reloading each time this error occurs.

             

            The error isn't intermittent so is a major obstacle.

             

            Can anyone help please?

            • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
              guohao yee

              Hi Mark,

               

              I do encounter this intermittent error  too.

              I am loading in data and storing them as QVD in step 1 before dropping the table.

              Then I load the QVD into the app in step 2.

               

              Seems like Qlik is not closing the QVDs in time after storing them before the load step.

              Can someone advise if my hypothesis correct?

              Would it help if i introduce a time buffer or can we set a condition for load in QVDs after verifying that it is closed?

               

              Thanks!

              • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                Eric Johnson

                I am having same issue within QV 12. I have a staged load so no other qvw is using the QVD at this time. I am doing a simple reload/rewrite of the QVD that will be called in the next stage.

                 

                When i try to delete the qvd, I get a message that I "require permission from the computer's administrator to make changes to this file." I am an admin on the server though.

                • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                  Vladimir Komarov

                  Got the same issue in QS 2.2.4.

                  In my case it looks like a system resources/threads problem.

                  I am running several processes at the same time (saving different QVDs for different clients) and they work well ~70% of the time.

                  But this problem started appearing after number of parallel processes reached 5.

                  4 Tasks completed, 1 failed. According to logs STORE QVD commands occurred within 1 second of each other...

                  Trying to find if this is the system limitation or this option could be configured.

                   

                  Running the tasks separately works 100% of the time...

                   

                  VK

                    • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                      Vladimir Komarov

                      FYI:

                      I've got a reply from support that it's a "known" problem. The QLIK-58841 bug is scheduled to be fixed in 3.1 release.

                       

                      Regards,

                      Vlad

                        • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                          Mark Costa

                          Thank you Vlad.

                           

                          Do you have the full description of the QLIK-58841 bug?
                          Is there any official workaround?

                           

                          Regards,

                          Mark Costa

                            • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                              Vladimir Komarov

                              Mark,

                              No, unfortunately I do not.

                              Here is the note from my support case:

                              ___________________________________________________________________

                               

                               

                               

                               

                               

                               

                               

                              Keith Harris (QlikTech)

                               

                              I missed posted. It is identified as fixed in 3.1.1 which is typically in the fall. There should be a release or 2 in between addressing issues found in 3.0

                               

                              Tuesday, July 12,2016 2:03 PM

                               

                               

                               

                               

                               

                               

                               

                              Keith Harris (QlikTech)

                               

                              When I reload an app with the following STORE INTO script (using a QVD file):

                              """

                              T1:

                              Load

                              Date(RecNo()+39000) as Date,

                              RecNo() as Value1,

                              RecNo() + 1 as Value2,

                              RecNo() + 2 as Value3

                              AutoGenerate 1000;

                              store T1 into lib://MyAppConn/resultQVD.qvd (QVD);

                              drop table T1;

                              """

                              I get the error message back:

                              "Script Error. Failed to open file in write mode for file qlibitem://appcontent/2E27B053-6F5C-4B0D-A481-CD890A55A65E/resultQVD.qvd"

                              The same script works for QVX files. See attached test (07_StoreIntoConnection) and script log.

                               

                               

                              ___________________________________________________________________

                              They did not provide a full scenario, but it looks very similar to my case.

                               

                               

                               

                              The workaround for me was to reduce the number of simultaneous tasks executed (down to 2).

                               

                              Regards,

                              Vlad

                                • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                  Mark Costa

                                  Thank you Vlad.

                                  I have been trying to search for this bug and contact Qlik but I was not able to find anything related to this bug so far.

                                  Anyway, I have good news for us. I think I found a work around.

                                   

                                  First, let me explain my theory on what is going on:

                                  The Qlik Sense Tasks are not terminating the execution of the load script properly and the STORE command still running while the task was already terminated. That in some way, is locking the process. So the STORE command is not holding the tasks to be terminated.

                                   

                                  What I did was to add this holding time in the load script by the following command, right after the STORE command:

                                   

                                  DO
                                  SLEEP 5000;

                                  LET _fwMessage = QvdNoOfRecords ('lib://My Library\myqvdfile.qvd');

                                  TRACE $(_fwMessage);

                                  LOOP WHILE (LEN('$(_fwMessage)') = 0)

                                   

                                  The QvdNoOfRecords will return NULL if the QVD file is opened and still loading with the data from your Load Script. When it is ready the code will proceed as normal and the task will be terminated.

                                   

                                  So far I have tested that for more than 2,000 times loading more than 2,000 QVD files and about 1TB of data without any error - and loading 10 tasks at the same time.

                                   

                                  I hope this help you. If you test this and still have errors, please just let us know.

                                   

                                  Regards,

                                   

                                  Mark Costa

                          • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                            Greg Williams

                            You've verified no conflicting Tasks hitting the QVD at same time. QVD file has closed completely. Introduced the Exit Script; statement at end of script. Any write interference from another OS process? Is file locked by another service? Do you have appropriate security permissions to file? How many records are you updating? Are you able to load data from QVD in another app? Has anyone accessed the qlik app creating the qvd(s) other than you? Version of software is using the most recent software release (is this possible)?

                            -gw

                              • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                Mark Costa

                                Hi Greg, following my answers:

                                 

                                1. You've verified no conflicting Tasks hitting the QVD at same time.
                                  MC: We have an isolated QS Server just to do the ETL process and we have one task per QVD. There is no other process loading QVDs or hitting the same QVD at the same time. One thing that we have verified was if there are external QS tools working with the QVD files such as Antivirus, backup or any kind of mirroring process. Anything like that was found.
                                2. QVD file has closed completely.
                                  MC: This problem is intermittent and this is important to keep in mind. Sometimes the QVD files just get locked by QS Engine Service so we cannot do any IO operation with the QVD file (move, delete, overwrite, rename or even load using a load script). I'm not sure about the state of the QVD file at this point but the only way that we found to release them was restarting the QS Engine Service.
                                3. Introduced the Exit Script; statement at end of script.
                                  MC: I haven't tried to add the Exit Script at the end of the script but I think it is worthy to try it. In any case, this should not be necessary - as far as I know.
                                4. Any write interference from another OS process?
                                  MC: No. We have one task per QVD file.
                                5. Is file locked by another service?
                                  MC: We haven't found anything doing that but it still a possibility.
                                6. Do you have appropriate security permissions to file?
                                  MC: Yes. We run the tasks under admin permission that have full access to the QVD files and folders.
                                7. How many records are you updating?
                                  MC: We have a wide variety of QVD files, from 3 records to 14 billion records. I don't recall this problem happening on small QVD files but the problems start on QVD files greater than 300MB (not sure how many records).
                                8. Are you able to load data from QVD in another app?
                                  MC: Yes. When the QVD file is not locked by this problem, we can normally load the QVDs.
                                9. Has anyone accessed the qlik app creating the qvd(s) other than you?
                                  MC: No.
                                10. Version of software is using the most recent software release (is this possible)?
                                  MC: I have tested the load process on 2.0.6.0 and now on 2.0.9.0. We should upgrade the server to the current release anytime soon.

                                 

                                I have submitted a ticket to Qlik months ago regarding this problem but Qlik was not able to reproduce it. Actually because of the random nature of this problem I was almost unable to show to Qlik this problem during a support call. At the end we were not able to identify the source of this problem.

                                 

                                Thank you,

                                Mark Costa

                                • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                  Vladimir Komarov

                                  Greg,

                                   

                                  Just to add to Mark's comments above:

                                  I am experiencing the same issue on 2.2.4 release (Re: QVD building scripts are failing in random order). Did not upgrade to 3.0.1 yet, but based on Qlik's support response, this (or similar) issue will be resolved no earlier than 3.1.1 release....

                                   

                                  Regards,

                                  Vlad

                                • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                  Simson A

                                  Hi All,

                                   

                                  We are facing same issue " Failed to open file in write mode " while reloading the app in our qlikview server/desktop.

                                   

                                  We are using qlikview 12 Sr4. Is there any workaround for this error? Kindly help.

                                   

                                  Regards,

                                  Simson

                                    • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                      Vladimir Komarov

                                      Simson,

                                       

                                      This is the result (most likely) of the fact that QV 12+ and QS 2.2+ are sharing the same engine...

                                      Looks like they are sharing it bugs too... :-)

                                       

                                      Based on my research there are actually 2 issues that causing this problem:

                                      1. QVD file is not getting closed after the STORE INTO procedure is completed in the script

                                      2. Engine is not handling simultaneous STORE INTO processes well.


                                      For issue N1: Mark Costa suggested a solution (see his note from Aug 2, 2016 5:43 PM above).

                                      I've created a subroutine that runs after EVERY QVD STORE call and it prevents the script to continue until the file is actually closed. I've noticed that few times in my case the file was closed 10 (!!) cycles (50 seconds) after the Qlik Script has completed the STORE command.... 

                                      For issue N2: You have to rearrange your tasks (until the bug will be resolved, at least). I was running 10 simultaneous QVD generating tasks. 3-4 of them were failing 100% of time (until I've added solution for N1 above). Now 1-2 tasks are failing 30% of a time if I am running all 10 tasks simultaneously. And reducing the number of concurrent tasks to 5 reduced the failure percentage to almost 0%.


                                      Hope it helps...

                                      Regards,

                                      Vlad

                                      • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                        Mark Costa

                                        Hi Simson,

                                         

                                        Try to add the code that I have created earlier in this topic.
                                        Please let us know if that helped you too.

                                         

                                        "

                                        ....

                                        What I did was to add this holding time in the load script by the following command, right after the STORE command:

                                         

                                        DO
                                        SLEEP 5000;

                                        LET _fwMessage = QvdNoOfRecords ('lib://My Library\myqvdfile.qvd');

                                        TRACE $(_fwMessage);

                                        LOOP WHILE (LEN('$(_fwMessage)') = 0)

                                        "

                                         

                                        You may have to replace the 'lib://My Library\myqvdfile.qvd' by your QVD full path.

                                         

                                        Regards,

                                        Mark Costa

                                          • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                            Vladimir Komarov

                                            Mark,

                                             

                                            I am still seeing some issues even after applying your solution. I've posted the reply with some details ~30 mins ago, but it's "being moderated" right now... 

                                             

                                            Your code improved my situation significantly, but it looks like other issues are still exist.

                                             

                                            Regards,

                                            VK

                                              • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                Mark Costa

                                                Great, thank you for let me know. I will uncheck my answer as correct.

                                                Mine still working 100% of time - about 400 tasks running daily.

                                                 

                                                I will take a look on your answer asap.

                                                 

                                                Regards,

                                                Mark Costa

                                                  • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                    Vladimir Komarov

                                                    Thanks, the earlier reply just appeared in the posting above...

                                                     

                                                    My problem #2 is (probably) a result of multiple issues.... My QVD generators are storing files with number of records from 400,000 to 30,000,000. I've noticed that running all 10 of them together (if they are starting at the same time) frequently causing failure issues. I was blaming the lack of system resources (since CPU load and memory usage is getting closer to 100% during these loads), but I've noticed that if I am starting the 5 biggest loads separately (when the CPU and memory are still reaching 100%), the failures are very rare, comparing with starting all 10 together....

                                                    So, I am still researching the problem....

                                                     

                                                    Will keep you updated.

                                                    VK

                                                • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                  Ainhoa Parra Miñano

                                                  Hi Mark, I am trying to use the code you had written but I am not sure if I am doing it right because the load didn't work and  remain plundered.

                                                   

                                                  I've put the CODE just after the STORE command, and  I've add a ';' at the end:

                                                  Captura2.PNG

                                                   

                                                  I've put the CODE just after the DROP TABLE command and I've add a ';' at the end:

                                                  Captura.PNG

                                                  None of them let me finish the load. Am I doing something wrong?

                                                  Thanks a lot.

                                                  • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                    Xinzhen Wang

                                                    Hi Mark,

                                                    We tried to implement the workaround. It works for the first few days but still fails intermittently with the same qvd file.

                                                    Xinzhen

                                                      • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                        Mark Costa

                                                        I'm sorry to hear that Xinzhen. That solved our problems 100% of the time, even in different implementations.

                                                         

                                                        Question:
                                                        Are you loading the QVD that you are trying to overwrite with the STORE command in the same load script - like in an incremental load? If possible, please share your load script.

                                                         

                                                        Regards,

                                                         

                                                        Mark Costa

                                                          • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                            Xinzhen Wang

                                                            Hi Mark,

                                                            Yes we need to implement incremental load on multiple datasets and the error always occurs on the largest qvd size.

                                                             

                                                            This is the step of incremental load.

                                                            (incremental load and concatenate other datasets)

                                                            1. load new records from database into SignInIncr table

                                                            2. //This step is not necessary but I was trying to debug the problem, the qvd got updated correctly

                                                                store SignInIncr into [$(filepath)/SignInIncr.qvd] (qvd);

                                                                Call Qvc.WaitForFileToClose('($(filepath)/SignInIncr.qvd'); //made your code a sub function to call

                                                                 drop table SignInIncr;

                                                            3. load all record in SigninIncr.qvd and concatenate with history data in Signin.qvd into SignIn table

                                                            4. //This step is where the intermittent error happens

                                                               store SignIn into [$(filepath)SignIn.qvd] (qvd);

                                                               Call Qvc.WaitForFileToClose('$(filepath)SignIn.qvd');

                                                               drop table SignIn;

                                                             

                                                            Here is the error message I got:

                                                            2016-12-14 04:02:14 0290 store SignIn into [lib://..filepath.../SignIn.qvd] (qvd)

                                                            2016-12-14 04:02:14      Failed to open file in write mode for file \filepath\SignIn.qvd

                                                            2016-12-14 04:02:14      Error: Failed to open file in write mode for file \filepath\SignIn.qvd

                                                            2016-12-14 04:02:14      General Script Error

                                                            2016-12-14 04:02:14      Execution Failed

                                                            2016-12-14 04:02:14      Execution finished.

                                                             

                                                            I called the Sub procedure after every "store" in this load script. It seems that the execution didn't get to the "buffer" code section when the error happens?

                                                             

                                                            Did I miss anything?

                                                            Xinzhen

                                                              • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                                Mark Costa

                                                                Bingo!

                                                                 

                                                                Ok, so this is where you'll need another workaround, yes, another Qlik Sense bug:

                                                                In the same load script, you cannot load/store the same QVD file. Like you noticed by yourself, sometimes we got an error doing that.

                                                                 

                                                                What I do to avoid this problem in my incremental loads is to rename - you can make a copy too - of the historical QVD file. You should not load at any point of your load script the original historical QVD file, just the renamed or historical one. Your load script should look like that:

                                                                 

                                                                //*************************************************

                                                                 

                                                                // 1. Rename or duplicate your historical QVD file - if it exists already (I will not cover this treatment in this example)

                                                                EXECUTE cmd.exe /c REN "\\filepath\qvdname.qvd" "TEMP_qvdname.qvd";

                                                                 

                                                                // Note 1: I just have renamed my QVDs because it takes time to copy large QVD files.

                                                                // Note 2: You have to allow your Qlik Sense Server to run EXECUTE commands. You can Google for that

                                                                 

                                                                // Now you can continue with your regular incremental load:

                                                                // 2. Load new/updated/deleted records from Database;

                                                                // 3. Concatenate the new/updated records with the renamed QVD file. Just load the data from your TEMP QVD file, never at any point in your load script load the data from the original historical QVD file;

                                                                 

                                                                // 4. Store the QVD file using the original name of the QVD file. Since your original QVD file was not loaded you will have no problem on this Store command;

                                                                 

                                                                // 5. Do the workaround to wait the Store command to be ready; and

                                                                 

                                                                // 6. Delete the temporary QVD file - not required, but will save some space

                                                                EXECUTE cmd.exe /c del /f /q "\\filepath\TEMP_qvdname.qvd";

                                                                 

                                                                //*************************************************

                                                                 

                                                                That is it. You should be good with those two workarounds.

                                                                 

                                                                Please let us know if that worked for you.

                                                                 

                                                                 

                                                                Regards,

                                                                 

                                                                Mark Costa

                                                                  • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                                    Xinzhen Wang

                                                                    Hi Mark,

                                                                    Thanks for the diagnosis! The task ran successfully today.

                                                                     

                                                                    Instead of modifying settings.ini to allow EXECUTE, I have made a copy of the qvd file to be a placeholder.

                                                                     

                                                                    // Make a duplicate copy of qvdname.qvd into temp_qvdname.qvd (one time)

                                                                     

                                                                    // 1. Load new/updated records from database;

                                                                    // 2. Concatenate the new/updated records with data from temp_qvdname.qvd

                                                                    // 3. Store the data table into qvdname.qvd

                                                                    // 4. Do the workaround to wait for Store command to be ready

                                                                    // 5. Load all data form qvdname.qvd and Store it into temp_qvdname.qvd (for next day execution)

                                                                    // 6. Do the workaround to wait for Store command to be ready

                                                                     

                                                                    This sequence seems to work even though I did Store and Load from the same qvd file.

                                                                     

                                                                    Thank you again for your help!

                                                                    Xinzhen

                                                        • Re: Failed to open file in write mode for file - QS Server 2.0.6.0
                                                          Joe Nadolny

                                                          Just so you know this bug is not fixed in Qlik Sense 3.1 R1, 3.1.1