Managing MySQL Persistence with Docker Containers

I recently started using Docker to manage deployment of some applications I maintain as part of my work. Docker makes developing on a Mac and deploying to a Linux machine much simpler, and obviates a lot of configuration (absolute paths work fine). Moreover, it makes integration tests far less painful; I have Jenkins rebuild my containers and end-to-end test them each time I push to my repositories (they’re small apps so e2e tests are still quick).

Containerising applications is nice but it does have some problems. The religion^Wconvention that all containers are supposed to be ephermal is great until you want to store anything. My applications typically consist of 2-3 containers; an application server, the database, and optionally some processing backend (abusing the database as a job queue). When it comes to testing these, my containers are actually ephermal so I can tear them down and rebuild them from scratch.

Unfortunately, I can’t tear down the deployed apps. This means a changed database schema ruins the nice workflow. This post shows how I got around that problem.

MySQL in Docker

The MySQL team has made a MySQL image available on Docker Hub. This makes it really easy to get a database running. Using docker-compose makes knitting the application to it a breeze too. The nice thing about the MySQL Docker images is that they work completely out of the box. There’s no need to layer your own stuff on top of the image. Instead, you can specify some scripts that run (once) to populate your database.

The MySQL image recommends you put the database on a volume. Doing this means when you tear down the container you can link a new one to it. Compose automatically does this for you so you can docker-compose stop database && docker-compose rm -f database at your leisure (assuming you’ve set the volume up in your compose file!).

This design does mean that your init scripts will never run more than once. This is probably a good thing – they probably all start with DROP DATABASE foo. Unfortunately it means there’s no way to make changes to your schema out of the box.

Database Migrations

The solution to the problem is fairly straight forward. Schema changes need to be played on top of the deployed version. Each time you change the schema, create an SQL script with the relevant ALTER TABLE commands. Then, when the application is restarted, run the sequence of changes against the database.

Using docker-compose this consists of defining a new container that runs the mysql image, connects to the database and runs each migration in turn. Ordering their names lexically allows you to avoid having to worry about making each migration totally bulletproof. That doesn’t mean the SQL scripts don’t have to have some defensive checks in them (especially if they transform existing data rather than just adding new, empty fields).

I’ve included an example docker-compose.yml file that shows how this works. This uses the current mysql image to run a database and uses an image that includes the script to run the migrations (derived from the original mysql init script). The Dockerfile for the image is available on GitHub and the image is available on Docker Hub.

database:
  image: mysql
  expose:
    - 3306
  volumes:
    - ./schema:/docker-entrypoint-initdb.d
    - /var/lib/mysql
  environment:
    MYSQL_DATABASE: database
    MYSQL_USER: user
    MYSQL_PASSWORD: password
    MYSQL_ROOT_PASSWORD: root_password
  
migration:
  image: mathewhall/mysql_migration
  volumes:
    - ./migrations:/docker-entrypoint-migrations.d
  links:
    - database
  environment:
    MYSQL_HOST: database
    MYSQL_DATABASE: database
    MYSQL_USER: user
    MYSQL_PASSWORD: password

When this compose file is run, the migration container will start and wait for the database to come up. Once it does, it runs the migrations in the host-linked migrations directory. The directory link ensures new changes will be picked up without having to rebuild an image or recreate the container. As long as the migration container is run (either automatically with docker-compose up or manually with docker-compose run migration) the database schema will be up to date.

Using an Efergy Current Transformer to Monitor Power Consumption

Household power consumption can be estimated non-invasively by attaching a current transformer(s) to the incoming phase(s). There are quite a few commercial kits available that do this, plus EmonTX. I wanted a solution that allowed me to log power consumption over the network, and bought an Efergy “Smart” sensor, designed for Efergy’s E2 and Elite monitors.

Unlike other current transformers, the Efergy sensor contains some more interesting components. There’s a long discussion on the OpenEnergyMonitor forums about this sensor, including internals, and response characteristics, that ultimately concludes the components should be replaced to get it to work with EmonTX’s design. I wanted to keep the sensor as a unit for safety, so opted to embrace the odd design.

In a nutshell, the EmonTX reference design is to take the AC signal from a transformer fitted with an appropriate burden resistor, DC-bias it to lift the whole signal so it’s always positive, and then scale it to a range that the microcontroller on the board can tolerate (5V or 3.3V).

This design is very simple, requires very few components (resistors and a capacitor), and makes estimation easy: sample the waveform, scale the value according to the burden resistance and turns ratio, then compute the RMS of those samples to get an estimate of current. Multiplying this by an estimated (or measured) voltage and power factor generates a power consumption estimate in Watts.

Efergy's current transformer internal circuit (modelled in LTSPICE)
Efergy’s current transformer internal circuit (modelled in LTSPICE)

The Efergy sensor’s extra components do a couple of things. Firstly, a half-wave rectifier diode (D2 in the schematic) chops half of the waveform off (in practice, this limits the voltage on one half of the signal to 0.7V). This immediately means sampling the waveform won’t work without extra logic.

The diode D1 serves to decrease the burden resistance for larger loads. This results in a non-linear response to the current through the primary. Both of these components allow more of the ADC’s resolution to be used measuring the waveform at lower (expected) loads.

I threw this circuit into LTSPICE, and simulated it under a sinusoidal current source with an amplitude of 110mA, corresponding to a sinusoidal current through the primary of about 100A with a turns ratio of 1350. The waveform’s shown below:

Efergy current transformer AC response for 100A RMS.
Efergy current transformer AC response for 100A RMS.

This shows the clipped waveform, limited by D2, and the non-linear “kink” in the output voltage as the current increases, roughly at -52mA on the secondary (about 73A on the primary).

I’m using an MSP430G2553 for this project, which includes a 10-bit ADC. Unfortunately, TI advises against applying voltages outside the region of 0-3.3V to the microcontroller, and the ADC only converts inputs between 0V and 3V.

Like the EmonTX design, this signal requires at least two transforms, one to shift it so it’s all positive, and another to scale it to the safe input range. The shift can be achieved by biasing the circuit from a voltage divider (as EmonTX does), and the scale can also be achieved using a voltage divider.

However, half of the waveform isn’t useful at all, so it doesn’t make sense to waste the ADC’s range on it. The negative voltage still needs to be removed, however, so the sensor needs to be biased by the drop of D2.

To claw back some useful range, and to clamp the negative cycle to (close to) zero, I placed a diode in line with the signal, which drops the signal by about 0.7V. With a 0.8V bas, this takes the 0mA offset for the positive cycle from 0.7V to about 0.4V. This bias also ensures (at least theoretically) that the rectifier diode is biased when the interesting signal is going through it. Putting this together with an output divider:

Efergy monitor circuit design

This produces the following waveforms, measured at the current transformer (red), at the diode D5 (green), and at the output (blue), against current (cyan):

Output Waveforms

This scales the input (roughly) into the 0-3V range. At the top end, the output does increase to around 3.3V, but this occurs when the current drawn at the primary is around 95A; at this point, I have bigger problems than a blown MSP430.

Up to now, I’ve only been simulating the circuit, so practical considerations might change things. I still need to build the hardware and deal with mapping the signal to estimates of instantaneous AC usage. I’ll cover these issues in later posts.

The LTSPICE model for this post is available here.

Using the watchdog timer to fix an unstable Raspberry Pi

I’m using a Raspberry Pi to make time-lapse photos using the motion daemon. The camera I use, a generic “GEMBIRD” (VID:PID 1908:2311) works out of the box, but causes the Pi to lock up from time to time. After replacing all the polyfuses with either a normal quick blow 1A fuse (for F3) or wire (USB fuses), the freezing still happened, even with different adapters with more than enough current capacity.

I’ve surmised the problem is the driver causing a kernel panic. This means the Pi won’t respond on the network and needs a hard reset to get it working again. I’ve not had time to diagnose the fault, but the BCM2708 has a watchdog timer that allows the freezing problems to be worked around.

After following the above tutorial, watchdog wasn’t using the hardware watchdog so was unable to reboot in the instance of a kernel panic or other mystery hardware freeze. The cause of the problem is the default heartbeat interval (60s). Setting it to 15s fixes it. To test that it works, just kill it and the system will reboot if the hardware timer is enabled.

Rebooting the system when it hangs is all well and good, but the freezing (or rebooting) could cause corruption on the SD card. To guard against this, the root partition can be kept read-only, so in the event of a crash the system should still remain bootable. A few daemons and other things need to be able to write to parts of the filesystem (/var, /home, parts of /etc). I followed the instructions here and here; here are the steps in full, after creating the new partition and mounting it on /persistent, and taking a backup image of the SD card. I ran the commands as root (sudo -i) rather than with sudo to avoid writes to /home while moving it.

1. Move/copy the /home, /var and /media partitions over to /persistent:

mv /home /persistent
cp /var /persistent
mv /media /persistent

2. Recreate mount points for each directory:

mkdir /home /var /media

3. Add bind entries for each mount point in /etc/fstab

nano /etc/fstab

Add lines:

/dev/mmcblk0p3  /persistentext4defaults,noatime    0       0
/persistent/media /media        none    bind       0       0
/persistent/home  /home         none    bind       0       0
/persistent/var   /var          none    bind       0       0

4. Link /etc/mtab to /proc/self/mounts:

rm /etc/mtab
ln -s /proc/self/mounts /etc/mtab

5. Move /etc/network/run to /dev/shm

rm -rf /etc/network/run 
sudo dpkg-reconfigure ifupdown

6. Delete the contents of /var

rm -r /var/*

7. Mount the replacement partitions:

mount -a

After these steps, everything should be OK. The current root filesytem is still writeable, so packages can still be installed, config files edited. The new /var partition worked, so I rebooted the Pi to see if it still came up.

The next test was to remount the / partition as read-only and see if everything still worked. Running mount -r -o remount / worked without any errors, suggesting nothing was still trying to write to the partition. After waiting a little while to see if anything popped up in /var/log/messages, I edited /etc/fstab to add “,ro” to the entry for / and rebooted to make / read-only by default.

These changes made the system more likely to survive random reboots, but it would still periodically lock up. I found that lockups only happened when motion was reading from the camera. The lock ups were caused just after a reboot, where the motion daemon started. The problem was caused by watchdog starting after motion, leaving a small time window for a lockup to happen without being caught by the watchdog timer.

To fix this, I set motion’s init script to depend on all services, and changed watchdog to only depend upon wd_keepalive. I changed /etc/init.d/motion to add $all to the #Required-Start directive, and /etc/init.d/watchdog to replace $all with wd_keepalive. After editing the inits script, they have to be refreshed by deleting and adding them in chkconfig: sudo chkconfig --del motion && sudo chkconfig --add motion and sudo chkconfig --del watchdog && sudo chkconfig --add watchdog. This shortens the window that motion can start (and freeze the system) before the watchdog has a chance to start.

It’s more of a cheap kludge than a fix, but it works.

Torness Power Station Visit

A while ago I came across Charles Stross’s account of visiting the AGR power station at Torness (not far from Edinburgh). Coincidentally, EDF recently opened a visitor’s centre there and now runs tours of the plant. I went on a visit recently and this is my account of what’s there. It’s worth a trip if you can make it; it only takes 90 minutes and only costs a photocopy of your passport (if you’re British). Sadly, the tour isn’t as involved as the one Charles Stross described, but it’s good fun nonetheless.

Public image seems to be the forefront of the tour. From the visitor’s centre and right through the tour, the virtues of nuclear power are prominently featured. Interestingly enough, the start of this campaign includes a video of a nuclear waste transit container surviving a collision with an ill-fated train (“this is a controlled test”, as the subtitle in the video helpfully adds).

The interesting part starts at a security gate, where you’re given a badge, checked for any verboten items (cameras, USB sticks, firearms) and pass through a secure turnstile. Once on-site the tour starts through a small glass building that leads, via a glass walled walkway into the main building.

Once inside the large building, the tour starts at a board showing Torness’ history, from its foundations to EDF’s latest mascot (whose name is Zingy). Emphasis on safety is paramount throughout the tour, starting with warnings on the use of handrails for stairs, walking with hands in pockets and untied shoelaces. Beyond the timeline, the entrance to the charge hall, and the extensive checks on dosage and material leakage are highlighted. Past a muster point stocked with potassium iodide and personal dosimeters, the tour begins, via an elevator and a bright red door that raises an alarm if held open for more than 15 seconds.

Viewing decks cover three of the main parts of the site. The first takes you over into the vast charge hall, overlooking the reactors, the spent fuel storage chamber and the fuel disposal chute. It’s not immediately clear why the hall is so large but this is clarified by the scale of the green “monster”; the machine, tasked with assembling fuel assemblies and loading them into the reactors.

Some corridors that wouldn’t look out of place in a 70s school building lead to a viewing area overlooking the control room. Decked out appropriately with a plethora of buttons, gauges and dials, the control room itself looks similarly dated (although well-maintained) — mostly down to the cream colour of the panels.

Red carpets in the control room mark out the areas where a button might find itself accidentally pushed. Sitting atop the two reactor control desks are several monitors showing old HMIs for the plant’s SCADA systems, complete with limited colour palette and cyan-on-black text. A common offender sits amongst these panels: Windows XP and Excel

The final stop on the tour is a gallery overlooking the cavernous turbine hall, showcasing the two blue GEC generator and turbine units. The noise emitted by these things is quite ferocious, even after attenuation by the glass.

The most interesting part of the tour was how clean the building is; this is to make spotting anomalies a little easier. The dated decor is also something to behold. It makes an interesting contrast — the building’s in great shape despite its obvious age. Like other AGRs (except the one at Sellafield), Torness will probably see its lifetime extended. As it stands, a lot of the technology at Torness is “obsolete”, down to the use of Windows XP in the control room. Despite that, it still produces electricity that powers approximately 2.5 million homes.

To top off the spectacular tour, the staff there are all friendly, not just those in the visitor’s centre.