Journal tags: mysql

I’m quite sure this is of no interest to anyone but me, but I finally managed to fix a longstanding weird issue with my website.

I realise that me telling you about a bug specific to my website is like me telling you about a dream I had last night—fascinating for me; incredibly dull for you.

For some reason, my site was being brought to its knees anytime I syndicated a note to Mastodon. I rolled up my sleeves to try to figure out what the problem could be. I was fairly certain the problem was with my code—I’m not much of a back-end programmer.

My tech stack is classic LAMP: Linux, Apache, MySQL and PHP. When I post a note, it gets saved to my database. Then I make a curl request to the Mastodon API to syndicate the post over there. That’s when my CPU starts climbing and my server gets all “bad gateway!” on me.

After spending far too long pulling apart my PHP and curl code, I had to come to the conclusion that I was doing nothing wrong there.

I started watching which processes were making the server fall over. It was MySQL. That seemed odd, because I’m not doing anything too crazy with my database reads.

Then I realised that the problem wasn’t any particular query. The problem was volume. But it only happened when I posted a note to Mastodon.

That’s when I had a lightbulb moment about how the fediverse works.

When I post a note to Mastodon, it includes a link back to the original note to my site. At this point Mastodon does its federation magic and starts spreading the post to all the instances subscribed to my account. And every single one of them follows the link back to the note on my site …all at the same time.

This isn’t a problem when I syndicate my blog posts, because I’ve got a caching mechanism in place for those. I didn’t think I’d need any caching for little ol’ notes. I was wrong.

A simple solution would be not to include the link back to the original note. But I like the reminder that what you see on Mastodon is just a copy. So now I’ve got the same caching mechanism for my notes as I do for my journal (and I did my links while I was at it). Everything is hunky-dory. I can syndicate to Mastodon with impunity.

See? I told you it would only be of interest to me. Although I guess there’s a lesson here. Something something caching.

Christmas is a time for giving, a time for over-indulgence, a time for lounging around and for me, a time for doing those somewhat time-consuming tasks that I’d otherwise never get around to doing… like upgrading my operating system.

I used the downtime here in Arizona to install Leopard on my Macbook. I knew from reading other people’s reports that it might take some time to get my local web server back up and running. Sure enough, I had to jump through some hoops.

I threw caution to the wind and chose the “upgrade” option. Normally I’d choose “Archive and Install” but it sounds like this caused some problems for Roger .

The upgrade went smoothly. Before too long, I had a brand spanking new OS that was similar to the old OS but ever so slightly uglier and slower.

My first big disappointment was discovering that my copy of Photoshop 7 didn’t work at all. Yes, I know that’s a really old version but I don’t do too much image editing on my laptop so it’s always been good enough. I guess I should have done some reading up on compatibility before installing Leopard. Fortunately, I was able to upgrade from Photoshop 7 to Photoshop CS3—I was worried that I might have had to buy a new copy.

But, as I said, the bulk of my time was spent getting my local LAMP constellation back up and running. I did most of my editing in BBEdit—if you install the BBEdit command line tools, you can use the word bbedit in Terminal to edit documents. If you use Textmate, mate is the command you want.

Leopard ships with Apache 2 which manages virtual hosts differently to the previous version. Instead of keeping all the virtual host information in /etc/httpd/httpd.conf (or /etc/httpd/users/jeremy.conf), the new version of Apache stores it in /private/etc/apache2/extra/httpd-vhosts.conf. I fired up Terminal and typed:

bbedit /private/etc/apache2/extra/httpd-vhosts.conf

That file shows a VirtualHost example. After unlocking the file, I commented out the example and added my own info:

<VirtualHost *:80>
   ServerName adactio.dev
   DocumentRoot "/Users/jeremy/Sites/adactio/public_html"
</VirtualHost>

The default permissions are somewhat draconian so to avoid getting 403:Forbidden messages when trying to look at any local sites, I also added these lines to the httpd-vhosts.conf file:

<Directory /Users/*/Sites/>
    Options Indexes Includes FollowSymLinks SymLinksifOwnerMatch ExecCGI MultiViews
    AllowOverride All
    Order allow,deny
    Allow from all
</Directory>

I then saved the file, which required an admin password.

The good news is that Leopard doesn’t mess with the hosts file (located at /private/etc/hosts). That’s where I had listed the same host names I had chosen in the previous file:

127.0.0.1 localhost
127.0.0.1 adactio.dev

But for any of that to get applied, I needed to edit the httpd.conf file:

bbedit /private/etc/apache2/httpd.conf

I uncommented this line:

# Include /private/etc/apache2/extra/httpd-vhosts.conf

While I was in there, I also removed the octothorp from the start of this line:

# LoadModule php5_module libexec/apache2/libphp5.so

That gets PHP up and running. Leopard ships with PHP5 which is A Good Thing.

Going into Systems Preferences, then Sharing and then ticking the Web Sharing checkbox, I started up my web server and was able to successfully navigate to http://adactio.dev/. There I was greeted with an error message informing me that my local site wasn’t able to connect to MySQL.

Do not fear: MySQL is still there. But I needed to do two things:

1. Tell PHP where to look for the connection socket and
2. Get MySQL to start automatically on login.

For the first step, I needed a php.ini file to edit. I created this by copying the supplied php.ini.default file:

cd /private/etc
cp php.ini.default php.ini
bbedit php.ini

I found this line:

mysql.default_socket =

…and changed it to:

mysql.default_socket = /private/tmp/mysql.sock

I had previously installed MySQL by following these instructions but now the handy little preference pane for starting and stopping MySQL was no longer working. It was going to be a real PITA if I had to manually start up MySQL every time I restarted my computer so I looked for a way of getting it to start up automatically.

I found what I wanted on the TomatoCheese Blog. Here’s the important bit:

Remove the MySQL startup item (we’ll use the preferred launchd instead):

 sudo rm -R /Library/StartupItems/MYSQLCOM

Also, right-click and remove the MySQL preference pane in System Preferences because we’ll be using the preferred launchd instead.

Copy this MySQL launchd configuration file to /Library/LaunchDaemons, and change its owner to root:

sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.plist

That did the trick for me. When I restarted my machine, MySQL started up automatically.

So after some command line cabalism and Google sleuthing, I had my local webdev environment back up and running on Leopard.