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]
