Synching FM shadow table data with MySQL

[jjbong]

I am using FM10 as a presentation layer to MySQL, with generally good results. The problem I have is when I use the Execute SQL script step to do modifications in MySQL. What do I have to do to have the data appear in FM shadow tables?

When the Execute SQL operates on a small amount of data, a Commit Records and Refresh Window script step, the latter with flush cached join results, flush cached SQL data seems to work.

However, when the Execute SQL operates on a large amount of data (1000 rows, say), it doesn't work. If I refresh (manually, with a script that just does the commit and refresh), the changes appear within two tries. I'd like to be able to do something in the script so it is automatic. The only thing that does the trick is to pass the row count back from MySQL to FM, and then to loop doing commit and refresh, with .5 second pauses in between, until the count matches the Get(FoundCount). I'm suspicious of anything this non-deterministic.

Any ideas? I have a relatively small test case that demonstrates the problem.