6 Replies Latest reply on Mar 10, 2016 6:30 AM by kmtenor

    Manually recovering a database with container fields

    kmtenor

      We have finally identified an opportunity to fix a database that has some major corruption issues.  We have attempted all of the "simple" recoveries, and are faced with re-building using a clone, as documented here and here, to rid ourselves of some nasty phantom records that are messing with our ability to run ExecuteSQL queries.

       

      Greg Durniak's suggestion to use Merge files as the export target seems to be a good one, and I've been able to write a script that runs through a list of tables and exports them all with minimal intervention.  When I was testing it, however, I got the dreaded "Container fields cannot be exported."  Drat.

       

      There are only about a half-dozen container fields, so they will be easy to target.  What I'm wondering is if I can (should?) make use of FM14's "BASE64ENCODE" and companion "BASE64DECODE" functions to more easily move these container fields out and back into the database.   The idea would be to encode each container field, with its filename embedded, before starting the exports.  Then, on import, decode the encoded files back into their containers, likely through a script since we aren't supposed to "perform auto-enter functions" during the imports.

       

      So far, the only caveat I can see is time and file size - when I encoded one field yesterday on an offline copy of the database, I ran into some indexing issues that seemed to take awhile to resolve themselves.  And, of course, encoded binaries create pretty large text fields.

       

      So, my question is this: Does it make sense to encode, export and then decode the containers to move them more easily into the clone, or is there another, simpler/better way that I'm overlooking? 

       

      Thanks.

       

      -Kevin

        • 1. Re: Manually recovering a database with container fields
          schwjm

          Base64 was going to be my generic approach for handling exports/imports for containers for non-FMP formats, I haven't gotten around to actually needing to try it, let alone completing it. If I'm not mistaken this is a common way of specifying images in other formats, such as vCard.

           

          You will also want to use GetContainerAttribute(;"filename") to preserve the name of the file.

           

          I'm not sure what kind of indexing issue you would have run into but presumably for this type of operation, the field for the base64encode probably could be simply an unstored calculation. There is no benefit to indexing it and keeping it unstored will prevent FMP from spinning when not using that file (of course it may mean the export operation itself is slower.)

           

          Maybe I'm not understanding as I have not had to rebuild a file corrupted that badly, but is there some reason to avoid exporting the container fields to FMP format with the primary key attached? I'll assume there's a reason the documentation you listed doesn't want you using FMP format--though I would be surprised if FMP "transfers" the corruption to an exported file directly. Even if it did, doing one import from text and another to import the containers after the records are already created would be fine I would think.

          • 2. Re: Manually recovering a database with container fields
            gdurniak

            If every record has a unique key ( e.g. serial number ),  you can import just the images from the old file later,  using the key as a match field

             

            greg

             

            > is there another, simpler/better way that I'm overlooking?

            • 3. Re: Manually recovering a database with container fields
              kmtenor

              The corruption we are dealing with is due to VM snapshot backups being taken without the database being stopped.  This caused records to be open while the snapshot was taken, but when the database came back online, the users that had the records open (on FMGO) had moved on, apparently causing the record corruption.   

               

              At least, that's how I'm able to understand it - I'm sure someone who's spent more time in the engine would have a more "correct" answer.

               

              At any rate, the corruption is complete enough that exporting to an FMP file will not clear it.  We found the corruption because FMP says the record is there, but displays nothing - but when the record is included in a set returned with ExecuteSQL, the query fails.  Deleting the record in FMP does no good, and there's no way to get ahold of the record to forcibly remove it, even via ODBC.

               

              The solution was to stop taking VM Snapshot backups, and go back to just doing a file-level backup of the Filemaker Server Backup directory.  There is a way to script a "pause" on the database, as well, but we haven't gone after that - I prefer my databases un-corrupted.

               

              Thanks.

               

              -Kevin

              • 4. Re: Manually recovering a database with container fields
                kmtenor

                Thanks, Greg. 

                 

                That's exactly what I was hoping to hear.  Yes, all of the container records have both an ID serial number and a UUID - so we should be all set.

                 

                Thanks also (again) for your great documentation on FM DB corruption.  It's tremendous to have that much information collected and shared.

                 

                -Kevin

                • 5. Re: Manually recovering a database with container fields
                  gdurniak

                  Keep in mind that Phantom Records has been a problem long before VM's

                   

                  and, FileMaker claims, not easy to find a cause, since it is not easily repeatable

                   

                  it would be interesting if you CAN make it happen,  with a snapshot

                   

                  greg

                   

                  > The corruption we are dealing with is due to VM snapshot backups being taken without the database being stopped

                  • 6. Re: Manually recovering a database with container fields
                    kmtenor

                    Good point, Greg.   It is a bit of conjecture on my part that the snapshot backups caused this particular corruption.

                    That said, many, many problems we were having with FileMaker - including additional corruption and client screens being out of sync with one another - went away as soon as we switched backup methods.  Seems a little more than coincidental. 

                    If the opportunity ever presents itself (HA!), I'll see about setting up a test platform where we can try to intentionally corrupt a database or two.

                    Thanks.

                    -Kevin