LinuxJedi's /dev/null

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

Whilst doing work on our development server at work today I tried to do an export of our live database and got the following error:

mysqldump: Error 1297: Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from ndbcluster when dumping table `xxxxxxxxxxxx` at row: 37542

After trying several things to get mysqldump to actually export the data I gave in and changed MaxNoOfConcurrentOperations to twice its current size, did a rolling restart and it was successful.

My main worry was that MaxNoOfConcurrentOperations would require a complete cluster restart which would mean downtime as with MaxNoOfConcurrentTransactions, luckily this wasn't the case and I have one happy and healthy cluster again.

MySQL NDB cluster is a relatively new technology which as of MySQL 5.0 in my opinion is still not fully mature. That said great amounts of work have gone into it in MySQL 5.1 and even the 5.0 version can be very stable if looked after properly.

Why use MySQL cluster?

MySQL cluster is designed for high availability, so downtime due to a single node failure should be significantly reduced, in fact in this setup any 2 servers can fail and the cluster will still run. I personally would not recommend running MySQL NDB cluster in the setup described in this document if you are looking for high performance as well as high availability, you will require more servers for this.