Next time it happens, you may try to rebuild the index for the field. (It sounds like an indexing issue, make sure no one is in the file when you do it.) That should fix the find but, if something is going on that corrupts the index it will happen again. (had a similar problem caused by FileMaker 8.0v1, before the first revision but have heard of other things that can cause it.)
@Martin: I am aware of the available update to FMS 10 but looking into the changelog I cannot find anything addressing my problem or similar. I will install the update and will see if this solves the problem but in the meantime can anyone suggest if 10.0.2 deals with this kind of issue and if it's not stated in the changelog available to the public?
@WoodApple: I thought that the recovery tool would rebuild all indexes. There is an option that does just that under the advanced recovery options. However, I forgot to mention that the calculation is unstored therefore there is no index on that field.
Any other ideas?
Thank you for your posts.
It is difficult to determine where the problem lies. However, you mentioned that the find doesn't work locally, so just to be sure, I want to make sure there are indeed records that satisfy the Find.
Assuming this is the case, I would like to see the file so I can determine what is causing the find to fail. I have sent you a private message (top of this page - right side - X Messages) with instructions where to send the file.
Thank you for your response.
I actually wrote that when the file is opened locally, the find always returns correct results. It is only when the database is shared under FileMaker Server 10 that it suddenly fails to produce results. I even closed the database and copied the affected DB on my desktop, and I verified it would just work. It would also start working again if server was rebooted, that is, for another 20 days, more or less.
I would like to give more background on the structure of the find:
The find is performed on an unstored calculation field which is the result of an algebric sum performed on a related table of movements. The field basically displays the amount of stock held for a particular product in different warehouses, at a given date, real-time. As movements are added the stock quantity will update immediately. It's on this field that I perform a ">0" find to identify all articles that are currently in stock and for many other purposes. There will always be records that will satisfy this criterion.
I have read that finds on unstored calculations may yield unexpected results when performed in a server environment but in my case, I never had any problems for over 2 years and the structure of the file has remained basically unchanged.
Over the holiday period, with the business closed, I installed the v10.0.2 update. Server was restarted on 7th January and so far I'm not receiving any reports on this problem occurring. But I guess it's still early to say whether the update solved the problem.
Will keep you informed
OK, problem has resurfaced again. Server was last rebooted on 7th January and FileMaker server 10 is up-to-date.
Also, I'm seeing this problem on other calculated fields within the same file. This time the calculations are much simpler, simple straight sums. Numbers show up correctly on video but finds always fail no matter the criteria entered.
I need a solution to this. I don't have available clones because the issue is nearly 4 months old. I could rebuild the file from scratch but I have no guarantee it will solve the problem if it's server related.
"simple straight sums" -- on a stored, indexed calculation or unstored calculation or summary field?
Have you tested these fields in a single user environment and confirmed that they work consistently in this situation just like the unstored fields you originally reported?
Just trying to help get a fuller picture of the problem.
I received your files. Thank you.
Since this takes 20 or more days to exhibit this behavior, I have forwarded this information to our Development and Software Quality Assurance (Testing) departments and have them try to reproduce the problem. I will report back when I hear back from them.
The fields in question are unstored Calculation fields based on Summary fields (Total of). That is, Calculation field = <Related Table::Summary field>
Yes, even these fields are unstored calculations. They sum row totals on a related table. Much like you would calculate an invoice total on the fly.
I've done the following:
1. Closed the shared database;
2. Copied that very database from the sharing folder of FileMaker Server to my macbook pro with snow leopard. Opened the file locally, performed same find and it works. Times and times again without problems.
3. Put back that file on FileMaker Server, reopened the database from server admin, logged in, tried find and it fails, all the time. Other unstored calculation fields I can search and get correct results. I haven't rebooted the server yet but will be forced to do it this week as it is causing problems to users.
This can be seen both on mac clients and windows clients connecting to the mac server.
What is peculiar is this:
On the same file I have another unstored calculation field which tests the very fields that are failing with finds. Very simply it displays a 1 if there's a value (either positive or negative) on at least one of the unstored calculation fields with problems, or a 0 if no value is present.
Finds on that unstored calculation field work like a charm, and have done so for years. Employee at this company would normally perform a query via a script on this field on average once every 5 minutes. It never fails.
I have sent my files to TSGal with instructions on how to reproduce the problem, but I am the first to think it's going to be very difficult to track it down as it appears to be happening only on this server (I have several installations of the same DB at other companies but none have been reporting problems so far using Server 10 either under mac os x or windows server).
It normally takes 20 seconds for the find to return results (this is normal) but when it fails, FileMaker instantly says: "no records match". It's like it's not even attempting to look into the DB.
I'll be happy to give more background information on this if anyone needs it.
At this time, run Activity Monitor to get an idea of what is currently running on your server. After you reboot, run Activity Monitor again. This may provide a clue to the problem.
In addition, you can run Terminal and enter "top", and this also gives a listing of everythingrunning.
Is there anything in the server logs that provides a clue?
"...it appears to be happening only on this server (I have several installations of the same DB at other companies but none have been reporting problems so far using Server 10 either under mac os x or windows server)."
That might just be a clue. Since this works on other installations perhaps you could check for possible file corruption. I'm speculating here that file corruption may keep your file from correctly generating an index on the unstored fields when you perform the find. The puzzler here is why this wouldn't also affect your file's behavior when you take it down off the server and test it.
Get a clone of your file from one of the sites where this problem has not occurred and import your data into it and see if it exhibits the same issue.
You could also take a back up copy of your file and recover it to see if the recover operation reports any problems. You could then put the recovered copy up on the server and see if it shows any differences in its behavior. I wouldn't put the recovered copy into regular use, but the test might at least tell you whether file damage might be the cause here...
Hi TSGal thank you for your response.
I have done what you said, I have compared services running before and after reboot. They are the mostly the same. This is a dedicated server that only runs FileMaker Server 10 and nothing else is performed on the machine.
It's a Mac Pro 2 X 3 Ghz Dual-core intel Xeon with 9GB 667mhz DDR2 FB-DIMM ram
Mac OS X 10.5.7
Only errors / warnings I can see in the system logs related to filemaker services are these:
Fm-Server /Library/FileMaker Server/Database Server/bin/fmserverd: dnssd_clientstub read_all (35) failed 0/28 0
and 2 minutes later:
localhost com.apple.launchd (com.filemaker.fms): Unknown key: ServiceDescription
These were logged when server was restarted. Additionally, I can see them as far back as 4th November 2009, around the time finds started to fail. There is nothing else that I find relevant that happened at that time.
I am not seeing these log entries on my development server which is running OS X 10.6.2 and FileMaker Server 10 Advanced
FileMaker Server logs are not reporting any problems except an old unrelated DB that is not passing consistency checks and that is no longer used. I should take that off their server anyway.
By the way, now that I have restarted the server, my finds are working normally.... I'm sure in less than a month they will fail again.
Yes I have checked for file corruption as soon as this problem came out. Nothing wrong was found by the recovery procedure. The same file was put back on the server and would still show the same symptoms until restart.
I am actually going to write a script that will perform the failing queries every night and have it send me an e-mail when any of them fails so at least I can identify the exact day this happens and look into the logs.
I am also going to share a copy of the same DB on the same server but this one will not be touched by anyone for the next days. As soon as problem arises I will compare finds on the production file and on the other file and see if there are any differences in behaviour. This may tell if activity on the file is a factor.