We have built our own Maintenance Management System in Filemaker Pro (Server 13, Go and Pro Advanced 13 Windows clients). This database manages work orders for three sawmills and about 35 Millwrights. There are a total of 18,100 records in the Work Order Header table at the moment (current and archived Work Orders). We have a dashboard screen which is comprised of multiple ExecuteSQL queries that evaluate the Work Order Header table entries, and is driven dynamically by data selected by end users. For instance, they can look at the data on this dashboard for their facility and one specific department within their facility, simply by selecting drop-downs.
Yesterday, the ExecuteSQL queries that generate the dashboard started failing for one particular facility and department combination. So far, all others are fine. Troubleshooting has narrowed down the error to possibly one particular record, but there's nothing remarkable or obviously broken about that record.
The record in question was found by repeatedly running queries that worked, using the record creation date as a constraint. As we moved the date constraint back from "wo_entered_on >= '2/26/2015'" to "wo_entered_on >= '2/25/2015'", the query stopped working. In ExecuteSQL, there is no error displayed - just a ? where the data should be. Using the technique outlined here, we also do not get an error - just blank.
So, we turned to ODBC to see what we would get, and the results got very interesting, though no more clear. Again, we got records when the wo_entered_on was set to 2/26/2015, but as soon as we moved to 2/25/2015, we got the following from the ODBC client (SquirrelSQL, using the JDBC driver):
Error: [FileMaker][FileMaker JDBC] FileMaker error
That error code is way beyond anything I have found in any Filemaker reference or elsewhere online. We are trying to avoid deleting the record, as there are related (child) records that are associated with it, as well as other records that are chained to it (it's a preventative maintenance entry, so is repeated every month). In addition, it's not entirely clear that eliminating the one record we found that is dated 2/25 will solve the problem - though it does appear as though we can query "around" it by changing the date. We have changed all of the fields used to limit the query (facility, department, status and wo_entered_on) to attempt to eliminate the error record from the query results, and the query still fails. All four fields being queried are stored with full indexes in the database.
Of course, deleting the record will also not answer the question "what happened?" - asking the question here is an attempt to find out what we might be overlooking. Obviously, we don't want this to happen again - though this is the first time it has happened in over a year of using this particular solution.
It's important to note that no other constraint combinations are failing for this query. This one particular combination of facility, department and create_date is the only one producing errors. We can query with other valid facility/department combinations (even eliminating the date constraint) and get accurate results with all of them.
The query being used to test is:
where wo_closed_flag = 'Open' and
wo_facility = 'Pittsfield' and
wo_department = 'Sawmill' and
wo_entered_on >= '2/25/2015'
When the above is executed, the query fails with the error noted. When the date is changed to 2/26/2015, we get results. When the date constraint is eliminated, the query fails as well.
This query has been replicated using FileMaker Find Mode (without the date) and produces 59 records - including the one that appears to be causing the error for SQL. There is only one record with a "wo_entered_on" timestamp of 2/25, and when we query "around" that date, the only serial number (wo_id) that does not appear in the results belongs to the record in question. The wo_entered_on field was only used in this query to attempt to isolate the bad record. The queries that are failing in the production environment are not using the wo_entered_on field. The above is the minimum amount of constraint that can be applied to isolate the records that may be having issues.
Thanks in advance for any suggestions.