I have a hosted FileMaker system set up that queries an external MySQL system for all records in Table X not already marked “processed” (null value in Processed column) and then loops through them, writing column values back to our other system (updating matching records, creating new records for the ones that don’t already have matches) and then marking those records with a timestamp in Processed.
A typical set of unprocessed records would be 175000 records out of a few million total in that MySQL table. (It gets updated by an external process).
Connection from our system to the MySQL database is via Microsoft ODBC 3.51 driver for MySQL, and the connection in general works fine. The loop starts off with a set of relevant / 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:
http://home.earthlink.net/~ah3files/Stuff/ODBC%20Error.jpg
I don’t personally administer the MySQL server but my client has looked it it extensively in response to my instruction to check for limitations on number of allowable connections, and says the only place where he’s found any mention of such option it is clearly set to NO limits of that sort.
The MySQL database runs on a different server, local to the same internal network as FileMaker Server. I know they are doing a LOT of this on virtual machines (virtual servers running on high end boxes). I don’t know if, in the real-life sense of sticking your arm out and whacking the server within the rack mount, the two servers are actually virtual boxes on the same hardware box or instead are virtual boxes on separate hardware boxes. Don’t know if the two servers are on the same subnet. Could find the answer to either question if relevant, and to others if I knew what to ask.
FileMaker is handling the MySQL table as an ESS (External SQL Source). The script performs a Find (equiv of a SELECT query) for records matching the parameter (Processed being NULL) and then treating that Found Set as an object, fetching values from it and writing back to it within the loop, cacheing all of it (small found sets) or large chunks of it (large found sets) into RAM then flushing changes back to disk as need be, fetching additional large chunks also as need be; 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 writebacks are being flushed back to the table translated into INSERT queries) but doesn’t explain why either type of transaction should be erroring out.
a) Is there any specific parameter within MySQL, at the administrative level, that is likely to be playing a role in this error, and if so, what specific language can I use to instruct my client (or his house staff IT people) on what to look for to modify or disable it?
b) Is there any likely process or setting on the NETWORK level that would be likely to be playing a role here? I can’t think of any (not being a network geek) aside from a general vague notion of some process that might be delaying network traffic long enough to make something time out — ??
c) This driver is version 3.51; I’ve heard mention of newer drivers from Microsoft (someone mentioned a version 5.x) driver. There may or may not also be high-quality 3rd party drivers from some driver vendor (on the Macintosh platform I often use drivers from Actual Technologies; I don’t have any familiarity with the world of 3rd party drivers for Windows boxes). I figure I would learn something / rule out the driver as the culprit if someone could recommend an alternative driver. Suggestions?
d) 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 (I have to write it and then something has to be set up to execute it regularly and it leaves us with yet another codebase we have to consider whenever debugging). 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.