Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9070

Access to MariaDB table from Oracle returns ORA-02085: database link <name> connects to HO.WORLD

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.0(EOL)
    • 10.0.20
    • OTHER
    • None
    • Server version: 10.0.20-MariaDB-log MariaDB Server
      Red Hat Linux
      Connecting from Oracle Enterprise Edition 11.2.0.4 with "global_names=true" set.

    Description

      See the following. This is using Oracle Gateway to ODBC to access MariaDB.

      SQL> create public database link ZLX70604.DB.ATT.COM connect to "dblink" identified by "<password>" using 'ZLX70604.DB.ATT.COM';

      Database link created.

       
      SQL> select * from "test1"@ZLX70604.DB.ATT.COM;
      select * from "test1"@ZLX70604.DB.ATT.COM
      *
      ERROR at line 1:
      ORA-02085: database link ZLX70604.DB.ATT.COM connects to HO.WORLD
       
      SQL> alter session set global_names=false;
       
      Session altered.
       
      SQL> select * from "test1"@ZLX70604.DB.ATT.COM;
       
      col       col2
      --- ----------
      B          987   <<< works with global_names=false.

      Attachments

        Activity

          aferrett,

          Could you please clarify why you consider it a MariaDB problem?
          Be it a bug or a feature, it appears to be happening on the gateway side.
          Also, quick internet search shows that it might be a known issue

          https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206
          https://arindomdam.wordpress.com/2013/05/07/access-mysql-from-oracle/

          elenst Elena Stepanova added a comment - aferrett , Could you please clarify why you consider it a MariaDB problem? Be it a bug or a feature, it appears to be happening on the gateway side. Also, quick internet search shows that it might be a known issue https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206 https://arindomdam.wordpress.com/2013/05/07/access-mysql-from-oracle/
          aferrett Andy Ferretti added a comment -

          We prefer to run Oracle with GLOBAL_NAMES=true. The question is whether a non-oracle database can support GLOBAL_NAMES. I'll be working with other products to see if this is possible.

          aferrett Andy Ferretti added a comment - We prefer to run Oracle with GLOBAL_NAMES=true. The question is whether a non-oracle database can support GLOBAL_NAMES. I'll be working with other products to see if this is possible.

          Andy Ferretti,
          I don't have experience with Oracle Gateway, but reading http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams088.htm I would imagine that it shouldn't be impossible with non-oracle database. The other thing is that I don't quite understand what is "database name" here - "GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects". But I assume you have experience with setting such linked oracle database, and thus configuring everything correctly with the link to MariaDb server as well.

          What ODBC connector do you use for the link? Ours or MySQL's? I would start from looking into ODBC trace to see if there are any errors there. If there is a problem here - most probably it is in the ODBC connector. Or in the database link configuration.

          Lawrin Lawrin Novitsky added a comment - Andy Ferretti, I don't have experience with Oracle Gateway, but reading http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams088.htm I would imagine that it shouldn't be impossible with non-oracle database. The other thing is that I don't quite understand what is "database name" here - "GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects". But I assume you have experience with setting such linked oracle database, and thus configuring everything correctly with the link to MariaDb server as well. What ODBC connector do you use for the link? Ours or MySQL's? I would start from looking into ODBC trace to see if there are any errors there. If there is a problem here - most probably it is in the ODBC connector. Or in the database link configuration.

          Is test1 the table name in MariaDB? Where do you specify what database (in MariaDB) you connect to? What is HO.WORLD?

          serg Sergei Golubchik added a comment - Is test1 the table name in MariaDB? Where do you specify what database (in MariaDB) you connect to? What is HO.WORLD ?
          aferrett Andy Ferretti added a comment - - edited

          Yes, test1 is a table in MariaDB.

          --------------
          describe test1
          --------------
           
          Field   Type    Null    Key     Default Extra
          col1    char(1) YES             NULL
          col2    int(10) YES             NULL

          The database name is defined in the ODBC connection to MySQL:

          [ODBC Data Sources]
          data_source_name = zlx70604
          data_source_name = zlx70604w
          data_source_name = zlx70604a
           
          [zlx70604]
          Driver          = /opt/app/x2dba3d2/mysql/mysql-connector-odbc-commercial-5.3.4-linux-el6-x86-64bit/lib/libmyodbc5a.so
          DATABASE        = dblink
          DESCRIPTION     = MySQL ODBC 5.3 ANSI Driver test
          SERVER          = zlx70604.vci.att.com
          UID             = dblink
          PASSWORD        =
          SOCKET          =

          This is linked to the Oracle Gateway to ODBC

          HS_FDS_CONNECT_INFO =zlx70604
          HS_FDS_TRACE_LEVEL =1
          ###HS_FDS_SHAREABLE_NAME =/opt/app/x2dba3d2/mysql/mysql-connector-odbc-commercial-5.3.4-linux-el6-x86-64bit/lib/libmyodbc5a.so
          HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
          HS_LANGUAGE=american_america.we8mswin1252
          HS_NLS_NCHAR=UCS2

          I believe that the HO.WORLD is a domain. My domain on the Oracle side is db.att.com. I believe that the error "ORA-02085: database link ZLX70604.DB.ATT.COM connects to HO.WORLD" is saying that my global name is ZLX70604.DB.ATT.COM, but the target global name is ZLX70604.HO.WORLD.
          I'm OK with putting this on hold for now. I'm currently working to get MariaDB replication working. Basically, I'm trying to increase my knowledge of MariaDB while testing the types of functionality needed to migrate Oracle to MariaDB.

          aferrett Andy Ferretti added a comment - - edited Yes, test1 is a table in MariaDB. -------------- describe test1 --------------   Field Type Null Key Default Extra col1 char(1) YES NULL col2 int(10) YES NULL The database name is defined in the ODBC connection to MySQL: [ODBC Data Sources] data_source_name = zlx70604 data_source_name = zlx70604w data_source_name = zlx70604a   [zlx70604] Driver = /opt/app/x2dba3d2/mysql/mysql-connector-odbc-commercial-5.3.4-linux-el6-x86-64bit/lib/libmyodbc5a.so DATABASE = dblink DESCRIPTION = MySQL ODBC 5.3 ANSI Driver test SERVER = zlx70604.vci.att.com UID = dblink PASSWORD = SOCKET = This is linked to the Oracle Gateway to ODBC HS_FDS_CONNECT_INFO =zlx70604 HS_FDS_TRACE_LEVEL =1 ###HS_FDS_SHAREABLE_NAME =/opt/app/x2dba3d2/mysql/mysql-connector-odbc-commercial-5.3.4-linux-el6-x86-64bit/lib/libmyodbc5a.so HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so HS_LANGUAGE=american_america.we8mswin1252 HS_NLS_NCHAR=UCS2 I believe that the HO.WORLD is a domain. My domain on the Oracle side is db.att.com. I believe that the error "ORA-02085: database link ZLX70604.DB.ATT.COM connects to HO.WORLD" is saying that my global name is ZLX70604.DB.ATT.COM, but the target global name is ZLX70604.HO.WORLD. I'm OK with putting this on hold for now. I'm currently working to get MariaDB replication working. Basically, I'm trying to increase my knowledge of MariaDB while testing the types of functionality needed to migrate Oracle to MariaDB.

          Lawrin, would you mind taking this to monitor further findings?
          At any point you can reassign it to whoever you think it rather belongs to, or to me if unsure.

          elenst Elena Stepanova added a comment - Lawrin , would you mind taking this to monitor further findings? At any point you can reassign it to whoever you think it rather belongs to, or to me if unsure.

          Andy Ferretti,
          I believe this is not the bug in MariaDB, but wrong configuration of the database link.
          Here are some links that can either help you, or at least prove that MariaDB has nothing to do with it:

          https://community.oracle.com/message/1444436 - probably most relevant
          http://www.dba-oracle.com/t_ora_02085_database_link_string_connrct_to_string.htm
          and maybe this https://community.oracle.com/thread/2323083 - it's probably wrong solution but still proves my point

          I guess the idea is change default domain name. Where to do that - I am not sure. On links I gave you people refer to old oracle versions

          I am closing the report for now

          Lawrin Lawrin Novitsky added a comment - Andy Ferretti, I believe this is not the bug in MariaDB, but wrong configuration of the database link. Here are some links that can either help you, or at least prove that MariaDB has nothing to do with it: https://community.oracle.com/message/1444436 - probably most relevant http://www.dba-oracle.com/t_ora_02085_database_link_string_connrct_to_string.htm and maybe this https://community.oracle.com/thread/2323083 - it's probably wrong solution but still proves my point I guess the idea is change default domain name. Where to do that - I am not sure. On links I gave you people refer to old oracle versions I am closing the report for now

          People

            Lawrin Lawrin Novitsky
            aferrett Andy Ferretti
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.