6 Replies Latest reply on Apr 12, 2013 4:10 AM by VincentL

    Strange Find Results (FMSA 11)



      Strange Find Results (FMSA 11)

      Your post


      Having a strange problem in one of our databases that is hosted on a Mac Server OS X 10.5.8 Xserve. After the server has been up for a month or so, it starts giving us wrong found sets, when searching some of the unstored calc fields. Restarting the server solves the problem (closing and reopening the file does not). If memory servers me correctly (which if often doesn't) FMSA does the number crunching prior to the search on any non-stored calc fields, if all the calc references are local to that file. Is the server getting loopy or is it the file. No errors in any logs btw. Win and Mac FMPA clients show same issue, until restarting of server of course. 

      Vince Dolan 
      Waymar Industries

        • 1. Re: Strange Find Results (FMSA 11)

          I realize that this is an old post, but we are experiencing this same issue on multiple FMSA11 machines.  Here are a couple of the servers that are affected:

          #1 - Mac OS X Server 10.6.3, Mac Mini aluminum, (single) Intel Core 2 Duo 2.66 GHz, 4 GB RAM.  FMSA, 800MB database cache, 00:01 cache flush distribution interval, no server plug-ins, only 18 hosted files.

          #2 - Mac OS X Server 10.5.8, Mac Pro (two) Quad-Core Intel Xeon 2.8 GHz, 10 GB RAM, FMSA, 800MB database cache, 00:01 cache flush distribution interval, no server plug-ins, Web Publishing enabled, ODBC/JDBC enabled, 82 hosted files

          So far, we have one server that seems to be unaffected:

          #3 - Mac OS X Server 10.6.4, Xserve (single) Quad-Core Intel Xeon 2.26 GHz, 6 GB RAM, FMSA, 800MB database cache, 00:03 cache flush distribution interval, no server plug-ins, ODBC/JDBC enabled, 11 hosted files

          So, it seems the server with the newest OS and Filemaker version has not yet been affected.  Yet.  I plan on updating the FMSA version on the other two servers, and see if the issue goes away.

          • 2. Re: Strange Find Results (FMSA 11)


            Thank you for your post.

            Please report back your findings after updating the other servers to FileMaker Server 11.0v3.

            You don't mention if FileMaker Server is ever restarted, or if the servers are ever rebooted.  Any other information you can provide may be helpful.

            FileMaker, Inc.

            • 3. Re: Strange Find Results (FMSA 11)

              Hi TSGal, thanks for noticing this thread.  We've experienced the issue only after the servers have been up for more than a month, so it will take awhile before I can be sure the update fixed the problem.  And just like the OP mentions, restarting FMS cures the problem.  In fact, on the server I updated, #1 in my previous post, I simply stopped FMS through the Admin Console, ran the FMS update, which automatically started FMS, and performing finds in the databases worked correctly.  So it definitely seems to be something related to FMS, and not specifically the OS of the clients or the server.

              • 4. Re: Strange Find Results (FMSA 11)

                Well, it didn't take long for the problem to come back on the server I updated.  The database affected has almost 220K records, and the calculated field having the issue is based on 4 self-related tables, and the calculation contains 4 simple condition checks:

                Case ( Count ( mail check 1::true_field ) = 1; 0; 1 ) & Case ( Count ( mail check 2::true_field ) = 1; 0; 1 ) & Case ( Count ( mail check 3::true_field ) = 1; 0; 1 ) & Case ( Count ( mail check 4::true_field ) = 1 or IsEmpty ( Email ); 0; 1 )

                The tables 'mail check 1' through '4' are the same table as the calculated field.  Each relationship is based on only one field - text, indexed, non-unique.  'true_field' is just a number field with the value 'true'.  By my standards, it's a pretty simple calculation, that should provide consistent results.  For the time being, I plan on replacing the calculation with an auto-enter field.

                • 5. Re: Strange Find Results (FMSA 11)

                       I have just discivered this thread and I think this may be the same bug I mentioned in my article at http://honza.24usoftware.com/filemaker-script-run-time-cut-from-5-hours-to

                       I ad a trouble reproducing the problem in a new file to be able to report it back to FileMaker as a bug which may mean I just did not keep the server running for long enough to reproduce it.

                       Now I see these are the conditions we had in common:

                  •           Finding in an unstored field
                  •           Performing the find on a file hosted on FileMaker Server
                  •           The unstored calc was based on a self-join relationship
                  •           The calc was set up to return boolean value (1 or 0 depending on relatively simple condition)

                       In my case I searched for records where the calc returned 1 and then I wa able to verify that:

                  •           The resulting found set contained records where the unstored calc field actually showed 0
                  •           The inverse found set contained records where the unstored calc field showed 1

                       Another thing I was experiencing was that even without modifying any data, repeating the find was not returning the same found set.

                       Not sure if the problem persists in FileMaker 12 as I generally avoid these kinds of searches completely since the time I discovered it, so I will appreciate if anyone can either confirmt the bug is still there or let us know it has been reportedly fixed in version XYZ.


                  • 6. Re: Strange Find Results (FMSA 11)


                         I've the exact same problem and it's very dangerous, it makes my reporting unacurate. I'm using 10.8.3 and FMS 11.0v5. The good new is that I discovered the reason for that bug, created a file that reproduces the problem 100% of the time (which I'd be glad to upload), and found a workaround.

                         But that workaround is not good, because it's slower, and it's almost imposible to updated every calc fields that can suffer from it amongst a huge solution. This is a major bug that needs to be fixed ASAP.

                         Here's the file full explanation which contains my observations :


                         This file is to illustrate a bug on unstored calc search. On client there's no problem the "Normal script" will work ok. If the file is hosted on FMS 11v5 search result will be unreliable : that's to say that regardles of the changes of the Global key field, the founcount will be the same. But upon restarting that erroneously "constant" founcount may vary (it'd be either the correct foundcout of one of the prevoius search).
                         So locally there's no problem, only if hosted on server which leads us to the Knowledge base article
                         Answer ID: 6996
                         Conditions that prevent those finds from being performed on the host include:
                         … An unstored calculation that uses a related field that is from a table in a different file.
                         This is a bad limitation in my opinion, because it deefats the point of that optimization for many solutions BUT from it we can get our workaround.
                         The workaround is this : include in your unstored calc a filed from another file to force Filemaker to do the search on client by preventing it to do it on host
                         How to use the file :
                         File is here
                         0. Host the files on Filemaker server 11 (11v5 also)
                         1. Change the Global ky field with the pop-up menu
                         2. Press "Normal Script" look at found count
                         3. Press "Woraround Script" look at found count. It may be different.
                         Correct found count are for Global key value
                         3 - > 1931
                         4 -> 19
                         5- > 18
                         What the script does is to do a search where the calc field is ≠ 1
                         So you'll see using the normal script on server when you get not correct found count, that amongts the foundset, instead of just 0 you'll find records with 1.
                         So there's no problem in the calc, only on the search done server side
                         Please note the terrible speed degradation of the workaround (from 9 sec to 25 on my setup), because of course the search is done on client.
                         File is unprotected if Login / pass asked  : Admin / leave blank