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

CONNECT engine with ODBC table type can not get columns from Oracle RDBMS properly

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.1.19, 10.1.23
    • N/A
    • None
    • Fedora 25, RHEL

    Description

      Even simplest tables in Oracle can not be accessed via CONNECT and ODBC table type in recent MariaDB 10.1.x versions. We get errors like these:

      MariaDB [test]> create table oracle_t1 engine=connect table_type=ODBC tabname='t1' connection='dsn=oracle';
      ERROR 1105 (HY000): Cannot get columns from t1
      MariaDB [test]> show warnings\G
      *************************** 1. row ***************************
        Level: Error
         Code: 1105
      Message: Cannot get columns from t1
      *************************** 2. row ***************************
        Level: Error
         Code: 1030
      Message: Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT
      2 rows in set (0.00 sec)
      

      This is what we have in Oracle:

      [openxs@fc23 maria10.1]$ sqlplus system/oracle@localhost:1521/xe.oracle.docker
      SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 24 11:32:23 2017
      Copyright (c) 1982, 2013, Oracle.  All rights reserved.
       
      Connected to:
      Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
       
      SQL> create table t1(c1 char(10));
       
      Table created.
       
      SQL> insert into t1 values ('abc');
       
      1 row created.
       
      SQL> create table t2(c1 number (30));
       
      Table created.
       
      SQL> insert into t2 values (123);
       
      1 row created.
       
      SQL> exit
      Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
      

      We can see the data in isql:

      [openxs@fc23 maria10.1]$ isql -v oracle
      +---------------------------------------+
      | Connected!                            |
      |                                       |
      | sql-statement                         |
      | help [tablename]                      |
      | quit                                  |
      |                                       |
      +---------------------------------------+
      SQL> select * from t1;
      +-----------+
      | C1        |
      +-----------+
      | abc       |
      +-----------+
      SQLRowCount returns -1
      1 rows fetched
      SQL> select * from t2;
      +---------------------------------+
      | C1                              |
      +---------------------------------+
      | 123                             |
      +---------------------------------+
      SQLRowCount returns -1
      1 rows fetched
      

      But in MariaDB we just can not get columns, neither directly nor via catfunc:

      [openxs@fc23 maria10.1]$ bin/mysql -uroot test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 4
      Server version: 10.1.23-MariaDB Source distribution
       
      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> create table oracle_t1 engine=connect table_type=ODBC tabname='t1' connection='dsn=oracle';
      ERROR 1105 (HY000): Cannot get columns from t1
      MariaDB [test]> show warnings\G
      *************************** 1. row ***************************
        Level: Error
         Code: 1105
      Message: Cannot get columns from t1
      *************************** 2. row ***************************
        Level: Error
         Code: 1030
      Message: Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT
      2 rows in set (0.00 sec)
       
      MariaDB [test]> create table oracle_t1_columns engine=connect table_type=ODBC catfunc=col tabname='t1' connection='dsn=oracle';
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> show warnings\G
      Empty set (0.00 sec)
       
      MariaDB [test]> select * from oracle_t1_columns;
      Empty set (4.96 sec)
      

      Some relevant details about the environment where I tested this:

      [openxs@fc23 maria10.1]$ rpm -q -a | grep oracle
      oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64
      oracle-xe-11.2.0-1.0.x86_64
      oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
      oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64
      [openxs@fc23 maria10.1]$ rpm -q -a | grep unixODBC
      unixODBC-2.3.4-3.fc25.x86_64
      unixODBC-devel-2.3.4-3.fc25.x86_64
       
      [openxs@fc23 maria10.1]$ cat /etc/oracle/tnsnames.ora
      XE =
       ( DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS =
        (PROTOCOL = TCP)
        (Host = 192.168.1.85)
        (Port = 1521)
        )
       )
       (CONNECT_DATA = (SID = XE)
       )
      )
       
      [openxs@fc23 maria10.1]$ cat /etc/odbcinst.ini
      ...
       
      [OracleODBC]
      Description = Oracle ODBC driver for Oracle 11g
      Driver64 = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
      FileUsage = 1
      Driver Logging = 7
       
      [openxs@fc23 maria10.1]$ cat /etc/odbc.ini
      [oracle]
      Driver = OracleODBC
      DSN = OracleODBC
      ServerName = XE
      UserID = system
      Password = oracle
      

      Attachments

        Activity

          People

            bertrandop Olivier Bertrand
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.