8 Replies Latest reply on Aug 28, 2013 11:10 AM by philmodjunk

    Record disappears, phantom record created



      Record disappears, phantom record created



           Over the last few months, I've lost 4 records from one table of my database. The record each time is replaced with a phantom record - question marks in all the fields. I've read that phantom records are caused by indexes breaking, and that rebuilding fixes them, but;

           a) rebuilding the indexes doesn't remove the phantom records in my case

           b) rebuilding the indexes doesn't restore the data that is missing. 

           Further, the known bug seems to report that phantom records are records that have been deleted but are still appearing. I don't believe that this is happening in my case. Delete is not available to users, unless as part of a script. I can't find a script that may be deleting these records, and even if it was, why each time do I get a phantom record? I've checked that there are no "delete relatated records" on any of the relationships. 

           Doing a full restore on the database removes the phantom records, but again, those records are lost. For us that can be detrimental, some data can simply not be regenerated. I've accessed backups in the past to retrieve the lost information, but twice the record was generated and lost between back ups. I've increased the back up schedule while I try and solve this.  

           The database is hosted on filemaker server adv 12, and is accessed with FIlemkaer Go 12 through a VPN. Users are reporting that this problem seems to happen when their wifi/VPN is lost, but not every time that their connection is lost. 

           I also have users that access the database through FM Pro 12 and IWP, and have not seen this problem occur there. 

           Help please! 


        • 1. Re: Record disappears, phantom record created

               Kate Birch:

               Thank you for your post.

               For clarification, are the phantom records appearing for the same lost records?  That is, when you retrieve the lost information from a backup, are the same four records lost again?

               You also mentioned that the FileMaker Go users have lost the connection to the host.  Do you know what the FileMaker Go users were doing at the time the connection was lost?  This may also be helpful.

               To date, no issue has been reported regarding lost/phantom records, and I know that doesn't help you.  However, it may be an issue, but I will need more information in order to find a way to replicate it.

               FileMaker, Inc.

          • 2. Re: Record disappears, phantom record created

                 For reference purposes, it appears that Kate Birch is referring to this known issue:    Phantom Record, damaged file message, Recover can't detect a problem

                 This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

                 It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

            • 3. Re: Record disappears, phantom record created


                   Thank you for the link.  This is definitely the same issue.

                   This is caused by a damaged index, and the preferred workaround is to clone the file and import the data from the original file.  The cause of this damaged index was never determined.

                   FileMaker, Inc.

              • 4. Re: Record disappears, phantom record created


                     Thanks TSGal for the response, and thanks PhilModJunk for adding the reference. After posting this yesterday I also found this, it looks like the same issue. http://fmdiff.com/fm/recordindex.html The bold "this cannot be fixed" line does not thrill me. 

                     To clarify, the phantom records are appearing from the lost records. They are in fact the lost record. I can perform a find on a record identifier for the lost record and it returns a row of question marks. 

                     Users are reporting that they are entering data into the record in question, when they lose connection to the host the database will freeze for a seconds and when they reestablish connection the record is gone. This doesn't happen every time they lose the connection, but I'm being told that is what happens when the record is replaced by a phantom. 

                     So a few questions to determine what to do next

                     1) Cloning the file and importing data from the original is never going to bring back my lost data is it? Its just going to remove the phantom records?

                     2) Should I be cloning the current version of the file, or finding a back up from before the problem first occurred? The second option there is going to be near impossible, as there have been incremental changes made to the database that I don't think I could replicate at this stage. 

                     3) How do I eliminate this problem in the future? 4 records in a couple of months is simply not acceptable, I need to prevent this happening in the future. 

                     Again, thanks heaps for the help. 



                • 5. Re: Record disappears, phantom record created

                       I'm not so sure that this is due to a damaged index. Not if you can perform a find for the record and get it to appear, but with all question marks. How exactly did you do that anyway?

                       And how are you rebuilding indexes? via Manage | Database | Fields or via advanced recover options?

                       1) I would try that with copies of the file and see what happens. It might just work. Exporting the data to a merge file and importing from the merge file into a clone is also sometimes tried when there is a possibility of issues with the data in the records.

                       exactly what did you mean by this? 'Doing a full restore on the database removes the phantom records,..."

                       Did you do a full on recover of your file and the recovered file does not have these records?

                       And when the users lose connectivity and this happens to the record, where they editing the record? Creating a new record? both? or where they just viewing the record?

                  • 6. Re: Record disappears, phantom record created


                         Hi PhilModJunk,

                         A little background so that I can answer with some context. This databsae collects medical data at the patient bedside in an acute hospital setting. Users create records through a script that searches the hospital's databases (via ODBC) for the patient, and then copies across the required data. Data goes into multiple tables (their hospital ID goes into a master list of patients, their next of kin details into another table etc). Once all the required info is copied over, a new record is created in the stroke_admission table in my FM solution. It is into this table that users enter data, and it is in this table that my records are being replaced with phantoms.  

                         When a record "turns phantom" my users notice that a record is missing, and they can tell me which one is missing. I can find the record by searching the stroke_admission table for the hospital ID that they tell me is missing (which is not the primary key for the table). I can also find intact records of the patient in the other tables (the master patient list for example). 

                         I've tried rebuilding indexes by removing indexes from all fields in the stroke_admission table. I think this worked to remove two of the phantoms that occurred in the past, but it has not removed the two that I have now. I have also tried rebuilding the indexes via the recover option, and again this doesn't remove the two current phantom records. 

                         1) I have tried this, and the phantom record is removed, but my original data is not recovered. I can see that when I export the record, nothing is actually being exported.  

                         "Doing a full restore on the database removes the phantom records,..." means that when I recover the file and use (from the advanced recover option) "scan blocks and rebuild file" and have all four options ticked, the phantom records are removed, but those records are not restored (I lose these records and the data that they contained). If I recover the file and only ask it to rebuild field indexes, the phantom records remain. (Sorry that wasn't clear). 

                         Users are editing the record when their connection is lost and the phantom record appears. 


                    • 7. Re: Record disappears, phantom record created

                           Kate Birch:

                           With any application being accessed remotely, any time you lose a connection while writing data can have adverse effects.  If you are having connection issues, consider entering the data into a temporary table(s).  When the record is then committed, a script can be run to import the data into the permanent tables.  If the connection is dropped during data entry, at least the main table indices will not be damaged.  Once the connection is re-established, return to the temporary table, add a new record and continue.  If the connection is dropped at the time the import script is running, at least you still have the data in the temporary table.

                           FileMaker, Inc.



                      • 8. Re: Record disappears, phantom record created

                             What I as alluding to with Advanced Recover Options was to use them, but only with the Rebuild Indexes, copy file blocks "as is" options selected. But I would be extremely surprised if that produced any different result. I've been thinking along the same lines as what TSGal has suggested in her last post, that the damage is occurring due to incomplete transmission of the data.

                             You might also investigate whether it is possible to improve your WiFi coverage to see if that can prevent this event in the first place.