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
|
Attachments
Activity
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
|
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.
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?
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.
See https://jira.mariadb.org/browse/MDEV-7624 also.