13 Replies Latest reply on Jul 31, 2013 4:57 PM by taylorsharpe

    ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit

    davidd

      We have been implementing a MySQL connection to our database with considerable difficulty.

      The supported version of MySQL Connector for FMSA12 is MySQL Connector 5.1.7

       

      However, when we installed this version on the server we found it has a reported bug which means you cannot configure the driver. This bug can be resolved by editing the registry (lots of fun), implementing what seems to be a fairly complex patch solution, or moviing to an unsupported later version. So we tried version 5.1.8, however, the connection seems to continually drop out making the Filemaker client accessing the system crash - this usually involves a Find dialogue appearing that cannot be cancelled. We found another entry in this forum which outlined success using version 5.1.10, which we have now installed.

       

      The result is better than the previous version but we are still experiencing similar problems as before. For example: if you log in to the system and go to the layout containing the MySQL records, you can find, sort, and change the odbc records without difficulty. This remains the case as long as you continue working with the data. If you leave layout open for say 5 minutes and then try to do something involving the MySQL data, a Find dialog window appears which says "Find In Progress... Processing query". This cannot be cancelled as outlined before. (in fact, I have one on my screen right now which I have left running for about an hour just to see if it ever finishes.) My guess is the MySQL connection somehow gets closed and Filemaker gets locked into a cycle of trying to re-establish the link but that is just a guess.

       

      An additional note:

       

      It turns out that after leaving the computer for about three hours, the Find dialog closes and the MySQL fields display the message Connection Lost.

      From my initial tests is appears a Refresh doesn't reconnect (ie Flush External Data) but syncing the table does.

       

      I think my issues are: understanding the connection/disconnetion process, how do you set the length of the timeout for the SQL connection and or set how long FM continues to try to find it (these controls dont appear to be in driver) and how do I make it automatically re-establish its connection (there is a setting for this in the driver but switching it on hasn't changed the problem).

      Any ideas?

        • 1. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
          mikebeargie

          http://www.actualtech.com/product_opensourcedatabases.php

           

          The MySQL driver from Actual Technologies is considerably more reliable for ODBC connections than the open source MySQL connector. At $34.95 it is a cheap investment.

          • 2. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
            davidd

            Thanks for that Mike, I will have a look and let you know how we go.

            • 3. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
              davidd

              Hi Mike, I've just had a look at the Actual Technologies site but it looks like these drivers are for Apple.  We are running Windows Server 2008 R2 and linking to MySQL on Linux.

              • 4. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                taylorsharpe

                I would be reluctant to edit the registry to make a driver work and would probably try a different driver.  The current version is 5.2.5 from http://dev.mysql.com/downloads/connector/odbc/#downloads 

                 

                If I continue to have these problems after upgrading, I would check and see if other programs are having the same issue.  Connect using a SQL tool that uses ODBC or Excel or Access and see if they have the same problem.  Basically, try to figure out if it is FileMaker or the ODBC driver that is the problem. 

                 

                FYI, I have not used ODBC connections except through FMS.  Is there any chance of trying it on a FileMaker Server?  ODBC via a service tends to be more reliable than via an application.  Also, is your FileMaker the latest upgrade?  12.0v4?

                 

                PS:  Using FMS will avoid the going to sleep issue you are having.  Also, you only have to set up the ODBC connection on the server and the FileMaker clients all use the same ODBC connection on the server instead of having to make an ODBC connection from each client to MySQL. 

                1 of 1 people found this helpful
                • 5. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                  mikebeargie

                  Ah, I didn't even think about that, but yes, they only make the mac installers.

                   

                  Taylor is offering you some good advice below, you might want to follow that route of trial and error with different drivers. Although it doesn't necessarily seem that your connection is broken, but rather that your configuration settings are off for disconnects and timeouts.

                   

                  In filemaker accessing ODBC before, I've done script triggers and script timers together to auto-logout any users that have been idle for a certain time period. This allows me to exit gracefully and get around some of the issues that ODBC represent.

                   

                  I'm not sure that privilege sets have anything to do with it, but have you made sure that your privilege sets are not set to timeout after a certain period?

                   

                  As for MySQL timeouts, there are some server side settings for timeout in the MySQL host config file. Usually people don't have access to that file, or can't modify it via command line.

                   

                  My last suggestion would be to check if this helps:

                  http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

                  Just as a detail of all the DSN settings.

                  1 of 1 people found this helpful
                  • 6. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                    davidd

                    Thanks for your response and trying another application may eliminate the network as an issue. As outlined we are running Filemaker Server Advanced 12.0v4 on Windows Server 2008 R2.   in relation to MySQL connector version, I have tried multiple including the latest.  the only reason we worked on getting version 5.1.7 installed successfully (which we did) was it is the supported version so we could deal with FM tech support. We are doing this now but the issue remains unresolved. To eliminate the client server connection from the equation (as a test)  we have run the FM client from the Server machine - the error was reduced but still remains.

                    • 7. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                      davidd

                      Thanks for the response and yes it does seem like timeout settings.

                      The biggest issue with the problem we are experiencing is being unable to reconnect in a 'clean' manner.

                      Once the Find dialog appears you're locked in, or in the scenario of the Coffee Cup icon - Filemaker simply says its not responding.

                      I do have access to the MySQL settings and I will look into the timeout settings there. Unfortunately the driver does not provide any control timeout except for Pooled Connections and this does not appear to have any affect.

                      • 8. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                        taylorsharpe

                        I assume the machine that the FMS is on is making the ODBC connection and not the FM client machine.  In other words, go to the FMS machine and ODBC connection and set up a System DSN from that machine.  Make sure through your FM client, opening the FMS hosted file remotely, that the external source references the MySQL System DSN entry on the server.  

                         

                        Unlike FileMaker client, most applications require the ODBC driver to be on the same machine.  So to use another application to test the ODBC on the FMS machine, you'll need to install those applications on the server.  You can install something like a SQL Query Tool (http://download.cnet.com/SQL-Query-Tool-Using-ODBC/3000-10254_4-10057486.html) or (http://sourceforge.net/projects/odbc-query-tool/) or Access or Excel.

                         

                        Is the MySQL database on the same LAN or out on the internet (WAN)?  There can always be networking issues. 

                         

                        The main thing you want to assure is that it is the FileMaker Server hosted database is making the ODBC connection and not FileMaker client.  Let us know how your testing goes and we'll help as best we can. 

                        • 9. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                          davidd

                          yes, the odbc driver is on the Windows Server machine which hosts FMSA 12.0v4.  The MySQL database is on our own linux web server and the two servers a connect via a VPN. I have been running network diagnostic software and the drop out rate on yhis connection is minimal.  There is occassional lag max around  400ms but an average response time of 32ms.

                          • 10. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                            taylorsharpe

                            OK, they helps.  I assume you're using MySQL 5.1 Community Edition.  It is the officially supported version even though other versions probably work as well. 

                             

                            One other thought, is the Find erroring out in an Import of ODBC data or in an ESS table in a layout? 

                             

                            Good job on testing the network and it being on the same LAN removes many of the networking concerns. 

                             

                            Is the DSN via a domain or direct IP?  If by domain, you might try direct IP to avoid DNS issues. 

                             

                            I know FMS 12 allows for more caching for things like SQL shadow tables.  What is the FMS cache set at in the Admin Console?  Can you try increasing it to the max and see what happens?

                             

                            And if you get a chance to check the other SQL tools through ODBC, let us know if they exhibit the same problems or not. 

                            • 11. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                              davidd

                              Yes, we're using MySQL 5.1 Community Edition.

                               

                              The Find Dialog box (where it gets locked) occurs on an ESS table layout (an importing process is not really suitable for this particular application - although if I cannot find a solution it may be necessary). There really doesn't seem to be too much consistencey to when it occurs.

                              For example, if I activate a script which goes to the MySQL data layout and creates a new record with the same data, it will work 19 out of 20 times then on one occasion it will just stop and the Find Dialog appears.  It's also the case that sometimes it just freezes with the coffee cup icon and says Filemaker Not Responding.

                               

                              The DSN is via a direct IP and the caching is set to 3999mb.

                               

                              I will look at the SQL tools and report back the outcomes. Thanks for you help.

                              • 12. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                                davidd

                                While we have not totally resolved this issue we have discovered things which make the connection fail - these may be helpful to others.

                                The ODBC failure only happens when we have more than one ODBC connection within a file and this failure seems more likely if there is increased latency on the network connection (ie where the FM database is on one server and the MySQL is on another). For example, in one file we had connections to multiple MySQL tables on the one server. While testing the connection, I setup a file with only one connection and this runs without issue. As soon as I add another connection the failure occurs. Also using MySQL data in portals seems to fail - the FM ODBC reference says " Note The ODBC and JDBC client drivers do not support Filemaker portals" and I guess that must include MySQL (but I may be wrong).

                                 

                                Therefore to make our system work, I have created multiple FM files used to make various ODBC connections. I have incorporated linked data into non SQL layouts on these files and this includes portals from the main database. To update SQL data I have incorporated scripts which jump to the SQL layout, insert and or extract data as required and then return to the non-SQL data layout.  Although more complex to manage, the system now works reliably without failure.

                                 

                                Hopefully, later versions will provide improved ODBC integration which at the moment seem a little lacking.

                                 

                                • 13. Re: ODBC/MySQL Filemaker Server Advanced & Windows Server 2008 R2 64bit
                                  taylorsharpe

                                  Interesting comment on ODBC not supporting use of MySQL data in portals.  I know FileMaker creates shadow tables and there must be some problem with how FileMaker wants to live update things in portals that doesn't technically work with SQL shadow tables.  Anyway, I learned something new to avoid and plan for when working with ODBC data.  Thanks for providing that comment.