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 188.8.131.52, 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 184.108.40.206, 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 220.127.116.119, 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.
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.
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 18.104.22.1689 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.
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.
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.
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 articleAnswer ID: 6996Conditions 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 hostHow to use the file :File is here0. Host the files on Filemaker server 11 (11v5 also)1. Change the Global ky field with the pop-up menu2. Press "Normal Script" look at found count3. Press "Woraround Script" look at found count. It may be different.Correct found count are for Global key value3 - > 19314 -> 195- > 18What the script does is to do a search where the calc field is ≠ 1So 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 sidePlease 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 blankI reported it as a bug Unstored calc field search unreliable if file hosted by server