1 2 Previous Next 15 Replies Latest reply on Feb 11, 2010 11:42 AM by TSGal

    ESS (mySQL) : loss of primary key from FM9 once the file is open with 10

    fabriceN

      Summary

      ESS (mySQL) : loss of primary key from FM9 once the file is open with 10

      Description of the issue

      A FM file is linked to a mysql database. (file created with FM9)if I open the file in 10, everything's all right.Go back to 9, and all ESS tables have primary key missing (got to Sync again)Both on FMS 9 and FMS10. Reproduced.

        • 1. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
          TSGal

          Fabrice:

           

          This problem was reported by one of our external test sites and we could not reproduce this problem.  Can you please provide us more with your computer set up?  The person who reported this was using a MacBook Pro using Leopard.  Any information you can provide that will help us duplicate the problem would be greatly appreciated.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
            fabriceN
              

            Hi,

             

            Actually I did something that apparently solved the problem. Maybe you can confirm something has changed between 9 and 10, so you could close the case.

             

            When the file was hosted on FMS 9, it happened one day that with no apparent reason, the reference of the ESS changed. Therefore, I had some TOs where ESS was 'A' and some where it was 'B', although they were occurrences of the same table.

            Now the reason why this situation was lasting is that if I changed the reference of the old (A) TO, all the relationship would be messed up (wrong match fields). Apparently, but please confirm, doing the same thing with 10 (FMS and Client) keeps relationship in place.

            After re-setting all the TO so they use the new 'file reference', the <primary key> issue is gone.

            It doesn't explain why it happens, but at least it's a good work-around. 

             

            HTH,

            Fabrice 

            • 3. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
              fabriceN
                 Sorry, the fix didn't last long. The problem occurred today again. :(
              • 4. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                DeanEBedford
                  

                I believe I am having the same issue!  After launching a database (built and hosting previously in FileMaker ) on FMS 10 and FMPro Advanced, the Table Occurences in the Relationships lose their associations with the ODBC data source (mysql tables).  Though the relationship still work properly in Browse mode in the portals and scripts, if you double click on the TO, the associated table is not highlighted in the Specify Table Dialog Box, though Data Source is correct.

                 

                Example:  I have a CONTACT table, which in FMPro 9 when I double clicked on the Table Occurence, the Specify Table dialog box would launch and the associatd ODBC (mysql table) Contact table would be highlighted.  Now in FMPro 10, that table is not highlighted.  If I then select Contact from the list of available ODBC table to select from, a new CONTACT 2 table is created.

                • 5. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                  TSGal

                  DeanEBedford:

                   

                  Thank you for your information, too.  I have also forwarded your post to our Development and Software Quality Assurance (Testing) departments so they can research it further.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                    fabriceN
                       Just to mention that the symptoms described by DeanEBedford are also  true in my case, which is I think normal if no PK is defined.
                    • 7. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                      jgraff
                        

                      I'm having the same problem fabriceN was having in the initial post.

                       

                      Setup:

                      FileMaker database created in FM9, hosted on FM9 Server Advanced 9.0.3.326 on a Windows Server 2003 Standard Edition running SP2.  The database is connected to a MySQL 5.0.45 database via ESS.

                       

                      Scenario:

                      FM 9.0v3 client (Pro or ProAdvanced on Windows XP and Mac OS X 10.5) can view data from a layout that is based a MySQL table via ESS.  If a FM 10 client views data from the same MySQL table, FileMaker 9 Clients can no longer view the data in the MySQL table.  Instead, you get '<Primary Key Missing>'.  Syncing the external table fixes the problem, until a FM 10 client views the data again.   This problem extends to any Table Occurrence that's based off of the MySQL table. 

                       

                      Table Names in the MySQL table haven't changed nor have the Table Occurrences in the FileMaker file.

                       

                      I will also be testing this with an Oracle database that I'm connecting to via ESS.

                       

                      We have a 180 client site license.  Migrating all clients to 10 at the exact same time is something I'd like to avoid.  

                       

                      -Jake

                      • 8. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                        danny
                          

                        I have been experiencing the exact same problem both Jake and Fabrice reported.

                         

                        We have FM9 Server 9.0.3.325 on a Mac Mini with Mac OS X 10.4(sorry, don't have the exact specs). FM 9 running on multiple computers including several Mac Minis of varying ages running 10.4-10.6 and a MacBook3,1. FM 10 running on the MacBook and a new Mac Mini.

                         

                        Anytime any FM 10 client views or edits MySQL data via ESS, all the FM 9 clients get the '<Primary Key Missing>' message until resynced.

                         

                        We have downgraded to FM 9 and have to make sure no one opens the database in FM 10.

                         

                        Has there been any QA progress on reproducing this issue? I haven't seen any mention of this for nearly a year.

                        • 9. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                          TSGal

                          dannydk:

                           

                          Thank you for your post.

                           

                          No, there has not been any progress on reproducing this issue.  I have attached your post to the original case.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                            imacguru
                              

                            TSGal,

                             

                            Failure of the ESS to retain links from shadow tables to the SQL source tables is a very serious bug in FileMaker Pro 10 that needs a fix ASAP. In my case, FileMaker Pro has spawned several phantom secondary shadow tables and refuses to let go of them. E.g. contactsSQL is a shadow table reference to a contacts table in SQL. Any new references to that SQL table added to Relationships point to a phantom contactsSQL 2 table. This phantom table doesn't have any of the calculation fields in the real table. It also shows under tables as having no records compared to the real table that presently has 8756 records.

                             

                            Deleting the phantom tables does not make them go away. Any new reference to the SQL table in Relationships instantly recreates the undesired phantom shadow table. 

                             

                            When this bug first surfaced back with the transition from FileMaker Pro 9 to 10, a temporary fix was to delete all references to the SQL table and start over. This was a massive undertaking as it broke numerous layouts, scripts etc. As you can imagine, there is also an enormous cost involved with having to redo so much work. Some scripts were difficult to repair as the field references were scrambled when the new SQL shadow tables were created. Eventually after the fix the problem returns as mentioned above.

                             

                            FileMaker heavily promoted the ESS features of FileMaker Pro 9 (and now 10) which were accepted in good faith as being in usable working order. The inability of FileMaker Pro 10 to retain a consistent link from a shadow table to the ESS is a catastrophic failure on FileMaker's part. This would be less of an issue if there was a way to manually repair the links but I see no access to do that. If there is, please advise immediately on how to go about doing that. It has been a year since FileMaker Pro 10 was released. The bug was not part of FileMaker Pro 9.

                             

                            Thank you for hopefully prompt action on this and a resolution from FileMaker in the form of an update to FileMaker Pro 10.0v4. 

                            • 11. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                              mrvodka
                                

                              Philip Kushnir wrote:

                               

                              Thank you for hopefully prompt action on this and a resolution from FileMaker in the form of an update to FileMaker Pro 10.0v4. 


                               
                              IF ( and a big IF ) FMI fixes this, given the past history I would get ready to purchase 11.

                              • 12. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                                TSGal

                                Philip Kushnir:

                                 

                                Thank you for your post.

                                 

                                I have attached your entire post to the original case, and our Development and Software Quality Assurance (Testing) departments will review it.  If more information becomes available, I will let you know.

                                 

                                TSGal

                                FileMaker, Inc. 

                                • 13. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                                  TSGal

                                  All:

                                   

                                  Our Testing department has been unable to reproduce the problem, and they would like more information from everyone.  Specifically, what version of the ESS driver is being used?

                                   

                                  TSGal

                                  FileMaker, Inc. 

                                   

                                   

                                  • 14. Re: ESS (mySQL) : loss of primary key from FM9 once the file is open with 10
                                    imacguru
                                      

                                    Hello TSGal,

                                     

                                    The system I'm having the problem with is using the Actual ODBC Driver for Open Source Databases version 2.9c which is the one recommended by FileMaker. The server runs Mac OS X Server 10.5.8 and FileMaker Server Advanced version 10.0.2.206. Client machines are running FileMaker Pro and FileMaker Pro Advanced 10.0v3 and are all running on Macs with a mix of Leopard and Snow Leopard with all updates.

                                     

                                    SQL tables with as few as 2 occurrences in graph are spawning additional shadow tables with a 3rd occurrence. The first time this happened it was with a table with numerous occurrences in graph (contacts table in a contacts-centric system).

                                     

                                    Speaking of the Relationships graph, there is no doubling of shadow tables in the list of available tables for the data source. Instead, selecting the desired table automatically links to the undesired redundant shadow table. This is the biggest problem: there is nowhere in the FileMaker Pro interface where one can attempt to correct this.

                                     

                                    I'm now wondering if the reason you can't reproduce the problem in your testing department is because you're starting with a file established in FileMaker Pro 10. For me the problem surfaced with files first created in prior versions of FileMaker Pro. Something bad happened with the version update from 9 to 10 as I never saw this happen before using 10.

                                     

                                    If you could enlighten me as to where in the FileMaker Pro database information on linking shadow tables to ESS is stored and whether or not there is a way to manually correct it that would be a big help. Even better, how about adding some extra editing to the ESS within FileMaker Pro so we can manually correct errors like this without trashing our database files. Thanks! 

                                    1 2 Previous Next