This discussion is archived
10 Replies Latest reply: Nov 27, 2012 3:25 AM by kgronau RSS

Error connecting to mysql via ODBC

348706 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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>;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points