7 Replies Latest reply on Aug 31, 2010 10:48 AM by AndrewAngell

    Big problem with ESS after updating to FMSA



      Big problem with ESS after updating to FMSA


      We're running FMSA 11 on OSX Snow Leaopord.  We've got FMP11 on client machines.  I've had External SQL Sources configured and working correctly with MySQL on our web server for quite some time now and have never had any issues.

      My client installed the update for FMSA and they're now running  Ever since that happened, though, my FileMaker layouts that are attached to MySQL using ESS are doing some very strange.

      I've got a table in MySQL called products with basic fields like name, price, description, etc.  I've got a very basic layout in FileMaker to display that data and allow you to add, update, delete records, etc.  Simple stuff.

      Well, for some reason now, any time I edit a TEXT field of any value on a FM layout attached to MySQL and commit the record it seems to "take" for just a moment and then immediately wipes out every character but the first one in the string.  For some reason FM can only send a single text character to MySQL. 

      When I connect to MySQL directly using phpMyAdmin, Navicat, with web apps or even the command line it works perfectly so the problem is definitely with FileMaker.

      I've checked all script triggers for the layout and objects on the page.  I checked the File Options dialog.  I've checked auto-update and calculation fields to see if anything could possibly be doing this.  None of that stuff is even set.  These MySQL layouts are very basic text/number fields with no formatting or anything applied.  The text fields are the only ones having a problem.  Numbers seem to be fine.

      I've tried shutting down all of the files and saving compact versions and then restoring from those.  Same problem.  I've also tried removing the MySQL ODBC Driver from the OSX Server and re-installing/configuring it but now I'm back in the exact same boat I was before.

      I'm completely stumped.   It really seems to me like maybe there's a bug with ESS and this new FMS11 update. Or, maybe it wants us to go ahead and use the most recent MySQL ODBC Driver now..??  All of the FM documentation says you ahve to use version 3.51 so that's what I've always used before.  It does still work except for you can't update products without losing all your text values. 

      Any information on this would be greatly appreciated.  Thanks!

        • 1. Re: Big problem with ESS after updating to FMSA

          Andrew Angell:

          Thank you for posting.

          I also tested the MySQL 3.51 driver on Snow Leopard and experienced the same issue. There does appear to be an incompatibility with this particular driver. While it has been tested on Windows, we actually recommend the Open Source Databases driver provided by Actual Technologies for use on Macs.

          You can find a trial and information about this driver on the Actual Technologies website:

          FileMaker, Inc.

          • 2. Re: Big problem with ESS after updating to FMSA

            I should reiterate for anybody else who sees this that the MySQL ODBC 3.51 driver worked just fine on Server 11 without the recent patch. 

            I guess I'll try this 3rd party driver.  It's rather frustrating that I'm using MySQL and I can't use the free MySQL drivers, though.  Are you guys associated with ActualTechnologies or something trying to get an extra $30 out of all your users..???

            Have you tested the 5.0 MySQL drivers by chance?  Maybe they work with the new version?  I know it's only $30 for this other driver but I already dumped $40 for a phone tech support who didn't even know how ESS worked.  I had to train him on that before I could even exlain the problem and he still had no answers for me.  His "answer" was to come here and post a message.  So what exactly did I pay the $40 for..??  The point of paying money was to get it done faster than waiting on answers here. 

            Anyway, now my answer is to go spend more money, but if I spend this $30 and it doesn't work they're probably not going to refund that to me either because they're a 3rd party.  Can you guarantee this driver will fix my issues?  Also, is there anything you can do to help me get a refund for my phone call?  I got absolutely nothing out of that call.

            Anything you can tell me on that would be greatly appreciated.  Thanks!

            • 3. Re: Big problem with ESS after updating to FMSA

              Andrew Angell:

              Your post has been sent to the Director of Customer Care.  Either myself of her will get back to you regarding a call refund.

              Try setting the maximum number of characters for the Text field in Field Options to 255. The default is one character, so that may be the issue.  Let me know if that works.

              The ODBC drivers tested by our Testing department can be found in Knowledge Base Article #6510:


              That list doesn't mean other drivers won't work.  It just means those are the ones that have been tested successfully.

              FileMaker, Inc.

              • 4. Re: Big problem with ESS after updating to FMSA

                Andrew Angell:

                Since my last post, I discovered two things:

                1. My suggestion for setting the maximum characters was incorrect.  I was thinking of FileMaker Pro being used as a data source.

                2. The original post from TSuki is correct.  The MySQL 3.51.xx driver is only supported on Windows.  The only drivers supported on the Mac are those from Actual Technologies.  See the link in my previous post for the correct Mac drivers.

                FileMaker, Inc.

                • 5. Re: Big problem with ESS after updating to FMSA

                  Ok, this is getting a little silly now.  I just went to buy the ODBC driver and I see that the one for $30 is only the Personal Edition that allows a maximum of 5 connections.  The server edition is $400! 

                  The MySQL driver worked perfectly fine with Server 11 before the update.  What changed in the update to break this?  Have you guys tested the MySQL 5.0 ODBC driver?

                  It really mind boggles me that FileMaker releases all these new features that are supposed to make working with web apps simple but there are major road blocks with every one of them (XML API, PHP API, ESS) that FM confirms and never seems to do anything about. 

                  I guess our only option now is to spend yet another $400 or just switch back to a Windows server, but then we'll be stuck on 32-bit and limited on RAM because the ESS stuff doesn't work on Windows 64-bit (confirmed awhile back with FM techs, which is why we tried Mac in the first place). 

                  This is going to get long, but if you could please review everything I've been through the past few years just to see if I've somehow missed something in all my history at fmforums, experts-exchange, FileMaker support, etc, I’d really appreciate it.  I just feel like there has to be something I’m missing.  Every road I take throws up a huge wall.

                  1) Before the XML API was ever available we were trying to use ODBC connected directly to FileMaker.  This is very slow and cannot handle live web applications.  In my own research I see that the reason is because every single query, no matter how simple or complex, to the server spikes the CPU on the (powerful servers at that) up to about 70%.  Well, obviously, you throw 2 or 3 calls at it and you're bringing your server to its knees.  I confirmed all of these issues with FM years ago but was told that the ODBC drivers were the problem and there is nothing they can do about it. 

                  2)  FM introduces the XML API, an alternative to ODBC that allows you to interact directly with FileMaker and was supposed to solve these issues (because the ODBC drivers were always to blame for the performance issues).  I spent lots of development time creating custom scripts to work directly with the XML API (before PHP API was introduced) only to find out the same exact problem still exists….the CPU spikes tremendously and cannot handle live web applications.  Once again I confirmed this with escalated support engineers at FM and was told there’s nothing they can do about it.  This time, though, with no driver to blame, they just kept asking me if there were any work-arounds I could come up with to move data around and avoid the spikes.  Not very helpful, of course. 

                  3)  FM introduces the PHP API making it easier for PHP developers to interact directly with FM data.  Upon further review it seems to use the same XML API I had been using before, it’s just easier now because it’s all been wrapped into PHP classes.  It is rather handy and nice to work with, but once again, the same problem exists because it’s still using the same technology.  The CPU spikes tremendously and it can’t be used. 

                  4)  Then FM releases ESS (external sql sources).  This seems to be the “solution”. Use a 3rd party database. Ours just doesn’t work.  So now the idea is to use a “real” SQL database and attach it to FM.  Then write scripts within FM to sync the data between FM and ESS layouts.  Ok, fine, I can handle that and it does seem to work out nicely except for these glaring problems.  Again, on Windows it’s been confirmed that it only works on 32-bit so we can’t put very much RAM in our server.  Now on Mac I find out that because of this most recent update I’m gonna have to spend $400 just to keep using this ESS solution. 

                  So first, am I missing something here?  And second, when is this going to end?  FM is such a great solution for people but it’s proven so difficult through-out the years to utilize it as a primary source for data for web apps that it’s tough to recommend to clients.  If only this CPU problem could be fixed (which for some reason you guys have been ignoring for years) it would be my opinion that FM is the best solution out there.  Because of it, though, we’re constantly forced to use work-arounds like ESS and now I’m finding out that even it has big problems of its own.

                  Anyway, sorry for all the ranting, but I just want to make sure I’m not missing something glaring that would solve all my issues and get any advice from you guys I can on all of this.  I’ve got clients who simply can’t leave FileMaker so we’re forced to pick our poison.  I thought I had it all worked out, finally, until now this update breaks the MySQL drivers.  Ugh. 

                  I’ll stop my novel for now.  Any info would be great.  Thanks!!!

                  • 6. Re: Big problem with ESS after updating to FMSA

                    Andrew Angell:

                    The exact wording regarding ESS on 64-bit Windows is:

                    "64-bit versions of ODBC drivers and the ODBC administrator are not supported. System DSNs should be created in the 32-bit version of the ODBC administrator."

                    The implication is that ESS will work on 64-bit versions of Windows, however they must use the 32-bit drivers and the 32-bit version of the ODBC administrator (%windir%\SysWOW64\odbcad32.exe). I still recommend testing it first, but this setup may provide an alternative to using the driver from Actual Technologies.

                    FileMaker, Inc.

                    • 7. Re: Big problem with ESS after updating to FMSA

                      I appreciate the feedback, but I've actually already tried that.  The 32-bit driver installs on Windows and allows me to setup the DSN on the server, but then when I open up FM Pro from a client and try to setup ESS the DSN won't show up in the data source list.