Sunday, November 29, 2020

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