Zombies of the Fourth Circle

It’s a little-known fact that those trapped in the Sixth Circle of Hell — heretics like Epicurus and me, who assert that the soul dies with the body— can take short holidays to the Fourth Circle, to make our subsequent confinement on Level 6 even more terrible, and allow the demons who tend our flaming tombs to take a little break and recharge.

I tried this Level 4 pilgrimage recently, in December 2020. Isaac Newton’s birthday, it seems, is on 25 December, so call it “Xmas hols for demons”. Newton was quite nasty enough to make it to hell, and surely even demons have to celebrate something. My demons took some time off.

The task was simple: abandon Windows for a bit (Memories of DLL hell must surely reside at Level 6, at the very least) and simply upgrade my old and under-used version of Ubuntu Linux 18.04 (long-term support) to 20.04. I long ago abandoned my guilty pleasure of an even earlier version of Ubuntu, and have largely succumbed to the Faustian blandishments of an institution that has sold its soul to Windows. But I still cherish those memories, flawed and faded though they may be.

There was something else — now what was it? Oh yes! For complex reasons that don’t deserve mention here, under Linux I wanted to test a Perl program that connects to a database or two, so it was reasonable to upgrade that LAMP stack, too, and install the appropriate connection (ODBC Connector), allowing Perl to use it. Let’s check that we have all of my tasks clear:

1. Upgrade Ubuntu Linux;

2. Reinstall MySQL;

3. Install the other parts of our LAMP stack (L is for Linux, M is for MySQL), that is, Apache and PHP;

4. Install MySQL Connector, for that all-important Open DataBase Connectivity; and

5. Run my little Perl program.

Simple, right? Of course it wasn’t. If you’re really keen, you can work through the following guff. However, you’re only likely to be keen if, like Dante, you’re lost in a dark wood. Unlikely circumstance brought you here, in search of terms like “Error 1819”, “Error 1045” and “Install ODBC Ubuntu”.

Even for those who are just visiting hell, there is however a moral fable to take away. I’m no Virgil, so such readers should scroll down to the end if all they want is a lesson about lost souls, or even — to give this a modern spin — horcruxes. Everyone else: are you ready? Let’s start.

1. Upgrade Ubuntu

Easy-peasy. Google “Upgrade Ubuntu” and you’re offered the option Upgrade Ubuntu 18.04 LTS to 20.04 LTS today. As a denizen of hell, it seems smarter to use the command-line option. We have no need for angels, wizards and the like. Bloody wizards. Harry Potter, begone! From the command line, I proclaim:

sudo do-release-upgrade -d

No worries, right? Bugger! We are warned that we need to update a number of packages before we do the update. Minor worries — we follow the rules. That is, after all, what got us into Hell in the first place. Or was it those wizards? I forget. A few upgrades later, we continue…

2. Reinstall MySQL

Did I mention that one of those carping complaints about upgrades was MySQL? So before I upgraded to Ubuntu 20.04 I thought “get rid of the blighter, I’m going to re-install it anyway”. Haven’t actually used it much, if at all, in its old incarnation. So I spake thusly:

sudo apt-get remove --purge mysql*
sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql

In retrospect, this was my first mistake. But, like most sinners, I was blissfully unaware at the time. More of this, later. But let’s reinstall. It seems pretty simple:

sudo apt-get update
sudo apt-get install mysql-server

Ok? Do the Ubuntu upgrade (flawless) and on to our next task…

3a. Install Apache

This seems simple too:

sudo apt-get update
sudo apt-get install apache2
systemctl status apache2

Yep, it’s up and working. Time to check it out. If we open up say Firefox and navigate to:

127.0.0.1

… we should obtain the default Apache page. Only, we don’t. No problem. A quick syntax check:

sudo apache2ctl -t

There’s no reliable server name, so just:

sudo nano /etc/apache2/conf-available/servername.conf

Add the line:

ServerName localhost

Restart Apache, and we’ll get our home page at 127.0.0.1 :

sudo systemctl reload apache2

Yep, there it is.

3b. Install PHP

Gosh, this is easy.

sudo apt-get install php
sudo apt-get install libapache2-mod-php

Now all we have to do is set up phpMyAdmin, and we’re practically there. Soon I’ll be back in the familiar warmth of Circle 6, toasting my toes. But not quite yet.

sudo apt-get update
sudo apt-get install phpmyadmin

A demon speaks:

“## ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements”

But that’s surely not a problem. A quick google:

“MySQL — ERROR 1819 (HY000): Your password does not satisfy the current policy requirements. If you run into this error it’s a strong indicator that the Password Validation Plugin is installed. The quick and dirty way to fix this is to uninstall the plugin. You will need to be the root user in the database.”

Hah! Thank you Auntie G. A further read, and I’m told that the quick, dirty solution is to open the MySQL console and say the ritual incantation:

UNINSTALL PLUGIN validate_password;

This was, of course, my second mistake. This is the apple in the Garden of Good and Evil; my original sin is already there, however. Perhaps we might clean up:

sudo apt-get --fix-broken install

Now open up MySQL, UNINSTALL that validating plugin (without thinking too hard), install phpMyAdmin, reinstall that password validation, and we’re away. Almost. After biting the fruit of evil, things seemed to work. I take my browser to:

localhost/phpmyadmin

But now I get:

The requested URL phpmyadmin was not found on this server

An inscrutable problem, but I can create a symbolic link to phpmyadmin:

sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin
sudo systemctl reload apache2

Now I can even log in. What I however cannot do is make a user. Initially, I encountered the rather ominous message:

You do not have privileges to manipulate with the users! [sic]

I can fiddle with (molest?) config.inc.php, or do the uninstall plugin thing again, but then when I try to edit the privileges of an existing user, I encounter similar problems — there isn’t even an option to allow me to do so! Others have suffered similarly. Why don’t I just open the MySQL console and say what I surely should have said earlier on?

GRANT ALL ON *.* to 'phpmyadmin@localhost';

Well, the first reason is that MySQL doesn’t see this as valid! A bit of reading convinces me that in version 8.0, the GRANT syntax has changed to:

GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;

Aha! The only problem though is that — despite the fact that I’m logged in as root — this doesn’t bloody work. I don’t have the GRANT permissions to make the change :( Why The Face? Let’s investigate this).

USE mysql;
DESCRIBE user;
SELECT Host, User, Grant_priv FROM user WHERE User = 'root';

For some strange reason, root lacks the privileges to do, well, almost anything. We can fix this. Fortunately, we can change the privileges in the user table to ‘Y’, and we’re there. Well, almost. There is the small matter of not just ‘Y’ but “Why?”, but let’s leave it for Dante’s teaching moment, later. Gosh! Surely all I now have to do is say:

FLUSH PRIVILEGES;

… and I’m away. Right? Well, phpMyAdmin now seems to work. There is however one teensy little problem. The GRANT statements that are generated by the most recent version I’ve just installed (4.9.5deb2) issues those old-style GRANTs that don’t work under MySQL version 8.

What should I do? It seems I must install phpMyAdmin manually. So I backup phpMyAdmin, like the good little denizen I am:

sudo mv /usr/share/phpmyadmin/ /usr/share/phpmyadmin.bak

Make a convenient directory (Let’s call it ~/INSTALL), go to it, and say:

wget www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.zip
unzip phpMyAdmin-latest-all-languages.zip

Now I can fill the new directory with the right stuff:

sudo mkdir /usr/share/phpmyadmin
sudo mv phpMyAdmin-*/* /usr/share/phpmyadmin/

Not quite there, but close. All I need do now is update the configuration:

sudo nano /usr/share/phpmyadmin/libraries/vendor_config.php 

and make the following changes:

define('TEMP_DIR', '/var/lib/phpmyadmin/tmp/');
define('CONFIG_DIR', '/etc/phpmyadmin/');

Finally, phpMyAdmin works. Whew! Are we there yet?

Adam, Eve, myostatin inhibitors, and a figtree!

4. MySQL Connector

Hmm, a little problem here. If you try something like:

sudo apt-get install libmyodbc

… You’ll get a rude message:

Package libmyodbc is not available, but is referred to by another package. This may mean that the package is missing, has been obsoleted, or is only available from another source.

Thanks Ubuntu; thanks, Oracle, the package no longer exists. Not the end of the world, however. A bit more research, and here’s the answer. We must get the Debian package from Oracle (in their Deathly Hallows cloak of ‘mysql’), and install it manually, as we did above. No problem, we’re good at this now.

wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.21-1ubuntu20.04_amd64.deb

Let’s save ourselves some fuss. We’ll use dpkg to check the dependencies:

sudo dpkg -i mysql-connector-odbc_8.0.22–1ubuntu20.04_amd64.deb

Oops! That carping little demon again:

dpkg: dependency problems prevent configuration of mysql-connector-odbc:amd64: mysql-connector-odbc:amd64 depends on mysql-community-client-plugins; however: Package mysql-community-client-plugins is not installed. mysql-connector-odbc:amd64 depends on libodbc1 (>= 2.3.1); however: Package libodbc1 is not installed. mysql-connector-odbc:amd64 depends on odbcinst1debian2 (>= 2.3.1); however: Package odbcinst1debian2 is not installed.

Uhh. No worries. Surely we can install those pesky dependencies? Let’s try this:

sudo apt-get install libodbc1
sudo apt-get install odbcinst1debian2
sudo apt-get install mysql-community-client-plugins

Well, the first two work. After further googling, back to our increasingly well-populated ~/INSTALL directory, to say:

wget https://repo.mysql.com/apt/ubuntu/pool/mysql-8.0/m/mysql-community/mysql-community-client-plugins_8.0.22-1ubuntu20.04_amd64.deb
sudo apt-get autoclean
sudo dpkg -i mysql-community-client-plugins_8.0.22-1ubuntu20.04_amd64.deb

We’re becoming good at this! Now I can retry that original dpkg:

sudo dpkg -i mysql-connector-odbc_8.0.22–1ubuntu20.04_amd64.deb

Done. Check that the executables (.so in Linux) are there:

ls /usr/lib/x86_64-linux-gnu/odbc/libodbc*.so

All good. There is however one other little catch. Saying:

odbcinst -j

I can see the paths default to the system paths in /etc. Let’s make and edit copies. Say:

cp /etc/odbc.ini ~/.odbc.ini
cp /etc/odbcinst.ini ~/.odbcinst.ini

We could put them elsewhere, but this is sensible. I’ll still need to edit these files — see how both file names in the user root begin with ‘.’ which hides them. Edit next, first .odbcinst.ini, inserting the following contents:

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
UsageCount=1
[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
UsageCount=1

And then .odbc.ini, containing:

; odbc.ini configuration
[ODBC Data Sources]
fehr = MySQL ODBC 8.0 Unicode Driver
[fehr]
Description = fEHR database
Driver=MySQL ODBC 8.0 Unicode Driver
server=localhost
user=phpmyadmin
password=AdminPasswordGoesHere
port=3306
database=fehr
socket=

See how the Driver=line refers precisely to the name used in the odbcinst.ini file. This is important, as otherwise, things just won’t work. For some lines, defaults are indicated by leaving the value blank. There’s one more little thing. We need some environmental variables.

Try env | grep ODBC or look for individual variables along the lines of printenv ODBCSYSINI — if you get back the full path to home (In my case /home/jo) and similar values then you’re good. You won’t. So say:

export ODBCSYSINI='/home/jo'
export ODBCINSTINI='.odbcinst.ini'
export ODBCINI='/home/jo/.odbc.ini'

You’ll need to substitute in your home directory for mine, of course. Check things with:

odbcinst -q -d

5. Finally, some Perl

We’re almost done. Setting up a MySQL ODBC connection using DBI in Perl would seem as simple as:

sudo apt-get install libdbi-perl
sudo apt-get install libdbd-odbc-perl

Here’s a crude Perl file test.pl :

use strict;
use DBI;
my @driver_names = DBI->available_drivers;
print ("Drivers for DBI: @driver_names \n");
my @data_sources = DBI->data_sources('ODBC');
print("DBI data sources for ODBC are @data_sources \n");
my $BASEDATABASE = 'fehr';
my $fehr_odbc;
$fehr_odbc = DBI->connect("dbi:ODBC:$BASEDATABASE", '', '', {AutoCommit=>0,LongReadLen=>50000000})
or die "Failed to connect$!\n";
print " => ODBC, fehr connection worked";
exit 0;

It works, as does my slightly larger file. Whew!

Portrait of Dante (post mortem)

Lessons from Dante

What have I learnt? Two things. The first is that setting up a LAMP stack with ODBC is a little more involved than under Windows where I’d (a) Trivially install WampServer and the MySQL ODBC driver; and (b) From the Control Panel select Data Sources, say [Add], select the MySQL driver, and simply enter the fehr DSN, user name, password and port. Okay, there’s one catch — if I specify the user name with @localhost, it won’t work. But that’s it. Hell is where you look for it.

The other thing to learn is that Dante’s fourth circle of Hell is about greed — a nation of lost souls, hoarders and wasters. Certainly, there is evidence of both profligate construction of new commands and new code in Linux and its applications; there is also a tendency to hoard old stuff past its sell-by date. You find a lot of this on the Web, if you’re trying to research “How do I…?” Would that it were easily discarded! Something resembling DLL hell, actually. Something Microsoft has got better at fixing and even preventing, in recent years.

Is this bloat intrinsic to Linux? Does it extend to the very core? Here’s how the number of lines in the Linux kernel has grown:

It’s now (2020) up to 27.8 million lines of code. Perhaps Andy Tanenbaum was right after all. But we also need to know something about our own personal circle. The sixth circle concerns those who commit heresy, for example denying the afterlife. Surely, I have sinned, and my sin was a sin from the sixth circle.

Thinking that I’d “uninstalled” MySQL, I failed to remove the old SQL mysql schema. It was still squirrelled away in /var/lib/mysql. And with the update, MySQL did the sensible thing: because there were new columns to add to the old user table, it wisely removed privileges where things were unclear. And this in turn led to most of my problems.

I denied the afterlife of the code I thought I’d removed. I should also have said:

deluser --remove-home mysql
delgroup mysql
rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld
updatedb

If you’re going to kill something, kill it dead. Zombies and horcruxes have a way of rising, and wreaking their revenge.