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
	ORDER BY hits DESC
	LIMIT 7

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