My SQL Program Connection Problems

Hello guys

I have a recently hosted FileMaker system set up that asks an external MySQL system for all records in Table X not already marked "processed"  which means that there is no value in the processed column and then loops through them, writing column values back to our other system and then marking those records with a timestamp in Processed.

Actually it is done by updating matching records and creating new records for those whose matches do not exist. An ideal set of unprocessed records would be 175000 records out of a few million total in the MySQL table and It gets updated by an external process.

Microsoft ODBC 3.51 driver for MySQL is actually the source for connecting our system to the MySQL database, and the connection in works fine generally. The loop starts off with a set of corresponding non-processed records and begins looping through them, writing out to our native system but at some point we get a "Can't connect" error. The screenshot of the error is as follows:

FileMaker Pro

ODBC Error: [MySQL][ODBC 3.51 Driver]Can't connect to

MySQL server on 'mysql.lynx.local' (10048)

[Microsoft][ODBC Driver Manager]Driver's

SQLSetConnectAttr failed

                                                        OK

I personally administered the MySQL server and had an extensive look for checking the limitations on the number of allowable connections and found that the only place where any such option is mentioned is clearly set to NO limits of that sort.

The MySQL database actually runs on a different server, local to the same internal network as FileMaker Server. I know that now days people have been doing a lot of this on virtual machines and virtual servers running on high end boxes. The two servers are actually virtual boxes on the same hardware box or instead are virtual boxes on separate hardware boxes. I don't know if the two servers are on the same Subnet. I just could not find the solution to either question. If someone has any idea please do let me know. I'll be really obliged.

Actually FileMaker is handling the MySQL table as an ESS (External SQL Source). The script starts searching for records matching the parameter where Processed are treated as null and then treating that Found Set as an object. Then it fetches values from it and writes back to it within the loop, caching all of it either small found sets or large chunks of it into RAM and flushing changes back to disk as needed. It also fetches additional large chunks according to the need, rather than assembling an array of results such as a temporary table and performing the operation on this in its entirety.

This may be relevant to explaining (on one level) processes of connection as they occur within the loop i.e., the connection error could be occurring exactly when another huge chunk of as-of-yet-unprocessed rows are being fetched, or it could be occurring when a huge chunk of cached write backs are being flushed back to the table translated into insert queries but doesn't explain why either type of transaction should be giving an error.

The problems that I could think that are creating something wrong and resulting in the error are listed below. Please feel free to give your opinions and suggestions as I might be wrong.

•  Is there any specific parameter within MySQL, at the administrative level, that is likely to play a role in this error. If yes, what specific language can I use to instruct my client on what to look for to modify or disable it?
• Is there any process or setting on the network level that would play a role here? I can't think of any beside from a general vague notion of some process that might be delaying network traffic long enough to make something time out ?
• This driver I am using is  version 3.51. I've heard of newer drivers from Microsoft. A friend of mine told me about version 5.x driver. There may or may not also be high-quality 3rd party drivers from some driver vendor . Should I forget the fact that the driver might be the reason for all this mess?
• I have thought about using PHP to query the SQL system and then write back to the FileMaker system, but that would be a process different enough that I'm not sure it helps me troubleshoot. I suppose it has merit as a permanent solution, but it's a lot of extra overhead. At the moment I'm more focused on making the ESS approach work, but any suggestions for external methods that may help pinpoint the problem (as opposed to a 3rd environment approach to transferring the data altogether would certainly be useful and helpful.

I have tried to write my problem with complete details. If anyone needs to know anything else regarding the issue, please do let me know. I'll be waiting for your feedback.

Thanking you loads

Waiting for your suggestions

Kind regards