Can mysqld get stuck in a loop?

I have a batch job that runs every night. It collects certain new data from a few dozen databases into one central database (named "ac") that's then used with SphinxSearch for autocomplete. The batch job and its queries have been working reliably for over a year.

Today I found that the batch job was stuck. mysqld was using 100% CPU on one core and a query that normally take开发者_JS百科s 5 minutes was still running after 13 hours.

dtruss shows that mysqld is continually reading from the data files involved in the query. The relevant indexes are all in mysqld's cache. There's no disk IO since mysqld is only reading the data files and they are buffered in memory by FreeBSD.

EDIT: Also relevant: dtruss seems to show that mysqld hasn't anything to written to ac.ac_schematic in many hours.

USE wxyz;

CREATE OR REPLACE VIEW ac_view AS SELECT 
  j.id AS pid, j.parent AS ppid, 
  table_a.name AS name_a,
  table_d.name AS name_d,
  table_s.name AS name_s
FROM junction j
  INNER JOIN table_a ON table_a.id=j.aid
  INNER JOIN table_d ON table_d.id=j.did
  INNER JOIN table_s ON table_s.id=j.sid
WHERE s.inserted > '2010-12-21 03:00:00';

INSERT IGNORE INTO ac.ac_schematic 
      (pid, ppid, aid, did, sid)
SELECT pid, ppid, aid, did, sid
FROM ac_view v
  INNER JOIN ac.dict_a a ON a.name=v.name_a
  INNER JOIN ac.dict_d d ON d.name=v.name_d
  INNER JOIN ac.dict_s s ON s.name=v.name_s;

The files mysqld is reading are:

  ac/ac_schematic.MYD (more than anything)
  wxyz/junction.MYD
  wxyz/table_a.MYD
  wxyz/table_d.MYD
  wxyz/table_s.MYD

Server version is 5.0.87

This is very curious and I'm baffled. There's no way the query could need to take 12+ hours of CPU time. And I bet I could leave the query running indefinitely with the CPU meter pegged.

Any ideas what I could examine to try to figure out what's going on. I have a pretty good pile Dtrace tools available.

---

Chances are it's stuck on a specific query, or set of queries. (I've seen this happen a few times over the years - often with no reason whatsoever.)

As such, a good first point of call would be to use the "SHOW PROCESSLIST;" SQL query to show the currently executing queries.