Xenial Ansible

I started building out some Ubuntu 16.04 (Xenial Xerus) servers this weekend with Ansible, and was impressed by how smoothly it went. The only major issue I encountered was that Ansible requires Python 2.x, whereas Ubuntu 16.04 ships Python 3.5 by default. Fortunately, it's not too hard to work around; here's how I fixed that — and a couple of other issues specific to the servers I was building out:

Python 2

Since Ansible doesn't work with Python 3, and that's what Ubuntu 16.04 provides by default, this is the error I got when I tried running Ansible against a newly-booted server:

/usr/bin/python: not found

So I had to make this the very first Ansible play (bootstrapping the ability of Ansible to use Python 2 for the rest of its tasks, as well as for its other record keeping — like gathering facts about the server):

- name: bootstrap python 2
  gather_facts: no
  tasks:
  - raw: sudo apt-get update -qq && sudo apt-get install -qq python2.7

And in the inventory variables (or group variables) for the server, I had to add this line (directing it to use Python 2 instead of the server's default Python):

ansible_python_interpreter: /usr/bin/python2.7

Aptitude

The next hiccup I ran into was using the Ansible apt module with the upgrade=full option. This option is implemented by using the aptitude program — which Ubuntu no longer installs by default. I was getting this error trying to use that option:

Could not find aptitude. Please ensure it is installed.

So I just tweaked my playbook to install the aptitude package first before running apt: upgrade=full:

- name: install apt requirements
  become: yes
  apt: pkg=aptitude

- name: update pre-installed packages
  become: yes
  apt: upgrade=full update_cache=yes

Mount with nobootwait

Then I started running into some minor issues that were completely unrelated to Ansible — simply changes Ubuntu had picked up between 15.10 and 16.04. The first of these was the nobootwait option for mountall (eg in /etc/fstab mountpoints). This option seems to be no longer supported — the server hung after rebooting, with this message in the syslog:

Unrecognized mount option "nobootwait" or missing value

Maybe this is just an issue with AWS EC2 instance-store volumes, but I had to change the /etc/fstab definition for the server's instance-store volume from this:

/dev/xvdb /mnt auto defaults,noatime,data=writeback,nobootwait,comment=cloudconfig 0 2

To this:

/dev/xvdb /mnt auto defaults,noatime,data=writeback,comment=cloudconfig 0 2

Java 7

The Ubuntu 16.04 repo no longer includes Java 6 or 7 — only Java 8 and 9. I got this error message trying to install Java 7:

No package matching 'openjdk-7-jdk' is available

So I first had to add a PPA for OpenJDK 7, and then could install it:

- name: register java 7 ppa
  become: yes
  apt_repository: repo=ppa:openjdk-r/ppa

- name: install java 7
  become: yes
  apt: pkg=openjdk-7-jdk

But the PPA doesn't include the timezone database, so the time formatting in our app was restricted to GMT. So I had to "borrow" the timezone data from Ubuntu 14.04:

- name: download tzdata-java
  get_url:
    url: http://mirrors.kernel.org/ubuntu/pool/main/t/tzdata/tzdata-java_2016d-0ubuntu0.14.04_all.deb
    dest: ~/tzdata-java.deb
    checksum: sha256:5131aa5219739ac58c00e18e8c9d8c5d6c63fc87236c9b5f314f7d06b46b79fb

- name: install tzdata-java
  become: yes
  command: dpkg --ignore-depends=tzdata -i tzdata-java.deb

That's probably going to be broken in a month or two after the next tzdata update, but it's good enough for now. Later I'll put together some Ansible tasks to build the data from source (although obviously the long-term solution is to upgrade to java 8).

Update 5/14/2016: I created a Xenial tzdata-java package to simplify the installation of Java 7 timezone data.

MySQL root

The final issue I hit was with the MySQL root user. Unlike previous versions of Ubuntu, which by default would come with 3 or 4 MySQL root users (covering all the different variations for naming your local host), and all with empty passwords, Ubuntu 16.04 comes with just a single root user — with which you can login only via MySQL's auth_socket plugin. So I was getting this error trying to login to MySQL as root the old way:

unable to connect to database, check login_user and login_password are correct or /home/me/.my.cnf has the credentials

The new way is simply to login to MySQL using the system's root user (ie sudo mysql). In Ansible tasks (such as those using the mysql_db module), this just means using the become (aka sudo) directive, and omitting the login_user and login_password options:

- name: create new database
  become: yes
  mysql_db: name=thedata

Importing the New RDS CA Certificate Into the Java Keytool

A few days ago (Feb 2015), Amazon released a new CA certificate bundle (rds-combined-ca-bundle.pem) for use with AWS RDS databases. In order to connect with a MySQL, PostgreSQL, or SQL Server DB over SSL, your client app has to trust the certificates in this bundle. For java apps (using JDBC) this generally requires importing the certs into your java keystore via the java keytool utility.

The first RDS cert (from 2010) was released as a single certificate in a single file; but this new release is actually 10 separate new certificates (plus the old certificate) jammed into the same file. The java keytool, however, supports importing only one certificate at a time — so you have to first split up the bundle into individual certificates, and then import the certs one by one. If you try to import the whole bundle as a single file, you'll end up trusting only the first cert listed in that file (which happens, in this case, to be the old cert).

I wrote up a quick shell script that 1) downloads the new CA bundle, 2) splits up the bundle into individial certificate files, and 3) imports each file separately into the java keytool. It assumes that you already have the keytool installed and on your path; that the keystore location is /etc/ssl/certs/java/cacerts and the password is changeit (the defaults for the OpenJDK on Ubuntu); that you're running the script as a sudoer; and that you have OpenSSL installed. You'll have to tweak those things if that's not the case:

#!/bin/sh -e

# create a temp dir in which to work
OLDDIR="$PWD"
mkdir /tmp/rds-ca && cd /tmp/rds-ca

# download the bundle
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
# split the bundle into individual certs (prefixed with xx)
csplit -sz rds-combined-ca-bundle.pem '/-BEGIN CERTIFICATE-/' '{*}'

# import each cert individually
for CERT in xx*; do
    # extract a human-readable alias from the cert
    ALIAS=$(openssl x509 -noout -text -in $CERT |
        perl -ne 'next unless /Subject:/; s/.*CN=//; print')
    echo "importing $ALIAS"
    # import the cert into the default java keystore
    sudo keytool -import \
        -keystore /etc/ssl/certs/java/cacerts \
        -storepass changeit -noprompt \
        -alias "$ALIAS" -file $CERT
done

# back out of the temp dir and delete it
cd "$OLDDIR"
rm -r /tmp/rds-ca

# list the imported rds certs as a sanity check
keytool -list \
    -keystore /etc/ssl/certs/java/cacerts \
    -storepass changeit -noprompt |
    grep -i rds

OpenSSL, in particular, is used by the script only to extract a convenient, unique alias for each certificate; so if you don't have OpenSSL installed, you could replace the alias-creating line with something like this (which will create aliases in the form of rdsxx00, rdsxx01, etc):

    ALIAS=rds$CERT

Moving the MySQL Tmpdir on Ubuntu

One thing I frequently forget when changing the default directories for various services in Ubuntu is that the AppArmor config for those services also needs to be updated. Case in point, the other day I needed to change MySQL's tmpdir to a different disk, and struggled for a while with "Can't create/write to file 'xyz' (Errcode: 13)" errors until I remembered AppArmor. These were the steps I ended up taking:

Create the new tmpdir

mkdir /mnt/foo/tmp && sudo chown mysql:mysql /mnt/foo/tmp

Change /etc/mysql/my.cnf to use the new tmpdir

tmpdir = /mnt/foo/tmp

Add new tmpdir entries to /etc/apparmor.d/local/usr.sbin.mysqld

/mnt/foo/tmp/ r,
/mnt/foo/tmp/** rw,

Reload AppArmor

sudo service apparmor reload

Restart MySQL

sudo service mysql restart

While I was troubleshooting, I found a nice, in-depth blog entry by Jeremy Smyth explaining how to debug issues with AppArmor and MySQL.

MySQL SSL Implementation Incompatibilities

So I just spent half a day trying to figure out why connecting over SSL to a redhat mysql server from a redhat mysql client works, but not from an ubuntu mysql client. Apparently, redhat is configured to use the OpenSSL implementation by default, whereas most other distributions (and Windows) use yaSSL by default — and OpenSSL and yaSSL aren't completely interoperable.

I initially had tried connecting just by specifying the CA certificate:

mysql --ssl-ca=my-ca-cert.pem -h myhost -u myuser -p

This is the minimum you (should) need to connect when you grant permissions for a mysql user with REQUIRE SSL (and the minimum needed for a worthwhile SSL connection — the CA cert allows the client to verify that you're connecting to the authentic mysql server). This worked fine connecting to a redhat box from a redhat box, but failed with the following inscrutable error-message when connecting to a redhat box from an ubuntu box:

ERROR 2026 (HY000): SSL connection error

This error can mean a whole lot of different things, but none of the common problems (like specifying the wrong certificate or using the same CN for both the CA cert and the client/server certs) turned out to be mine. After digging around a bunch, I found mysql bug 40141, where someone else had discovered connecting over SSL from a non-redhat box to a redhat box wasn't working, and had isolated it to a yaSSL-to-OpenSSL incompatibility. Following that trail, I came across mysql bug 29841, which documents the yaSSL-to-OpenSSL issue pretty clearly, as well as a message on the openssl-users mailing list confirming some of the low-level details:

Apparently, when initiating the SSL connection, the OpenSSL-server implementation expects the client always to send a client certificate; if there's no client certificate to send, it expects a blank cert. The yaSSL client, however, doesn't send any client certificate (even a blank one) when there's none to send.

So I ended up working around the problem by creating a client certificate (which ordinarily you'd use as an alternative to password authentication), and specifying it when connecting:

mysql --ssl-ca=my-ca-cert.pem --ssl-cert=my-client-cert.pem --ssl-key=my-client-key.pem -h myhost -u myuser -p

I didn't change the permissions on the mysql user to REQUIRE X509 (so from OpenSSL clients I can still connect without the client cert) — but specifying the cert does allow the connection to be made, and I haven't noticed any other interoperability issues between OpenSSL and yaSSL (so far).

MySQL to Groovy Gotchas

I've been running some raw sql in groovy recently (against a mysql db), and while most of the time the marshalling from sql result-sets to groovy objects is super convenient, there have been a couple things that caught me by surprise.

TINYINT Display Width

I had originally assumed that the "display widths" for integer types (like the (4) in INT(4)) were merely ornamental. But it turns out that, at least for TINYINTs, somewhere along the sql-to-groovy marshalling chain the display width is used to determine whether a TINYINT value should be marshalled as a boolean (for TINYINT(1)) or as a byte (for TINYINT(2)).

That's actually pretty clever, but not what I expected — maybe for BIT(1), where the values could only be 0 or 1— but not for TINYINT(1), where you'd expect the values might at least range from -9 to 9 (and still can actually be -128 to 127).

GROUP_CONCAT

I would have expected the result of this to be marshaled as a String (at least for TEXT fields) — but this seems to be instead marshalled as some sort of primitive array. When I dumped out its class name, it was [B. I think that might be like a primitive byte array; however, using groovy's join() method didn't work on it, so there must be something a little more complicated going on there. I didn't bother to check it out further — I just used the following to convert it to a string:

def sql = new Sql(dataSource) sql.eachRow('SELECT GROUP_CONCAT(my_field) AS concat FROM my_table GROUP BY other_field') { row -> println (row.concat as Character[]).join('') }