MariaDB CONNECT Storage Engine allows to access heterogeneous datasourses. This includes various file formats. But this also includes ODBC accessible datasources
The CONNECT Storage Engine ODBC table type allows to access SQLite, Excel, SQL Server or Oracle databases. Some nice features of the ODBC CONNECT table type are:
- Auto discovery of table structure. This means that you do not need to specify the columns of the target table.
- Condition push down. This is an optimization that push filtering conditions to the target database. This can avoid a lot of network roundtrips.
Let us have a look and test the Oracle access through the CONNECT Storage Engine. I downloaded, unzipped and installed Oracle 11gR2 in a virtual box.
Let us start Oracle 11gR2
[oracle@centos1 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 29 15:15:59 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> connect sys/manager1 as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 617975808 bytes Fixed Size 2215864 bytes Variable Size 377487432 bytes Database Buffers 230686720 bytes Redo Buffers 7585792 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now let us start the oracle listener (this concept doest not exist in MySQL).
[oracle@centos1 ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-NOV-2013 20:08:20 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/centos1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos1)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 29-NOV-2013 20:08:20 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/centos1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos1)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "plsextproc" has 1 instance(s). Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@centos1 ~]$
On the machine with the MariaDB CONNECT Storage Engine we need to install the Oracle ODBC Driver. We obtain it through the installation of the Oracle instant client. Let us download and unzip the instant client. Installation is just unzip of the instant client zip files.
unzip instantclient-basic-linux.x64-12.1.0.1.0.zip unzip instantclient-odbc-linux.x64-12.1.0.1.0.zip unzip instantclient-sdk-linux.x64-12.1.0.1.0.zip
We need to add the path of the ODBC driver shared library. Let us 'vi .bash_profile' and add the required LD_LIBRARY_PATH.
CLIENT_HOME=/home/oraclec/instantclient_12_1 ; export CLIENT_HOME LD_LIBRARY_PATH=$CLIENT_HOME:$LD_LIBRARY_PATH ; export LD_LIBRARY_PATH
On linux we also need to install the unixODBC open source ODBC driver manager :
yum install unixODBC yum install unixODBC-devel
The configuration of the unixODBC driver manager is simple and just requires 2 files updates:
/etc/odbcinst.ini wich points the driver to the installed shared library
[Oracle 12c ODBC driver] Description = Oracle ODBC driver for Oracle 12c Driver = /home/oraclec/instantclient_12_1/libsqora.so.12.1
/etc/odbc.ini wich defines here the DSN that will be used for the ODBC connection
[orcl] Driver = Oracle 12c ODBC driver ServerName = //centos1.localdomain:1521/oracle DSN = orcl UserName = scott Password = manager1
How to test that the unixODBC + oracle ODBC driver is correctly installed ?
The unixODBC includes an ODBC client tool 'isql'. Let us use it to test our installation and the DSN we just created.
root@centos1 ~]# isql orcl scott manager1 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from emp; +-------+-----------+----------+-------+--------------------+---------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL +-------+-----------+----------+-------+--------------------+---------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800 … +-------+-----------+----------+-------+--------------------+---------- SQLRowCount returns -1 15 rows fetched SQL>
Now let us create a CONNECT table of ODBC type. we will make this MariaDB ODBC table point to the Oracle 11g 'scott.emp' table.
[root@centos1 ~]# mysql -u root -pmanager1 -S /usr/local/mariadb10data/mariadb10.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.0.6-MariaDB-log MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [test]> create table emp ENGINE=CONNECT TABLE_TYPE=ODBC tabname='emp' CONNECTION='DSN=orcl;UID=scott;PWD=manager1'; ERROR 1105 (HY000): Unsupported table type ODBC
Come on ! what happened ? I just installed the latest 10.0.6 MariaDB and the CONNECT storage Engine does not support the ODBC table type. That is true and that is an issue caused by old machine being used to build the binary the unnixODBC. This problem is not present in the MariaDB 10.0.6 rpm packages. But I have multiple tar based MariaDB version installed on my Centos box(in fact a virtual box vm) and I do not want any rpm based installation. Hopefully a storage engine is just a shared library.
Let us download MariaDB-10.0.6-centos6-x86_64-connect-engine.rpm and unpack the rpm without installing it :
[root@centos1 ~]# rpm2cpio ../MariaDB-10.0.6-centos6-x86_64-connect-engine.rpm | cpio --extract --make-directories 8216 blocs [root@centos1 plugin]# cp ./usr/lib64/mysql/plugin/ha_connect.so ha_connect.so /usr/local/mariadb10/lib/plugin/
We now have the CONNECT storage with the support of the ODBC table type. Our second try :
[root@centos1 local]# mysql -u root -pmanager1 -S /usr/local/mariadb10data/mariadb10.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.0.6-MariaDB-log MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use test Database changed MariaDB [test]> create table toto ENGINE=CONNECT TABLE_TYPE=ODBC tabname='emp' CONNECTION='DSN=orcl;UID=scott;PWD=manager1'; ERROR 1105 (HY000): [unixODBC][Driver Manager]Can't open lib '/home/oraclec/instantclient_12_1/libsqora.so.12.1' : file not found
?
I had forgotten that the mysql service is run under the mysql linux user. This user also needs to have access to the Oracle ODBC driver (libsqora.so.12.1 shared library provided by the Oracle instant client).
CLIENT_HOME=/home/oraclec/instantclient_12_1 ; export CLIENT_HOME LD_LIBRARY_PATH=$CLIENT_HOME:$LD_LIBRARY_PATH ; export LD_LIBRARY_PATH
Once done everything should goes better:
MariaDB [test]> create table toto ENGINE=CONNECT TABLE_TYPE=ODBC tabname='EMP' CONNECTION='DSN=orcl;UID=scott;PWD=manager1';Query OK, 0 rows affected (0.79 sec) MariaDB [test]> MariaDB [test]> select * from toto; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | … | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.09 sec) MariaDB [test]> insert into toto values ( 8000,'serge','peintre',7902,'1980-12-17',123,234,20); Query OK, 1 row affected (0.15 sec) MariaDB [test]> select * from toto;+-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 8000 | serge | peintre | 7902 | 1980-12-17 | 123.00 | 234.00 | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | … | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 15 rows in set (0.03 sec)
That looks nice. I have created an ODBC table pointing to a remote Oracle 11g table. That was quite simple as the auto discovery mechanism has automatically discovered the structure of the Oracle table and converted Oracle data types to compatible mysql column type.
Let us try to update Oracle data through MySQL :
MariaDB [test]> update toto set sal=999 where empno=8000; ERROR 1296 (HY000): Got error 174 'No DELETE/UPDATE of ODBC tablesd' from CONNECT
This is not supported ! That is right. This ODBC table update feature has been developed and is currently only available in the launchpad repository of the CONNECT Storage Engine.
CONNECT Storage Engine Sources on launchpad