Monday, 15 August 2011

Glassfish problem: java.sql.SQLException: Query execution was interrupted

There was a report in one of our application which was not showing any result.
The server log was showing the following exception:
java.sql.SQLException: Query execution was interrupted Query: “The query which was interrupted”
I checked the slow query log and I found query for this report was in slow query log too. Our slow query log was set to record queries taking 20 seconds or more.
So I was sure that this problem was related to timeout in the connection pool setting.

When I saw the connection pool settings

I found that Statement Timeout was set to 20 seconds. That means that a connection will terminate the query which takes 20 seconds or more.
As the query of the report was taking more than 20 seconds, so this query was terminated by the connection and that was the reason of report not getting displayed.

So I changed the Statement Timeout parameter was set to -1, which means that the connections of this connection pool will keep waiting for a query and it will not timeout. 

After that I could see the report in the application.

জয় আই অসম,
প্রণব শর্মা