JSP, Connecting to SQL throws exception

[Ghost]

I have a server that is hosting one of my websites. The website
consists of servlets, JSPs and HTML. When a user logs in to the
website, a servlet will query the SQL database to see if the username
and password exist in the database.

I can deploy this website by logging into the tomcat manager and
providing my .war file. When I do this, everything is fine. Users can
log in and log out all day long.

Here is my problem:
If I deploy the website today, users can log in and log out all day.
Tomorrow when a user goes to log in, the sql query to see if the
username and password exist in the SQL database throw the following
exception:
// My Website Log File:
[09:15:51,066] (VerifyLogin.java ) DEBUG - caught sql exception:
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: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)
at com.cognitronics.servlets.VerifyLogin.doGet(VerifyLogin.java:94)
at com.cognitronics.servlets.VerifyLogin.doPost(VerifyLogin.java:136)
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(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.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
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.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)


Does anyone know what might be going on here? Every morning that I
come in, I have to re-deploy the website.

Any advice would be greatly appreciated. Thanks in advance.

 

[Jeff Zhao]

I think your some codes are wrong.Maybe you haven't get the connection
because the all connections have been running.

 

[Malte]

Ghost wrote:

First of all, your sql probably needs to be more robust, ie after
checking the connection you might want to be prepared to reestablish the
connection if need be.

Second, I have seen cases where a timeout causes (especially Oracle
connections) to go lost. This might also be the case when the firewall
cuts off on port 1521 after one hour. Since this happens for you during
(possible) slow hours, it seems reasonable to assume this is the case.

For these events I usually use a Singleton pattern for the connection
pool, check for SQL connection errors and re-initialize the pool when
necessary.

If this is indeed a timing issue, you can have the server query the
database every 30 minutes (select sysdate or some such) to keep the
connection alive.

 

[Roedy Green]

at com.mysql.jdbc.Connection.execSQL(Connection.java:2379)

Just guessing here. But, are connections supposed to survive for days
at a time? Do they time out after X minutes of inactivity? Are
connections supposed to survive a database reorg or backup?

Perhaps what is needed is some code to catch an exception when the
connection breaks and reestablish it.

Perhaps you need to keep some dummy traffic going to maintain the
connection.

Put these ideas low in priority. They are just wild guesses in case
you don't get better solutions.

 

[Raymond DeCampo]

Just guessing here. But, are connections supposed to survive for days
at a time? Do they time out after X minutes of inactivity? Are
connections supposed to survive a database reorg or backup?

Perhaps what is needed is some code to catch an exception when the
connection breaks and reestablish it.

Perhaps you need to keep some dummy traffic going to maintain the
connection.

Better yet, use a connection pool. Your JSP container should already
have an implementation.

Put these ideas low in priority. They are just wild guesses in case
you don't get better solutions.

HTH,
Ray