At InVision, we recently upgraded our MySQL database servers to use MySQL 5.7.x. And, while I’m excited for access to new features like the native JSON column type and the sys performance schema, the upgrade did have some bumps. In particular, we had a subset of SQL queries that started running with terrible performance (typically those that have very large IN(id-list) clauses). I had to refactor a number of queries; but, while that code was being changed, I had to put together an emergency utility that would allow me to start killing SQL queries in production. I thought this was a fun little exercise that would be worth sharing in Lucee CFML 5.3.7.47.

ASIDE: Before I started killing queries manually, I did try adding a timeout to my  tags. Unfortunately, this did not seem to have any impact. I am not sure why the timeout didn’t kill long-running queries in Lucee CFML - I know that this mostly worked when the code was running on Adobe ColdFusion.

This emergency utility has two main aspects: the ProcessListGateway.cfc, which gives me access to the MySQL processlist that returns all the database thread-states; and, a View that lists the currently-running queries and allows me to kill them individually.

Let’s look at my ColdFusion component gateway first. It has two main methods: one that returns the processlist and one that executes a KILL QUERY SQL statement against a given thread ID. Each of these queries takes a datasource against which to run the query since we have multiple replicas in production.

#coldfusion #sql #mysql