DBCP error
am 12.05.2011 09:06:53 von Dhaval Jaiswal
--0015174c1ab87e25bb04a30ed50a
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
I have seen the best practices for the connection pooling as below.
Connection conn =3D null;
Statement stmt =3D null; // Or PreparedStatement if needed
ResultSet rs =3D null;
try {
conn =3D ... get connection from connection pool ...
stmt =3D conn.createStatement("select ...");
rs =3D stmt.executeQuery();
... iterate through the result set ...
rs.close();
rs =3D null;
stmt.close();
stmt =3D null;
conn.close(); // Return to connection pool
conn =3D null; // Make sure we don't close it twice
} catch (SQLException e) {
... deal with errors ...
} *finally {* // Always make sure result sets and statements are close=
d,
// and the connection is returned to the pool
if (rs !=3D null) {
try { rs.close(); } catch (SQLException e) { ; }
rs =3D null;
}
if (stmt !=3D null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt =3D null;
}
if (conn !=3D null) {
try { conn.close(); } catch (SQLException e) { ; }
conn =3D null;
}
}
However, we are closing the connection only in *finally* block not before
that. Is that the reason to throw the DBCP errors?
--
Regards,
Dhaval
On Wed, May 11, 2011 at 12:44 PM, Dhaval Jaiswal wr=
ote:
>
> Yeah, we have some of the SQL statements which are running more than 1
> minutes. However, for small queries it's also throwing errors DBCP.
>
>
> --
> Regards,
> Dhaval
>
>
> On Fri, May 6, 2011 at 4:59 PM, Martin Gainty wrote=
:
>
>> Id the execution delivers results in under a minute then adhere to Mr
>> Woods advice
>> If over a minute the other consideration is you might have a terribly
>> long-executing SQL statement that needs to be tuned.
>>
>> if you can display your SQL statement and the relevant schemata we could
>> suggest more expeditious means to acquire your data
>>
>> Martin
>> ______________________________________________
>> Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentiali=
t=E9
>>
>> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
>> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefug=
te
>> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachrich=
t
>> dient lediglich dem Austausch von Informationen und entfaltet keine
>> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
>> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>>
>> Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EA=
tes pas le destinataire pr=E9vu, nous te demandons avec bont=E9 que pour sa=
tisfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9=
e ou la copie de ceci est interdite. Ce message sert =E0 l'information seul=
ement et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant =
donn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation,=
nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni.
>>
>>
>>
>>
>>
>>
>> > Subject: Re: Fwd: DBCP error
>> > From: nwood@plus.net
>> > To: dhaval.jaiswal@via.com
>> > CC: mysql@lists.mysql.com; shrinivas.devarkonda@via.com
>> > Date: Fri, 6 May 2011 11:35:30 +0100
>>
>> >
>> > On Fri, 2011-05-06 at 11:12 +0100, Dhaval Jaiswal wrote:
>> > > Caused by: java.net.SocketException: Socket closed
>> >
>> > I'd suggest you look at server side timeout and maximum connection
>> > settings in
>> > http://dev.mysql.com/doc/refman/5.1/en/server-system-variabl es.html I'=
d
>> > suspect wait_timeout is the setting you're interested in. You might al=
so
>> > want to check the maximum TCP session/idle timeouts on firewalls,
>> > routers and other network devices between the failing system and its
>> > MySQL server.
>> >
>> > Hope that helps,
>> > Nigel
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>> >
>>
>
>
>
> --
> Regards,
> Dhaval Jaiswal
>
>
>
--=20
Regards,
Dhaval Jaiswal
--0015174c1ab87e25bb04a30ed50a--
Re: DBCP error
am 01.06.2011 16:05:57 von Dhaval Jaiswal
--90e6ba6e8110fd0f9d04a4a704df
Content-Type: text/plain; charset=ISO-8859-1
Hi Martin,
We have modified all timeout,buffersize,pool related parameters from mysql
server side. However, still we are getting DBCP errors.
We have practice to close the database connection in finally block of java ,
in the std practice order of resultset to conn.close..
What would be other things to check and fix these errors?
On Fri, May 6, 2011 at 5:54 PM, Martin Gainty wrote:
> connection leaks will occur because of code maladies
> did you check your code to ensure:
> you are closing Statement Handles when you have finished executing the
> statement?
> you are closing Connection Handles when you have finished DML and DDL
> operations with the Database?
> ?
> Martin
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung
>
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
> dient lediglich dem Austausch von Informationen und entfaltet keine
> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>
>
>
>
>
> > Date: Fri, 6 May 2011 15:42:56 +0530
> > Subject: Fwd: DBCP error
> > From: dhaval.jaiswal@via.com
> > To: mysql@lists.mysql.com
> > CC: shrinivas.devarkonda@via.com
>
> >
> > Hi All,
> >
> > We are frequently getting DBCP error. We have seen this error in
> > catalina.log & apps log. This issue has started since last 2 -3 days. We
> > have checked the application code when this error has occured and assure
> > that there is no connection leakage in code. Mysql server is also running
> > fine. We have tune memory parameters in mysql, however effectless. Still
> we
> > are getting the same error.
> >
> > Any suggestion/solution would be appreciated.
> >
> >
> > Mysql server version: 5.0.45
> >
> > JDBC version: mysql-connector-java-5.1.5-bin.jar
> >
> > apache Tomcat version: 6.0.16
> >
> >
> > server.xml parameters are as follows::
> >
> >
> > url="jdbc:mysql://hostname:3306/dbname" username="user" password="pass"
> > maxActive="40" maxIdle="20" maxWait="5000"
> >
> >
> >
> > ERROR:
> >
> >
> > 2011-05-05 16:32:57,666 [ajp-8009-177] ERROR TrainSearchResultCache.java
> -
> > Error retrieving Train Search from DB: s-
> >
> > org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
> > pool error Timeout waiting for idle object
> >
> > at
> >
> org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection( PoolingDataSource.java:104)
> >
> > at
> >
> org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(Ba sicDataSource.java:880)
> >
> > at
> > com.eos.gds.data.DatabaseManager.getConnection(DatabaseManag er.java:73)
> >
> > at
> > com.eos.gds.data.DatabaseManager.getConnection(DatabaseManag er.java:60)
> >
> > ............................................
> >
> > Caused by: java.util.NoSuchElementException: Timeout waiting for idle
> object
> >
> > at
> >
> org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObj ect(GenericObjectPool.java:958)
> >
> > at
> >
> org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject (AbandonedObjectPool.java:84)
> >
> > at
> >
> org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection( PoolingDataSource.java:96)
> >
> > ... 28 more
> >
> >
> > com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications
> > link failure
> >
> > Last packet sent to the server was 24 ms ago.
> >
> > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Nativ e
> > Method)
> >
> > at
> >
> sun.reflect.NativeConstructorAccessorImpl.newInstance(Native ConstructorAccessorImpl.java:39)
> >
> > at
> >
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(De legatingConstructorAccessorImpl.java:27)
> >
> > at java.lang.reflect.Constructor.newInstance(Constructor.java:5 13)
> >
> > at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
> >
> > at
> > com.mysql.jdbc.SQLError.createCommunicationsException(SQLErr or.java:1074)
> >
> > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2873)
> >
> > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2763)
> >
> > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3299)
> >
> > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
> >
> > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
> >
> > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:25 43)
> >
> > at
> >
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedSta tement.java:1737)
> >
> > at
> >
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedState ment.java:2022)
> >
> > at
> >
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedState ment.java:1940)
> >
> > at
> >
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedState ment.java:1925)
> >
> > at
> >
> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.exec uteUpdate(DelegatingPreparedStatement.java:102)
> >
> > at
> >
> com.eos.gds.cache.FareQuoteDBCache.storeItinQuotesIntoPresis tentCache(FareQuoteDBCache.java:1029)
> >
> > at
> >
> com.eos.gds.cache.FareQuoteDBCache.processInputQueue(FareQuo teDBCache.java:899)
> >
> > at
> > com.eos.gds.cache.FareQuoteDBCache.access$000(FareQuoteDBCac he.java:37)
> >
> > at
> > com.eos.gds.cache.FareQuoteDBCache$1.run(FareQuoteDBCache.ja va:103)
> >
> > at java.lang.Thread.run(Thread.java:619)
> >
> > Caused by: java.net.SocketException: Socket closed
> >
> > at java.net.SocketInputStream.socketRead0(Native Method)
> >
> > at java.net.SocketInputStream.read(SocketInputStream.java:129)
> >
> > at
> >
> com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInput Stream.java:113)
> >
> > at
> >
> com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingS treamIfNecessary(ReadAheadInputStream.java:160)
> >
> > at
> >
> com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInput Stream.java:188)
> >
> > at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2329)
> >
> > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2774)
> >
> >
> >
> >
> > --
> > Regards,
> > Dhaval Jaiswal
> >
> >
> >
> >
> > --
> > Regards,
> > Dhaval Jaiswal
>
--
Regards,
Dhaval Jaiswal
--90e6ba6e8110fd0f9d04a4a704df--