[MDEV-12355] CONNECT engine with ODBC table type can not get columns from Oracle RDBMS properly Created: 2017-03-24  Updated: 2020-08-25  Resolved: 2017-04-03

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.19, 10.1.23
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

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



 Comments   
Comment by Valerii Kravchuk [ 2017-03-24 ]

See https://jira.mariadb.org/browse/MDEV-7624 also.

Comment by Valerii Kravchuk [ 2017-04-03 ]

Adding dbname does not help:

[openxs@fc23 maria10.1]$ export TNS_ADMIN=/etc/oracle
 
[openxs@fc23 maria10.1]$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64
 
[openxs@fc23 maria10.1]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 
[openxs@fc23 maria10.1]$ export PATH=$PATH:$ORACLE_HOME/bin
 
[openxs@fc23 maria10.1]$ sudo ldconfig
 
[sudo] password for openxs:
 
[openxs@fc23 maria10.1]$ sqlplus system/oracle@localhost:1521/xe.oracle.docker
 
 
 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 3 11:18:36 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 scott.t1(c1 number(30));
 
 
 
Table created.
 
 
 
SQL> insert into scott.t1 values (1234567890);
 
 
 
1 row created.
 
 
 
SQL> select * from scott.t1;
 
 
 
        C1
 
----------
 
1234567890
 
 
 
SQL> exit
 
Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
 
[openxs@fc23 maria10.1]$ isql -v oracle
 
+---------------------------------------+
 
| Connected!                            |
 
|                                       |
 
| sql-statement                         |
 
| help [tablename]                      |
 
| quit                                  |
 
|                                       |
 
+---------------------------------------+
 
SQL> select * from scott.t1;
 
+---------------------------------+
 
| C1                              |
 
+---------------------------------+
 
| 1234567890                      |
 
+---------------------------------+
 
SQLRowCount returns -1
 
1 rows fetched
 
SQL> quit
 
[openxs@fc23 maria10.1]$ bin/mysqld_safe --no-defaults &
 
[1] 21391
 
[openxs@fc23 maria10.1]$ 170403 11:19:49 mysqld_safe Logging to '/home/openxs/dbs/maria10.1/data/fc23.err'.
 
170403 11:19:50 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/maria10.1/data
 
 
 
[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 2
 
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]> show engines;
 
+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
 
| Engine             | Support | Comment                                                                                          | Transactions | XA   | Savepoints |
 
+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
 
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                            | NO           | NO   | NO         |
 
| CSV                | YES     | CSV storage engine                                                                               | NO           | NO   | NO         |
 
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                        | NO           | NO   | NO         |
 
| MyISAM             | YES     | MyISAM storage engine                                                                            | NO           | NO   | NO         |
 
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats                               | NO           | NO   | NO         |
 
| SEQUENCE           | YES     | Generated tables filled with sequential values                                                   | YES          | NO   | YES        |
 
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                                           | NO           | NO   | NO         |
 
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                               | NO           | NO   | NO         |
 
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
 
+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
 
9 rows in set (0.00 sec)
 
 
 
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='scott.t1' connection='dsn=oracle';
 
ERROR 1105 (HY000): Cannot get columns from scott.t1
 
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='t1' dbname='scott' connection='dsn=oracle';
 
ERROR 1105 (HY000): Cannot get columns from t1

Comment by Olivier Bertrand [ 2017-04-03 ]

Indeed, setting dbname cannot help as they are both in the same shema.

I think the issue is that Oracle is sometimes case sensitive about table names. Your tables are actually named T1 and T2. Specifying their name as such could solve your problem.

Comment by Valerii Kravchuk [ 2017-04-03 ]

Indeed, when both shcema name and table name are written in uppercase, it works:

MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='t1' dbname='scott' connection='dsn=oracle';
 
ERROR 1105 (HY000): Cannot get columns from t1
 
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='T1' dbname='scott' connection='dsn=oracle';
 
ERROR 1105 (HY000): Cannot get columns from T1
 
MariaDB [test]> create table t1_oracle engine=connect table_type=ODBC tabname='T1' dbname='SCOTT' connection='dsn=oracle';
 
Query OK, 0 rows affected (1.29 sec)
 
 
 
MariaDB [test]> select * from t1_oracle;
 
+-------+
 
| C1    |
 
+-------+
 
| 12345 |
 
+-------+
 
1 row in set (0.07 sec)
 
 
 
MariaDB [test]> create table t2_oracle engine=connect table_type=ODBC tabname='SCOTT.T1' connection='dsn=oracle';
 
Query OK, 0 rows affected (3.54 sec)
 
 
 
MariaDB [test]> select * from t2_oracle;
 
+-------+
 
| C1    |
 
+-------+
 
| 12345 |
 
+-------+
 
1 row in set (0.06 sec)

All the details in setup are the same as before.

Can this be documented explicitly/properly somewhere in the knowledge base?

Comment by Olivier Bertrand [ 2017-04-03 ]

At the end of the CONNECT knowledge base documentation on ODBC table:
#https://mariadb.com/kb/en/mariadb/connect-table-types-odbc-table-type-accessing-tables-from-other-dbms/
There is a paragraphe on table names explaining this.

Generated at Thu Feb 08 07:57:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.