Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySqlNonTransientConnectionException

 
Arpan Patil
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My Application runs well when I start it for the first time around but when I try to revisit any page it shows a MySqlNonTransientConnectionException: No operation allowed after connection closed exception. This exception blocks my revisit to any page that I want to visit. In such a case I need to restart my server once again and then restart the app. The exception is as follws


com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

I've closed my connections in finally block. When I don't close connections I don't see this exception either

Pleas Help me to fix this issue
 
Arpan Patil
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No one to fix this issue???
Or is it too late at night :-)
 
K. Gil
Ranch Hand
Posts: 75
Java Linux Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.


usually it means that your connections timeout on mysql server side already, so mysql closed them, but your jdbc connection pool still "thinks" connection is valid, and trying to re-use it.

connection properties can be appended to the Jdbc Url
connectTimeout (Timeout for socket connect (in milliseconds), with 0 being no timeout. Defaults to 0.)
socketTimeout (Timeout on network socket operations (0, the default means no timeout).)
autoReconnect (Should the driver try to re-establish stale and/or dead connections.)

you can also use Connection Pooling to validate your connection with validationQuery

<Resource name="jdbc/blabla"

driverClassName="com.mysql.jdbc.Driver"
maxActive="XX" maxIdle="XX" maxWait="XXX"
url="jdbc:mysql://localhost:3306/database?autoReconnect=true"
removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
testOnBorrow="true" validationQuery="SELECT 1" />

Also, make sure your mysql server settings for connection timeout are at reasonable level.
 
Arpan Patil
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'll try and respond soon
Hope this helps
 
Arpan Patil
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How am I supposed to append these connection properties to JDBC URL through programming?

I mean i am not getting how to this stuff by coding???
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18272
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am strongly suspicious that you are obtaining your JDBC Connections by brute force.

J2EE is an expensive technology. Because it's expensive to develop for, it is best used for applications that need the extra power, robustness, and flexibility that don't come with the "quick-and-dirty" webapp platforms. Which means, in short, that you shouldn't use a cheap data access method for an expensive environment any more than you should put bicycle tires on a Formula 1 race car.

J2EE provides a facility called Connection Pooling, where instead of having the webapp directly access the database driver to get connections, a subsystem of the webapp server handles that job, and keeps idle connections around because offering up an existing Connection from a pool is more efficient than creating a Connection directly from the driver every time.

The source of your error message appears to be that your webapp is trying to use a connection after it has been closed. This could be because you tried to obtain a Connection and re-use it forever, but after lying around unused too long the database server closed it. Or it could be because application code closed it. In either case, the reason I'm willing to wager that you are not using a pooled Connection is that when you close a pooled Connection, you are actually commanding a fa├žade object to close and the actual Connection doesn't close, just get returned to the Connection Pool. And, since you are getting a driver error message, it's definite that the actual Connection is being closed.

A good book on J2EE should contain details on how to work with pooled connections. The Tomcat documentation tells how to set up a Connection Pool, including options that allow for recovery from timeouts on Connections in the pool.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic