MySQL

Hot on the heals of ndb_watch 0.6.0, 0.6.1 has been released.  This is primarily a bug fix release dealing with minor reporting issues and a couple of potential crashes.

0.7 is also nearing completion and will have features such as better configuration handling and better memory status reporting.

0.6.1 can be downloaded at SourceForge and 0.7.x is currently in the SVN trunk hosted there.  I will branch it as soon as I am ready to declare a feature freeze.

After a year of almost no development I decided recently to pickup ndb_watch again and see if I could improve it.  A few weeks later and I have 0.6.0 ready for the world to use.

ndb_watch is a utility I written in C to monitor MySQL Cluster NDB nodes and mail a systems administrator when they fail.

It has many new features such as memory statistics (thanks to Monty Taylor), multiple management nodes and better email handling.  There are also many bug fixes.

If you fancy taking a look please go to its sourceforge page.

I am open to patches, bug reports and suggestions for future versions.

UPDATE 10th August
There is now an Ubuntu Hardy i386 package on SourceForge for this too.  Built against the packages at https://launchpad.net/~ndb-bindings/+archive

Just one week after my Zend Certified Engineer exam I have taken and passed both CMDBA I & II.  This means I am now a Certified MySQL Database Administrator!

Now I think I will have a break from exams

Well, I have finally booked my Zend Certification exam and MySQL DBA exams.  I will be taking the ZCE on the 4th July and CMDBA I and II on the 11th July.

Wish me luck

Say for example you have 2 tables, one containing items and another containing item revisions and you wanted the newest revision ID for each item, if you are like me (and not many are) your first thought would be:

SELECT itemID, revisionID
FROM revisions
ORDER BY revisionID DESC
GROUP BY itemID

If you tried this like I did you will find it will throw a syntax error, as MySQL can only do GROUP BY followed by ORDER BY. I believe there are other DBMSs that so support this however. So, what do you do if you get stuck like I did? Well, you do the ORDER BY as a subquery!:

SELECT itemID, revisionID
FROM
(SELECT itemID, revisionID
FROM revisions
ORDER BY revisionID DESC)
revisions
GROUP BY itemID

This is effectively extracting the results, doing the order by and giving this result a pseudo table name, this results set is then grouped with the GROUP BY clause. You then have your ORDER BY / GROUP BY.

This is a problem I came across quite a while ago now, but thought I would share it whilst going through my own documentation