Connection Pooling Problem

Snipex · 13. Februar 2007

was hat es mit folgendem fehler auf sich ?

diese fehlermeldung tritt in sehr unregelmässigen zeitabständen auf
mal nach(30h) mal nach(2h) mal nach (20h) bis zu diesem zeitpunkt läuft
die verbindung optimal, danach geht nix mehr.

nach dem crash muss ich den gesamten win2003server neu starten denn
auf den mysql habe ich keine verbindung mehr error 2013 (lost connection)

was kann ich tun ? welche ansätze gibt es ?

meine tomcat config :

XML:

      		<Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="20" maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="true"
               username="xxx" password="xxx" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/xxx"/>

meine web.xml config für die datasource :

XML:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	<display-name>
	test</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.htm</welcome-file>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>default.html</welcome-file>
		<welcome-file>default.htm</welcome-file>
		<welcome-file>default.jsp</welcome-file>
	</welcome-file-list>
<description>MySQL App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/test</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Code:

13.02.2007 20:23:57 org.apache.catalina.core.StandardWrapperValve invoke
SCHWERWIEGEND: Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Software caused connection abort: recv failed

STACKTRACE:

java.net.SocketException: Software caused connection abort: recv failed
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.read(Unknown Source)
	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:1910)
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:501)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:971)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2670)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
	at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)
	at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:74)
	at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source)
	at org.apache.jsp.pictureStrip_jsp._jspx_meth_sql_query_0(pictureStrip_jsp.java:148)
	at org.apache.jsp.pictureStrip_jsp._jspService(pictureStrip_jsp.java:92)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	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.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.Http11AprProcessor.process(Http11AprProcessor.java:833)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:639)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1285)
	at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Software caused connection abort: recv failed

STACKTRACE:

java.net.SocketException: Software caused connection abort: recv failed
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.read(Unknown Source)
	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:1910)
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:501)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:971)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2670)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
	at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)
	at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:74)
	at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source)
	at org.apache.jsp.pictureStrip_jsp._jspx_meth_sql_query_0(pictureStrip_jsp.java:148)
	at org.apache.jsp.pictureStrip_jsp._jspService(pictureStrip_jsp.java:92)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	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.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.Http11AprProcessor.process(Http11AprProcessor.java:833)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:639)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1285)
	at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:569)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:971)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2670)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
	at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)
	at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:74)
	at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source)
	at org.apache.jsp.pictureStrip_jsp._jspx_meth_sql_query_0(pictureStrip_jsp.java:148)
	at org.apache.jsp.pictureStrip_jsp._jspService(pictureStrip_jsp.java:92)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	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.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.Http11AprProcessor.process(Http11AprProcessor.java:833)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:639)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1285)
	at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago."
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source)
	at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source)
	at org.apache.jsp.pictureStrip_jsp._jspx_meth_sql_query_0(pictureStrip_jsp.java:148)
	at org.apache.jsp.pictureStrip_jsp._jspService(pictureStrip_jsp.java:92)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	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.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.Http11AprProcessor.process(Http11AprProcessor.java:833)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:639)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1285)
	at java.lang.Thread.run(Unknown Source)

Thomas Darimont · 15. Februar 2007

Hallo,

hmm seltsames Problem... schon mal versucht der MySQL JDBC Connection zusätzliche Konfigurationsparameter mitzugeben?

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
-> autoReconnect -> true
bzw.
-> autoReconnectForPools -> true
...
weiterhin könnte das auch ein Konfigurationsproblem auf der MySQL-Server Seite sein. Ist beim Server vielleicht ein anderes Timeout angegeben als beim Client?

Gruß Tom

Snipex · 16. Februar 2007

Hallo Thomas, danke für deine Antwort.

ja das problem stellt wirklich seltsame anforderungen, vorallem an die Fehler eruirung,

die autoreconnects hatte ich schon mal drinne haben aber nichts gebracht,

an mysql und tomcat kann praktisch nicht mehr liegen, da ich jegliche einstellungen
am mysql was time_outs usw. und caches anbelang angegeben habe auch die aborted
connects usw. da in der netstat -a -n keine :3306 mehr vorhanden sind und der mysqld-nt
im taskmanager noch läuft und auch der tomcat service noch läuft gehe ich mitlwerweile
davon aus, dass irgendetwas z.B. ein anderer dienst den 3306 port blockiert oder so
etwas in der art aber mal schauen.

bin bei folgendem prozess mitlwerweile bei step 2 angelang also habe den mysql
server gezügelt auf einen andern externen server um auszuschliessen, dass es an
mysql selbst liegt.

werde nun folgenden prozess durchmachen, damit ich eingrenzen kann wo das problem liegt,

1. Connector/J auswechseln (Treiberwechsel)

-> wenn fehler immer noch da (Treiber ok)

2. Datenbank auf andere Kiste laden habe 2. Identischen Server

-> wenn fehler immer noch da (OS und Hardware OK)

3. Webapp überprüfen ( Routinen gestalten die die Querys der App absetzen ) Alle abgesetzten Querys überprüfen mit mysqldbin oder so...

-> wenn fehler immer noch da ( Webapp OK )

4. Server switch auf Java Application PE 9.0 mit Glassfish V1

-> wenn fehler immer noch da ( WebApp Server OK )

5. Switch auf db2 express oder evtl. derby

-> wenn fehler immer noch da (DB OK)

p.s. auf der Tomcat Api zu jdbc / jndi ressources wird davon abgeraten autoreconnect=true zu verwenden. wiso auch immer.....

Connection Pooling Problem

Snipex

Grünschnabel

Thomas Darimont

Erfahrenes Mitglied

Snipex

Grünschnabel

Neue Beiträge