6 Replies Latest reply on Jul 19, 2014 2:08 PM by Oliver_Reid

    ESS MYSQL MYSTERY?

    Oliver_Reid

      I have a Mysql database.

       

      Please read carefully as this is subtle

       

      I have a copy hosted om OSX 6.8

       

      And a copy hosted on Linux Centos 6.5

       

      I have a view defied in boih dbs, which have been synchonized, that includes this derived column:

       

      SELECT

      `people`.`a_person_id` AS `a_person_id`,

      `people`.`b_name_first` AS `b_name_first`,

      `people`.`b_name_last` AS `b_name_last`,

      concat(

      MONTH (`people`.`b_dob`),

      '/',

      YEAR (`people`.`b_dob`)

      ) AS `b_dob2`

      FROM

      `people`

       

       

      Using Navicat ( latest versions which include ther own driver ) on either Windows or OSX I can display this vew and see the derived column --no problem-- with either OSX or the Linux hosted copies

       

      Mapping the view into FIlemaker using MysQL 32 bit driver 5.2a (on Windows) or the Actual driver (OS X) v 3.1.13 I can view the column just fine - but only for the OS X version. For the Centos version the Column Just ain't there -- no error message. (see attached) . I am using same access creds for Navicat and Filemaker.

       

      So its not a permissions issue.

       

      Its not a MySQL view construction issue.

       

      Its not ODBC driver issue per se.

       

      The only possible clue is that the OSX MySQL version is 5.5 whereas the Centos version is 5.2, and somehiw both the Windows and Actual driver versiond just happen to be incompatibe with this particar column (Other derived columns in the same view are working fine) when used to supply results to FM ESS shadow tables.

       

      Which is a stretch -- any thoughts? (I am upgrading the Centos machine to MySQL 5.5 and will report)

        • 1. Re: ESS MYSQL MYSTERY?
          taylorsharpe

          I would start out asking such things as what version of FileMaker, but apparently FileMaker is working for you and it appears to be a MySQL issue.  And I think this is generic SQL function issue with ODBC and not specifically ESS.  The issue you seem to dismiss is the ODBC driver, which really can make a big difference on the version of SQL it supports.  There actually are quite a number of different versions of SQL.  FileMaker supports the SQL-92 standard and I assume that the Actual Technologies ODBC driver does also since it was made for FileMaker. 

           

          Does this work in Navicat?

           

          SELECT

          a_person_id,

          b_name_first,

          b_name_last,

          b_dob

          FROM

          People

           

          If so, then it is the Concat function that does not work for you.  It is not universally supported in all platforms (e.g., Postgres does not support it).  I understand MySQL supports piping concactentation such as SELECT 'A'||'B'||'C'||'D'

           

          Try this in Navicat on the Linux machine:

           

          SELECT

                    a_person_id,

                    b_name_first,

                    b_name_last,

            MONTH ( b_dob ) || '/' || YEAR ( b_dob )

          FROM

                    People

           

          See if the piping version works for you. 

          • 2. Re: ESS MYSQL MYSTERY?
            Oliver_Reid

            AS i said in my posty it all works perfclty with Navicat on both copis of th edb  and with FMP on the copy of the Mysql databse hosted on OSX

             

            I am using FmpA 13

             

            The only case it does not work is when FMPA queries the Linux hosted copy -- as  you can see from the atthachment teh fiels does not appear (syncing does not fix it)

             

            I also have now hosted file on FMS13 using the Microsoft 64 bit driver, syced th table, and now the field shows up.

            • 3. Re: ESS MYSQL MYSTERY?
              taylorsharpe

              I suspect the ODBC driver version on your FMPA computer is not the same ODBC driver version on the FileMaker Server machine.  Can you confirm what drivers?  And I'm confused when you saying hosting on Mac OS X 6.8 which probably is 10.6.8 and you later talk about hosting FMS13, which doesn't work on anything prior to 10.8.5.  Then you say you are using the Microsoft 64 bit driver, which to mean indicates your FileMaker Server 13 is on a Windows OS. 

               

              I guess I am still confused, but I suspect the ODBC driver on your FMPA machine that is connecting to MySQL on the Linux box.  The current version is 3.1.16 and you said you were using 3.1.13.  Maybe upgrade the driver. 

              • 4. Re: ESS MYSQL MYSTERY?
                Oliver_Reid

                Same machine actually (Windows 2012 R2.)  FMPA13 in Windows only works with the 32 bit version. FMS 13 requires the 64 bit version. P.i.a.

                 

                I have 10.6.8 machine hosting one copy of the mysql db. (not running FSMP or FMS) . Linux hosting the other. Also have a client version of FMPA 13 running in Mavericks

                 

                I will try upgarding the Actual driver on that machine but the probel also arisea on the windows machine wth the 32 bit 0soft drver, which in the latest version. asd is where I want to run in production.

                • 5. Re: ESS MYSQL MYSTERY?
                  taylorsharpe

                  Ahhh, ok, I better understand now.  Sorry if I'm a little slow sometimes.  Yes, FMS 13 requires a 64 bit driver which is different than the 32 bit one for the FMPA.  So it sounds like you'll do OK as long as you host the FileMaker database on the FileMaker Server, but it will not work through a locally hosted FMPA file.  It doesn't answer your question as to why it does not work, but is a solution to the problem. 

                  • 6. Re: ESS MYSQL MYSTERY?
                    Oliver_Reid

                    Actuall chnaging th calc slighty fixed it:

                     

                     

                    concat(

                                        cast(

                                                  MONTH (`people`.`b_dob`) AS CHAR (2) charset utf8

                                        ),

                                        '/',

                                        cast(

                                                  YEAR (`people`.`b_dob`) AS CHAR (4) charset utf8

                                        )

                              ) AS `b_dob`

                     

                    Still would like to know why though -- I think MONTH and YEAR a numeric type and perhaps MySQL 5.2 less less tolerant of  that than  v 5.5