Wednesday, May 17, 2017

MySQLTimeoutException: Statement cancelled due to timeout or client request

Dear Reader,
Recently I was facing this error in Production when my application was connecting to DB.

Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

I had set Statment.setQueryTimeout(15);  //15 Seconds.
Hence after 15 Seconds, the Application was cancelling the request if the record was not getting inserted in DB.

I tried lot to re-produce this issue as DBA was adamant to accept this as a problem. Finally after lot of R&D I did below.

Complete Error Message:    
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1757)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at com.enstage.DEEPAK_KUMAR_MODI_JAVA_API_EguardSummary.saveSummary(EguardSummary.java:412)
    ... 5 more

1st Reproduction of Issue:
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("URL");
            st = con.createStatement();            
            
            //Either Throw the exception Manually
            throw new java.lang.Exception(new com.mysql.jdbc.exceptions.MySQLTimeoutException());
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                con.close();
                st.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally{
            System.out.println("Finally");
        }

//Output
java.lang.Exception: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at MySqlTester.main(MySqlTester.java:32)
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    ... 1 more
Finally



2nd Reproduction of Issue:
Use "SELECT SLEEP(10)"
See below entire program:

//MySqlTester.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*@author Deepak Kumar Modi
*/
public class MySqlTester {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        //SELECT * FROM ACTOR
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://X.X.1.54:3306/deepak_modi?user=root&password=deepakmodi");
            st = con.createStatement();
            System.out.println("Start Date: "+new java.util.Date());
            st.setQueryTimeout(2);    //Max wait is set for 2 Seconds            
            rs = st.executeQuery("SELECT SLEEP(10)");  //DB will wait for 10 Seconds
            int count=0;
            while(rs.next()) {                
                System.out.print("ID: " + rs.getString("actor_id"));
                System.out.print(", FN: " + rs.getString("first_name"));
                System.out.print(", LN: " + rs.getString("last_name"));
                System.out.println();
                count++;
                if(count==5)
                    break;                 
            }
            //Either Throw the exception Manually
            //throw new java.lang.Exception(new com.mysql.jdbc.exceptions.MySQLTimeoutException());
        }
        catch (Exception e) {
            System.out.println("End Date: "+new java.util.Date());
            e.printStackTrace();
            try {
                con.close();
                st.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally{
            System.out.println("Finally");
        }
    }
}

//Output (See the exception is thrown just after 2 seconds. Application cancelled the request.)
Start Date: Wed May 17 14:34:11 IST 2017
End Date:   Wed May 17 14:34:13 IST 2017 
com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
Finally
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1622)
    at MySqlTester.main(MySqlTester.java:19)

    
Fix of this issue: Get HOLD of DBA. or Modify Mysql Configuration.    

No comments:

Post a Comment