10 Replies Latest reply: Nov 27, 2012 5:25 AM by Kgronau-Oracle RSS

    Error connecting to mysql via ODBC

    348706
      Hi folks,
      I set up Oracle Database Gateway for ODBC with mysql driver (I tried mysql-connector-odbc-5.1.11-1.x86_64.rpm (currently used and log output shown) and mysql-connector-odbc-5.2.2-1.x86_64.rpm). I am unable to create a connection to the mysql database.

      Here are the parameters:

      Oracle database: 11gR2, AL32UTF8 , Suse SLES11 64 Bit

      Oracle Database Gateway for ODBC: installed on Suse SLES10, 64 Bit

      ODBC DSN: ona_nb

      initona_nb.ora in /opt/oracle/product/11.2.0/gtw_odbc/hs/admin:

      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO=ona_nb
      HS_FDS_TRACE_LEVEL=255
      HS_FDS_SHAREABLE_NAME =/usr/lib64/libmyodbc5.so
      HS_LANGUAGE=AMERICAN_AMERICA.ISO8859P1
      #HS_TRANSACTION_MODEL=READ_ONLY
      #HS_FDS_SQLLEN_INTERPRETATION=32
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/home/oracle/odbc/ona_nb-odbc.ini
      set ODBCINSTINI=/home/oracle/odbc/ona_nb-odbc.ini

      (I tried diffenet Language settings or no language setting at all, slightly different ..../hs/log trace files)

      ODBC file:

      ona_nb-odbc.ini :
      [ODBC Data Sources]
      ona_nb= MySQL ODBC Driver 5.2.2-1
      [ona_nb]
      Driver = /usr/lib64/libmyodbc5.so
      DATABASE = ona_default
      DESCRIPTION = MySQL Verbindung zur ona V11 Datenbank auf Christians Notebook
      PORT = 3306
      SERVER = cwolbert.gsi.de
      UID = oreader
      PWD = xxxxxx
      [ODBC]
      TRACEFILE = /tmp/mysql-odbc-ona_nb.trc
      TRACE = Yes
      ForceTrace = Yes
      set LD_LIBRARY_PATH=/usr/lib64:/opt/oracle/product/11.2.0/gtw_odbc/

      Listener.ora:

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_3)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (GLOBAL_DBNAME = gridrep.gsi.de)
      (ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_3)
      (SID_NAME = gridrep)
      )
      (SID_DESC=
      (SID_NAME=ona_nb)
      (ORACLE_HOME=/opt/oracle/product/11.2.0/gtw_odbc)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib64:/opt/oracle/product/11.2.0/gtw_odbc/lib)
      )
      )



      Trying

      select * from locations@ona_nb

      whre ona_nb is the dblink

      CREATE DATABASE LINK "ONA_NB.GSI.DE"
      CONNECT TO OREADER
      IDENTIFIED BY <PWD>
      USING 'ona_nb';


      I get the error

      ORA-28511: RPC-Verbindung zu heterogenem Remote Agent mit SID=ORA-28511: RPC-Verbindung zu heterogenem Remote Agent mit SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pcora11.gsi.de)(PORT=1521))(CONNECT_DATA=(SID=ona_nb))) abgebrochen abgebrochen
      ORA-02063: vorherige line von ONA_NB



      and here is the trace file from hs/log. I am unable to create any ODBC trace file, fiddling around with differnt settings

      Oracle Corporation --- FRIDAY NOV 23 2012 15:37:21.114


      Heterogeneous Agent Release
      11.2.0.1.0




      Oracle Corporation --- FRIDAY NOV 23 2012 15:37:21.113

      Version 11.2.0.1.0

      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
      Entered hgosdip
      setting HS_OPEN_CURSORS to default of 50
      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
      setting HS_FDS_RECOVERY_PWD to default value
      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
      setting HS_IDLE_TIMEOUT to default of 0
      setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
      setting HS_NLS_NCHAR to default of "AL32UTF8"
      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
      setting HS_FDS_DATE_MAPPING to default of "DATE"
      setting HS_RPC_FETCH_REBLOCKING to default of "ON"
      setting HS_FDS_FETCH_ROWS to default of "100"
      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
      setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
      setting HS_FDS_MAP_NCHAR to default of "TRUE"
      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
      setting HS_FDS_QUERY_DRIVER to default of "TRUE"
      setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
      Parameter HS_FDS_QUOTE_IDENTIFIER is not set
      setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
      Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
      setting HS_FDS_DELAYED_OPEN to default of "TRUE"
      setting HS_FDS_WORKAROUNDS to default of "0"
      Exiting hgosdip, rc=0
      ORACLE_SID is "ona_nb"
      Product-Info:
      Port Rls/Upd:1/0 PrdStat:0
      Agent:Oracle Database Gateway for ODBC
      Facility:hsa
      Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:ona_nb
      Exiting hgogprd, rc=0
      hostmstr: 0: HOA After hoagprd
      hostmstr: 0: HOA Before hoainit
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=873
      HOCXU_DRV_NCHAR=873
      HOCXU_DB_CSET=873
      HOCXU_SEM_VER=112000
      Entered hgolofn at 2012/11/23-15:37:21
      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libmyodbc5.so"
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLAllocHandle
      symbol_peflctx=0xaab3f456
      hoaerr:0
      Exiting hgolofns at 2012/11/23-15:37:21
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLBindCol
      symbol_peflctx=0xaab44ce3
      hoaerr:0
      Exiting hgolofns at 2012/11/23-15:37:21
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLBindParameter
      symbol_peflctx=0xaab4342c
      hoaerr:0
      Exiting hgolofns at 2012/11/23-15:37:21
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLCancel
      symbol_peflctx=0xaab3e6e5
      hoaerr:0
      Exiting hgolofns at 2012/11/23-15:37:21
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLDescribeParam
      symbol_peflctx=0xaab4346b
      .... many similar stuff
      Exiting hgolofns at 2012/11/23-15:37:21
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLStatisticsW
      symbol_peflctx=0xaab477de
      hoaerr:0
      Exiting hgolofns at 2012/11/23-15:37:21
      Entered hgolofns at 2012/11/23-15:37:21
      libname=/usr/lib64/libmyodbc5.so, funcname=SQLTablesW
      symbol_peflctx=0xaab47a16
      hoaerr:0
      Exiting hgolofns at 2012/11/23-15:37:21
      Exiting hgolofn, rc=0 at 2012/11/23-15:37:21
      HOSGIP for "HS_OPEN_CURSORS" returned "50"
      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
      Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
      treat_SQLLEN_as_compiled = 1
      Exiting hgoinit, rc=0 at 2012/11/23-15:37:21
      hostmstr: 0: HOA After hoainit
      hostmstr: 0: HOA Before hoalgon
      Entered hgolgon at 2012/11/23-15:37:21
      reco:0, name:OREADER, tflag:0
      Entered hgosuec at 2012/11/23-15:37:21
      Exiting hgosuec, rc=0 at 2012/11/23-15:37:21
      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
      using OREADER as default value for "HS_FDS_DEFAULT_OWNER"
      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
      Entered hgocont at 2012/11/23-15:37:21
      HS_FDS_CONNECT_INFO = "ona_nb"
      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
      Entered hgogenconstr at 2012/11/23-15:37:21
      dsn:ona_nb, name:OREADER
      optn:
      Entered hgocip at 2012/11/23-15:37:21
      dsn:ona_nb
      Exiting hgocip, rc=0 at 2012/11/23-15:37:21
      ##>Connect Parameters (len=33)<##
      ## DSN=ona_nb;
      #! UID=OREADER;
      #! PWD=*
      Exiting hgogenconstr, rc=0 at 2012/11/23-15:37:21
      Entered hgopoer at 2012/11/23-15:37:21
      hgopoer, line 233: got native error 0 and sqlstate H; message follows...
      [
      Exiting hgopoer, rc=0 at 2012/11/23-15:37:21
      hgocont, line 2752: calling SqlDriverConnect got sqlstate H

      Using no HS_LANGUAGE or HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
      I still get the same error meassages and logs.


      Can you help please?

      Cheers Michael
        • 1. Re: Error connecting to mysql via ODBC
          Kgronau-Oracle
          Hi Michael,
          commonly the gateway parameter HS_FDS_SHAREABLE_NAME needs to point to an ODBC driver manager , in your case it points to the ODBC driver directly: /usr/lib64/libmyodbc5.so
          This will only work, when you have compiled all common ODBC functions into the driver (like for example IBM does for some of their drivers). Commonly for MySQL connections an ODBC driver manager (for example you can get one from www.unixodbc.org) is needed.

          Can you please verify if you have an ODBC driver installed on your machine and if it misses compile the unixODBC driver manager and use it with the DG4ODBC gateway?
          BTW, the ODBC driver manager library is called in general libodbc.so and it has to be a 64bit library.

          - Klaus
          • 2. Re: Error connecting to mysql via ODBC
            348706
            Hi Klaus,
            thanks fpr teh response.
            I followed the instruction from http://dba-blog.de/index.php?option=com_content&view=article&id=36:datenbank-link-zwischen-oracle-und-mysql-herstellen&catid=3:administration&Itemid=5.

            There the pointed to the mysql driver as well:
            HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc5.so

            and downloaded the mysql odbc driver from http://www.mysql.de/downloads/connector/odbc/#downloads

            In fact there is a /usr/lib64/libodbc.so library on the system (was there). But using this library it fails as well.
            :
            ##>Connect Parameters (len=33)<##
            ## DSN=ona_nb;
            #! UID=OREADER;
            #! PWD=*
            Exiting hgogenconstr, rc=0 at 2012/11/26-12:25:19
            Entered hgopoer at 2012/11/26-12:25:19
            hgopoer, line 233: got native error 0 and sqlstate I; message follows...
            [
            Exiting hgopoer, rc=0 at 2012/11/26-12:25:19
            hgocont, line 2752: calling SqlDriverConnect got sqlstate I
            Exiting hgocont, rc=28500 at 2012/11/26-12:25:19 with error ptr FILE:hgocont.c LINE:2772 FUNCTION:hgocont() ID:Something other than invalid authorization
            Exiting hgolgon, rc=28500 at 2012/11/26-12:25:19 with error ptr FILE:hgolgon.c LINE:781 FUNCTION:hgolgon() ID:Calling hgocont
            hostmstr: 0: HOA After hoalgon
            RPC Calling nscontrol(0), rc=0
            hostmstr: 0: RPC Before Exit Agent
            hostmstr: 0: HOA Before hoaexit
            Entered hgoexit at 2012/11/26-12:25:19
            Exiting hgoexit, rc=0
            hostmstr: 0: HOA After hoaexit
            hostmstr: 0: RPC After Exit Agent

            Any hints about how to set up the odbc driver manager with the mysql driver properly?
            Thanks

            Michael
            • 3. Re: Error connecting to mysql via ODBC
              Mkirtley-Oracle
              Michael,
              Try adding -

              HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

              to the gateway init<sid>.ora file and try a connection from a new SQLPLUS session.

              Regards,
              Mike
              • 4. Re: Error connecting to mysql via ODBC
                Kgronau-Oracle
                Michael,
                it really depends how the MySQL ODBC driver was compiled, sometimes you have the ODBC Driver Manager compiled into the driver, then using HS_FDS_SHAREABLE_NAME with the MySQL ODBC driver works, in other cases you need a separate Driver Manager. I commonly prefer the second method as here I know for sure which Driver Manager is installed.


                When you use the libodbc driver manager library, do you also get the 28511 error in SQL*Plus or an 28500?
                BTW, do you know which version of the ODBC Driver Manager you are using (odbcinst -j)?

                Just in case you want to compile the driver manager on your own, goto the UnixODBC page (www.unixODBC.org), download the latest source and compile it from scratch.
                I commonly prefer to have the unixODBC I compiled on my own in a dedicated directory (/home/odbc64/unixODBC/unixODBC) and also to use the config files from /etc location. So I use:

                export CFLAGS="-DBUILD_REAL_64_BIT_MODE"
                ./configure prefix=/home/odbc64/unixODBC/unixODBC sysconfdir=/etc enable-gui=no enable-drivers=no
                make
                sudo make install

                - Klaus
                • 5. Re: Error connecting to mysql via ODBC
                  348706
                  Hi Mike,

                  I tried again, but niow I get error

                  ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht zur³ck:
                  [unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user
                  'OREADER'@'pcora11.gsi.de' (using password: YES) {HY000,NativeErr = 1045}
                  ORA-02063: vorherige 2 lines von ONA_NB

                  How about username and password?

                  In Oracle I can only user upper case usernames OREADER in defining the DBLINK, but in mysql it is lowercase.

                  The lowercase username and password is stored in initona_nb.ora file, but is it used?
                  • 6. Re: Error connecting to mysql via ODBC
                    Kgronau-Oracle
                    You need to surround the username and password by double quotes when defining the database link
                    create database link "<MySQL UID>" connect to "<MySQL PWD>" using '<your gateway alias>';
                    • 7. Re: Error connecting to mysql via ODBC
                      348706
                      Hi Klaus,

                      I think the unixODBC driver manager is installed

                      I have:

                      oracle@pcora11:/opt/oracle/product/11.2.0/gtw_odbc/hs/log> odbcinst -j
                      unixODBC 2.2.11
                      DRIVERS............: /etc/unixODBC/odbcinst.ini
                      SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
                      USER DATA SOURCES..: /home/oracle/.odbc.ini

                      and

                      cat /etc/unixODBC/odbcinst.ini
                      [MySQL ODBC 5.1 Driver]
                      Driver = /usr/lib64/libmyodbc5.so
                      UsageCount = 1


                      but the two file for SYSTEM and USER DSN are empty.

                      I tried now:

                      HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
                      and
                      HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1


                      and the result is still the same, what I posted in the prevoius answer to Mike,

                      ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht zur³ck:
                      [unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user
                      'OREADER'@'pcora11.gsi.de' (using password: YES) {HY000,NativeErr = 1045}
                      ORA-02063: vorherige 2 lines von ONA_NB

                      and in the trace file:
                      ##>Connect Parameters (len=33)<##
                      ## DSN=ona_nb;
                      #! UID=OREADER;
                      #! PWD=*
                      Exiting hgogenconstr, rc=0 at 2012/11/26-13:25:50
                      Entered hgopoer at 2012/11/26-13:25:50
                      hgopoer, line 233: got native error 1045 and sqlstate HY000; message follows...
                      [unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user 'OREADER'@'pcora11.gsi.de' (using password: YES) {HY000,NativeErr = 1045}
                      Exiting hgopoer, rc=0 at 2012/11/26-13:25:50
                      hgocont, line 2752: calling SqlDriverConnect got sqlstate HY000
                      Exiting hgocont, rc=28500 at 2012/11/26-13:25:50 with error ptr FILE:hgocont.c LINE:2772 FUNCTION:hgocont() ID:Something other than invalid authorization
                      Exiting hgolgon, rc=28500 at 2012/11/26-13:25:50 with error ptr FILE:hgolgon.c LINE:781 FUNCTION:hgolgon() ID:Calling hgocont
                      hostmstr: 0: HOA After hoalgon
                      RPC Calling nscontrol(0), rc=0
                      hostmstr: 0: RPC Before Exit Agent
                      hostmstr: 0: HOA Before hoaexit
                      Entered hgoexit at 2012/11/26-13:25:50
                      Exiting hgoexit, rc=0
                      hostmstr: 0: HOA After hoaexit
                      hostmstr: 0: RPC After Exit Agent

                      Even if I copy the content of ona_nb-odbc.ini file to /etc/unixODBC/obc.ini

                      I still get the same error.

                      Any ideas?
                      Michael
                      • 8. Re: Error connecting to mysql via ODBC
                        Kgronau-Oracle
                        Michael,
                        unixODBC 2.1.1. is outdated and has a couple of issues in a 64bit environment, so not the best choice.
                        I would commonly prefer to use a newer unixODBC Driver manager.

                        Regarding the username and password, to preserve the case in Oracle you need to surround the username and password in the database link statement by double quotes:
                        create database link "<MySQL UID>" connect to "<MySQL PWD>" using '<your gateway alias>';

                        Usernames/passwords stored in the gateway init file or in the odbc.ini aren't used by the gateway initial connection.

                        Cheers,
                        Klaus
                        • 9. Re: Error connecting to mysql via ODBC
                          348706
                          Hi Klaus,

                          Thank you for the support!

                          I finally managed to connect!

                          I still have an issue with the data returning back to Oracle (only the first character of each cell is visible in Oracle) Do you have any hint?

                          I will update the ODBC Driver and check if the issue can be solved then.

                          Thank you

                          Michael
                          • 10. Re: Error connecting to mysql via ODBC
                            Kgronau-Oracle
                            Michael,
                            sorry, no adhoc solution. Updating the ODBC driver is always a good idea - especially when you already have it installed on your box. When it continues to fail, maybe create a new thread and provide the table description of the source MySQL table and the way it is seen in Oracle using describe <tablename>@<dblink>;