An ODBC connection to MySQL slows down Filemaker database randomly

[dataapex]

Very strange and bothering behavior appears randomly in our company FileMaker (FM) system since we have linked it to another DB (MySQL).

Occasionally when we access a FM layout with a table which is connected to an external MySQL DB a dialog "Find in progress" accompanied by cup of coffee appears.

That process is not possible to interrupt - there is Cancel button which disappears after click on it but the process continues for 1-3 minutes and whole FileMaker is being blocked.

Our system consists of:

- FileMaker Server Advanced 11 (it happened at version 10 as well) and 25 FileMaker clients (versions 9-11, some of them Advanced) all running under Windows OS

- MySQL database version 5.xx

- MySQL ODBC driver 5.1

The interconnection is made between a simple MySQL table of registered web users (Web users table, 3000 records) and FileMaker table of customers (Customer table, 30 000 records).

It happens mostly (only?) if we access FM Customer table through related FM Companies table (each customer is related to respective company using company ID). It means if we make a selection of customers.

It does not happen when we access Customer table directly by displaying respective layout without changing of set of selected records.

1. It happens randomly a couple of times a day

2. It seems it happens only after the Customer table at the FM client is 10-20 minutes without of any activity, but mostly does not happen for hours.

It means it is not possible to repeat this annoying behavior within next 10-20 minutes by any way, sometimes even for hours.

3. Sometimes it happens at more than one client at one time, but not always. It means at the same time one user can do this action and second has to wait for 1-3 minutes.

4. An operation made directly in FM layout displaying MySQL Web users table is mostly swift and even one client has to wait due to "Find in progress dialog" another can work in this table smoothly.

5. It seems this behavior does not rely on possible changes of records in MySQL DB. It means regardless of we edit or not records in MySQL this problem appears independently.

There is a simple relation between Customer table and Web users based on email field matching.

There is no calculated field in FileMaker Customer table based on any MySQL field.

It looks like a problem at FileMaker client side, but we can't find any description or explanation of what might be a reason of such behavior.

[NYPoke]

Nasty kinds of problems to have. Better when they are consistent.

Two obvious possibilities:

1) You might need some more caching on the said clients. If all clients have the same cache setting, this probably isn't the problem.

2) The machines in question could be older. We had to upgrade some machines, with Version 11. They weren't 486 machines, but they were 5-7 years old. We didn't get brand new machines, just found some that were newer (1-2 years old). Worked magic.

Getting ready to tie in SQL Server to Version 11. I've seen some of these delays on my testing.

I had to make some SQL Server Adjustments, but your setup shouldn't need any MY SQL changes.

Good luck.