12 Replies Latest reply on Apr 7, 2017 6:44 AM by beverly

    Unable to connect to mysql using odbc on FMP14

    bellenaessens

      I am attempting an ODBC connection between Filemaker and Prestashop (externally hosted mysql server).

       

      I have set up the DSN Manager on the server with server, port, database, user etc.

       

      When trying to add an occurence from the DSN to the relationship graph, the system appears to be making a connection but keeps running. After a while, I am prompted for my login and password again, then it runs again without results, asks for my login credentials, and again, and again.

       

      My database is hosted on FM Server 14 on MacOS 10.9.

      I have tried adding the occurences through both FMPA 14 on Mac OS and FMP 14 on Windows 7 (both on different machines than the server machine).

       

      Any ideas what I'm doing wrong?

        • 1. Re: Unable to connect to mysql using odbc on FMP14
          planteg

          Hi bellenaessens ,

           

          have you tested your DSN outside of FileMaker ? I mean tested that it connects to you MySQL database (the is usually a Connection Test button) ?

           

          MySQL security is very high (at least on Linux). When you want to connect from a remote computer, you have to setup your MySQL Server to accept incoming connections, from everybody or specific adresses.

          • 2. Re: Unable to connect to mysql using odbc on FMP14
            bellenaessens

            Hi planteg

            Thanks for your reply. No, in ODBC Manager I can find no sign of a "test connection" button of any sort. I would indeed expect to find this in such an environment (for obvious reasons), but nothing.

             

            I have no idea how else to try and test the connection. I am trying to see also in phpMyAdmin if there are any security settings I should change, but cannot find any that could cause the problem.

            • 3. Re: Unable to connect to mysql using odbc on FMP14
              planteg

              I searched about ODBC Manager for the Mac, it looks like testing the DSN depends on the driver supplier.

               

              For example:

               

              Mac ODBC: DSN configuration for Mac OS X by Actual Technologies

               

              Installing and Configuring the ODBC Driver for Mac OS — Snowflake Documentation Here they use iODBC

               

              Regarding phpMyAdmin, select the database, click on the Privileges tab:

               

               

              I have on user set for web_alarm that can access this database. For global, I have these permission set:

               

              Hope that helps.

              1 of 1 people found this helpful
              • 4. Re: Unable to connect to mysql using odbc on FMP14
                fmpdude

                When you're re-re-re- presented with login credentials, it can often mean you have the wrong password, or it's case sensitive, or something like that.

                 

                Another issue is that the server may not allow remote connections outside the machine itself. That is, MySQL is intended to be used as a service within the server, but not directly accessed from outside the server. It looks like you have the correct external user defined using "%", so I'm not sure about this being the cause.

                 

                ----

                Things to try...

                 

                If you download the FREE MySQL JDBC driver (not ODBC in this case, but JDBC) you could try a tool like RazorSQL for an interactive-outside-FMP test. See if you get any useful connect error messages when you try to connect. You're not going to do SQL with Razor in this test, just try to set up a connection to MySQL and see if you can get to the database and, if not, what errors you get.

                 

                http://razorsql.com/

                 

                I would also (if it were me) write a short Java program that would try to get a connection to the database and see what actual error information I get. If you can code a little, there are lots of examples on the Internet how to write JDBC programs. They are actually quite simple, but in this case all you want to try to do is connect. Using RazorSQL will do more or less the same thing, but I'm not sure about Razor's connection error reporting.

                 

                Connect to MySQL with JDBC driver

                 

                HOPE THIS HELPS.

                1 of 1 people found this helpful
                • 5. Re: Unable to connect to mysql using odbc on FMP14
                  bellenaessens

                  It gets even stranger: there is no privileges tab in phpMyAdmin. I knowfrom earlier setups of mySQL that that's where to adapt the security settings, but it's not there. (This is a set-up I didn't initiate myself, I think it was done by the hosting provider, and it shows only 1 user directly under "database server".)

                   

                  I'm now contacting support from our hosting provider to see if it could be a security issue.

                  • 6. Re: Unable to connect to mysql using odbc on FMP14
                    bellenaessens

                    Thank you for your suggestions, fmpdude! I have tried razorsql and the connection fails. Since I can't access the privileges (the printscreens above are from someone else trying to help me), I have contacted support from our hosting provider, who I think set up the MySQL server in the first place.

                     

                    Frustrating...

                    • 7. Re: Unable to connect to mysql using odbc on FMP14
                      fmpdude

                      Hmmm, in that case, I bet it's that you need a "%" user so you can access the server outside the server itself (as in, remotely).

                       

                      Contacting the support team at your hosting provider is the correct way to proceed at this point. You've clearly given it the "college try" (and then some)! 

                       

                      Thanks for your update.

                      • 8. Re: Unable to connect to mysql using odbc on FMP14
                        bellenaessens

                        Got word from support: since it's a shared hosting package, they don't allow direct access to the mysql-database from the outside. They suggested SSH tunneling as an alternative. Will give that another try now, since I couldn't get it to work yesterday.

                        • 9. Re: Unable to connect to mysql using odbc on FMP14
                          fmpdude

                          That's certainly understandable from their point of view but completely unsatisfactory from your point of view (as in, technical needs).

                           

                          Therefore, if it's within your power, change your hosting to a dedicated "VPS" server. With a VPS you still don't have the entire server, but you have your own Linux "image" and can do whatever you need to. The hosting company could do all the work for you to switch you to the new hosting configuration.

                           

                          Linux VPS hosting is not expensive. Often about $50/month, depending on memory, disk space, and such.

                           

                          One of the best VPS providers I have researched, but not tried, is RoseHosting.

                           

                          At least you know what was going on now.

                           

                          Good luck!!!

                          • 10. Re: Unable to connect to mysql using odbc on FMP14
                            bellenaessens

                            I was thinking in the same direction, but not my decision to make. I have suggested changing the hosting package to customer in the past (for a number of other reasons as well), but no go so far.

                             

                            Thanks for the suggestion and for staying on the thread, though! Bye!

                            • 11. Re: Unable to connect to mysql using odbc on FMP14
                              fmpdude

                              Yet bet.

                               

                              Let us know if we can help you in the future!

                               

                               

                              • 12. Re: Unable to connect to mysql using odbc on FMP14
                                beverly

                                Yes if you don't have control of the external sql source, then you are limited to permissions. They have put the security in there for a reason that does not help you!

                                 

                                At this point I typically suggest trying to get feeds (from their data) and a method to send data to them. This is sometimes allowed as they can "clean" the data (prevent SQL attacks and do other necessary housekeeping). But you have to get that information from them.

                                 

                                Keep us posted on the SSH tunneling!

                                beverly