Re: com.mysql.jdbc.exceptions.MySQLQueryInterruptedException: Query execution was interrupted
Hi Steve,
That error message is commonly associated with a query being explicitly killed by another thread. Here's an example from the mysql command line client, when KILL QUERY is issued in another connection, targeting this one:
mysql> SELECT SLEEP(100), a FROM test.t;
ERROR 1317 (70100): Query execution was interrupted
So, the question is: What/who is killing your queries?
There's a few ways to get this information. First, check global status counter for Com_kill:
mysql> SHOW GLOBAL STATUS LIKE '%kill%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill | 3 |
+---------------+-------+
1 row in set (0.00 sec)
Assuming you see non-zero values there, you know that somebody has issued KILL commands, but not much more than that. You can enable the general query log (or MySQL Enterprise Audit Log plugin) to catch who's issuing it. Or, if you are using MySQL 5.6, you can leverage PERFORMANCE_SCHEMA:
http://mysqlblog.fivefarmers.com/2013/07/30/practical-p_s-finding-the-killer/
A common cause for JDBC users is that somebody, somewhere, has called Statement.setQueryTimeout():
http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29
This causes Connector/Java to spawn up a new connection when the threshold is reached, issue KILL QUERY, and disconnect. This should be pretty easy to identify in general query or audit log output - the boilerplate connection statements executed by Connector/Java are the only other things besides the KILL QUERY done by that connection.
Hope that helps,
--
Todd Farmer
MySQL @ Oracle
http://www.oracle.com/mysql/