When MySQL joins become disjointed

I honestly don't know how I've managed to avoid this until now, but I ran into an issue today where I could not get a SQL query with a join to execute properly.  I kept getting errors saying that I had referenced an unknown column.  After rewriting the query several times and questioning my sanity, I decided to do a quick search online.  Much to my surprise, I quickly found the answer.  The way joins are handled changed ever so slightly in MySQL 5.0, such that they are now more closely aligned with ANSI SQL standards.  By tweaking the query and adding parentheses in the from clause, all worked perfectly.

Many thanks to jbrinkmann for his excellent article on the subject at MySQLjoin.com.

This box is so 1337

I've been hovering over this MySQL server waiting for the uptime to roll...

(click on the image for a larger view)

ZRM for MySQL and NFS locking

I ran into this issue a couple of years ago when we decided to implement a VTL solution for our backups.  We use ZRM for MySQL for our database backups, and it has been extremely dependable.  We were initially backing up to local storage on the backup server, but then moved our target over to a Data Domain DDR530 data restorer.  We accessed the DDR530 via NFS and took the necessary precautions regarding file locking only to still have problems backing up to the DDR530.  After a bit of digging and a minor patch to the ZRM code to disable flocks, backups worked perfectly but purges remained a problem.  All of this was due to forces at work under the hood of the DDR530 which threw a wrench in attempting to thwart locking.

Unable to get the supplied purge script from ZRM to play well with the DDR530, I set out to create a suitable stand-in.  It's nothing fancy, but if for some reason you cannot get the supplied purge process to work, feel free to give this a spin.  It will get the job done without a lot of fuss.

#!/bin/bash

##################################################
# purge-zrm-backups
#
# purges backups without file locking
#
##################################################

BACKUPDIR="/path/to/backups"
PURGELOG="/var/log/mysql-zrm/purgelog"
CURDATE=`date +%s`
TIMESTAMP="date -Iseconds"

echo "$($TIMESTAMP) -- Starting purge session" >> $PURGELOG

for buset in $BACKUPDIR/*
do
   for budate in $buset/*
   do
      KEEP=`grep retention-policy $budate/index | awk -F= '{print$2}'`
      WEEKS=${KEEP:0:1}
      TICKS=$((WEEKS * 7 * 86400))
      CUTOFF=$((CURDATE - TICKS))
      TSTAMP=`grep backup-date-epoch $budate/index | awk -F= '{print$2}'`

      if [ "$TSTAMP" -lt "$CUTOFF" ]; then
         echo "$($TIMESTAMP) -- | Purging $budate" >> $PURGELOG
         rm -rf $budate 2>&1 &
      fi
   done
done

echo "$($TIMESTAMP) -- Finished purge session" >> $PURGELOG

exit 0