7 Replies Latest reply: Sep 7, 2012 7:07 AM by M Paeper RSS

    QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview

    M Paeper

      Hi,

       

      Going in circles on this. Have been successful developing with QV11SR1 on a Win7x64 desktop using the same MySQL 5.5 external database and connecting to it using 64 bit MySQL 5.1 ODBC driver v 5.01.11.00 28/04/2012 for the past 6 months.

       

      This week everything still connects fine - i.e. all models reload fine etc, but on the QV wizard which opens the database and shows the fields in the tables is no longer showing me the available field list, just * select all

       

      It still works with * but no table field names or table previews are available - its just blank. See attached screencap.

       

      This worked till last week.

       

      I havent installed anything new - except for Windows updates - and nothing I'm aware of has changed on the dababase access.

       

      I have now uninstalled the ODBC drivers, downladed the latest ones from Oracle (still the same version #) rebooted, Uninstalled Qlikview desktop, removed everything QV related I could find in registry. Reinstalled everything. Removed the system DSNs and recreated from scratch. Tried a user DSN. Installed 32 bit version of ODBC drivers and used force use 32 bit in QV. Tried connecting to a different MYSQL 5.5 database on localhost.

       

      and still the field names don't appear in any of these tests.

       

      I have another database using ODBC 9.1 Postgresql drivers and in that I can see the field names in preview as I'm used to.

       

      Can anyone shed any light?

       

      In the Fields box below the names of the fields in the table used to appear. They dont any more. Why?

       

      Thanks

       

      odbcqt.png

        • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
          Miguel Angel Baeyens de Arce

          Hi,

           

          When something very similar to this happened to me, it was due to lack of permissions in the user that is set up in the ODBC drivers, in some cases because of database upgrades or cleanups. Is there any chance that this is happening to you as well?

           

          Hope that helps.

           

          Miguel

          • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
            M Paeper

            I've just tried this on a completely different QV desktop - actually its desktop installed in the QV server - win2008R2x64. This was working fine last time I checked.

             

            Anyway, when I load the Postgres accessing model and try the wizard I can see field names in the table.

             

            However, when I choose a model containing MYSQL access I get the following.

             

            emcCreate Select Statement_2012-09-06_14-49-56.png

             

            I also see this.

             

            QvConnect 11.png

             

            Now that I think about it I was also getting QVconnect errors last week on the Win7 desktop, but after reinstall QC and ODBC it hasnt recurred. However I cannot see field names there either just *

             

            Can anyone help me resolve. Thanks.

              • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
                Miguel Angel Baeyens de Arce

                Hi,

                 

                Did you have any other MySQL driver in your computer that you may have uninstalled?

                 

                Miguel

                  • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
                    M Paeper

                    Hi Miguel,

                     

                    Thanks for your continued interest. I have asked the MySQL database application developers whether anything may have changed on the read only user access permissions I'm using to access their db. I know the application is being upgraded this week, but AFAIK this should affect any of the user access methods.

                     

                    However, I have used MySQL workbench with the same password and in that I can see tables, fields just fine. Of course that may not be using ODBC to connect even though I am using the same password for that and in the ODBC DSN setup.

                     

                    Something is clearly giving the QVConnect indigestion.

                     

                    The ODBC driver side isnt very exciting. When I first set it up, I just installed the drivers - created the DSN and then QV connected from then on.

                     

                    I'm not aware of there being other MySQL drivers installed - they are not visible in the create new DSN list at least. I have installed both 32 and 64 bit right now (although last week I didnt - only 64 bit then) and the same issue exhibits with both.

                     

                    From within ODBC setup in Windows it tests fine, but something is missing in how QVconnect is working with the driver from what I can see.

                     

                    Also what's interesting is that other QV desktop systems that used to work and havent been touched are experiencing the same issue. i.e. the install and the QV, DSN and ODBC driver is unchanged.

                     

                    Will keep flogging it because without fixing it I'm not going to be productive in dev because without knowing the field names one doesnt get far in scripting after all and the non-wizard approach is painful when I know there's a more efficient way if only it would just work.

                     

                    Now going to reboot QV server and see what else I can find. Thanks

                      • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
                        M Paeper

                        Hmmm, just installed 5.21 beta! driver from MySQL site and I can see field names again. Go figure! :-))

                         

                        Will retrace steps tomorrow and see what else I can find that could have caused this.

                          • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
                            Rob Wunderlich

                            If you search the forum for MySQL 5.1, you'll find a number of problems. This recent thread and it's referenced thread sounds similar to your problem.

                            http://community.qlik.com/message/235759#235759

                             

                            I've experienced problems with QV and th V5 drivers in the past, and my workaround has been to use the V3 drivers.

                             

                            -Rob

                              • Re: QV11SR1 external MYSQL db access using ODBC driver doesnt show field names in connect wizard preview
                                M Paeper

                                Hi Rob,

                                 

                                Thanks for your input. I think I know what you mean by the link you provided (for some reason when I try go there it tells me I need admin access to see it). Anyway I came across postings from 2009 mentioning something similar in the forums and the workaround was to use the 3.5x drivers. I thought given the time that has elapsed since then that surely such an issue had been fixed in the current GA release of the MySQL ODBC drivers 3 years on and also with 1-2 versions later of QV as well.

                                 

                                Also in my case I know that I'd been developing just fine using the v5 MySQL ODBC drivers for the past 6 months so it was something on the combo of QV, Win7 and the drivers that had gone wrong but where.

                                 

                                Anyway I had tried the v3.5 drivers in my attempts to fix things but for some reason I couldnt get the ODBC admin test authentication to see the server I wanted so I uninstalled it.

                                 

                                One thing I noticed today is it possibly was related to the DSN settings not being picked up by QVconnect because when I go to the Create Select statement wizard now the metadata is populated and when it wasnt working this was blank. Have attached what I see now and is working below.

                                 

                                I'm guessing one of the numerous driver reinstalls eventually did re-register whatever link was missing. I was using the MSI driver and hadnt tried the zip installer yet.

                                 

                                Anyway for me, I realise MySQL ODBC x64 driver 5.2 is a beta but if its working (and seemingly a little faster than I recall the previous version to be) I'm sticking with it so I can get onto more productive things and will only worry about this again if something stops working. I see below v3.51 is mentioned but I only have v5.21 installed so who knows.

                                 

                                Thanks to both you and Miguel for your sterling assistance here.

                                 

                                ====================DataBase====================

                                SQL_DATA_SOURCE_NAME          EMCmysql-live

                                SQL_SERVER_NAME               192.168.0.16 via TCP/IP

                                SQL_DBMS_NAME                 MySQL

                                SQL_DBMS_VER                  5.1.41-3ubuntu12-log

                                SQL_DRIVER_NAME               myodbc5w.dll

                                SQL_DRIVER_VER                05.02.0001

                                SQL_DRIVER_ODBC_VER           03.51

                                SQL_ODBC_VER                  03.80.0000

                                 

                                If I look in the folders now I only see one MySQL folder:

                                 

                                C:\Program Files\MySQL\Connector ODBC 5.2>myodbc-installer -d -l -n "MySQL ODBC 5.2 Driver"

                                FriendlyName: MySQL ODBC 5.2 Driver

                                DRIVER      : C:\Program Files\MySQL\Connector ODBC 5.2\myodbc5.dll

                                SETUP       : C:\Program Files\MySQL\Connector ODBC 5.2\myodbc5S.dll

                                 

                                Anyway so long as its working again. Thanks. Cheers