CODEWITHSUNDEEP
javamysqljspjdbc
Vaishnavi
Vaishnavi

Reputation: 91

java.sql.SQLException: No operations allowed after connection closed

I have build an application, maintained with session. It uses JDBC & mysql.

When the application is deployed on server (Apache Tomcat 6). I can log in and log out of the session. Works totally fine.

Now I keep the server running for next 24 hrs. And now on the next day I try to log in into the system after entering the credentials, on clicking the Login button I get the error on web page as below: (what is the cause for this?)

Http status 500

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /login.jsp at line 11

9:     Connection con =ConnectionProvider.getConnection();
10:     System.out.println("con "+con);
11:     con.setAutoCommit(false);
12:     Statement st = con.createStatement();
13:     ResultSet rs;
14:     rs = st.executeQuery("select * from ejduge_login where uname='" + userid + "' and pass='" + pwd +"'");


Stacktrace:
    org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:524)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:417)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause 

javax.servlet.ServletException: java.sql.SQLException: No operations allowed after connection closed.
    org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:850)
    org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
    org.apache.jsp.login_jsp._jspService(login_jsp.java:97)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:393)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause 

java.sql.SQLException: No operations allowed after connection closed.
    com.mysql.jdbc.Connection.checkClosed(Connection.java:2726)
    com.mysql.jdbc.Connection.setAutoCommit(Connection.java:498)
    org.apache.jsp.login_jsp._jspService(login_jsp.java:65)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:393)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

Thanks in advance!

Upvotes: 4

Views: 8391

Answers (3)

Vaishnavi
Vaishnavi

Reputation: 91

Well I found, the default time for closing connection if it has been inactive too long is 28800 seconds(8 hrs) for MYSQL (& not 34,247,052 milliseconds).

show global variables;

In MYSQL, I reset that value to 172800 seconds(48 hrs as per my requirement) using

set global wait_timeout=172800;

and it worked fine.

Thanks a lot for the help!

Upvotes: 5

jerry_li
jerry_li

Reputation: 1

it may just worked fine now, it will error after 172800 seconds,you should add check state value and set the value less then 172800 .

just like c3p0 idleConnectionTestPeriod and maxIdleTime.

Upvotes: -1

Santhosh
Santhosh

Reputation: 8207

mysql will implicitly close the connection if it has been inactive too long time ( 34,247,052 milliseconds by default ).

This would have caused the No operations allowed after connection closed error. so you could increase the inactive state timeout or handle it through efficient connection pooling

Upvotes: 2

Related Questions