First nightmare was caused when I tried to run drush rsync and drush sql-sync between two mismatched versions of Drupal.
drush sql-sync -v --no-cache -y @hdchd.he245 @hdchd.xulu
drush rsync -v -y @hdchd.he245 @hdchd.xulu
sql-sync kept failing with the following error:
drush --all 2>&1 Error: no database record could be found for target
The problem was that I forgot the site was D6 and had built my local site alias with D7. Dumbass. This caused an endless chain of problems until I figured out the issue.
Solution: Wipe my local document root and database and replaced it with a stock D6 installation. Then both drush sync's started working.
The problem was solved by reverting my dev PHP to 5.2 which is the officially supported version of PHP for Drupal 6.
Thankfully the latest version of MAMP allowed me to run 5.2.17.