WARN [TxConnectionManager] Connection error occured
Hello, I have a problem which I hope someone could help point me in the right direction with.
background:
database: MySQL4.1
OS: xp
server: jboss4.0.2
I have a web application where by it uses mysql to input/retrieve content manually using Statments and PrepairedStatements from the Servlet Tier.
when testing the application using one browser everything seems to run smoothly, however when opening another browser and testing both pages at the same time I first get the following windows error box:
"mysql-nt.exe has encounterd a prblem and needs to close." lovely
then when i click send error report or dont send, the server gives me the stack trace:
00:35:08,968 WARN [TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@14da86 b[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1f2412a handles=1 lastUse=1138340078281 permit=true trackByTx=false mcp=org.j boss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@194566d context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@1b64b7 0] com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Connection reset STACKTRACE: java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:168) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1902) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2351) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2988) at com.mysql.jdbc.Connection.execSQL(Connection.java:2917) at com.mysql.jdbc.Statement.executeQuery(Statement.java:824) at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:208) at com.test.controller.DispatchServlet.edit(DispatchServlet.java:647) at com.test.controller.DispatchServlet.processRequest(DispatchServlet.java:73) at com.test.controller.DispatchServlet.doPost(DispatchServlet.java:53) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at javax.servlet.http.HttpServlet.service(HttpServlet.java:810) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39) at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:153) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112) at java.lang.Thread.run(Thread.java:534) ** END NESTED EXCEPTION ** Last packet sent to the server was 30609 ms ago. at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2563) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2988) at com.mysql.jdbc.Connection.execSQL(Connection.java:2917) at com.mysql.jdbc.Statement.executeQuery(Statement.java:824) at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:208) at com.test.controller.DispatchServlet.edit(DispatchServlet.java:647) at com.test.controller.DispatchServlet.processRequest(DispatchServlet.java:73) at com.test.controller.DispatchServlet.doPost(DispatchServlet.java:53) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at javax.servlet.http.HttpServlet.service(HttpServlet.java:810) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) ........................repeat............ ....................
after double checking my code and searching for answers over the web, I have made sure that all statements/connections are closed in the try/catch/finally. so I dont think their is a datasource leak.
mysql-ds.xml is configured here is some of it:
<datasources> <local-tx-datasource> <jndi-name>MySqlDS</jndi-name> <connection-url>jdbc:mysql://localhost:3306/test</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>root</user-name> <password>password</password> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name> <!-- sql to call when connection is created <new-connection-sql>some arbitrary sql</new-connection-sql> --> <!-- sql to call on an existing pooled connection when it is obtained from pool <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql> --> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) --> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources>
login-config.xml is configured as advised:
<application-policy name="dukesbank"> <authentication> <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required"> <!-- <module-option name="dsJndiName">java:/DefaultDS</module-option> --> <module-option name="dsJndiName">java:/MySqlDS</module-option> <module-option name="principalsQuery"> select passwd from Users where username=? </module-option> <module-option name="rolesQuery"> select userRoles,'Roles' from UserRoles where username=? </module-option> </login-module> </authentication> </application-policy>
jboss-web.xml is configured as:
<resource-ref> <res-ref-name>jdbc/MySqlDS</res-ref-name> <jndi-name>java:/MySqlDS</jndi-name> </resource-ref> <resource-ref> <res-ref-name>java:comp/UserTransaction</res-ref-name> <jndi-name>UserTransaction</jndi-name> </resource-ref>
same is reflected in the web.xml
<resource-ref > <res-ref-name>jdbc/MySqlDS</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> <resource-ref > <res-ref-name>java:comp/UserTransaction</res-ref-name> <res-type>javax.transaction.UserTransaction</res-type> <res-auth>Container</res-auth> </resource-ref>
here is a view of the code that is being pointed in the trace above for (DispatchServlet.java:647)
........
Connection conn = null;
Statement statement1 = null, statement2 = null;
ResultSet rs1 = null, rs2 = null;
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySqlDS");
conn = ds.getConnection();
statement1 = conn.createStatement();
statement2 = conn.createStatement();
DispatchServlet.java:647------> rs1 = statement1.executeQuery(
" select distinct POSTAL_CODE, STREET, CITY, PROVINCE " +
" from ban, address, company_info " +
" where ban.ADDRESSID = address.ADDRESSID " +
" and ban.`COMPANYID` = company_info.`COMPANYID` " +
" and company_info.COMPANY_NAME IN ( " +
" select COMPANY_NAME " +
" from ban, company_info " +
" where ban.COMPANYID = company_info.COMPANYID " +
" and ban = '" + BAN + "') " +
" ORDER BY POSTAL_CODE" );
rs2 = statement2.executeQuery(
"select distinct CONTACT_NAME, CONTACT_TYPE, `CONTACT_DEPT`, `CONTACT_EMAIL`, `CONTACT_NUM`, `CONTACT_EXT`" +
" from ban, company_info, address, contact" +
" where ban.`COMPANYID` = `company_info`.`COMPANYID`" +
" and company_info.`PRIMARY_CONTACTID` = contact.`CONTACTID`" +
" and `company_info`.`COMPANY_NAME` IN (" +
" select COMPANY_NAME" +
" from ban, company_info" +
" where ban.COMPANYID = company_info.COMPANYID" +
" and contact_type = 'PRIMARY'" +
" and ban = '" + BAN + "' )" +
" ORDER BY CONTACT_NAME");
Collection addressDAO = new ArrayList();
while(rs1.next()) {
Meid addressInfo = new Meid();
addressInfo.setStreet(rs1.getString("STREET"));
addressInfo.setCity(rs1.getString("CITY"));
addressInfo.setProvince(rs1.getString("PROVINCE"));
addressInfo.setPostal_code(rs1.getString("POSTAL_CODE"));
addressDAO.add(addressInfo);
}
// FILTER # 2
Collection primContactDAO = new ArrayList();
int i = 2;
while(rs2.next()) {
Meid contactInfo = new Meid();
contactInfo.setIndex(i++);
contactInfo.setPrimary_contact_name(rs2.getString("CONTACT_NAME"));
contactInfo.setPrimary_contact_dept(rs2.getString("CONTACT_DEPT"));
contactInfo.setPrimary_contact_email(rs2.getString("CONTACT_EMAIL"));
contactInfo.setPrimary_contact_num(rs2.getString("CONTACT_NUM"));
contactInfo.setPrimary_contact_ext(rs2.getString("CONTACT_EXT"));
primContactDAO.add(contactInfo);
}
session.setAttribute("addressDAO", addressDAO);
session.setAttribute("primContactDAO", primContactDAO);
session.setAttribute("invContactInfo", invContactInfo);
session.setAttribute("COMPANY_NAME", COMPANY_NAME);
session.setAttribute("BAN", BAN);
page = "somepage.jsp";
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs1!=null) rs1.close();
if(rs2!=null) rs2.close();
if(statement1!=null) statement1.close();
if(statement2!=null) statement2.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
page = "somppage2.jsp";
return page;
}
I seem to be missing something. I have read some online searches and I have checked faqs and did some googling I feel that something is missing
in some xml file and I would kindly ask for some assistance
