| Bug #1202 | java.io.IOException, then java.net.SocketException until restart of tomcat-stru | ||
|---|---|---|---|
| Submitted: | 5 Sep 2003 3:15 | Modified: | 5 Sep 2003 8:12 |
| Reporter: | Ralf Hauser | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 3.1.0-alpha and earlier | OS: | Linux (RedHat 9) |
| Assigned to: | CPU Architecture: | Any | |
[5 Sep 2003 6:14]
Mark Matthews
This is covered in the documentation, in the troubleshooting section of the README or online at http://www.mysql.com/documentation/connector-j/index.html#id2802490 in the section entitled "I have a servlet/application that works fine for a day, and then stops working overnight". Unfortunately, the underlying socket exception _doesn't_ have a useful message. You should check your configuration pool documentation and configure the pool to only allow connections to be idle for less than 8 hours, and also to check connections for validity either before handing them out, or upon return to the pool. Tomcat does have configuration parameters for this.
[5 Sep 2003 7:16]
Ralf Hauser
Thx for the hint - I guess your URL was supposed to bring me to 4.4?
[If so, would it be hard to 1) add another html target anchor right there, 2) enrich/amend the exception you are throwing (I guess you are catching it and passing it along unchanged?) with that URL?]
Furthermore, the documentation you suggested wasn't totally clear to me as a Newbie:
3) as per the documentation, I extended my web.xml as follows:
<< <servlet>
<servlet-name>database</servlet-name>
<servlet-class>com.mydomain.myapp.db.Database</servlet-class>
<init-param>
<param-name>config</param-name>
<param-value>/WEB-INF/database-config.xml</param-value>
</init-param>
<init-param>
<param-name>writeURL</param-name>
<param-value>jdbc:mysql://127.0.0.1:3306/myDB?autoReconnect=true</param-value>
</init-param> <!-- switch to port 3307 in a mysql cluster -->
<init-param>
<param-name>readURL</param-name>
<param-value>jdbc:mysql://127.0.0.1:3306/myDB?autoReconnect=true</param-value>
</init-param>
<load-on-startup>3</load-on-startup>
</servlet>
>>
if this "autoReconnect" is correct (even without having slave hosts to fail over to), I suggest to add this to the documentation!
4) Will I still need to "configure the pool to only allow connections to be idle for less than 8 hours" ? And what happens if I go beyond the 8 hours after that?
5) You say: "check connections for validity either before handing them out, or upon return to the pool"? Is that the line
stmt = conn.createStatement();
in http://www.mysql.com/documentation/connector-j/index.html#id2802095 ?
At least the
if (stmt.execute("SELECT foo FROM bar")) {
in the example doesn't have an "else" clause that could inspire me...
[5 Sep 2003 8:12]
Mark Matthews
The documentation is generated from DocBook, so I guess the web team will have to look into a stylesheet change :( It looks like you're using your own code to create JDBC connections. If you're using a J2EE application server (like Tomcat), in most cases you shouldn't do that! (You'll be re-inventing a lot of functionality, and probably not doing it the most optimal way your first few attempts). You should take a look at the documentation for JNDI datasources in Tomcat at http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html , my article on Connection Pooling with Connector/J at http://www.mysql.com/articles/connection_pooling_with_connectorj.html, and as well take a look at any decent JDBC book to see more in-depth details (Chapter 10 of the 'MySQL and Java Developer's Guide' I co-authored available from Wiley, Chapter 2 from Oreilly's 'JDBC Pocket Reference' or Chapter 5 from Oreilly's 'Database Programming with JDBC and Java, Second Edition') The nightly snapshots of Connector/J (both 3.1 and 3.0) do a better job of passing socket exceptions back up the chain. On the other hand, like I said, the message doesn't give you much (if anything) depending on the failure mode and the particular version of JVM (and operating system). It's usually something along the lines of 'Connection reset by peer' (which could be any of 100 things wrong), or 'Read terminated in recv()', (which again could be any of 100 things going wrong).
[26 Sep 2003 3:14]
Ralf Hauser
Mark, is it possible that with Struts, the example to get this right needs to be amended by the file WEB-INF/database-config.xml:
<struts-config>
<!-- data source configuration -->
<data-sources>
<data-source
autoCommit = "false"
description = "data source configuration"
driverClass = "org.gjt.mm.mysql.Driver"
maxCount = "15" minCount = "1"
url = "jdbc:mysql://127.0.0.1:3306/privalope?autoReconnect=true"
user = "privasphere"
password = "cm53ct2"
/>
</data-sources>
</struts-config>
==> there is no field "autoReconnect" known to the struts DTD, thus it needs to be added to the url!
------
As per your comment re java.net.SocketException: o.k., they might not provide a decent error message, but at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1079), you could catch it and amend it with something more useful (e.g. the reference to this bug or the pointers mentioned in here!) - couldn't you?
[29 Sep 2003 8:39]
Ralf Hauser
Unfortunately, it is still not working :( As per the mailing list, I see that the autoCommit status is an important contributary variable to set. AFAIK, we set autoCommit to true and only for some few prepared-statements we need to be able to roll-back and thus disable it. But immediately thereafter, we re-enable it. Is there any way to poll the connector (at my discretion) for 1) its autoConnect status 2) its autoCommit status? If so, my suggestion for com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1079) would be that if the relevant exception comes along, the connector should amend the e.getMessage() of the exception with 1) and 2).
[30 Sep 2003 5:59]
Mark Matthews
Nightly builds of 3.0.x and 3.1.x (see http://downloads.mysql.com/) do a better job of not 'hiding' the messages from underlying exceptions, including SocketExceptions, so you might want to take a look at those. Also, (sent in the java@lists.mysql.com), why aren't you using the features of your connection pool built in to tomcat that expire idle connections and send verification queries? That would save you a lot of trouble.
[4 Oct 2003 1:47]
Ralf Hauser
Mark, thx for the hint.
We are still working on the Tomcat-Connection stuff.
But I got yesterday's nightly build. Yes, there is more info in the error I am getting now, but unfortunately, I (=still a Java/MySQL beginner) am not much smarter with it (I still don't see any reference to autoCommit or autoReconnect and their respective statuses or an URL to consult for more details on typical root causes for this exception). But probably, you get a lot more insight out of this.
As usual - any further hints would be highly appreciate.
java.sql.SQLException: Communication link failure: java.io.IOException, underlyi
ng cause: Unexpected end of input stream
** BEGIN NESTED EXCEPTION **
java.io.IOException
MESSAGE: Unexpected end of input stream
STACKTRACE:
java.io.IOException: Unexpected end of input stream
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1744)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2067)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1341)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1423)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1779)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja
va:1714)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
1556)
at com.privasphere.privalope.db.MySqlSystem.getUser(MySqlSystem.java:252
[28 Oct 2003 5:34]
Ralf Hauser
the tomcat pooling approach doesn't seem to be all that straight-forward and well-paved with easy to understand examples. Do you have better links than http://www.midrangeserver.com/mgo/mgo062602-story01.html and http://tyrex.sourceforge.net/ ? --------- We found [wait_timeout=400000] as a quick-and-dirty solution of which I hope it won't bite us too soon in its "dirtiness" - so the need for fixing it really has decreased a lot since this is more than a long weekend ...
[15 Sep 2005 13:20]
[ name withheld ]
Hi everyone, Did you finally reached a conclusion for this? It would be verry helpfull for me.
[15 Sep 2005 14:14]
Ralf Hauser
hasn't happened for months anymore. Using commons-dbcp in the meantime without any problem. The reason might have been the same as http://issues.apache.org/bugzilla/show_bug.cgi?id=29907#c7
Description: After a night of rest for the server, I often get <<DEBUG [Thread-5] (MySqlSystem.java:1852) - com.mysql.jdbc.PreparedStatement@1598 3b7: SELECT * FROM TBL_USER WHERE login = 'hauser@acm.org' AND active = '1' java.sql.SQLException: Communication link failure: java.io.IOException at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1675) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1083) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1142) at com.mysql.jdbc.Connection.execSQL(Connection.java:1876) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja va:1590) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1463) at com.myproj.myapp.db.MySqlSystem.getUser(MySqlSystem.java:185 3) at com.myproj.myapp.control.action.CreateAction.perform( CreatePrepaidAction.java:97) at org.apache.struts.action.ActionServlet.processActionPerform(ActionSer vlet.java:1787) ...>> subsequently, I get repeatedly: <<java.sql.SQLException: Communication link failure: java.net.SocketException at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1079) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1142) at com.mysql.jdbc.Connection.execSQL(Connection.java:1876) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja va:1590) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1463) at com.myproj.myapp.db.MySqlSystem.getReciPaying(MySqlSystem.ja va:1310) at com.myproj.myapp.control.action.FindKeyUserAction.perform(Fi ndAction.java:81) at org.apache.struts.action.ActionServlet.processActionPerform(ActionSer ...>> Meanwhile, I can still work on the database with GUI tools without any problems. Everything gets back to normal when I restart tomcat4.1.24 with its struts1.0. For all How to repeat: wait a night and then execute a web action that uses a database query Suggested fix: no clue, at least, I would hope that java.io.IOException and java.net.SocketException could be more verbose about the reasons why they are raised if the jdbc layers around it would pass on such more specific information (e.getMessage()...)?