1

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
dae-hee Kim.
  • 23
  • 6
  • 1
    Oracle home is in /home/`oracle`/instantclient_11_2, while in odbcinst.ini driver is /home/`oraclec`/instantclient_11_2/libsqora.so.11.1 ?? Looks like a typo – Georg Richter Jul 07 '23 at 06:52
  • Sorry it was a typo by my mistake, The text has been modified. – dae-hee Kim. Jul 07 '23 at 11:03
  • Take a look through [this blog](https://mariadb.org/data-migration-from-oracle-to-mariadb-with-docker-and-connect-se-a-step-by-step-guide/). – danblack Jul 11 '23 at 12:03
  • I see your other question resolved the connection problem. How did you do it? – danblack Jul 13 '23 at 23:10

1 Answers1

0

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"
danblack
  • 12,130
  • 2
  • 22
  • 41
  • @dae-hee-kim, if this correctly solves your problem, can you please [accept the answer](https://stackoverflow.com/help/someone-answers). Welcome to SO. Good questions and accepted answers are the basis for other people encountering the same problem. – danblack Jul 10 '23 at 22:09
  • It doesn't work, the same error message : `ERROR 1105 (HY000): SQLDriverConnect: [unixODBC][Driver Manager]Can't open lib '/usr/local/oracle/instantclient_11_2/libsqora.so.11.1' : file not found` – dae-hee Kim. Jul 11 '23 at 04:37
  • Try `ldd /usr/local/oracle/instantclient_11_2/libsqora.so.11.1` - there might be a missing dependency it needs. – danblack Jul 11 '23 at 04:50
  • Absolutely not. `[root@localhost instantclient_11_2]# ldd libsqora.so.11.1 linux-vdso.so.1 => (0x00007ffefbb18000) libdl.so.2 => /lib64/libdl.so.2 (0x00007fd8e145a000) libm.so.6 => /lib64/libm.so.6 (0x00007fd8e1158000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd8e0f3c000) libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fd8e0d22000)` – dae-hee Kim. Jul 11 '23 at 07:49
  • `libclntsh.so.11.1 => /usr/local/oracle/instantclient_11_2/libclntsh.so.11.1 (0x00007fd8de3b3000) libodbcinst.so.1 => /lib64/libodbcinst.so.1 (0x00007fd8de1a1000) libc.so.6 => /lib64/libc.so.6 (0x00007fd8dddd3000) /lib64/ld-linux-x86-64.so.2 (0x00007fd8e165e000) libnnz11.so => /usr/local/oracle/instantclient_11_2/libnnz11.so (0x00007fd8dda06000) libaio.so.1 => /lib64/libaio.so.1 (0x00007fd8dd804000) libltdl.so.7 => /lib64/libltdl.so.7 (0x00007fd8dd5fa000)` – dae-hee Kim. Jul 11 '23 at 07:49
  • I see you've just accepted this. Did something magicly resolve or are you still stuck? – danblack Jul 11 '23 at 08:57
  • I still couldn't find a solution, so I rebuilt the CentOS environment using Docker and performed the same tasks. Despite still not resolving the issue, I started getting connections after executing the "chmod 777 libsqora.so.11.1" command and adding "Environment="LD_LIBRARY_PATH=/usr/local/oracle/instantclient_11_2"" to the "systemctl edit mariadb.service" as you instructed. Anyway, thank you. I still haven't resolved the problem with the original PC. – dae-hee Kim. Jul 11 '23 at 09:00
  • When I set up a different PC environment, the issue was resolved, but it still doesn't work on the original PC. I've tried to think about what could be different, but other than the fact that the version of MariaDB is higher and that the successful environment was in a Docker environment, there are no other differences. I still can't figure out the reason. – dae-hee Kim. Jul 11 '23 at 11:13
  • Although you didn't provide a fundamental solution, I sincerely appreciate your assistance. – dae-hee Kim. Jul 11 '23 at 11:33
  • I have accepted your answer, but I will still keep an eye on the responses. – dae-hee Kim. Jul 11 '23 at 11:35