Catching up on RSS feeds this morning I was extremely interested to see this technique for speeding up GROUP BY queries using approximation. In the first example, it knocked down the processing time from over 30 minutes to 30 seconds pretty much (as far as I can make out) because of MySQL’s speed in string comparison operations.

I can see this would be really useful for search tables…

Ben’s in the office with Nicky today and I’m sat at home waiting for my car to be ready.

As seen in another window:

okay – so Ben doesn’t want to play with the games on CBeebies, but he is enjoying looking at the source code for the site.

🙂

The sort of work I do is usually to do with end user services – websites which allow a user to create a login and password to give them access to larger featuresets, etc. You know the kind of thing – social network websites are a prime example: as part of that I frequently get asked to log information a user may do, recording the source IP address of any messages they may leave in order to protect from abuse.

A frequent mistake folks make in writing code to do this is in the IP address which made the HTTP request. Most larger ISPs enforce transparent proxying on the user, meaning that instead of having the IP address of the end user themselves you’ve got the IP address of a proxy which may handle hundreds of thousands of users! It causes issues not just in traceability, but also in things like bandwidth logging if you’re doing it at an application level.

Enter X-Forwarded-For. It’s an HTTP header which most proxy software will add, giving the source IP address which requested the page through the proxy and preventing proxy servers becoming anonymising services.

This is exposed to PHP through the $_SERVER superglobal, so you can use it like so:

if ( isset($_SERVER['HTTP_X_FORWARDED_FOR']) ) {
  logEntry("User connected from ".$_SERVER['HTTP_X_FORWARDED_FOR']);
} else {
  logEntry("User connected from ".$_SERVER['REMOTE_ADDR']);
}

Now there’s a gotcha here. A lot of ISPs such as AOL and NTL allocate ‘private’ RFC1918 IP space internal to their network. That means if you get 10.5.3.4 or similar then you’re on a hide into nothing because you’ve not got the proxy which may be ‘seeing’ that address – so you need to log both when you find a proxy:

if ( isset($_SERVER['HTTP_X_FORWARDED_FOR']) ) {
  logEntry("User connected via proxy ".$_SERVER['REMOTE_ADDR'].
           " from ".$_SERVER['HTTP_X_FORWARDED_FOR']);
} else {
    logEntry("User connected from ".$_SERVER['REMOTE_ADDR']);
}

But what if users are stringing proxies together? Well, that’s dealt with too. The proxy software appends itself to each X-Forwarded-For header, separated by commas.

Of course this should be treat as a first line of defence – if someone’s really wanting to hide themselves they can, through IP spoofing or fiddling their own proxy software to remove the header. However, it’s a neat way of preventing false positives and – more importantly – finding out who’s really behind stuff, now RFC1918 endpoints are becoming more and more common.

Wikipedia has a bit about X-Forwarded-For here, if you fancy a little more reading.

With Microsoft’s announcement that they’ll be opening up their binary file formats from Office, I read with interest Joel Spolsky’s reasons why MS Office formats are so complicated – it certainly explains an awful lot.

(I’d strongly suggest reading Spolsky’s books and essays – they’re often insightful and you learn a lot.)

Ben’s lunch had RSS mashed potato.

Just a quickie note because I’m bored of relating the same data to everyone – there’s a Linux kernel exploit which seems to affect versions 2.6.17 to 2.6.24.1 and give root privs from any local user:

joel$ ./exploit
[..]
[+] mmap: 0xb7f29000 .. 0xb7f5b000
[+] root
root#

You can in some cases patch it live (!) but I’ve got at least two installations where I had to compile a new kernel.

There’s a patch at git.kernel.org, and you can test whether you’re vulnerable using milw0rm’s exploit proof-of-concept.

Just been reading on the BBC website that the Self Assessment submission page has died on the final day for submissions (giving this error).

The Service Availability page looks to be partially dead too – the stylesheet’s failing to load and without sensible fallback the information is patchy at best.

Trebles all round!

Monty Widenius (creator of MyISAM and MySQL) has announced the first public release of the Maria engine. This basically sounds like crash-safe MyISAM crossed with the InnoDB featureset, and there’s a Bitkeeper archive here.

It would certainly be nice to have fulltext column indexing in a format which supports ACID and row-level locking, together with the capability for foreign keys. It’s useful in some cases to have a table-per-file and would be even better if it was crash-safe 😛

I guess it’ll end up being version 2 or 3 before it’s ready for production, however. I certainly wouldn’t feel safe using something like that before it’s been through the mill at larger MySQL installations such as Yahoo, either.

More fodder for the ENUM-Bad-Or-Good argument!

I’ve just read this article over on the MySQL Performance Blog, comparing speed of ENUM vs VARCHAR vs Joined Tables.

I like ENUM and find it makes my life a lot easier in a lot of cases (but not all, let’s get that straight – it’s good where it’s appropriate).

Nice to know other people can justify use of it too 😛

Once again demonstrating an utter lack of clue, the EU guy in charge of the ‘personal data and privacy’ working group has stated IP addresses are personal data. I wonder what planet he’s on?

Back at Mailbox in the late 90s we’d allocate a block of 255 IP addresses to share between approximately 3000 dialup users, for instance. Then there’s the RFC1918 private address space, which not only is reused again and again the world over, but is regularly allocated by ISPs nowadays looking to conserve address space (as an example, Vodafone do this with their 3G service).

What an astounding display of fuckwittery.