Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1.19, 10.1.23
-
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
|