Installing the Latest MariaDB from the Repository on Debian 10 and Downgrading to Older Minor Version

 I had not written any new blog posts here since this one for quite a some time, for two reasons. First of all, I have another project to spend time on, not related to any software at all. Not that it is very successful so far, but I enjoy the process and the results... I was also a bit disappointed by the lack of reactions to some previous posts I considered really useful like this one on BCC tools or the other one on tracing the mutex locks.

Anyway, I write posts here mostly for myself to be used as references later, as it was proved by the sad experience over last 30 years or so that I can forget the solutions of both minor and serious problems I once successfully resolved... So I am going to document one of tests of this week when I had to downgrade MariaDB to some previous version on Debian 10 due to some regression bug. Yes, shit happens and there are regression bugs reported once in a while for MariaDB, all kindly marked with the "regression" label after checks.

I like to use Docker for such tests, so this time I used debian:buster from the official images, pulled and started bash there. I tried to follow this fine MariaDB KB article that is mostly correct, but miss some small details. So, this is what I did:

1. Starting fresh and executing the repository configuration script

openxs@ao756:~$ sudo docker run -it debian:buster bash
root@8af6489c0df8:/# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
bash: curl: command not found

I had to know better, We need to update and install curl package first:

root@8af6489c0df8:/# apt-get update
...
root@8af6489c0df8:/# apt-get install curl
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  ca-certificates krb5-locales libcurl4 libgssapi-krb5-2 libk5crypto3
  libkeyutils1 libkrb5-3 libkrb5support0 libldap-2.4-2 libldap-common
  libnghttp2-14 libpsl5 librtmp1 libsasl2-2 libsasl2-modules
  libsasl2-modules-db libssh2-1 libssl1.1 openssl publicsuffix
Suggested packages:
  krb5-doc krb5-user libsasl2-modules-gssapi-mit
  | libsasl2-modules-gssapi-heimdal libsasl2-modules-ldap libsasl2-modules-otp
  libsasl2-modules-sql
The following NEW packages will be installed:
  ca-certificates curl krb5-locales libcurl4 libgssapi-krb5-2 libk5crypto3
  libkeyutils1 libkrb5-3 libkrb5support0 libldap-2.4-2 libldap-common
  libnghttp2-14 libpsl5 librtmp1 libsasl2-2 libsasl2-modules
  libsasl2-modules-db libssh2-1 libssl1.1 openssl publicsuffix
0 upgraded, 21 newly installed, 0 to remove and 0 not upgraded.
Need to get 5010 kB of archives.
After this operation, 11.9 MB of additional disk space will be used.
Do you want to continue? [Y/n]
...
done.
root@8af6489c0df8:/# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
[error] The following package is needed by the script, but not installed:
            apt-transport-https
        Please install and rerun the script.

OK, so let's install this one too:

root@8af6489c0df8:/# apt-get install apt-transport-https 
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
  apt-transport-https
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 149 kB of archives.
After this operation, 156 kB of additional disk space will be used.
Get:1 http://deb.debian.org/debian buster/main amd64 apt-transport-https all 1.8.2.1 [149 kB]
Fetched 149 kB in 0s (848 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package apt-transport-https.
(Reading database ... 7160 files and directories currently installed.)
Preparing to unpack .../apt-transport-https_1.8.2.1_all.deb ...
Unpacking apt-transport-https (1.8.2.1) ...
Setting up apt-transport-https (1.8.2.1) ...

root@8af6489c0df8:/# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
[info] Running apt-get update...
[info] Done adding trusted package signing keys

Now we are ready to continue with adding the repository for MariaDB 10.5.

2. Adding the repository

KB says we should start with adding software-properties-common package:

root@8af6489c0df8:/# apt-get install software-properties-common
Reading package lists... Done
...
0 upgraded, 71 newly installed, 0 to remove and 0 not upgraded.
Need to get 31.6 MB of archives.
After this operation, 139 MB of additional disk space will be used.
Do you want to continue? [Y/n]
...
Setting up software-properties-common (0.96.20.2-2) ...
Processing triggers for systemd (241-7~deb10u4) ...
Processing triggers for libc-bin (2.28-10) ...
Processing triggers for dbus (1.12.20-0+deb10u1) ...
root@8af6489c0df8:/#

Now we can add the repository:

root@8af6489c0df8:/# add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster main'

root@8af6489c0df8:/# apt-get update
Hit:1 http://security.debian.org/debian-security buster/updates InRelease
Hit:2 http://deb.debian.org/debian buster InRelease
Hit:3 http://deb.debian.org/debian buster-updates InRelease
Get:5 http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster InRelease [4635 B]
Hit:4 https://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/debian buster InRelease
Hit:6 https://downloads.mariadb.com/Tools/debian buster InRelease
Get:7 https://dlm.mariadb.com/repo/maxscale/latest/debian buster InRelease [3515 B]
Get:8 http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster/main amd64 Packages [29.1 kB]
Get:9 http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster/main ppc64el Packages [19.7 kB]
Get:10 http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster/main arm64 Packages [19.8 kB]
Fetched 76.7 kB in 2s (50.7 kB/s)
Reading package lists... Done

Note that I asked for 10.4 and got 10.5 in the outputs and on one of the next steps. One day I'll figure out why it was so...

3. Importing the MariaDB GPG public key

KB articles says I have to install dirmngr first starting from Debian 9, so I did it:

root@8af6489c0df8:/# apt-get install dirmngr
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  gnupg gnupg-l10n gnupg-utils gpg gpg-agent gpg-wks-client gpg-wks-server
  gpgconf gpgsm libassuan0 libksba8 libnpth0 pinentry-curses
Suggested packages:
  dbus-user-session pinentry-gnome3 tor parcimonie xloadimage scdaemon
  pinentry-doc
The following NEW packages will be installed:
  dirmngr gnupg gnupg-l10n gnupg-utils gpg gpg-agent gpg-wks-client
  gpg-wks-server gpgconf gpgsm libassuan0 libksba8 libnpth0 pinentry-curses
0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded.
Need to get 7089 kB of archives.
After this operation, 14.9 MB of additional disk space will be used.
Do you want to continue? [Y/n]
...
Setting up gnupg (2.2.12-1+deb10u1) ...
Processing triggers for libc-bin (2.28-10) ...
root@8af6489c0df8:/#

Then I called apt-key as suggested by the KB article, with the key fingerprint listed there:

root@8af6489c0df8:/# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
Executing: /tmp/apt-key-gpghome.h3pkEOHaEm/gpg.1.sh --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
...

4. Installing MariaDB packages with apt-get

Finally I can install what I need:

root@8af6489c0df8:/# apt-get install mariadb-server galera-4 mariadb-client libmariadb3 mariadb-backup mariadb-common
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  gawk libaio1 libcgi-fast-perl libcgi-pm-perl libdbd-mariadb-perl libdbi-perl
  libencode-locale-perl libfcgi-perl libgdbm-compat4 libgdbm6 libgpm2
  libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl
  libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libmpfr6 libncurses6 libpcre2-8-0 libperl5.28
  libpopt0 libprocps7 libreadline5 libsigsegv2 libterm-readkey-perl
  libtimedate-perl liburi-perl libwrap0 lsof mariadb-client-10.5
  mariadb-client-core-10.5 mariadb-server-10.5 mariadb-server-core-10.5
  mysql-common netbase perl perl-modules-5.28 procps psmisc rsync socat
Suggested packages:
  gawk-doc libclone-perl libmldbm-perl libnet-daemon-perl
  libsql-statement-perl gdbm-l10n gpm libdata-dump-perl
  libipc-sharedcache-perl libwww-perl mailx mariadb-test netcat-openbsd
  perl-doc libterm-readline-gnu-perl | libterm-readline-perl-perl make
  libb-debug-perl liblocale-codes-perl openssh-client openssh-server
The following NEW packages will be installed:
  galera-4 gawk libaio1 libcgi-fast-perl libcgi-pm-perl libdbd-mariadb-perl
  libdbi-perl libencode-locale-perl libfcgi-perl libgdbm-compat4 libgdbm6
  libgpm2 libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl
  libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libmariadb3 libmpfr6 libncurses6 libpcre2-8-0
  libperl5.28 libpopt0 libprocps7 libreadline5 libsigsegv2
  libterm-readkey-perl libtimedate-perl liburi-perl libwrap0 lsof
  mariadb-backup mariadb-client mariadb-client-10.5 mariadb-client-core-10.5
  mariadb-common mariadb-server mariadb-server-10.5 mariadb-server-core-10.5
  mysql-common netbase perl perl-modules-5.28 procps psmisc rsync socat
0 upgraded, 49 newly installed, 0 to remove and 0 not upgraded.
Need to get 44.7 MB of archives.
After this operation, 302 MB of additional disk space will be used.
Do you want to continue? [Y/n]
...

and start the service (called mariadb, as 10.5 is installed):

root@8af6489c0df8:/# service mariadb start
[ ok ] Starting MariaDB database server: mariadbd.
root@8af6489c0df8:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.5.8-MariaDB-1:10.5.8+maria~buster mariadb.org binary distribution

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)]>

So, this is how installation from the repository is done. Now what if we need to downgrade to some older minor version, let's say, to 10.5.6?

5. Downgrading to specific minor release

MariaDB KB article explains the process. For this you can create a repository with the URL hard-coded to that specific minor release. You can get these URLs from the MariaDB Foundation's archives. I tried:

root@8af6489c0df8:/# add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://archive.mariadb.org/mariadb-10.5.6/repo/debian/ buster main'

root@8af6489c0df8:/# apt-get update
Hit:1 http://deb.debian.org/debian buster InRelease
Hit:2 http://security.debian.org/debian-security buster/updates InRelease
Hit:3 http://deb.debian.org/debian buster-updates InRelease
Hit:6 http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster InRelease
Get:4 https://archive.mariadb.org/mariadb-10.5.6/repo/debian buster InRelease [3154 B]
Hit:5 https://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/debian buster InRelease
Hit:7 https://downloads.mariadb.com/Tools/debian buster InRelease
Get:8 https://dlm.mariadb.com/repo/maxscale/latest/debian buster InRelease [3515 B]
Get:9 https://archive.mariadb.org/mariadb-10.5.6/repo/debian buster/main amd64 Packages [36.0 kB]
Fetched 42.7 kB in 2s (23.6 kB/s)
Reading package lists... Done
N: Skipping acquire of configured file 'main/binary-ppc64el/Packages' as repository 'http://archive.mariadb.org/mariadb-10.5.6/repo/debian buster InRelease' doesn't support architecture 'ppc64el'
N: Skipping acquire of configured file 'main/binary-arm64/Packages' as repository 'http://archive.mariadb.org/mariadb-10.5.6/repo/debian buster InRelease' doesn't support architecture 'arm64'

and from the messages above it seems the repository is taken into account during update. The repository is added to the list:

root@8af6489c0df8:/# cat /etc/apt/sources.list
# deb http://snapshot.debian.org/archive/debian/20201117T000000Z buster main
deb http://deb.debian.org/debian buster main
# deb http://snapshot.debian.org/archive/debian-security/20201117T000000Z buster/updates main
deb http://security.debian.org/debian-security buster/updates main
# deb http://snapshot.debian.org/archive/debian/20201117T000000Z buster-updates main
deb http://deb.debian.org/debian buster-updates main
deb [arch=ppc64el,arm64,amd64] http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster main
# deb-src [arch=ppc64el,arm64,amd64] http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.4/debian buster main
deb [arch=amd64,ppc64el,arm64] http://archive.mariadb.org/mariadb-10.5.6/repo/debian/ buster main
# deb-src [arch=amd64,ppc64el,arm64] http://archive.mariadb.org/mariadb-10.5.6/repo/debian/ buster main
root@8af6489c0df8:/#

but upgrade suggested nothing to do:

root@8af6489c0df8:/# apt-get upgrade
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

The problem is that this added repository has no priority over existing one. You can find some related details on how to fix that in Debian Wiki, while I've got a hint from smart customer actually. I had to create a file in /etc/apt/preferences.d/ for that, to pin thew repository on top for all packages it provides:

root@8af6489c0df8:/# cat /etc/apt/preferences.d/mariadb.pref
Package: *
Pin: origin archive.mariadb.org
Pin-Priority: 1001
root@8af6489c0df8:/#

Now we can downgrade:

root@8af6489c0df8:/# apt-get upgrade
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
The following packages will be DOWNGRADED:
  galera-4 libmariadb3 mariadb-backup mariadb-client mariadb-client-10.5
  mariadb-client-core-10.5 mariadb-common mariadb-server mariadb-server-10.5
  mariadb-server-core-10.5 mysql-common
0 upgraded, 0 newly installed, 11 downgraded, 0 to remove and 0 not upgraded.
Need to get 32.4 MB of archives.
After this operation, 158 kB of additional disk space will be used.
Do you want to continue? [Y/n]
 ...
Setting up galera-4 (26.4.5-buster) ...
Setting up mysql-common (1:10.5.6+maria~buster) ...
Setting up mariadb-common (1:10.5.6+maria~buster) ...
Setting up libmariadb3:amd64 (1:10.5.6+maria~buster) ...
Setting up mariadb-server-core-10.5 (1:10.5.6+maria~buster) ...
Setting up mariadb-client-core-10.5 (1:10.5.6+maria~buster) ...
Setting up mariadb-backup (1:10.5.6+maria~buster) ...
Setting up mariadb-client-10.5 (1:10.5.6+maria~buster) ...
Setting up mariadb-client (1:10.5.6+maria~buster) ...
Setting up mariadb-server-10.5 (1:10.5.6+maria~buster) ...
Installing new version of config file /etc/logrotate.d/mysql-server ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of stop.
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.
Setting up mariadb-server (1:10.5.6+maria~buster) ...
Processing triggers for systemd (241-7~deb10u4) ...
Processing triggers for libc-bin (2.28-10) ...

Note that Galera library is also downgraded. You may want to avoid that and list packages with priority in a less generic way. 

Now we can restart the service and check that downgrade really happened as expected:

root@8af6489c0df8:/# service mariadb restart
[ ok ] Stopping MariaDB database server: mariadbd.
[ ok ] Starting MariaDB database server: mariadbd.
root@8af6489c0df8:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.5.6-MariaDB-1:10.5.6+maria~buster mariadb.org binary distribution

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)]> show variables like '%version%';
+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.5.6                                   |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| tls_version                       | TLSv1.1,TLSv1.2,TLSv1.3                  |
| version                           | 10.5.6-MariaDB-1:10.5.6+maria~buster     |
| version_comment                   | mariadb.org binary distribution          |
| version_compile_machine           | x86_64                                   |
| version_compile_os                | debian-linux-gnu                         |
| version_malloc_library            | system                                   |
| version_source_revision           | 5b8ab1934a10966336e66751bc13fc66923b02f6 |
| version_ssl_library               | OpenSSL 1.1.1d  10 Sep 2019              |
| wsrep_patch_version               | wsrep_26.22                              |
+-----------------------------------+------------------------------------------+
15 rows in set (0.002 sec)

So, the roblem is resolved, and all steps are documented for me to find them later online easily. I rarely use packages, as I prefer to build myself from GitHub sources or at least rely on .tar.gz binaries and tools like MySQL Sandbox for my tests, so this excercise was really needed.

Now you know what I had to work on this week. In the video above you can check what I had for breakfast, if you are interested :)

To summarize:

1. MariaDB KB has a lot of details on installation and downgrade, but some of them are still missing.
2. In case of .deb pakages installed from the MariaDB repositories one has to pin specific packages to the repositories providing older versions (like those from http://archive.mariadb.org/) and to set higher priority for this repository in some .pref file in the /etc/apt/preferences.d/ directory.
3. Docker is useful for testing installation steps and anything in a clean environment. It's easy to miss some step otherwise.
4. Those who are interested in what exact regression bug forced me to consider and document downgrade procesude can just ask in comments :)
   

Fun with Bugs #79 - On MySQL Bug Reports I am Subscribed to, Part XV

More than 3 weeks passed since my previous review of public MySQL bug reports I am subscribed to, so it's time to present some of the bugs I've considered interesting in January, 2019.

As usual, I'll review them starting from the oldest and try to summarize my feelings about these bugs at the end of this post. Here they are:

• Bug #93806 - "Document error about ON DUPLICATE KEY UPDATE". Years pass, but fine MySQL manual still does not explain some cases of InnoDB locking properly. Xiaobin Lin found yet another case that it does not explain properly. Or, maybe, the manual is correct and the problem in the implementation? MariaDB 10.3.7 shows the same behavior.
• Bug #93827 - "dict_index_has_desc() is not efficient". Yet another bug report from Zhai Weixiang. I see 50 still active bug reports from him! Maybe Oracle should send some nice T-shirts to top N most productive bug reporters?
• Bug #93845 - "Optimizer choose wrong index, sorting index instead of filtering index". yet another bug report of a known class, this time from Daniele Renda. It's good example of optimizer trace usage to make a point. Note also that using ANALYZE ... UPDATE HISTOGRAMS does not help. As a side note, implementation of optimizer trace for MariaDB is finally in progress and should be done for upcoming 10.4. See MDEV-6111 for the details if you care.
• Bug #93875 - "mysqldump per-table dump is slow since 5.7 on instances with many tables". This performance regression bug (that was "verified" without adding the regression tag) was reported by Nikolai Ikhalainen from Percona. This bug report is a nice example of using Docker to create easily repeatable test cases for bug reports.
• Bug #93878 - "innodb_status_output fails to restore to old value". This great bug report from Yuhui Wang  not only describes 3 cases when InnoDB status is printed to the error log automatically, but also shows that in one of these cases, when we can not found free block in the buffer pool in 20 loops, this printing is not stopped after the problem is resolved, and provides a patch that resolves the problem. See also his nice Bug #94065 - "MySQL fails to startup when setting persist variable" with detailed analysis of the problem.
• Bug #93917 - "Wrong binlog entry for BLOB on a blackhole intermediary master". Nice corner case was found by Sveta Smirnova from Percona. With her 52 "Verified" bug reports at the moment she also deserves a T-shirt from Oracle as one of top bug reporters!
• Bug #93922 - "UNION ALL very slow with SUM(0)". This weird bug was found and reported by Sergio Paternoster. He had to spend notable efforts to see this bug "Verified"...
• Bug #93948 - "XID inconsistency on master-slave with CTAS". Krunal Bauskar from Percona noted this inconsistency in XID generation on slave vs master. Let's wait and check if it ends up as "Not a bug".
• Bug #93957 - "slave_compressed_protocol doesn't work with semi-sync replication in MySQL-5.7". This bug report from Pavel Katiushyn also looks like a regression, as similar bug was fixed in older 5.7.x release. But I do not see any public comment with verification attempt neither in recent 5.7, nor in recent 8.0 (where older bug also had to be fixed). So, the bug is "verified", but the real impact and versions affected are not clear.
• Bug #93963 - "Slow query log doesn't log a slow CREATE INDEX with admin statements enabled". This clear and properly tagged regression vs MySQL 5.7 was reported by Jeremy Smyth.
• Bug #93986 - "Transactions in serializable mode are not actually serializable". I've subscribed to this bug report mostly for (expected) fun of reading further comments. It's still "Need feedback", but single comment so far is worth reading.
• Bug #94121 - "Enable hardware CRC32 under Valgrind". Laurynas Biveinis from Percona also provided a patch for this 8 years old problem.
• Bug #94130 - "XA COMMIT may lead replication broken". Yet another proof that XA transactions implementation is broken in MySQL. This time from Phoenix Zhang and in semi-sync replication case.

This photo reminds me current state of MySQL bugs processing in Oracle - it seems there is no clear and straightforward way to follow. Everything is fuzzy these days...

There are few more bugs reported in January, 2019 that I am watching, but their status is not yet clearly defined, so I decided to skip them in this review.

To summarize:

1.  Oracle engineers who process bugs still do not add regression tag to many regression bugs. This is a shame, really. If I were their boss I'd make this a policy and one of important KPI values to monitor.
2. In some cases bugs get verified immediately without any demonstrated attempt to show how the check was performed, while in other cases poor bug reporters have to fight hard to re-make their point and get a real check done. It seems these days good old approaches to bugs verification are not followed strictly by some Oracle engineers.

Accessing Oracle tables via MariaDB CONNECT engine and ODBC

In my previous working environment it was typical to consider MariaDB Server as just a set of "random" storage engines mixed together, sometimes for unclear reasons and with questionable extra value. But even at that times I was already impressed by some features of one of the engines supported only by MariaDB, CONNECT. PIVOT table type specifically was of interest in a couple of customer cases I had to work on, but I quickly found out that the engine does not work with Oracle's MySQL or Percona Server, and thus had to forget about it for some time.

Recently while working in MariaDB I've got several more chances to deal with some new CONNECT use cases (and some related problems, that ended up mostly minor or caused by ignorance). This engine can be (and is, widely) used to access tables from virtually any other database management system or data source, as long as there is an ODBC or JDBC driver for it. I had to set up a testing environment to reproduce some of the problems noted while accessing remote tables in Oracle RDBMS, and eventually decided to document setup steps, mistakes and findings for myself and my readers.

I think the topic may require a series of posts, and in this first one I plan to concentrate on creating a basic testing environment to access Oracle RDBMS via ODBC driver and CONNECT engine on my main testing box running Fedora 25, and resolving simplest access issues, like those I described in MDEV-12355 (ended as not a bug thanks to quick help and clarifications from engine creator, Olivier Bertrand).

For pure testing purposes I had not planned to spend time installing and setting up Oracle RDBMS on that Fedora 25 box directly (later I tried and failed), but decided to rely on some existing Docker image. So, I started with installing Docker and dependencies (see this article for some hints, if needed):

[openxs@fc23 ~]$ sudo dnf install docker
...
Installed:
  container-selinux.noarch 2:2.10-1.fc25
  docker.x86_64 2:1.12.6-6.gitae7d637.fc25
  docker-common.x86_64 2:1.12.6-6.gitae7d637.fc25
  oci-register-machine.x86_64 0-2.7.gitbb20b00.fc25
  oci-systemd-hook.x86_64 1:0.1.6-1.gitfe22236.fc25
  skopeo-containers.x86_64 0.1.17-1.dev.git2b3af4a.fc25

Complete!

[openxs@fc23 ~]$ sudo systemctl start docker

I am going to skip details on images that had not worked well (not a topic for this blog). Eventually I ended up working with https://hub.docker.com/r/sath89/oracle-12c/:

[openxs@fc23 server]$ sudo docker run -d -p 8080:8080 -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true sath89/oracle-12c:latest
4515bc4a6fb4804ac8d714115de12e47a9e8f9baabb2300cde672aedb4843c2b
[openxs@fc23 server]$ sudo docker ps
CONTAINER ID        IMAGE                      COMMAND             CREATED             STATUS              PORTS                                            NAMES
4515bc4a6fb4        sath89/oracle-12c:latest   "/entrypoint.sh "   14 seconds ago      Up 8 seconds        0.0.0.0:1521->1521/tcp, 0.0.0.0:8080->8080/tcp   adoring_turing

[openxs@fc23 server]$ sudo docker logs -f 4515bc4a6fb4
ls: cannot access /u01/app/oracle/oradata: No such file or directory
Database not initialized. Initializing database.
Starting tnslsnr
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/xe/xe.log" for further details.
Configuring Apex console
Database initialized. Please visit http://#containeer:8080/em http://#containeer:8080/apex for extra configuration if needed
Starting web management console
PL/SQL procedure successfully completed.
Starting import from '/docker-entrypoint-initdb.d':
found file /docker-entrypoint-initdb.d//docker-entrypoint-initdb.d/*
[IMPORT] /entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
Import finished
Database ready to use. Enjoy! ;)
^C

This image initializes new database by default (unless you pass a volume on host, then it is initialized for the first time if the database is not there), so you have to wait (maybe for many minutes) and check the logs before trying to access the database. Note that container's port 1521 is mapped to host's port 1521 (and my Fedora 25 IP address, 192.168.1.85, is then used later below to access "remote" Oracle server on this port).

So, my "Oracle server" is ready (probably). Now, my Fedora 25 host will work as a client, so I had downloaded and installed basic Oracle client software RPMs (including ODBC driver and SQL*Plus) from OTN:

[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

I also installed unixODBC (including -devel, as I built MariaDB from source and one needs unixODBC-devel to have CONNECT engine build properly) as fine manual stated at the beginning:

[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

One has to set up tnsnames.ora for Oracle client to work with (remote) instance, and /etc/odbcinst.ini + /etc/odbc.ini to inform unixODBC about the known drivers and system data sources (this discussion had some useful hints):

[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 = OracleODBCDSN = OracleODBC
ServerName = XE
UserID = system
Password = oracle

 Surely, I tried to access my Oracle server in Docker container via sqlplus command first:

[openxs@fc23 server]$ /usr/lib/oracle/11.2/client64/bin/sqlplus system/oracle@localhost:1521/xe.oracle.docker/usr/lib/oracle/11.2/client64/bin/sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
[openxs@fc23 server]$ ldd /usr/lib/oracle/11.2/client64/bin/sqlplus
        linux-vdso.so.1 (0x00007ffe8813b000)
        libsqlplus.so => not found
        libclntsh.so.11.1 => not found
        libnnz11.so => not found
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f0aadc93000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f0aad98a000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0aad76a000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f0aad551000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f0aad18b000)
        /lib64/ld-linux-x86-64.so.2 (0x0000563b14bfb000)

This is what happens when one does not care to read all the details and forgets to set some environment variables and inform dynamic loader about libraries location. So, I did the following to resolve the problem:

[openxs@fc23 server]$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64
[openxs@fc23 server]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
[openxs@fc23 server]$ export PATH=$PATH:$ORACLE_HOME/bin
[openxs@fc23 server]$ sudo ldconfig
[openxs@fc23 server]$ ldd /usr/lib/oracle/11.2/client64/bin/sqlplus
        linux-vdso.so.1 (0x00007ffd31b50000)
        libsqlplus.so => /usr/lib/oracle/11.2/client64/lib/libsqlplus.so (0x00007f88f680f000)
        libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f88f3ea0000)
        libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f88f3ad3000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f88f38b2000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f88f35a9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f88f3389000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f88f3170000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f88f2daa000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f88f2ba8000)
        /lib64/ld-linux-x86-64.so.2 (0x000056123639a000)

[openxs@fc23 server]$ sqlplus system/oracle@localhost:1521/xe.oracle.docker    
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 24 11:00:21 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> select count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL> create table scott.t1(c1 number(30));

Table created.

SQL> insert into scott.t1 values (12345);

1 row created.

SQL> desc scott.t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER(30)

SQL>

SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

So, SQL*Plus (that I still miss sometimes, after using it maybe only 10 times over last 12 years) client works. Let's try to access Oracle via ODBC and isql now:

[openxs@fc23 server]$ isql -v oracle
[08004][unixODBC][Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified

[ISQL]ERROR: Could not SQLConnect

Surely, one more environment variable is not set, the one pointing out the location of tnsnames.ora file:

[openxs@fc23 server]$ export TNS_ADMIN=/etc/oracle
[openxs@fc23 server]$ isql -v oracle
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from scott.t1;
+---------------------------------+
| C1                              |
+---------------------------------+
| 12345                           |
+---------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> select count(*) from scott.emp;+-----------------------------------------+
| COUNT(*)                                |
+-----------------------------------------+
| 14                                      |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
[openxs@fc23 server]$

Looks like now we should be able to use ODBC table type of CONNECT storage engine of MariaDB (that was already loaded with INSTALL SONAME 'ha_connect.so'; check the details here) to access remote Oracle table:

MariaDB [test]> create table oracle_t1 engine=connect table_type=ODBC tabname='t1' dbschema='scott' 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)

Do not be like me and do not report bugs when you see these messages! Go read the fine manual that explicitly mentions Oracle as being case sensitive and converting identifiers to upper case unless they are quoted.

As soon as you use proper case in tabname (and dbname) settings, everything works as expected, at least for my primitive use case:

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)

To summarize, with some initial setup efforts (that should include reading the official CONNECT manual) it is easy to access Oracle tables from MariaDB server via ODBC driver. It may be very useful if you plan to migrate or use some data from Oracle RDBMS.

As a side note, Docker really rocks sometimes. I spent maybe 15+ years installing Oracle RDBMS (starting from version 7.0.16 or so) on all kinds of OSes dozens of times, and I do not miss this experience and do not mind to have it up and running with one simple command any time (rarely) I need it.