Converting .mysql_history Files to New Format

When I upgraded one of my servers from Fedora 20 to 21 recently, I noticed that – among a few other issues – the command line history of my mysql client was missing. Too me, this is valuable information and documentation, even. So I investigated and found, that the file format for the .mysql_history seemed to have changed and the client had just discarded my old data. The new file now contains various characters (including ‘space’) in octal notation and it starts with a special marker line:


I assume that this change might have been caused by either MariaDB or Fedora itself having switched from readline to using libedit.

#!/usr/bin/env php
$oldData = file($argv[1]);
foreach ($oldData as $line) {

The PHP package that comes with Fedora 21 seems to be using the same library. So, after restoring my old .mysql_history from backup it was easily converted to the new format using but a few lines of PHP code:

./convert_history.php ~/.mysql_history.bkp ~/.mysql_history



Wireless Sensor Network, Part 2

Still trying to save my batteries

By now it is time for at least an intermediate update on this project. In the meantime I have indeed revised the sensor boards by adding a switching transistor controlled through one of the remaining digital pins on the ATmega. The firmware was modified, and it will now power down the attached sensors before entering sleep mode. Disappointingly, this resulted in a battery lifetime extended by no more than roughly 10%.

On a mostly unrelated note, the voltage graph now looks “stepped” when compared to the previous one. This seemed to be a direct result of switching from maniacbug’s original driver for the RF24 module to the one newly optimized by tmrh20. I’ve got absolutely no clue why this would affect the voltage measurement against the bandgap reference, but there it was. If you can shed some light on this, please leave me a comment.

The next thing I tried, somewhat desperately, was to reduce the number of data samples by increasing the sleep time from 1 minute to 5 minutes. This had no discernible effect on battery life-time. But at least it leads to the conclusion that I need to further investigate power consumption during sleep mode.

So, just assuming that it was the sensors that were draining the batteries in sleep mode turned out to be a bad idea. Now I really need to replace my crappy 7€ multimeter with something a little more precise that will allow me to actually measure which part of the circuit is drawing which amount of current.

Wireless Sensor Network, Part 1

With all components finally having arrived from China, I built the first couple of wireless sensors a few months ago. They are using the cheap nRF24L01 transceiver modules for data transmission based on the RF24Network network layer. I’m using a Raspberry Pi with the transceiver module directly connected to its GPIO pins as the root node. The sensor nodes are controlled by an ATmega328 microcrontroller flashed with the Arduino firmware for convenience.

Each sensor node currently features a DHT11 humidity sensor, a DS18B20 digital thermometer, a BMP180 barometric pressures sensor and it’s powered by three AA cells. The controller spends most of its time in power-saving sleep mode, waking up to send a data packet about every minute. Each packet contains the readings from the three sensors as well as the battery voltage as determined against the bandgap reference.

The setup is far from final. For one, I’m still not sure how to store the data. Currently, it is just logged into a text file with an occasional and experimental import into OpenTSDB. I haven’t really come to trust OpenTSDB since I haven’t found a suitable and convincing way to backup the HBase data, yet.  Also, I might still replace the Raspberry Pi root node with an Arduino based MQTT relay.

First though, the power consumption of the sensor nodes needs improving. When I started out I was hoping for each node to run for close to a year on a single set of batteries. As you can see in the graph above, with the current design a set of three NiMH batteries with low self discharge lasts for no more than 6 weeks. What comes to mind in order to improve this, is to switch off the sensors and the transceiver in between samples. That is probably what I’ll try next. Please let me know if you’ve got any other ideas!



Minimal Arduino using an ATmega88

In preparation of the quest to build my own wireless sensor network I spent some time in getting a minimal Arduino up and running based on the ATmega8 chip. It was not before I finally succeeded that I realized it would be of no use in this case, because the ATmega8 lacks the ability to trigger an interrupt handler through its watchdog timer. So, I wouldn’t be able to use its power down sleep mode.

ATmega88 using internal clock

I then found a couple of ATmega88 chips in my stockpile, which aren’t subject to this limitation. Fortunately, setting this one up as a minimal Arduino turned out to be much the same. I mostly followed an existing guide, but added the Optiboot bootloader to the mix.

I first needed to compile the bootloader for the use with the internal osciallator at 8Mhz. So I added the following lines to arduino-1.0.5-r2/hardware/arduino/bootloaders/optiboot/Makefile:

atmega88_8: TARGET = atmega88
atmega88_8: MCU_TARGET = atmega88
atmega88_8: CFLAGS += '-DLED_START_FLASHES=3' '-DBAUD_RATE=19200'
atmega88_8: AVR_FREQ = 8000000L
atmega88_8: LDSECTIONS  = -Wl,--section-start=.text=0x1e00 -Wl,--section-start=.version=0x1ffe
atmega88_8: $(PROGRAM)_atmega88_8.hex
atmega88_8: $(PROGRAM)_atmega88_8.lst

I limited the baud rate to 19200 as greater values turned out to be less reliable in my setup. For this to work, I had to explicitly disable SOFT_UART in optiboot.c:

/* Switch in soft UART for hard baud rates */
#if (F_CPU/BAUD_RATE) > 280 // > 57600 for 16MHz
#ifndef SOFT_UART
//#define SOFT_UART

With that you can build the bootloader by running make atmega88_8 or omake.bat atmega88_8 on Windows in that same directory.

Next, I added the new Arduino type to arduino-1.0.5-r2/hardware/arduino/boards.txt. It should have been possible to add the board definition to a boards.txt file below the sketchbook directory but that did not work for me and the current version of my Arduino IDE. Optiboot (8MHz internal OSC)

Finally, with the ISP connected, I installed the new bootloader using the Aduino IDE and can now upload sketched through the serial connection.

There seems to be one annoying issue left though. When the compiled sketch size is getting close to the limit of 7680 bytes the upload will fail with an error message. If anyone can shed some light on what I might have missed here, please leave me a comment.

Android Development on Windows using an Intellibook Tablet

Earlier this year I acquired a 7 inch Intellibook Android tablet by ordering a trial subscription for a magazine on Java programming. When I tried to use it for Android development on Windows today, I couldn’t find a suitable USB driver. It took me a while to find out that the driver released by Google for their own devices would actually work.

Intellibook Tablet

Here is how to install it for the Intellibook tablet:

  1. Download the Google USB driver package for Windows.
  2. When finished, the driver is located in <android-sdk>\extras\google\usb_driver\, and you need to modify the file android_winusb.inf contained within. Add the following lines twice, once in each section [Google.NTx86] and [Google.NTamd64]:
    %SingleAdbInterface%        = USB_Install, USB\VID_2207&PID_0010
    %CompositeAdbInterface%     = USB_Install, USB\VID_2207&PID_0010&MI_01
  3. Connect the tablet to the PC and install the modified driver.device_manager
  4. Create the file %USERPROFILE%\.android\adb_usb.ini containing the vendor code 0x2207 or add it if the file exists:
    echo 0x2207 >> "%USERPROFILE%\.android\adb_usb.ini"
  5. Remember to enable developer options and USB debugging on the device.

This worked for me, but of course there is no guarantee since Google does not support this device. On the other hand, it might also work for other “no-name” devices lacking suitable drivers. To find the hardware id string needed to modify the driver’s *.inf file connect the device to the PC, locate it in the Device Manager, and right-click to open properties -> details -> hardware-id. Try at your own risk…

Cheap USBASP Sticks for V-USB Development

A while ago I ordered a couple of cheap USBASP sticks. They are to be had on Ebay for as little as 2,50 € including world wide shipping and meant to be used for programming AVR microcontrollers. The programmer itself is based on an ATMega 8 controller so one of the sticks can actually be used to update the USBASP firmware on the other one. All it needs is shortening JP2 on the board to be flashed.


avrdude -v -c usbasp -p atmega8 -U flash:w:usbasp.atmega8.2011-05-28.hex

But that is not even best part: the USBASP firmware is based on the open source V-USB driver, meaning these sticks can easily flashed with any other firmware using the same driver. So, they make for a cheap, complete, and easy to handle development platform for USB projects like the IR receiver introduced in an earlier post.

USB Receiver for the Ruwido Merlin IR Keyboard

When I noticed this compact IR Keyboard was on sale for no more than 1.- € I had no choice but to order a couple. One of them might prove useful when I manage to set up a media center based on a Raspberry Pi some day.

Ruwido Merlin Keyboard

Of course, this keyboard comes without a receiver. But how hard could it be to get it to work with LIRC? Very hard, as it turns out. Fortunately, I wasn’t the first one to encounter this problem. There is an open source decoder for this keyboard targeting AVR microcontrollers and I had just come up with the idea to combine it with the V-USB stack to create a USB receiver when I found out that someone else had already done just that.

The circuit was easily set up on a breadboard, but the firmware needed a few minor modifications to compile with recent versions of avr-gcc. When those were done and the firmware flashed, the operating system would still refuse to accept the circuit as a valid HID device. It took me quite a while to figure out that the author had made and committed some changes that seemingly broke the USB stack. When I reverted to an earlier version, the device worked fine. My clone of the repository is available on Github and its master branch now holds the still working version of the firmware.

Ruwido HIDUSB 1.0

Then I created a compact board to hold all the component. It has a single sided layout with only a few jumper wires on the top side and I used the toner transfer method for etching. The result did not turn out very pretty but it does the job. Unfortunately, in the first version of my design I missed R3 so the images on the gallery show a quick-fix for that problem. The board also features a 10 pin connector for the ISP since the firmware will probably still require more work. I added the revised EAGLE files to the repository.

OpenWRT and Scripting Languages, Part 1

The RGB wall light project is currently on hold due to a massive lack of time. Still, I’m taking little steps of preparation for the second phase of the project, the software intended to control the RGB matrix. I could use the gcc cross compiler for the job, but using a scripting language to develop directly on the device promises to be far more comfortable. I’d like to narrow this down to languages readily available as packages for OpenWRT which leaves me with Erlang, Lua, Ruby, Perl, and Python. Since I feel like trying something new I’m going to rule out Perl for now.

Make Room

The installation of the bleeding edge OpenWRT version called “Attitude Adjustment” on the TL-MR3220 leaves about 1MB of space for additional packages to be installed – which isn’t even close to being enough for this purpose. So, the first step is to extend that space and the currently recommended way to do this is to move the overlay mount to an external USB device: Continue reading

Fixing Broken PSU of Asus WL-500gP Router

I’ve been using the Asus WL-500g Premium under OpenWRT for years. I always keep a second cold-spare device in stock to minimize our household’s downtime in case the router breaks. Last year, it was actually the router’s power supply unit that ceased to function. After I replaced it with the PSU from the spare device everything was back to normal. The same thing happened again this week, so I decided to document how I fixed the wall-wart back then.

Note: This is potentially dangerous so don’t try this at home! You’ve been warned.

Actually, the hardest part was to open the PSU. Both parts of the case are glued together, so separating them will inevitably scar the case. After the case was open, the worn out capacitor that broke the unit was clearly visible. I just de-soldered the capacitor, replaced it with a new one, and closed the case using two strips of adhesive tape. Done.

Wifi Enabled RGB Matrix Wall Light, Part 1

Inspired by the so called Lampduino and several other related projects I decided to build my own version of a RGB matrix wall light some time ago. During the last few weeks I finally managed to start out on this project.


The matrix of 64 individual cells and the LED back-plane are made from 4mm thin plywood that I spray-painted with a silvery varnish. The outer frame is made from a stronger 14mm birch multiplex board, yielding a very solid construction. I also bought some thin, white PVC board intended as the material for the front cover, but I still have to figure out how to cut that nicely.

Lacking the ambition to create my own LED driver board I settled for the “Rainbowduino”, an Arduino compatible board developed and sold by Seeed Studio. In addition to that, I wanted the matrix to be controlled and programmed over the air. One of the cheapest, easiest, and most versatile ways to accomplish this was to modify an OpenWRT based router for this purpose.

Continue reading