9 Replies Latest reply on Nov 12, 2008 1:24 PM by swc

    Out of Memory Error: FMP - MySQL integration.

    swc

      Title

      Out of Memory Error: FMP - MySQL integration.

      Post

      We want to dump some data from FMP into a MySQL table that is used for reporting by other applications.


      To do this we:
      1) Add an External Datasource that connects to the MySQL database.
      2) Create a table occurrence that is bound to the MySQL table.
      3) Write a script that loops through all the existing fmp records that we are interested in and does the following:
      ---- Create a new record in the MySQL table occurrence.
      ---- Set the fields.
      ---- Commit the record.

      The table is relatively small, maybe 10 fields.

      The error we are encountering is that when we attempt to run the script, FMP throws an error that it is out of memory, after getting through only 4000 records or so (of 25000).

      This error only happens if we create the records in the MySQL database directly. If we write to a temporary fmp table, and then import from the temporary fmp table into the MySQL table via the import records script step, the script runs successfully. Unfortunately, we want to schedule this to be a nightly task on our fmp server, so import records is not a viable way to approach this. We need to be able to create the records directly in the MySQL table via the fmp table occurrence.

      Anyone have an idea as to why this would be happening, and how we could resolve it? We're using the lastest MySQL odbc drivers. MySQL 5.0.44. Fully updated FMP9. 








        • 1. Re: Out of Memory Error: FMP - MySQL integration.
          swc
             No one has any ideas here?
          • 2. Re: Out of Memory Error: FMP - MySQL integration.
            TSGal

            swc:

             

            Sorry.  No immediate answer here.  I'm checking with some other people here for more information, and see if any problem like this has been reported.  I may not have an answer today, but I'll let you know as soon as I hear something.

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: Out of Memory Error: FMP - MySQL integration.
              TSGal

              swc:

               

              Assuming it is a problem, can you program a workaround to update 3000 records at a time?

               

              Just a thought...

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Out of Memory Error: FMP - MySQL integration.
                swc
                  

                Here is a little more information.  We added a catch in the script to report the Get(LastODBCError), and what we are getting back is:

                  Error [MySQL][ODBC 5.1 Driver ] Can't connect to 'ip.address.here'(10048)

                 

                Since we know the connection works in general, as it processes the first 3900 records fine, I am wondering if it may be a problem with the number of open connections.  When we try to insert each of the records, how many connections does FMP try to create?  If it is creating a connection per N records, then maybe that is causing the problem, if we are processing records faster than the connections are being released?

                 

                Also, are there any flags that ought to be checked in the MySQL Connector options/flags portion of the DSN definition?  These are largely dependent on the client using the ODBC connection, which is FMP in this case.

                 

                Additional insight welcome. 

                 

                Thanks!

                 

                • 5. Re: Out of Memory Error: FMP - MySQL integration.
                  TSGal

                  swc:

                   

                  Thank you for the additional information.  I have sent your posts to our Software Quality Assurance (Testing) department for more information about the inner workings of the ODBC driver.  I will contact you when I hear more information.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Out of Memory Error: FMP - MySQL integration.
                    TSGal

                    swc:

                     

                    Here is the latest information I received...

                     

                    ============== 

                     

                    The problem is not with FileMaker.  Please see:

                     

                    http://bugs.mysql.com/bug.php?id=6580

                    http://bugs.mysql.com/bug.php?id=10498

                     

                    Solution: Add parameter to registry
                    HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters\

                    TcpTimedWaitDelay set to DWORD 30 (decimal) 
                    MaxUserPort set to DWORD 65534 (decimal)

                     

                    ===============

                     

                    I hope this helps.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Out of Memory Error: FMP - MySQL integration.
                      kalle_samuelsson
                        

                      Hmm.. If i´m not mistaken I can see 2 problems with what you describe. Feel free to correct me if I´m wrong here!

                       

                      1: Aren´t we suppoed to use the 3.51 ODBC driver. Since the 5.1 driver was a beta when FileMaker 9 was release I have been told to use 3.51 (and still do). As I understand it using 5.1 does not add any new functionality to FileMaker External Datasoruces?

                       

                      2: The reason you communication fails is becouse you send the "Commit record" command after each record. As you suspected this uses up all sessions/threads in MySQL in a very uneffective way. A new session is created for each record without clearing the old ones. It  has been a problem since waaay back =D

                       

                      On a brighter note there is a simple workaround.

                       

                      You can create a type of "transaction" based soultion. Create a layout and in the "Layout Setup" uncheck the checkbox "Save records automaticly". On the layout, create a portal using a relationship to your External SQL datasource. The layout in FileMaker should use data from a FileMaker table, and the portal should show you a MySQL table. Make sure you are allowed to create data via the portal.

                       

                      Then rewrite your script to loop trough the records you want to export to MySQL, add each record as a new portal row but DO NOT commit the record! It is when you commit the record the the data in the portal will be sent to MySQL. When you have looped trough your records and created all of the portal rows, then you can commit the record.

                       

                      I don´t know if there is a max limit to how many records you can create at one time. But it you have 25000 records to move, make them batches of 5000. This will result in only 5 connections to MySQL.

                       

                      Hope this works for you (and that I´m not flamed if I´m not 100% correct)

                      • 8. Re: Out of Memory Error: FMP - MySQL integration.
                        swc
                          

                        TSGal wrote:

                         

                        The problem is not with FileMaker.  Please see:

                        http://bugs.mysql.com/bug.php?id=6580

                        http://bugs.mysql.com/bug.php?id=10498

                         

                        Solution: Add parameter to registry
                        HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters\

                        TcpTimedWaitDelay set to DWORD 30 (decimal) 
                        MaxUserPort set to DWORD 65534 (decimal)

                         


                         

                        Thanks for the suggestion.  I disagree that this isn't a FileMaker issue.  In the items you quote, the MySQL -server- running on Windows is running out of ports because of too many client connections.  This makes sense, as a server could very easily be serviceing thousands of clients within a short timeframe.  The error I am seeing is on a -client- not the server.  There is no reason for a client to open 5000 connections to a server.  It just doesn't make sense.  At a minimum, FileMaker ought to make a small connection pool, and share the connections in the pool.  

                         

                        This was what was causing the issue, however, so thank you for pointing us down the correct path as far as identifying what was causing the issue.

                         

                         




                        • 9. Re: Out of Memory Error: FMP - MySQL integration.
                          swc
                            

                          KalleSamuelsson wrote:

                          Hmm.. If i´m not mistaken I can see 2 problems with what you describe. Feel free to correct me if I´m wrong here!

                           

                          1: Aren´t we suppoed to use the 3.51 ODBC driver. Since the 5.1 driver was a beta when FileMaker 9 was release I have been told to use 3.51 (and still do). As I understand it using 5.1 does not add any new functionality to FileMaker External Datasoruces?

                           

                          2: The reason you communication fails is becouse you send the "Commit record" command after each record. As you suspected this uses up all sessions/threads in MySQL in a very uneffective way. A new session is created for each record without clearing the old ones. It  has been a problem since waaay back =D

                           

                          On a brighter note there is a simple workaround.

                           

                          You can create a type of "transaction" based soultion. Create a layout and in the "Layout Setup" uncheck the checkbox "Save records automaticly". On the layout, create a portal using a relationship to your External SQL datasource. The layout in FileMaker should use data from a FileMaker table, and the portal should show you a MySQL table. Make sure you are allowed to create data via the portal.

                           

                          Then rewrite your script to loop trough the records you want to export to MySQL, add each record as a new portal row but DO NOT commit the record! It is when you commit the record the the data in the portal will be sent to MySQL. When you have looped trough your records and created all of the portal rows, then you can commit the record.

                           

                          I don´t know if there is a max limit to how many records you can create at one time. But it you have 25000 records to move, make them batches of 5000. This will result in only 5 connections to MySQL.

                           

                          Hope this works for you (and that I´m not flamed if I´m not 100% correct)


                           

                          Thank you very much!  Reason (1) does not appear to affect anything.  The new odbc driver just adds support for additional features, and some optimizations.  For the sake of completeness, I did try reverting to the 3.51 driver, but with identical results.

                           

                          Your workaround successfully got us past the point where we were encountering issues.  We set up the script to commit each 1000 records via the portal, and have not had a problem doing it that way.  

                           

                          This was a big help.  Thanks very much!