Connecting to Oracle from MariaDB Enterprise Server using Spider
The Spider Storage Engine was originally created to implement sharding for MariaDB, where Spider acts as a link from one MariaDB Enterprise Server to one or more other MariaDB Enterprise Servers with the Partitioning Engine doing the actual sharding. Spider does this and does it well, but there are more use cases for Spider. For one thing, it can act as a singular link from one MariaDB Enterprise Server to another. And not only that, on the other end of the link might be something else other than MariaDB using ODBC.
Let’s say you run a website, powered by MariaDB in a classic LAMP-stack setup with Linux, Apache, MariaDB and PHP. In that website you want to access data from your ERP database that is running Oracle. You can get at that data by connecting the PHP application to Oracle, but that is less than perfect, for example you cannot join data in that Oracle database with application data that is in MariaDB. Or maybe you are migrating from Oracle to MariaDB and need a means to copy the data from Oracle to MariaDB. Or maybe you are in the midst of a migration project with most of the data you need is in MariaDB but some data still resides with some other application data in Oracle. There are, as you can see, several reasons why you would want to connect MariaDB to Oracle.
This blog post will show you how to do just that, but let’s begin to have a look at how this would work in theory and in this example, we are going to show you how to connect to Oracle. One thing that is different from Spider compared to, say, database links in Oracle is that Spider needs to know the schema of the table we are referencing. If all columns aren’t included, that is fine and if the data types don’t match, well that might cause issues. If you leave out a NOT NULL column that doesn’t have a default, then you will have difficulties inserting data obviously. Anyway, it would look something like this, from a high level:
So now we know what we are aiming for. Let’s start installing what we need, and first we need to install the Spider Storage Engine package, like this:
$ sudo yum install MariaDB-spider-engine
Once the Spider engine is installed in the operating system we need to install the plugin in MariaDB Enterprise Server using the MariaDB console
$ sudo mariadb -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 23 Server version: 10.6.15-10-MariaDB-enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> INSTALL SONAME 'ha_spider'; Query OK, 0 rows affected (0.001 sec)
The next thing we need to do is to install the ODBC driver manager, unixODBC, but this is installed by default in most Linux distributions so it is likely there already, check if the file /etc/odbcinst.ini is there and if it is, then unixODBC is installed. Two configuration files are used by unixODBC, one that configures the ODBC drivers and one that configures the connections for these drivers as each driver may be used for multiple connections. If you check in the /etc/odbcinst.ini you can see if an Oracle ODBC driver is already in place. The likely case is that there is no Oracle ODBC driver installed so we need to fix that. The Oracle ODBC driver is part of an Oracle package called Oracle Instant Client and this may be found in different places depending on the version but in my case, I found it here: https://www.oracle.com/se/database/technologies/instant-client/downloads.htmlÂ
To install the ODBC driver you also need the Basic Client installed (the Basic Lite Client will not suffice) and I picked an Oracle 12.2 rpm installation. Once that was downloaded we install the Basic Client first, followed by the ODBC driver:
$ sudo yum install \ oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm $ sudo yum install \ oracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpm
Is this enough then? I mean, it is all Oracle stuff and the ODBC drivers require the libraries installed by the first Basic package so they know about each other. No, this is Oracle after all.
To begin with, it is likely best to add the Oracle client library directory to the ldd configuration so that it can be found by the ODBC driver. In my case, with CentOS 7, that means I have to add a file to the /etc/ld.so.conf.d that I call oracle.conf and then add the path to the client libraries there, have a look at what is installed by the first Basic Instant client (run rpm -qlp <rpm file> to see what is installed and where). So we do this:
$ sudo vi /etc/ld.so.conf.d/oracle.conf
And then add something like this to this file, but make sure to use the path used in your case, save it and then exit the editor:
# Oracle client library. /usr/lib/oracle/12.2/client64/lib
For this to take effect you need to run ldconfig.
$ ldconfig
At this point, the Oracle ODBC driver should be installed, but it is not configured. Oracle wants you to run a supplied script to set up the ODBC configuration files. This is not a very good script and gets a few things wrong, but it is a starting point. The script takes some arguments and in my case, with a default CentOS 7 installation, it looks like this:
$ sudo /usr/share/oracle/12.2/client64/odbc_update_ini.sh / "" "" "" \ /etc/odbc.ini
As I said, this gets a few things wrong, the location of the ODBC driver in the /etc/odbcinst.ini file for example. So let’s fix that, first check where the ODBC driver file is, in my case, it is in /usr/share/oracle/12.2/client64/lib/libsqora.so.12.1 and then we can edit this path:
$ sudo vi /etc/odbcinst.ini
And then go to the Section with the newly installed Oracle ODBC driver and edit the Driver directive to reflect the correct driver path. After editing this section looks like this in my case:
[Oracle 12c ODBC driver] Description   = Oracle ODBC driver for Oracle 12c Driver     = /usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1 Setup      = FileUsage    = CPTimeout    = CPReuse     =
I know, this isn’t going anywhere fast, but don’t blame me, I didn’t invent it, I’m just trying to explain it. Before we get back to working with Spider, we have to set up an ODBC Data Source (the name of a Data Source is called a DSN, Data Source Name. Cute) in the /etc/odbc.ini and the script we run above set up a framework for this. Now we need to make sure it points to the right server. In my case, Oracle is running on the default Oracle port (1521) on a host called moe so to connect to this server, we have to change the ServerName in the Data Source. In addition, I’d like to change the name (DSN) of this, but all the other parameters I will leave as they are. After editing the Oracle Data Source in /etc/odbc.ini looks like this:
[OracleMoe] Application Attributes = T Attributes = W BatchAutocommitMode = IfAllSuccessful BindAsFLOAT = F CloseCursor = F DisableDPM = F DisableMTS = T Driver = Oracle 12c ODBC driver DSN = OracleODBC-12c EXECSchemaOpt = EXECSyntax = T Failover = T FailoverDelay = 10 FailoverRetryCount = 10 FetchBufferSize = 64000 ForceWCHAR = F Lobs = T Longs = T MaxLargeData = 0 MetadataIdDefault = F QueryTimeout = T ResultSets = T ServerName = //moe:1521 SQLGetData extensions = F Translation DLL = Translation Option = 0 DisableRULEHint = T UserID = StatementCache=F CacheBufferSize=20 UseOCIDescribeAny=F SQLTranslateErrors=F MaxTokenSize=8192 AggregateSQLType=FLOAT
With this in place, let’s try it and see if it works. For this unixODBC comes with a nifty tool called isql that allows you to connect to an ODBC Data Source. To connect to that remote Oracle database using the username scott with the password tiger we would do this:
$ isql OracleMoe scott tiger
And we can then run a query
SQL> SELECT * FROM dept; +-------+---------------+--------------+ | DEPTNO| DNAME Â Â Â Â | LOCÂ Â Â Â Â | +-------+---------------+--------------+ | 10Â Â | ACCOUNTINGÂ Â | NEW YORK Â Â | | 20Â Â | RESEARCHÂ Â Â | DALLAS Â Â Â | | 30Â Â | SALES Â Â Â Â | CHICAGOÂ Â Â | | 40Â Â | OPERATIONSÂ Â | BOSTON Â Â Â | +-------+---------------+--------------+ SQLRowCount returns -1 4 rows fetched
Hey, finally we have the Oracle ODBC Driver working! Great! Now it is time to connect MariaDB with Oracle, let’s have a go at the dept table that we used above. To connect to Oracle we need to connect to unixODBC and pass the DSN, or the Data Source Name, in our case that would be OracleMoe and we also need the username and password.
There are a couple of different ways to define how you connect from Spider through ODBC, one is to specify all parameters as part of the table being created, in the CONNECTION section of the table creation. Another means would be to use a SERVER as defined by the CREATE SERVER command, which is how Spider usually does it, but that is currently not available for ODBC connections. So the only other way is to pass parameters by defining them in the /etc/odbc.ini file.
Let’s give it a shot by creating a dept table in MariaDB and pointing it to the same table in Oracle. As we said above, Spider needs to know the schema of the table it is linking to, it doesn’t pick this up from the remote table. Let us see what the table looks like in Oracle:
CREATE TABLE dept( Â Â deptno NUMBER(2, 0), Â Â dname VARCHAR2(14), Â Â loc VARCHAR2(13), Â Â CONSTRAINT pk_dept PRIMARY KEY (deptno));
First, we need to deal with the data types. The two VARCHAR2 columns are easy, a VARCHAR2 in Oracle is the same as a VARCHAR in MariaDB, but what about NUMBER(2,0)? The Oracle NUMBER datatype is kind of odd, it is a variable length fixed point data type that can store both INTEGERS and DECIMALS. In the case above though the number of decimals is 0, so this is an INTEGER and it has only up to 2 digits, which means this column stores integers in the range 0 – 99 inclusive. In other words, we can use a TINYINT. So let’s make a first attempt at creating a table in MariaDB Server that links to the dept table in Oracle.
We need to create the table itself, with the 3 columns as above, but we also need to specify how to connect to Oracle. We will use ODBC for this, so we have to state that and we also need to specify the DSN to connect to and a username and a password. All this would look like this in MariaDB:
MariaDB> CREATE TABLE dept(deptno TINYINT PRIMARY KEY,
  dname VARCHAR(14),
  loc VARCHAR(13)) ENGINE=Spider
  CONNECTION='WRAPPER "odbc", DSN "OracleMoe",
  USER "scott", PASSWORD "tiger"';
Query OK, 0 rows affected (0.025 sec)
That looks good, so let’s try to do a SELECT from the table:
MariaDB> SELECT * FROM dept; ERROR 12712 (HY000): Error from ODBC 1017 28000 [unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
What is this then? The username and password worked fine when we connected with the isql tool? Well, we ran into an annoying bug, when Spider passes a username and password as part of the connection string to unixODBC the quoting is not done properly. Luckily, there is a solution to this which is to specify the username and password as part of the DSN in /etc/odbc.ini. So edit this file and edit the line UserID to the user you want to use and add a Password parameter entry:
UserID = scott Password = tiger
We also need to recreate that dept table and remove the USER and PASSWORD parameters:
MariaDB> DROP TABLE dept; Query OK, 0 rows affected (0.029 sec) MariaDB> CREATE TABLE dept(deptno TINYINT PRIMARY KEY,   dname VARCHAR(14),   loc VARCHAR(13))   ENGINE=Spider CONNECTION='WRAPPER "odbc", DSN "OracleMoe"'; Query OK, 0 rows affected (0.030 sec) MariaDB> SELECT * FROM dept; ERROR 12712 (HY000): Error from ODBC 942 42S02 [Oracle][ODBC][Ora]ORA-00942: table or view does not exist
What? Another error, and a strange one? Again, this worked with isql so why not with Spider? The reason is that names of columns and tables and stuff in the SQL syntax get quoted when using Spider, and quoted names are treated as case-sensitive in Oracle. And Oracle identifiers are uppercase by default, even though you used lowercase when creating them.
Fixing this is easy though, all we need to do is tell Spider what the name we are linking to and use upper case and also use upper case for the column names. So again, let’s recreate the table in MariaDB with this in mind:
MariaDB> DROP TABLE dept; Query OK, 0 rows affected (0.032 sec) MariaDB> CREATE TABLE dept(DEPTNO TINYINT PRIMARY KEY,   DNAME VARCHAR(14),   LOC VARCHAR(13))   ENGINE=Spider CONNECTION='WRAPPER "odbc", DSN "OracleMoe", TABLE "DEPT"'; Query OK, 0 rows affected (0.025 sec) MariaDB> SELECT * FROM dept; +--------+------------+----------+ | DEPTNO | DNAME   | LOC   | +--------+------------+----------+ |   10 | ACCOUNTING | NEW YORK | |   20 | RESEARCH  | DALLAS  | |   30 | SALES   | CHICAGO | |   40 | OPERATIONS | BOSTON  | +--------+------------+----------+ 4 rows in set (0.302 sec)
Hey, pretty cool, it works! Once you have it all set up it is pretty useful, right. So, one thing that Oracle users who come to MariaDB ask for now is “Where is the ROWID?” And the answer is that MariaDB doesn’t have a rowid. But in this case, the dept table actually does have rowid, but it is only on the Oracle side of things, but it would really be useful to have access to it. Sure, that is possible, we just add it to the table definition, let’s recreate that table once again.
MariaDB> DROP TABLE dept; Query OK, 0 rows affected (0.036 sec) MariaDB> CREATE TABLE dept(DEPTNO TINYINT PRIMARY KEY,   DNAME VARCHAR(14),   LOC VARCHAR(13),   ROWID VARCHAR(18))   ENGINE=Spider CONNECTION='WRAPPER "odbc", DSN "OracleMoe", TABLE "DEPT"'; Query OK, 0 rows affected (0.030 sec) MariaDB> SELECT * FROM dept; +--------+------------+----------+--------------------+ | DEPTNO | DNAME   | LOC   | ROWID       | +--------+------------+----------+--------------------+ |   10 | ACCOUNTING | NEW YORK | AAA36sAAEAABB4lAAA | |   20 | RESEARCH  | DALLAS  | AAA36sAAEAABB4lAAB | |   30 | SALES   | CHICAGO | AAA36sAAEAABB4lAAC | |   40 | OPERATIONS | BOSTON  | AAA36sAAEAABB4lAAD | +--------+------------+----------+--------------------+ 4 rows in set (0.389 sec)
That is close, but no cigar. Yet. “Really, I only want the ROWID when I ask for it, I don’t want it when I do a SELECT *” is what your Oracle user tells you and thinks he’s got now, but no. OK, let’s use another neat MariaDB feature then, invisible columns and recreate the table, again.
MariaDB> DROP TABLE dept; Query OK, 0 rows affected (0.035 sec) MariaDB> CREATE TABLE dept(DEPTNO TINYINT PRIMARY KEY,   DNAME VARCHAR(14),   LOC VARCHAR(13),   ROWID VARCHAR(18) INVISIBLE)   ENGINE=Spider CONNECTION='WRAPPER "odbc", DSN "OracleMoe", TABLE "DEPT"'; Query OK, 0 rows affected (0.025 sec) MariaDB> SELECT * FROM dept; +--------+------------+----------+ | DEPTNO | DNAME   | LOC   | +--------+------------+----------+ |   10 | ACCOUNTING | NEW YORK | |   20 | RESEARCH  | DALLAS  | |   30 | SALES   | CHICAGO | |   40 | OPERATIONS | BOSTON  | +--------+------------+----------+ 4 rows in set (0.340 sec) MariaDB> SELECT deptno, dname, rowid FROM dept; +--------+------------+--------------------+ | deptno | dname   | rowid       | +--------+------------+--------------------+ |   10 | ACCOUNTING | AAA36sAAEAABB4lAAA | |   20 | RESEARCH  | AAA36sAAEAABB4lAAB | |   30 | SALES   | AAA36sAAEAABB4lAAC | |   40 | OPERATIONS | AAA36sAAEAABB4lAAD | +--------+------------+--------------------+ 4 rows in set (0.106 sec)
Happy SQL’ing everyone
/Karlsson
