Catching Bugs Before They Catch You

top - crushed

I got itchy about magic quotes the other day because it’s the cause (through a fairly long cascade of errors) of some performance problems and runaways I’ve been seeing lately (pictured above).

But I deserve most of the blame for allowing a query like this to run at all:

SELECT type, data, count(*) AS hits
	FROM wpopac_WPopac_bibs_atsk
	WHERE data LIKE '%'
	AND type IN ('subjkey','author', 'title')
	GROUP BY data

As executed, it’s trying to select all 1.2 million records from the table, group them (a hugely memory intensive activity with text), and then report only the top seven.

It’s one of a few queries that returns the alternate searches in WPopac. I’m planning to rethink how that works, but I also should have built in a fail-safe to prevent it from trying to execute if the search variable is empty (the query above is written WHERE data LIKE '$searchterms%' or something like that). I’m amused that MySQL tries so hard to fulfill the search — so hard that it’ll consume all available RAM and swap to do so.

The sysadmin and DBA are now much happier — along with any user frustrated by poor performance — with this patched.

bug, bugs, mysql, patch, performance, queries, query, query failures, wpopac