Tags: sql

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.

Picture someone tediously going through a spreadsheet that someone else has filled in by hand and finding yet another error.

“I wish to God these calculations had been executed by steam!” they cry.

The year was 1821 and technically the spreadsheet was a book of logarithmic tables. The frustrated cry came from Charles Babbage, who channeled his frustration into a scheme to create the world’s first computer.

His difference engine didn’t work out. Neither did his analytical engine. He’d spend his later years taking his frustrations out on street musicians, which—as a former busker myself—earns him a hairy eyeball from me.

But we’ve all been there, right? Some tedious task that feels soul-destroying in its monotony. Surely this is exactly what machines should be doing?

I have a hunch that this is where machine learning and large language models might turn out to be most useful. Not in creating breathtaking works of creativity, but in menial tasks that nobody enjoys.

Someone was telling me earlier today about how they took a bunch of haphazard notes in a client meeting. When the meeting was done, they needed to organise those notes into a coherent summary. Boring! But ChatGPT handled it just fine.

I don’t think that use-case is going to appear on the cover of Wired magazine anytime soon but it might be a truer glimpse of the future than any of the breathless claims being eagerly bandied about in Silicon Valley.

You know the way we no longer remember phone numbers, because, well, why would we now that we have machines to remember them for us? I’d be quite happy if machines did that for the annoying little repetitive tasks that nobody enjoys.

I’ll give you an example based on my own experience.

Regular expressions are my kryptonite. I’m rubbish at them. Any time I have to figure one out, the knowledge seeps out of my brain before long. I think that’s because I kind of resent having to internalise that knowledge. It doesn’t feel like something a human should have to know. “I wish to God these regular expressions had been calculated by steam!”

Now I can get a chatbot with a large language model to write the regular expression for me. I still need to describe what I want, so I need to write the instructions clearly. But all the gobbledygook that I’m writing for a machine now gets written by a machine. That seems fair.

Mind you, I wouldn’t blindly trust the output. I’d take that regular expression and run it through a chatbot, maybe a different chatbot running on a different large language model. “Explain what this regular expression does,” would be my prompt. If my input into the first chatbot matches the output of the second, I’d have some confidence in using the regular expression.

A friend of mine told me about using a large language model to help write SQL statements. He described his database structure to the chatbot, and then described what he wanted to select.

Again, I wouldn’t use that output without checking it first. But again, I might use another chatbot to do that checking. “Explain what this SQL statement does.”

Playing chatbots off against each other like this is kinda how machine learning works under the hood: generative adverserial networks.

Of course, the task of having to validate the output of a chatbot by checking it with another chatbot could get quite tedious. “I wish to God these large language model outputs had been validated by steam!”

Sounds like a job for machines.

Personal Data Warehouses: Reclaiming Your Data

I like the way that Simon is liberating his data from silos and making it work for him.

‘Real’ Programming Is an Elitist Myth | WIRED

The title says it all, really. This is another great piece of writing from Paul Ford.

I’ve noticed that when software lets nonprogrammers do programmer things, it makes the programmers nervous. Suddenly they stop smiling indulgently and start talking about what “real programming” is. This has been the history of the World Wide Web, for example. Go ahead and tweet “HTML is real programming,” and watch programmers show up in your mentions to go, “As if.” Except when you write a web page in HTML, you are creating a data model that will be interpreted by the browser. This is what programming is.

Pinboard is Eleven (Pinboard Blog)

I probably need to upgrade the Huffduffer server but Maciej nails why that’s an intimidating prospect:

Doing this on a live system is like performing kidney transplants on a playing mariachi band. The best case is that no one notices a change in the music; you chloroform the players one at a time and try to keep a steady hand while the band plays on. The worst case scenario is that the music stops and there is no way to unfix what you broke, just an angry mob. It is very scary.

I Dreamed of a Perfect Database | New Republic

A really nice piece by Paul Ford on the history of databases and the dream of the Semantic Web.

Sometimes I get a little wistful. The vision of a world of connected facts, one big, living library, remains beautiful, and unfulfilled.

One thing though: the scrolling on this page is sooooo janky that I had to switch off JavaScript just to read these words comfortably.

How to support full Unicode in MySQL databases · Mathias Bynens

Some good database character-encoding advice from Mathias.

Amazon Relational Database Service (Amazon RDS)

You can now store (and scale) MySQL databases with Amazon. Handy.

Welcome - Perch - A Really Little Content Management System (CMS)

Drew and Rachel's little CMS looks very nice indeed.

Web Security Horror Stories: The Director's Cut at

The slides from Simon's excellent full-length presentation at the head conference. Every web developer needs to be aware of these issues.

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.

Surfin’ Safari - Blog Archive » WebKit Does HTML5 Client-side Database Storage

I just learned from Kelly that Webkit is supporting local storage and database queries, as proposed in HTML5. Kinda like Google Gears. Potentially excited for the iPhone/iPod Touch.

xkcd - A webcomic of romance, sarcasm, math, and language - By Randall Munroe

Yes, you have to be a bit of a database geek to find this funny but if you are, this is very funny indeed.

Mysql database migration and special characters | Orthogonal Thought

This article is a life-saver for me. I'm constantly having trouble with special characters when I'm backing up databases for local copies of my sites.

Samuel's Blog

The working example from Richard's chapter in Blog Design Solutions. It's a home-rolled PHP/MySQL blog for Samuel Pepys featuring beautiful typography... natch.