The error message "libsqora.so.11.1: file not found" in the context of connecting to Oracle from MariaDB

Question

I'm having trouble resolving an issue.

The OS is CentOS, and the version of MariaDB is 10.4.

UnixODBC is installed and configured properly with odbc.ini and odbcinit.ini.

When I try connecting using 'isql -v orcl scott tiger', it successfully connects.

However, when I try the same in MariaDB, I encounter the following error:

Maria02 [test]> CREATE TABLE ora_emp -> ENGINE=CONNECT -> TABLE_TYPE=ODBC TABNAME='EMP' CONNECTION='DSN=orcl;UID=scott;PWD=tiger'; ERROR 1105 (HY000): SQLDriverConnect: [unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_11_2/libsqora.so.11.1': file not found The LD_LIBRARY_PATH environment variable is set to /home/oracle/instantclient_11_2.

[root@localhost instantclient_11_2]# ldd libsqora.so.11.1
linux-vdso.so.1 => (0x00007fff0d2b5000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fb5a7c54000)
libm.so.6 => /lib64/libm.so.6 (0x00007fb5a7952000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb5a7736000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fb5a751c000)
libclntsh.so.11.1 => /home/oracle/instantclient_11_2/libclntsh.so.11.1 (0x00007fb5a4bad000)
libodbcinst.so.1 => /lib64/libodbcinst.so.1 (0x00007fb5a499b000)
libc.so.6 => /lib64/libc.so.6 (0x00007fb5a45cd000)
/lib64/ld-linux-x86-64.so.2 (0x00007fb5a7e58000)
libnnz11.so => /home/oracle/instantclient_11_2/libnnz11.so (0x00007fb5a4200000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007fb5a3ffe000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007fb5a3df4000)

The above is the result of the 'ldd' command, and it seems there are no issues.

The file '/home/oracle/instantclient_11_2/libsqora.so.11.1' definitely exists.

I would greatly appreciate any assistance in resolving this issue.

Thank you.

$ rpm -qa unixODBC*
unixODBC-2.3.1-14.el7.x86_64
unixODBC-devel-2.3.1-14.el7.x86_64
$ export ORACLE_HOME=/home/oracle/instantclient_11_2
$ export LD_LIBRARY_PATH=$ORACLE_HOME

$ vi /etc/odbcinst.ini
​[Oracle 11g ODBC driver]
Description  = Oracle ODBC driver for Oracle 11g
Driver       = /home/oracle/instantclient_11_2/libsqora.so.11.1

 
$ vi /etc/odbc.ini  
​[orcl]
Driver       = Oracle 11g ODBC driver
ServerName   = //192.168.80.1:1521/oracle
DSN          = orcl
UserName     = scott
Password     = tiger

$ isql -v orcl scott tiger

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

$ mysql -uroot

MariaDB [(none)]> install plugin connect soname 'ha_connect.so';
MariaDB [(none)]> use test;
MariaDB [test]> CREATE TABLE ora_emp ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='EMP' CONNECTION='DSN=orcl;UID=scott;PWD=tiger';

ERROR 1105 (HY000): SQLDriverConnect: [unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_11_2/libsqora.so.11.1' : file not found 

Answer 1

As MariaDB is usually started by systemd on CentOS (and others), and the default systemd services have ProtectHome=yes causing the /home files to be inaccessible by the service.

Recommend:

mv /home/oracle /usr/local

And change paths to /usr/local/oracle/instantclient_11_2

The LD_LIBRARY_PATH also needs to exist for the systemd service.

systemctl edit mariadb.service and append to this file:

[Service]
Environment="LD_LIBRARY_PATH=/usr/local/oracle/instantclient_11_2"