MariaDB Indexes on DATETIME and DATE functions

Quick note in haste, but important for me to remember.

SELECT * FROM table WHERE DATE(`period`)='2016-09-05'

is orders of magnitude slower than

SELECT * FROM table WHERE `period`>='2016-09-05 00:00:00' AND `period`<'2016-09-06 00:00:00'

where `period` is an indexed DATETIME field. The former uses an assortment of WHERE and INDEX clauses; the latter relies on the INDEX and uses a RANGE search, which is much faster.

554 Message not allowed – Headers are not RFC compliant[291]

If you get this message when sending emails to Yahoo addresses from PHP, it’s quite possible the subject line is being duplicated and this is causing your email to be rejected.

PHP’s mail() function takes a Subject as a parameter. It also accepts a custom list of headers. The mail() function seems to always append a subject header irrespective of the contents of the custom list.

So, if you have the Subject already defined in your custom headers, PHP will add another. This is not RFC compliant, and Yahoo has a particularly strict checker for incoming emails (most other mail servers seem to ignore this, as far as I can see).

Take the Subject line out of your custom headers, make sure it’s in the mail() call itself, and the mail should send (assuming no other issues….)

Huawei E3131

It seems like I’m having mixed success with devices at the moment. To be fair, this one isn’t a product mismatch but a manufacturer issue, and thankfully seems fairly easy to get around.

This post is as much a personal note/reminder as anything else. If anybody needs detailed notes please drop me a line 🙂

The Huawei E3131 has been a pretty good 3G modem for the Raspberry Pi. After a bit of hacking about, it lights up and connects to the Internet.

The first batch (black cover in the photo) I bought worked as follows:

IMG_20151003_154035A network interface appears on eth1. The Pi gets an IP address and the modem appears as The modem has a mini web server which can be interrogated to control & get information about the connection.

The latest batch (white ones) work as follows:

Network interface is now on and the modem is on The web server still works, but it has some kind of basic security mechanism built into it, so you need to get a token first.

The original device had a USB id of 12d1:14db; the newer ones have a USB id of 12d1:14dc

So, not only has the IP address/local interface changed but there’s a new security mechanism involved. Time for some extra work!

Useful links

Update: Looks like it validates the Referer field, perhaps for cross-site protection – I set it to and requests work now.

Raspberry Pi Wifi Dongle on Amazon

Slightly annoyed to discover that my most recent batch of USB wifi adapters, sold as “for Raspberry Pi” are not quite compatible.

For my various wifi projects, I’ve been buying up lots of little wifi adapters. All are labelled in the same way “USB Wifi Adapters for the Raspberry Pi – By New IT”. It looks like they’re all based on this from supplier ‘New IMG_20150927_095541IT’ (whom I’ve no reason to believe are involved in the issues here).

The first few batches worked exactly as I wanted. Plug and play.

The most recent batch has, however, been an entirely different story. They’re simply different.

The picture shows two USB devices. On the left is the most recent, non-working device. On the right is the working device. The most notable difference is the ‘802.11n’ label is on a different side for each.

What’s the difference?

It’s all in the internals. Despite there being hundreds of wifi products they’re all based on a surprisingly small number of variants.

The working version is based on a chipset from Ralink, the RT5370. This works out of the box for the Raspberry Pi, seems to be a good performer, and – crucially for me – supports monitor mode (so it can act as an access point too).

The problematic version uses the Ralink 8188 (RT8188) chipset. This doesn’t have full support in the Raspberry Pi, and frankly it’s been a pain in the backside to work with. I have a box full of old USB wifi dongles; most seem to be based on RT8188 and never really had much luck with them.

It’s possible to get them up and running, by compiling special drivers, but frankly it isn’t worth the effort and I’ve had lots of memory leaks and other issues with them.

Be careful what you buy

So, back to Amazon. It seems that some devices sold as ‘for Raspberry Pi’ are in fact not ideal – and take a lot of work to get going. This shows up in the reviews as it looks like a few people have been stung.

The whole thing isn’t helped by Amazon’s peculiar supplier system, where it opts for the cheapest supplier based on the product you choose. It looks like one or two suppliers have these RT8188 chips and are selling them under the wrong label – no idea if intentional or not.

Thankfully it’s fairly easy to pick the right supplier. Make sure you choose ‘New IT’. I’ve just ordered another four devices from them and they work fine, straight out of the box, for the Raspberry Pi. Yes, they’re a bit more expensive but they work!

Now to decide whether it’s worth my effort negotiating Amazon’s support line and sending the two dud devices back…

Visual Studio/ASP.NET Code Behind

I’m going to need to remember this, as it runs slightly counter-intuitive to what I was expecting (although makes sense).

Code compiled in App_Code is available globally, so any other piece of code can reference it.

Code compiled in code-behind (i.e. the .cs files ‘behind’ ASPX pages) is only available to its corresponding ASPX page unless you explicitly reference it.

This can be done by adding the following to the ASPX page:

<%@ Reference Page=" to .aspx" %>

One to remember.

Wifi Visitor Counting

It’s been a little while since my last post about visitor tracking, and some updates are long overdue.

The good news is that it’s ready to launch, with lots of interest. This has been a challenging project in many respects, and I’ll be documenting the various aspects shortly.

Initial results are very promising. We’re seeing figures that have never before been seen; stats and information that were until now completely invisible. It’s thrown up issues, about data transfer, privacy and many other factors.

Privacy is a huge issue for me – there are lots of companies that (in my mind) are far too aggressive on this issue – so expect more on this as I continue to share my work.

It’s exciting stuff, and I’m keen to share my experiences and thoughts for others. Stay tuned!

Avoiding SD Card Corruption on a Raspberry Pi

As part of my visitor mapping project, the bulk of my time has been spent throwing curse words at the SD cards.

Raspberry Pis depend on SD cards. There’s little other choice. More specifically, the newer devices use Micro SD cards. It’s fairly incredible to realise just how much data can be stored on something around the size of a thumbnail, but I digress.

The problem with these cards (& flash memory in general) is that they’re very sensitive to corruption. Any power fluctuation or problem writing is a potential problem, with a real chance that the next boot will end in failure/disaster.

In my experience (ten devices in lab conditions; regular reboots) failure rate was as high as one in ten reboots. Slightly oddly, software resets (i.e. rebooting from the command line) appeared to fail more often than unpredictable power loss.

Read-Only is a winner

There is a way to boot a Raspberry Pi in read-only mode, although it requires a bit of care. I found a couple of ways to do this, by editing the boot config file and by setting the fstab to boot as read only.

The consequences are hopefully clear – nothing is retained. However, it’s also possible to temporarily remount the file system in writeable mode (and revert shortly after). My software is capable of self-updating, and writes its logs to the filesystem in case of Internet problems. Both work very well.

This website gave the best instructions. It includes removing a bunch of daemons that – frankly – I had no need for. I also removed fake-hwclock (for reasons which I’ll describe on another post) and preferred mounting ramfs on places like /var/log/ over linking.

The results have been pretty impressive. Since switching to this method I haven’t had a single corruption issue (despite many tens of reboots)

Power is a concern

I suspect that one of the big problems is power, and began testing with a voltmeter. My suspicion is that brownouts – i.e. dips in power – were causing reboots and corruption.

The latest Raspberry Pi hardware supports a configuration that boosts the current to USB drives. Setting max_usb_current=1 in /boot/config.txt appears to increase the per-device supply to 1.2A which avoids many of the power-related issues on high-demand peripherals.

My non-scientific and unmeasured experience confirms this. I’ve seen much better performance and reliability as a result, and – although can’t be sure – I suspect this has helped the SD card woes as well.

In any case – touch wood and all that – this appears to have solved the corruption issues.

The 5 Weekends 823 Years Thing

There’s a thing going around Facebook about ‘this month has five weekends, it only happens once every 823 years’.

At first glance, it is pretty amazing. The dates line up just right to grant us five whole weekends, including Friday. Bliss.

11796265_868726643198344_7206118342273344835_nSadly, it’s rubbish. Or, to put it another way: if you like 5 weekend months, I’ve got good news for you!

The only way a month have even a chance of five weekends (I’m counting Fri+Sat+Sun) is if it has 31 days. Any shorter, and at least one of those weekends would also be cut short.

That means only January, March, May, July, August, October and December can qualify.

We can also only achieve this if the first of the month is a Friday, since it’s the only way we can fit all five weekends into a single month.

So, of the seven months of the year that fit the criteria, at least one of those needs to start on a Friday. You’ve seven months; seven days. The odds are pretty good.

I make it 1-(6/7)^7, around 66% chance that any given year will have at least one 31-day month that starts on a Friday.

But wait, there’s more…

Years are pretty regular things. Apart from the small annoyance of February (damn you February) every month is a fixed length – and even the changeable one is predictable.

This is partly how people manage to tell you the day of the week, given any date in recent history. They don’t memorise each day – it’s systematic.

So if January is a ‘5 weekend month’ then October of the same year will also be one, provided it’s not a leap year. If it is, then July is also a 5 weekend month.

This affects the probabilities a little, since the dates aren’t truly random, but 66% is a fairly good ballpark for most purposes.

Good news for fans of the weekend

2015 has one month that fits the ‘5 weekend month’ rule: May (not August as the original email suggested).

2016 has two: January and July

In 2017 we’ll have to wait until December to celebrate again.

2018 there won’t be any – so we’ll have to wait until March 2019 before we pull out the party poppers again.

Whatever the outcome, it’s certainly not once every 823 years so don’t worry if you missed it.

Slice of Radio for the Raspberry Pi

As part of an ongoing project I have been trying to get multiple Raspberry Pis to talk to each other across a city centre. First, they used wifi for ad-hoc networks. In some cases I’ve used 3G dongles to connect over the Internet. Both have their downsides. Now, I am looking at radio communications to build a mesh of devices.

After a bit of investigation I’ve purchased three Ciseco Slice of Radio devices from Pimoroni or Ciseco themselves. These are fairly straightforward devices that enable the Pis to communicate over radio via the serial port.

There’a a fair amount of information out there about configuring them (although you need to look for the chip itself – SRF – rather than ‘Slice of Radio’ in Google) – this is the best resource I found (with handy links at the bottom).

These devices send 12 byte packets by default, so I’ve written a low level protocol to support a mesh-style network. This will allow the individual devices to relay messages through the network and eventually communicate with a server on the Internet. More on that soon.

Meanwhile, the range of the devices as bought is somewhat limited. I have two devices successfully talking to each other over radio, from the front of the house to the garage in the back garden. Straight-line this is going through two internal walls, an external wall and a metal garage door – so not too bad.

In production this is likely to be more challenging, so I will need to try adding an antenna to the device. There are two types I can use – u.FL and a simple (vertical) wire.

I’m an absolute novice with radio so please excuse/correct me if something is wrong! These are more notes than anything concrete!

u.FL supports the ‘standard’ coaxial aerial found on wifi hubs and – as far as I can tell – this is the connector (presuming u = micro). One can then get a u.FL to SMA (bigger) connector and start adding standard antennae.

Here’s a fairly good overview from Instructables.

Option two is to use a ‘wire whip’ or ‘whip antenna’ connected directly to the board. For an 868Mhz device this needs an 82.2mm vertical wire.

In terms of aesthetics and practicality I’d rather not have a vertical wire coming directly out of the Pi – as shown here – and need to figure out the options.

For the antenna, then it looks like I’ll need the following:

u.FL connection for mounting on the board itself. – £1.00

u.FL to SMA cable – £3.33

SMA ‘rubber duck’ aerial. – £4.58

Total material cost of adding the aerial is therefore £8.91 + VAT

There are various other issues to deal with, but this seems like it’ll give a significant signal boost for the devices. As I explore further, expect more updates!



Bruges (or ‘Brugge’ locally) is a wonderful mediaeval city in the north of Belgium. The cobbled city centre and its canals form a UNESCO World Heritage Site and regularly attracts visitors from all over the world.

Last week, my wife and I visited the city for a long weekend (our second visit) along with the in-laws (their first visit). A quick-ish trip across Eurotunnel and a 90 minute drive the other end, and we were at a B&B just outside the city.

Belfort Tower
The Belfort Tower dominates the main square
Near Christmas, the main square is transformed into a seasonal market and ice rink. It’s quite beautiful.


View from the top. Looking out over the city of Bruges
View from the top. Looking out over the city of Bruges.

Between this and the last visit, we’ve taken in most of the major attractions. The city lends itself to wandering. There are plenty of beautiful areas and little streets to walk through. Coffee shops and brasseries are numerous – you don’t go hungry in Bruges (except Mondays; many places close – although this seems to be diminishing).

The Belfort Tower stands high above the main market square and gives some impressive views of the city and area (as well as a nervous walk back down on a tight spiral staircase).

De Halve Maan (‘Half Moon’) Brewery is an interesting guided tour around the place where local (delicious) beers are brewed. Brugse Zot is a particular favourite of mine. There is now a beer museum near the main square, although we didn’t try this.

The Choco Story features a detailed history of chocolate (its manufacture, health benefits and economic status) which is pretty interesting. Tied with this is the Frietmuseum (‘Chip Museum’) which is a potato-based equivalent. You might also take in a visit to the Diamond Museum – combined tickets are available between the three venues.

Canal tours are readily available from several locations, and are a great way to see the city from its numerous watery thoroughfares. You will take in the convent/monastery Ten Wijngaerde; Sint-Janshospitaal – an 11th c. hospital; Onze-Lieve-Vrouwekerk (‘Church of Our Lady’) and plenty more.

Similarly, horse rides through the city take a different route and explore even more. With a group of four, it was certainly a good way of seeing the city. Rides start in the main square.

We left the in-laws to take in the canal trip while retreating to the Bierwall, a fabulous – if slightly touristy – place to sit, drink and enjoy the passing crowds and canal tours.

If travelling in by train, you should have no problem getting around. Most areas are within walking distance. The station is a short trip outside the city centre.

It’s possible to drive through the centre but I wouldn’t recommend it unless you need to. The streets are bursting with tourists who seem to have no skills of awareness. It could also be a little daunting for those driving on the continent for the first time.

Fortunately parking is fairly easy, find ‘t Zand or Centraal Station parking in the south west. Both are reasonable, spacious and the station has a park & ride facility (although walking is again quite possible). Station parking was €3.50.

I believe it’s also possible to park along the ring road on the northern parks – but don’t quote me on that.

All in, a great time (again) and somewhere we could well imagine visiting again & again. The restaurant food is fantastic and not too pricey. Once all the tourist activities are done, it’s still an incredibly beautiful city and a great place to relax and explore.

I’m not a huge fan of tourist-heavy hotspots. This is definitely one of them, and prepare to fight your way through a sea of selfie sticks and tours. Normally something I’d avoid at any cost, but in this case the beauty of the city is well worth it, and the facilities that come with the crowds are appreciated.