Connection Failures, Slow queries, Hung Connections on socketRead
Posted by: Alex Socrates
Date: June 24, 2005 10:45AM

I'm having the following intermittent problem with my JDBC connection to my MySQL database.
I'm using the MySQL Connector/J version 3.1.8 (which is the latest) with Tomcat.
I'm connecting to MySQL 4.1.12 running on a different machine with 1 network switch in between
I'm running on Linux Fedora Core 3 AMD64

I get the following errors (See "EXCEPTION #1" below) every so often for no apparent reason. The same query will work 1000 times without a problem, and then one time it will fail with an error like the following. I run about 10 million queries a day against the database, and about 20 of them give an error like the "EXCEPTION #1" over the course of the day.

I don't think this is related to server load becuase it is not very busy on either the database server, or the tomcat application server, although it may be somewhat correlated.

The "MESSAGE:" varies between
MESSAGE: Connection timed out
MESSAGE: No route to host
MESSAGE: Connection reset

I was thinking this may be due to a "slow query", so I turned that up to 40 seconds for a slow quer. But I don't think it is a 'slow query' because the same query will work fine most of the rest of the time.

I've read through the forum archives and another problem I was having (which I'm pretty sure is related) is when the query is sent to the MySQL Server, but a response is never received. The thread is blocked on a call to SocketInputStream.socketRead0(). The stack trace that I get in that case is below in "STACK TRACE #1". From the reading the forum there was supposed to be some old bug in the driver that would hang the thread on a slow query, but it is supposed to be fixed in the newer versions.

I'm pretty sure the two problems are related, but I could be wrong.

I'm hoping someone has been having a similar problem and found a solution. The system has been running fine for 4 months or so in it's current configuration. This problem has started happening in the last few weeks.

Thank-you very much for your help.

Alex.


************* EXCEPTION #1*****************

SELECT ID, count(ID) FROM DataTable WHERE ( (Name = '8234' AND Value = '4732' ) OR (Name = '38' AND Value = '2164' )) GROUP BY ID
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:104)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:172)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1839)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2788)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2379)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2306)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1192)


************* STACK TRACE #1*****************

java.net.SocketInputStream socketRead0 -2
java.net.SocketInputStream read 129
com.mysql.jdbc.util.ReadAheadInputStream fill 104
com.mysql.jdbc.util.ReadAheadInputStream readFromUnderlyingStreamIfNecessary 144
com.mysql.jdbc.util.ReadAheadInputStream read 172
com.mysql.jdbc.MysqlIO readFully 1839
com.mysql.jdbc.MysqlIO reuseAndReadPacket 2341
com.mysql.jdbc.MysqlIO checkErrorPacket 2788

Options: ReplyQuote


Subject
Written By
Posted
Connection Failures, Slow queries, Hung Connections on socketRead
June 24, 2005 10:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.