List:General Discussion« Previous MessageNext Message »
From:amiljusevic Date:March 14 2006 6:37pm
Subject:MySQL JDBC Exception: "No operations allowed after connection closed"
View as plain text  
  I use Tomcat 5.0.28 on both Fedora and FreeBSD, MySQL 4.1.14, 
J/Connector JDBC driver for MySQL 3.1.11 and JOTM 2.0.10. From time to 
time I get the MySQL JDBC driver exception "No operations allowed after 
connection closed", after which DB calls from my application keep 
failing. The stack trace of the exception is as follows:

java.sql.SQLException: No operations allowed after connection closed.
        at com.mysql.jdbc.Connection.checkClosed(Connection.java:1831)
        at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:4866)
         at 
org.enhydra.jdbc.core.CoreConnection.setAutoCommit(CoreConnection.java:23
4)
         at 
org.enhydra.jdbc.standard.StandardXAConnectionHandle.setAutoCommit(Standa
rdXAConnectionHandle.java:123)
         at 
org.enhydra.jdbc.standard.StandardXAConnectionHandle.prepareStatement(Sta
ndardXAConnectionHandle.java:220)
         at 
org.enhydra.jdbc.standard.StandardXAConnectionHandle.prepareStatement(Sta
ndardXAConnectionHandle.java:197)
         at 
com.kc.ppt.dao.mysql.UserDeviceDAOImpl.updatePhoneNumber(UserDeviceDAOImp
l.java:499)
         at 
com.kc.ppt.bo.UserDeviceBO.updateUserDeviceNumber(UserDeviceBO.java:187)
        at com.kc.ppt.bo.UserBO.addUserProfile(UserBO.java:188)
        at com.kc.ppt.bapi.BAPIImpl.addUserProfile(BAPIImpl.java:97)
         at 
com.kc.ppt.servlet.CreateUserProfileServlet.processRequest(CreateUserProf
ileServlet.java:262)
         at 
com.kc.ppt.servlet.CreateUserProfileServlet.doPost(CreateUserProfileServl
et.java:123)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
         at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicat
ionFilterChain.java:252)
         at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilte
rChain.java:173)
         at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve
.java:214)
         at 
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon
text.java:104)
         at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52
0)
         at 
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardCont
extValve.java:198)
         at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve
.java:152)
         at 
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon
text.java:104)
         at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52
0)
         at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:
137)
         at 
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon
text.java:104)
         at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:
118)
         at 
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon
text.java:102)
         at 
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:535)

         at 
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon
text.java:102)
         at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52
0)
         at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.j
ava:109)
         at 
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCon
text.java:104)
         at 
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:52
0)
         at 
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)

When defining datasources in the application's context.xml I tried both 
the new recommended configuration for MySQL J/Connector JDBC driver 
(shown for Resource name="jdbc/primaryDS") and the "autoreconnect=true" 
approach (shown for Resource name="jdbc/primaryDS"). Please note that I 
did not mix those two approaches, I just used different datasources to 
indicate configurations that I used. Does anybody know if the new MySQL 
J/Connector configuration works with Tomcat? Here's my context.xml file:

<Context path="/myapp" docBase="myapp"
            debug="5" reloadable="true" crossContext="true">

        <Logger className="org.apache.catalina.logger.FileLogger"
               prefix="localhost_myapp_log." suffix=".txt"
               timestamp="true"/>

        <!-- Primary AV data source -->
        <Resource name="jdbc/primaryDS"
               auth="Container"
               type="javax.sql.DataSource"/>

        <ResourceParams name="jdbc/primaryDS">
                <parameter>
                        <name>factory</name>
                         
<value>org.objectweb.jndi.DataSourceFactory</value>
                </parameter>

                 <!-- Maximum number of dB connections in pool. 
Configure mysqld
                          max_connections large enough to handle all DB 
connections.
                         Set to 0 for no limit.
                -->

                <parameter>
                        <name>maxActive</name>
                        <value>100</value>
                </parameter>

                 <!-- Maximum number of idle dB connections to retain in 
pool.
                         Set to 0 for no limit.
                -->
                <parameter>
                        <name>maxIdle</name>
                        <value>30</value>
                </parameter>

                 <!-- Don't use autoReconnect=true, it's going away 
eventually
                  and it's a crutch for older connection pools that 
couldn't
                  test connections. You need to decide whether your 
application is
                  supposed to deal with SQLExceptions (hint, it should), 
and
                 how much of a performance penalty you're willing to pay
                 to ensure 'freshness' of the connection -->

                <parameter>
                      <name>validationQuery</name>
                      <value>SELECT 1</value>
                 </parameter>

                <!-- The most conservative approach is to test 
connections
                     before they're given to your application. For most 
applications
                     this is okay, the query used above is very small 
and takes
                     no real server resources to process, other than the 
time used
                    to traverse the network.

                     If you have a high-load application you'll need to 
rely on
                    something else. -->

                    <parameter>
                      <name>testOnBorrow</name>
                      <value>true</value>
                    </parameter>

                   <!-- Otherwise, or in addition to testOnBorrow, you 
can test
                    while connections are sitting idle -->

                <parameter>
                      <name>testWhileIdle</name>
                      <value>true</value>
                </parameter>

                <!-- You have to set this value, otherwise even though
                     you've asked connections to be tested while idle,
                     the idle evicter thread will never run -->

                <parameter>
                      <name>timeBetweenEvictionRunsMillis</name>
                      <value>10000</value>
                </parameter>

                <!-- Don't allow connections to hang out idle too long,
                      never longer than what wait_timeout is set to on 
the
                     server...A few minutes or even fraction of a minute
                      is sometimes okay here, it depends on your 
application
                     and how much spikey load it will see -->
                <parameter>
                  <name>minEvictableIdleTimeMillis</name>
                  <value>60000</value>
                </parameter>

                 <!-- MySQL dB username and password for dB connections  
-->
                <parameter>
                        <name>username</name>
                        <value>myuser</value>
                </parameter>

                <parameter>
                        <name>password</name>
                        <value>mypasswd</value>
                </parameter>

                <!-- Class name for mm.mysql JDBC driver -->

                <parameter>
                        <name>driverClassName</name>
                        <value>com.mysql.jdbc.Driver</value>
                </parameter>

                <parameter>
                        <name>url</name>
                         
<value>jdbc:mysql://10.10.10.10:3306/mydb</value>
                </parameter>
        </ResourceParams>

       <!-- Secondary AV data source -->
        <Resource name="jdbc/secondaryDS"
               auth="Container"
               type="javax.sql.DataSource"/>

        <ResourceParams name="jdbc/secondaryDS">
                <parameter>
                        <name>factory</name>
                         
<value>org.objectweb.jndi.DataSourceFactory</value>
                </parameter>

                 <!-- Maximum number of dB connections in pool. 
Configure mysqld
                          max_connections large enough to handle all DB 
connections.
                         Set to 0 for no limit.
                -->

                <parameter>
                        <name>maxActive</name>
                        <value>10</value>
                </parameter>

                 <!-- Maximum number of idle dB connections to retain in 
pool.
                         Set to 0 for no limit.
                -->
                <parameter>
                        <name>maxIdle</name>
                        <value>10</value>
                </parameter>

                 <!-- Maximum time to wait for a dB connection to become 
available
                          in ms, in this example 10 seconds. An 
Exception is thrown if
                          this timeout is exceeded.  Set to -1 to wait 
indefinitely.
                -->
               <parameter>
                        <name>maxWait</name>
                        <value>10000</value>
                </parameter>

                 <!-- MySQL dB username and password for dB connections  
-->
                <parameter>
                        <name>username</name>
                        <value>myuser</value>
                </parameter>

                <parameter>
                        <name>password</name>
                        <value>mypasswd</value>
                </parameter>

                <!-- Class name for mm.mysql JDBC driver -->

                <parameter>
                        <name>driverClassName</name>
                        <value>com.mysql.jdbc.Driver</value>
                </parameter>

                <!-- The JDBC connection url for connecting to MySQL dB.
                          The autoReconnect=true argument to the url 
makes sure that the
                          mm.mysql JDBC Driver will automatically 
reconnect if mysqld closed the
                          connection.  mysqld by default closes idle 
connections after 8 hours.
                -->

                <parameter>
                        <name>url</name>
                         
<value>jdbc:mysql://10.10.10.11:3306/mydb?autoReconnect=true</value>
                </parameter>
        </ResourceParams>

        <!-- Description of the resource user transaction -->
         <Resource name="jdbc/tx" auth="Container" 
type="javax.transaction.UserTransaction" />

        <ResourceParams name="jdbc/tx">
                <parameter>
                        <name>factory</name>
                         
<value>org.objectweb.jotm.UserTransactionFactory</value>
                </parameter>

                <parameter>
                        <name>jotm.timeout</name>
                        <value>60</value>
                </parameter>
        </ResourceParams>
</Context>

The method that starts the transaction looks as follows:

public User addUserProfile(User newUser, User existingUser,
            UserDevice userDevice, UserRegistrationType regType)
            throws BOException {
        boolean success = false;

        try {
            userTx.begin();
             userBO.addUserProfile(newUser, existingUser, userDevice, 
regType);
            success = true;
        } catch (NotSupportedException nse) {
            logger.warn("Tx - operation not supported", nse);
        } catch (SystemException se) {
            logger.warn("Tx - system exception", se);
        } finally {
            try {
                if (success) {
                    /*
                     * Transaction was successful, commit it.
                     */
                    userTx.commit();
                } else {
                    /*
                     * Transaction failed, roll it back.
                     */
                    userTx.rollback();
                }
            } catch (Exception e) {
                // Report problem to the client
                success = false;
            }
        }

        return newUser;
    }

The method that invokes the DAO layer:

public void updateUserDeviceNumber(UserDevice userDevice, PhoneNumber 
number)
            throws BOException {
        try {
            getUserDeviceDAO().updatePhoneNumber(userDevice, number);
        } catch (DAOException daoe) {
            throw new BOException(daoe);
        }
    }

The DAO method that throws the SQLException:

     public void updatePhoneNumber(UserDevice userDevice, PhoneNumber 
number)
            throws DAOException {

        Connection conn = null;
        PreparedStatement prepStmt = null;

        try {
            conn = MySQLDataStore.getInstance().getConnection();
            prepStmt = conn.prepareStatement(UPDATE_PHONE_NUMBER_STMT);

            prepStmt.setLong(1, number.getCountryCode());
            prepStmt.setLong(2, number.getAreaCode());
            prepStmt.setLong(3, number.getLocalNumber());
            prepStmt.setLong(4, userDevice.getId());

            prepStmt.execute();

        } catch (SQLException sqle) {
             logger.warn("Failed to update user phone number for device: 
"
                    + userDevice.getId(), sqle);
            throw new DAOException(sqle);
        } finally {
            MySQLDataStore.getInstance().close(prepStmt, conn);
            prepStmt = null;
            conn = null;
        }
    }

Connections are retrieved in the following way:

public Connection getConnection() throws SQLException {
    if (primaryDS == null) {
        primaryDS = (DataSource) ctx.lookup(DataSourceNames.PRIMARY_DS);
    }
    return primaryDS.getConnection();
}

And the statement and connection are closed as follows:

public void close(PreparedStatement ps, Connection conn) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException sqle) {
                avLogger.warn("Failed to close statement: " + ps, sqle);
            }
        }

            if (conn != null) {
            try {
                conn.close();
            } catch (Exception ex) {
                logger.debug(
                        "Exception when closing connection: "
                                + conn, ex);
            }
        }
    }


___________________________________________________
Try the New Netscape Mail Today!
Virtually Spam-Free | More Storage | Import Your Contact List
http://mail.netscape.com

Thread
MySQL JDBC Exception: "No operations allowed after connection closed"amiljusevic14 Mar
  • Re: MySQL JDBC Exception: "No operations allowed after connection closed"amiljusevic16 Mar