Category Archives: Software Development

Munin plugin for graphing MySQL slave delay

I wanted to track the delay of a mysql slave server in Munin, and found nothing appropriate after a quick google. So, in case anyone else finds it useful, here is a plugin which will allow you to do just that.

Here’s a typical graph:

Slave Delay Graph

Download: mysql_slave (1 Kb)

EDIT: I wrote this so I could better understand the recovery time of a slave which had 13 hours of updates to catch up on. While it was good for that, it’s less useful for the day-to-day lag as munin will only be polling it every 5 minutes. A better way would be to use mk-heartbeat to get a more accurate moving average.

MySQL gotcha using BETWEEN with dates

We just updated a development server from Ubuntu 6.06 to 6.10. As a result, MySQL went up a couple of point releases from 5.0.22 to 5.0.24a.

Suddenly, the web application we were working on stopped working. It wasn’t possible to log in.

We quickly narrowed it down to a clause in query which checked a users subscription was valid by checking its start and end dates. It looked bit like this:

SELECT foo FROM bar WHERE NOW() BETWEEN start AND end;

MySQL 5.0.22 returned rows, but 5.0.24 didn’t. After scratching our heads, I checked the manual:

For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as ‘2001-1-1’ in a comparison to a DATE, cast the string to a DATE.

The phrase “best results” make it sound like we’re baking a cake, and that your query results will be fluffier and more flavoursome if you use CAST! What they actually mean is, if you’re comparing different date types, you’re screwed if you don’t cast. You might get nothing.

Sure enough, our start and end columns were dates, and using cast in the query worked just fine:

SELECT foo FROM bar WHERE CAST(NOW() AS DATE)  BETWEEN start AND end;

I couldn’t find anything directly related to this change in behaviour in the changelogs, but hopefully this will help someone out there…

Zend PDT – PHP plugin for Eclipse

I’ve been using PHPEclipse for PHP development for 9 months or so and finding it a real time saver. Sadly it seems that further development has stalled, possibly because of Zend’s announcement in late 2005 that they too would build their own Eclipse plugin.

That has finally borne some fruit with the recent release of a reasonably functional version of Zend PDT, or PHP Development Tool.

Snappy name.

Still, even though a final release isn’t slated until september, this version isn’t too shabby and appears to have a comparable feature set to PHPEclipse already. I haven’t tried the debugger yet, but editing features seem good, with folding of comment blocks and methods, help with PHPDoc tags, nice class inspector pane and reasonable parsing of php code for problems (it doesn’t seem to spot as many problems as PHPEclipse, but maybe I’ve missed a setting somewhere).

So far, it’s shaping up to be pretty good IDE, particularly if you are already using Eclipse. Remote debugging of PHP, in combination with Subclipse SVN plugin and the Web Tools Platform plugin, gives you a pretty capable development environment.

Methinks I’ll be switching permanently soon!