Re: MySQL JDBC Exception: "No operations allowed after connection closed"

Thu, 16 Mar 2006 07:57:17 -0800

The fact that I use JOTM with Tomcat forces me to use a different data source factory from the one described in the MySQL document "Using Connector/J with Tomcat", (http://dev.mysql.com/doc/refman/4.1/en/cj-tomcat-config.html) - org.objectweb.jndi.DataSourceFactory vs. org.apache.commons.dbcp.BasicDataSourceFactory. The data source factory required for JOTM is specified in the following document: "How to use JDBC and transactions in Tomcat with JOTM" (http://jotm.objectweb.org/current/jotm/doc/howto-tomcat-jotm.html)
Different data source factories translate into different data sources and connections (XA pool vs. DBCP) so I've been wondering if anybody knows what parameters should be used to configure XA pool running in Tomcat to use the new MySQL driver recommendations (autoReconnect being an obsoleted parameter)? 

-----Original Message-----
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tue, 14 Mar 2006 13:37:55 -0500

Subject: MySQL JDBC Exception: "No operations allowed after connection 
closed"



  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

-- MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

 To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]




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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]















    











					Reply via email to