12 Replies Latest reply on Oct 6, 2014 2:40 PM by tesla51

    Detecting/repairing bad indexes

    anothersmurf

      Lately, I've had several cases of indexes going bad in hosted DBs. That is, I do a search on a field and the results don't correspond to the data that's stored/displayed in the field. There may be few or no records found when I expect thousands, or there may be no records when I search for >0 in a field which has data for most records. The solution is always toggling indexing on and off, but if the field I'm searching is a calc, the actual problem might be somewhere down the line, and tracking down which field needs reindexing can be cumbersome.

       

      Is there some way to force FMP to recalculate all the indexes? Or is there at least some way of detecting this problem that doesn't rely essentially on chance?

        • 1. Re: Detecting/repairing bad indexes
          Mike_Mitchell

          Indexes don't normally just "go bad" for the heck of it. There's usually a cause. Here are some things to look for:

           

          1) Have the databases shut down abnormally (i.e., server process crash, server shut down without closing the databases first, that sort of thing)? This can cause FileMaker to be unable to perform its normal housekeeping on file close.

           

          2) Do you have processes or scripts that perform large-scale changes across tables, such as imports or Replace Field Contents on fields that are indexed or that are referenced by indexed calculations? How often do these processes run? The more you beat up the indexes, the more likely it is that some corruption will creep in.

           

          Those are the two major reasons I can think of off the top of my head. Basically, abnormally closing a file and globally updating large numbers of records (especially if you perform a Delete All Records followed by an Import into the same table) are things I've seen cause index corruption.

           

          Do you periodically shut down the databases and perform a Save as Compacted Copy? This operation will rebuild the indexes, as needed. (Which is the answer to your question, but we really want to avoid damaged indexes in the first place.) You can also perform a limited version of the Recover command that rebuilds just the indexes. Either option requires you to unhost the database first.

           

          There may be something I've missed, in which case I'm sure someone more cognizant will chime in. Or at least I hope so.   

           

          HTH

           

          Mike

          • 2. Re: Detecting/repairing bad indexes
            Stephen Huston

            I've also noted peer-to-peer-hosted files which had indexing problems, most probably caused by lost client connections during data-entry, which is really just another variant of your #1 - files being shut down abnormally. Just another reason to discourage peer-to-peer.

            • 3. Re: Detecting/repairing bad indexes
              gdurniak

              Most "real" databases offer a utility to verify indexes. FileMaker has none

               

              I have seen this on and off,  for years,  and collected references here:

               

              http://www.fileshoppe.com/recover.htm

               

              Bulk operations,  e.g. repeated imports,  and deletes, can be problematic

               

              Which version are you running ?

               

              Note: Save as Compacted rebuilds the file's block index, but does not re-index fields

               

              If you use FileMaker as a SQL Datasource,  you could try running CREATE and DROP Index on the fields in question

               

              greg

               

              > Is there some way to force FMP to recalculate all the indexes? Or is there at least some way of detecting this problem that doesn't rely essentially on chance?

              • 4. Re: Detecting/repairing bad indexes

                What you must know about indexes:

                It's all been said and done at <http://fmdiff.com/fm/recordindex.html>

                 

                Winfried

                 

                --

                Huslik Verlag GmbH • Bgm.-Widmeier-Str. 42 • 86179 Augsburg, DE

                CEO Winfried Huslik - HRB Augsburg 12386 -  VAT-Id. DE127485099

                Phone +49 821 565606, Fax +49 821 565001, Email info@fmdiff.com

                Verify your FileMaker Pro files with FMDiff - http://fmdiff.com

                Linkedin: http://de.linkedin.com/pub/winfried-huslik/2/505/1a1/

                • 5. Re: Detecting/repairing bad indexes
                  coherentkris

                  Fixing the indexes will help get the file usable again to varying degrees. As suggested by Mike et al dig into what conditions caused the index manfunction in the first place (root cause analysis) and fix what you find. It is infrequent, but not impossible, that FM indexes break in FM server hosted files without some outside influence.

                  • 6. Re: Detecting/repairing bad indexes
                    anothersmurf

                    Server (version 13, since someone asked) is stable, no unexpected shutdowns of the server or of DBs. I've noticed the index failures in two different (but related) DBs, and not in the table that gets the most churn (delete all/import new data). My "gut" says that there's a single cause of the several bad indexes I've found; I was thinking maybe a client lost connection to the server in the process of editing something.

                     

                    I've never used Save Compacted Copy; is that something that should be done periodically, and if so how often is recommended?

                     

                    For now, the Recover options sounds like exactly what I need. Thanks for your help!

                    • 7. Re: Detecting/repairing bad indexes
                      Mike_Mitchell

                      Save as Compacted should be done at a frequence based on how much use the database gets and how much data manipulation goes on in it. So there's no "rule" that tells you how often. Generally, every 6 - 12 months for most applications is adequate, but YMMV.

                       

                      Yes, a user can, in some circumstances, cause index damage if a connection is interrupted during a record save. However, that generally can only affect a single record (unless you're running some sort of transactional system or the equivalent, like multiple edits to a parent record and several related records that all try to commit at the same time).

                       

                      Another cause I forgot to mention: Modifying database schema while users are in the database. This one can cause a lot of damage if you, for example, make a change to a calculation field in a large table while users are running scripts that affect that same table. (Voice of experience.)   :-/

                       

                      Mike

                      1 of 1 people found this helpful
                      • 8. Re: Detecting/repairing bad indexes
                        gdurniak

                        Save as Compacted won't help here

                         

                        However, if the "churn" resulted in much free space, your file may get much smaller

                         

                        greg

                         

                        > I've never used Save Compacted Copy; is that something that should be done periodically, and if so how often is recommended?

                        • 9. Re: Detecting/repairing bad indexes
                          Mike_Mitchell

                          Greg -

                           

                          I’m not so sure. I’ve had experience where value lists failed to report all the values in a table and Save As Compacted fixed the problem.

                           

                          I know the literature says that’s not supposed to happen. But I watched it happen. So …

                          • 10. Re: Detecting/repairing bad indexes
                            gdurniak

                            Yes, if the file's Block Index is scrambled, anything can happen. FileMaker actually gets lost

                             

                            And by rebuilding the file, Save as Compacted can even make records not properly deleted reappear

                             

                            The only downside to Save as Compacted is that it can result in more fragmentation, but that is a discussion for another thread  :-)

                             

                            greg

                             

                             

                            > I’m not so sure. I’ve had experience where value lists failed to report all the values in a table and Save As Compacted fixed the problem.

                             

                            I know the literature says that’s not supposed to happen. But I watched it happen. So …

                            • 11. Re: Detecting/repairing bad indexes
                              Mike_Mitchell

                              Yeah, I wasn’t going to go there either …  

                              • 12. Re: Detecting/repairing bad indexes
                                tesla51

                                While, I'm very often hard towards the filemaker platform, I find that I haven't encountered many indexes problems since all those years I've been using it to make databases.

                                 

                                Take 4D aci database for instance which has an utility index tool and so many more bells and whistles, I've never used a more buggy and unrealiable database platform (may be Access I don't use due to its file corruption) due to all the indexes corruption... Unbelievable...

                                 

                                Filemaker here rocks even though sometimes a problem can sometimes appear.

                                Good Luck, I hope you can solve your problem.