Matt RaibleMatt Raible is a Web Developer and Java Champion. Connect with him on LinkedIn.

The Angular Mini-Book The Angular Mini-Book is a guide to getting started with Angular. You'll learn how to develop a bare-bones application, test it, and deploy it. Then you'll move on to adding Bootstrap, Angular Material, continuous integration, and authentication.

Spring Boot is a popular framework for building REST APIs. You'll learn how to integrate Angular with Spring Boot and use security best practices like HTTPS and a content security policy.

For book updates, follow @angular_book on Twitter.

The JHipster Mini-Book The JHipster Mini-Book is a guide to getting started with hip technologies today: Angular, Bootstrap, and Spring Boot. All of these frameworks are wrapped up in an easy-to-use project called JHipster.

This book shows you how to build an app with JHipster, and guides you through the plethora of tools, techniques and options you can use. Furthermore, it explains the UI and API building blocks so you understand the underpinnings of your great application.

For book updates, follow @jhipster-book on Twitter.

10+ YEARS


Over 10 years ago, I wrote my first blog post. Since then, I've authored books, had kids, traveled the world, found Trish and blogged about it all.

RECENT ENTRIES


Tag Cloud


abbie appfuse birthday denver family grails gwt jack java javascript jhipster jsf linkedin maven roller skiing softwaresummit spring springmvc struts2 travel trish vacation webframeworks wicket
« RE: Mozilla Firebird... | Main | Tomcat 5.0.9 Alpha... »
Thursday August 21, 2003

MySQL Problem: No operations allowed after connection closed

I asked the following question on the Hibernate Forums, but thought I'd put it here to see if anyone can help.

I'm using the open-session-in-view pattern and getting the following error at random: 

2003-08-21 07:00:08,873 WARN [Thread-27] JDBCExceptionReporter.logExceptions(38)
    | SQL Error: 0, SQLState: 08003
2003-08-21 07:00:08,874 ERROR [Thread-27] JDBCExceptionReporter.logExceptions(46
    ) | No operations allowed after connection closed
2003-08-21 07:00:08,875 ERROR [Thread-27] JDBCException.(37) | Could not execute
    query java.sql.SQLException: No operations allowed after connection closed
at com.mysql.jdbc.Connection.checkClosed(Connection.java:2497)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1287)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1267)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement
    (DelegatingConnection.java:187)
at net.sf.hibernate.impl.SessionFactoryImpl.getPreparedStatement
    (SessionFactoryImpl.java:537)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:56)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:482)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:136)
at net.sf.hibernate.loader.Loader.find(Loader.java:620)

I've used my same ServiceLocator.java class for the past 6 months on a couple of different projects (MySQL and Oracle), with no issues. But now I've developed a new app for a new client, and this error is popping up a LOT. Any ideas? I'm only closing the connection after my doFilter() method in a Filter.

It doesn't happen at all on my Windows XP machine (I've never seen the error). Only on Linux.

uname -a gives me:

Linux kgb08 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686 i386 GNU/Linux

Tomcat 4.1.27, MySQL 4.0.13-Max-log, JDBC Driver mysql-connector-java-3.0.8-stable-bin.jar.

I've also seen it happen on MySQL 3.23.56, but now I can't seem to reproduce it.

Any ideas?

Posted in Java at Aug 21 2003, 07:50:12 AM MDT 11 Comments
Comments:

Don't know much about MySQL but it's possible that the connection gets closed when an exception occurs. You're not supposed to continue to use the same Hibernate session in that case anyway, so it might be something to check regardless.

Posted by Chris Nokleberg on August 21, 2003 at 04:30 PM MDT #

I think this is the exact same problem I had when I deployed deepBlack to my production site, all of a sudden I'd get those errors, and after weeks of fiddling around and troubleshooting, (which like you, this problem never occured on my OS X machine), I finally fixed it by reverting back to the mysql 2.0 JDBC driver. It seems to be a problem that occurs (for both of us at least) under Linux, with the mySQL 3.x JDBC driver. It's a problem with the mysql 3 driver apparently, and after contacting the mysql bugs list, I got in touch with a jdbc developer who suggested additional configuration changes in the web.xml setup (send me a e-mail me if you want that e-mail), and when I replied back asking why this works just fine with the 2.x driver but not the 3.x, I got nothing but silence. Oh well. I've been running the 2.x jdbc driver on the production server without any problems now. Let me know if that solves your situation.

Posted by Tim on August 21, 2003 at 08:08 PM MDT #

For some reason your connection is being closed out from underneath you. At this point you are not allowed to issue any further operations on it (as required by the JDBC spec). MM.MySQL-2.0 played a little bit more 'fast and loose' with the JDBC spec, so you wouldn't get an exception until you tried to execute the prepared statement. Connector/J 3.0.x and beyond can 'detect' when a connection goes stale, and set their state accordingly. What is 99% certain is happening is that either MySQL is terminating your idle connection (after 8 hours of inactivity), you have a flaky network (or a firewall in-between you and your MySQL server is terminating the connection), or some code somewhere is calling .close() on the connection, but still letting you try and use it or any statements that were created from it, which can often happen if you don't carefully keep track of finally{} blocks, or don't have a 'standard' pattern for what code is responsible for de-allocating resources such as JDBC connections and statements. Tim, I don't recall ever seeing an e-mail about this from you, but if you could send a copy to [email protected], I'll try and follow up with you on your particular problem.

Posted by Mark Matthews on August 21, 2003 at 08:20 PM MDT #

Did you set autoreconnect to true in your JDBC connection
Look at the example for MySQL in this doc
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html
Specifically the connection url.
I banged my head on this one for quite a while

Posted by Steven Citron-Pousty on August 22, 2003 at 12:06 AM MDT #

I've had autoReconnect=true on in my jdbc URL since last year - and nope, this isn't helping. It's actually happened in under an 8 hour period. I only close connections in one place - after doFilter(), in a finally block after every response. It's worked great on many projects - but I used MySQL 3.23.51 on those.

Posted by Matt Raible on August 22, 2003 at 03:06 AM MDT #

Mark,

Thanks for your reply on the Hibernate forums. I downloaded the nightly build you recommended - and amazingly enough, haven't had a problem since. I asked my client to notify me if they'd seen any of the "No operations allowed after connection closed" errors, but haven't heard from them at all. I've clicked around their site a few times today, and no errors have popped up. With the 3.0.8 driver, the error would crop up every 4th click. Looks like you fixed something. Thanks - much appreciated! ;-)

Posted by Matt Raible on August 23, 2003 at 04:23 AM MDT #

I've been having this very problem myself lately. I'm going to try that nightly build and see if it helps.

Posted by Rafe on August 23, 2003 at 10:33 PM MDT #

A colleague read somewhere today that one of the things that contributes to this problem is using Hibernate's built in connection pool. It ships with DBCP (from Jakarta Commons) and C3P0, and both of them seem to handle this problem much more gracefully than Hibernate's craptastic built in pool. Have you tried either of them? Without doing much configuration tweaking at all, C3P0 seems to fail once when MySQL kills the dead connections and then reconnects and picks up where it left off. As best as I can tell, DBCP seems to avoid the problem entirely. I'm doing more testing with DBCP right now to make sure that's the case.

Posted by Rafe on August 27, 2003 at 02:58 PM MDT #

>> Hibernate's craptastic built in pool << "craptastic", nice. As I keep saying till I'm blue in the face: DO NOT use Hibernate builtin pool in production! It is not meant for that! I might remove the bloody thing. It just that its useful for me for performance testing....

Posted by Gavin on August 28, 2003 at 10:47 PM MDT #

[Trackback] i just implemented the open session in view pattern, and after i deployed it on my host, i had exact the same problem as Matt No operations allowed after connection closed so, i did the same thing as Matt and

Posted by atog on September 17, 2003 at 09:44 PM MDT #

I seem to have a similar problem (posted here http://lists.mysql.com/java/6442), and 3.0.9 doesn't get around it. I want to make sure it is a ConnectorJ bug, and not something silly that I am doing. Is this a known bug?

Posted by Loren on October 20, 2003 at 05:10 PM MDT #

Post a Comment:
  • HTML Syntax: Allowed