External database records are being 'skipped'

[brian rich]

We've got a Filemaker 'robot' applications which is used to transfer staff holiday information from a mySQL database into a Filemaker database, where the information is used for scheduling and planning. The robot users a Filemaker Client 11.0.3 (running on Windows XP Professional) which is triggered by a Doscript plug-in (legacy reasons) to run every morning at 2:00am. The database is hosted on an FMP11 Server Advanced on Windows Server 2008. The robot uses an ODBC connection to the mySQL server, and a number of the mySQL tables are exposed via ESS in the Filemaker database.

 

Every night, the robot makes local copies of these tables in the Filemaker application by running a scripted find process to select the relevant records in the mySQL table, clear the local tables,  then step through the found set in the mySQL table and create a new record in the local table though a parametised script. There is no conditional processing, each mySQL record should have a matching record in the local table.

 

This has worked well for over two years, but we recently found that some holiday data had gone 'missing'. Further investigation showed that the data was missing because the local table had two records fewer than the mySQL table - for some reason they were 'skipped' by the script that creates the local record. This does not seem to be a once-off issue, the same two records have been skipped several nights in a row.

 

Can anyone suggest why this might be happening, and what the cure might be?

 

Thanks

 

Brian

[bcooney]

Can you create an error log? Perhaps your process will throw an error that you can write to the log. I've re-read scripts a million times and they "looked fine," only to not catch an unexpected data issue that I wasn't handling.

[brian rich]

The problem has disappeared! 

 

The server was restarted over the weekend as part of a planned outage.   Now I'm really puzzled. Is there some kind of maintenance that should be carried out when you are using ESS?

 

I'll think I'll install some error logging and see if that shows anything obvious.

 

Thanks

 

Brian

[bcooney]

If you do, please post results.