com.mysql.jdbc.exceptions.MySQLQueryInterruptedException: Query execution was interrupted
Posted by: Steve Ramage
Date: November 05, 2013 02:25PM

I have been having problems recently with an application / library that is otherwise running fine for the past 10 months, recently getting the above exceptions regularly. It seems to happen randomly with different queries in my application, sometime after many many executions of the same query. I am using MySQL Connector Java 5.1.26 with the c3p0 acting as a connection pool.

One stack trace is:

Caused by: com.mysql.jdbc.exceptions.MySQLQueryInterruptedException: Query execution was interrupted
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2460) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2377) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2361) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147) ~[c3p0-0.9.2-pre8.jar:0.9.2-pre8]
at ca.ubc.cs.beta.mysqldbtae.persistence.worker.MySQLPersistenceWorker.resetUnfinishedRuns(MySQLPersistenceWorker.java:466) ~[mysqldbtae.jar:na]

The query that it is executing is the following:

"UPDATE dsmac_test_runConfigs SET status="NEW", priority="LOW" WHERE status="ASSIGNED" AND workerUUID="some-string";

Here is an EXPLAIN:

EXPLAIN SELECT status, priority, workerUUID FROM dsmac_test_runConfigs WHERE status="ASSIGNED" AND workerUUID="89fedc21-3a38-4d39-b010-e6d4e7d478bf" \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dsmac_test_runConfigs
type: ref
possible_keys: status2,status,statusCutoff,statusEndtime
key: status2
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)





Another example:

Caused by: com.mysql.jdbc.exceptions.MySQLQueryInterruptedException: Query execution was interrupted
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2324) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116) ~[c3p0-0.9.2-pre8.jar:0.9.2-pre8]
at ca.ubc.cs.beta.mysqldbtae.persistence.worker.MySQLPersistenceWorker.getRuns(MySQLPersistenceWorker.java:238) ~[mysqldbtae.jar:na]
... 2 common frames omitted

Query:

UPDATE dsmac_test_runConfigs A JOIN (
SELECT runConfigID, priority FROM (
(SELECT runConfigID,0 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="LOW" ORDER BY runConfigID LIMIT 3)
UNION ALL
(SELECT runConfigID,1 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="NORMAL" ORDER BY runConfigID LIMIT 3)
UNION ALL
(SELECT runConfigID,2 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="HIGH" ORDER BY runConfigID LIMIT 3)
UNION ALL
(SELECT runConfigID,2 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="UBER" ORDER BY runConfigID LIMIT 3)
) innerTable ORDER BY priority DESC LIMIT 3)
B ON B.runConfigID = A.runConfigID SET status="ASSIGNED", workerUUID="89fedc21-3a38-4d39-b010-e6d4e7d478bf"

Explain of inner part:

mysql> EXPLAIN SELECT runConfigID, priority FROM (
(SELECT runConfigID,0 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="LOW" ORDER BY runConfigID LIMIT 3)
UNION ALL
(SELECT runConfigID,1 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="NORMAL" ORDER BY runConfigID LIMIT 3)
UNION ALL
(SELECT runConfigID,2 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="HIGH" ORDER BY runConfigID LIMIT 3)
UNION ALL
(SELECT runConfigID,2 AS priority FROM dsmac_test_runConfigs WHERE status="NEW" AND priority="UBER" ORDER BY runConfigID LIMIT 3)
) innerTable ORDER BY priority DESC LIMIT 3 \G

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: dsmac_test_runConfigs
type: ref
possible_keys: status2,status,statusCutoff,statusEndtime
key: status2
key_len: 2
ref:
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 3
select_type: UNION
table: dsmac_test_runConfigs
type: ref
possible_keys: status2,status,statusCutoff,statusEndtime
key: status2
key_len: 2
ref:
rows: 1
Extra: Using where; Using index
*************************** 4. row ***************************
id: 4
select_type: UNION
table: dsmac_test_runConfigs
type: ref
possible_keys: status2,status,statusCutoff,statusEndtime
key: status2
key_len: 2
ref:
rows: 1
Extra: Using where; Using index
*************************** 5. row ***************************
id: 5
select_type: UNION
table: dsmac_test_runConfigs
type: ref
possible_keys: status2,status,statusCutoff,statusEndtime
key: status2
key_len: 2
ref:
rows: 1
Extra: Using where; Using index
*************************** 6. row ***************************
id: NULL
select_type: UNION RESULT
table: <union2,3,4,5>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
6 rows in set (0.00 sec)


Finally:

Caused by: com.mysql.jdbc.exceptions.MySQLQueryInterruptedException: Query execution was interrupted
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2324) ~[mysql-connector-java-5.1.26-bin.jar:na]
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116) ~[c3p0-0.9.2-pre8.jar:0.9.2-pre8]
at ca.ubc.cs.beta.mysqldbtae.persistence.worker.MySQLPersistenceWorker.getMinCutoff(MySQLPersistenceWorker.java:690) ~[mysqldbtae.jar:na]
... 2 common frames omitted

Query:

SELECT MIN(cutoffTime) FROM dsmac_test_runConfigs WHERE status='NEW'

mysql> EXPLAIN SELECT MIN(cutoffTime) FROM dsmac_test_runConfigs WHERE status='NEW' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)


The architecture of the application is roughly a task processing system, and the database it is operating on is brand new. The table they are operating on is defined as follows:

Create Table: CREATE TABLE `dsmac_test_runConfigs` (
`runConfigID` int(11) NOT NULL AUTO_INCREMENT,
`runConfigUUID` char(48) NOT NULL,
`execConfigID` int(11) NOT NULL,
`problemInstance` varchar(2048) NOT NULL,
`instanceSpecificInformation` varchar(2048) NOT NULL DEFAULT '0',
`seed` bigint(20) NOT NULL,
`cutoffTime` double NOT NULL,
`paramConfiguration` varchar(2048) NOT NULL,
`cutoffLessThanMax` tinyint(1) NOT NULL,
`status` enum('NEW','ASSIGNED','COMPLETE','PAUSED') NOT NULL DEFAULT 'NEW',
`priority` enum('LOW','NORMAL','HIGH','UBER') NOT NULL DEFAULT 'NORMAL',
`workerUUID` char(48) NOT NULL DEFAULT '0',
`killJob` tinyint(1) NOT NULL DEFAULT '0',
`retryAttempts` int(11) NOT NULL DEFAULT '0',
`runPartition` int(11) NOT NULL,
`noop` tinyint(1) NOT NULL DEFAULT '0',
`runResult` enum('TIMEOUT','SAT','UNSAT','CRASHED','ABORT','KILLED') NOT NULL DEFAULT 'ABORT',
`runLength` double NOT NULL DEFAULT '0',
`quality` double NOT NULL DEFAULT '0',
`resultSeed` bigint(20) NOT NULL DEFAULT '1',
`runtime` double NOT NULL DEFAULT '0',
`walltime` double NOT NULL DEFAULT '0',
`additionalRunData` varchar(2048) NOT NULL DEFAULT '',
`worstCaseEndtime` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
`lastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`runConfigID`),
UNIQUE KEY `runConfigUUID` (`runConfigUUID`),
KEY `status2` (`status`,`priority`),
KEY `status` (`status`,`workerUUID`,`priority`,`retryAttempts`,`runConfigID`),
KEY `statusCutoff` (`status`,`cutoffTime`),
KEY `statusEndtime` (`status`,`worstCaseEndtime`)
) ENGINE=InnoDB AUTO_INCREMENT=58447 DEFAULT CHARSET=utf8



There are a few tables in the application, but this table is the one hit hardest. I don't understand why I am seeing these exceptions however: MySQLQueryInterruptedException . If it was a DeadLockException I could understand, but there seems to be very little documentation that I can find about this exception. Looking at the log there is nothing on the server that seems to indicate an error.

Options: ReplyQuote


Subject
Written By
Posted
com.mysql.jdbc.exceptions.MySQLQueryInterruptedException: Query execution was interrupted
November 05, 2013 02:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.