I’ve discovered what must be a bug in MySQL. A certain kind of query which should be relatively innocuous can cause the database to spin off into oblivion, ignoring query limits and client disconnections and generally sucking up all CPU time on the system until it’s killed. I happened to notice this while trying to hack pMachine to allow multiple categories for a single post. Here’s how I know it’s a bug.

  • The query was not malformed. It’s well known that joining two tables in certain ways without a relational constraint can cause the result to contain as many rows as the product of the two tables’ row counts, which could add up to a lot of rows, but this query did in fact have a relational constraint and variants that differ in no essential details do return the expected (small) number of rows.
  • Even if there had been no relational constraint, the tables in question only had about 800 rows apiece. That would have resulted in “only” about 640,000 result rows, which should tie up the server for no more than a minute or two.
  • MySQL implements query limits to address exactly the “dot product” problem mentioned above. The limits are deliberately set, both by default and by any sane administrator, to values that would also limit the query to a couple of minutes, but they apparently didn’t kick in.
  • Both PHP and Apache also have request timeouts. If those timeouts fire, the database connection the request is using should be dropped, which in turn should cause MySQL to abort any queries issued on that connection within a couple of minutes. Again, this didn’t seem to happen.

What we have, then, is a query that gets MySQL into a state where it hogs the CPU, ignoring both query limits and loss of client connections. I’ve been able to reproduce this scenario several times on my machine at home, but whether it can also happen at a commercial web-hosting service is still a matter for speculation. I will neither confirm nor deny rumors that the bug in question might have something to do with this site being unavailable for much of the day.