Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with C3po -- Connection to DB

 
Ramakrishna Goli
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,,

I am working with C3p0 because of lost DB Connections when network is up and down. To prevent this error I planned to use C3PO. So for this I downloaded some of examples from internet. But its not working

And I want to know, How to check the first connection happened with C3PO or HibernateSession.

Becuase, I am bringing network is down and up immediately. But after enable network, I tried to connect to application then I am getting the following error.

16:23:03,578 INFO EmployeeAction:37 - Local Port:8080
16:23:03,578 INFO EmployeeAction:36 - Local Addr:192.168.1.129
16:23:03,578 DEBUG SessionImpl:247 - opened session at timestamp: 13696519835
16:23:03,578 INFO EmployeeAction:37 - Local Port:8080
16:23:03,578 DEBUG AbstractBatcher:410 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
16:23:03,578 DEBUG SessionImpl:247 - opened session at timestamp: 13696519835
16:23:03,578 DEBUG ConnectionManager:444 - opening JDBC connection
16:23:03,578 DEBUG AbstractBatcher:410 - about to open PreparedStatement (open PreparedStatements: 0, globally: 1)
16:23:03,578 DEBUG SQL:111 - select * from dual
Hibernate: select * from dual
16:23:03,578 DEBUG ConnectionManager:444 - opening JDBC connection
16:23:03,578 DEBUG SQL:111 - select * from dual
Hibernate: select * from dual
16:23:03,578 DEBUG AbstractBatcher:418 - about to close PreparedStatement (open PreparedStatements: 1, globally: 2)
16:23:03,578 DEBUG AbstractBatcher:418 - about to close PreparedStatement (open PreparedStatements: 1, globally: 2)
16:23:03,578 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection
16:23:03,578 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection
16:23:03,593 DEBUG ConnectionManager:464 - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
16:23:03,593 DEBUG ConnectionManager:464 - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
16:23:03,593 DEBUG JDBCExceptionReporter:92 - could not execute query [select * from dual]
java.sql.SQLException: Io exception: Connection reset by peer: socket write error
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:826)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at com.example.action.EmployeeAction.saveData(EmployeeAction.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:595)
16:23:03,593 DEBUG JDBCExceptionReporter:92 - could not execute query [select * from dual]
java.sql.SQLException: Io exception: Connection reset by peer: socket write error
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:826)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at com.example.action.EmployeeAction.saveData(EmployeeAction.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:595)
16:23:03,593 WARN JDBCExceptionReporter:100 - SQL Error: 17002, SQLState: null
16:23:03,593 WARN JDBCExceptionReporter:100 - SQL Error: 17002, SQLState: null
16:23:03,593 ERROR JDBCExceptionReporter:101 - Io exception: Connection reset by peer: socket write error
16:23:03,593 ERROR JDBCExceptionReporter:101 - Io exception: Connection reset by peer: socket write error
16:23:03,609 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection



So, please can you help me how to solve this problem and explain me the procedure how to do this..


Tx...
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Connection pool doesn't eliminate network errors. But it makes it easier for your application to recover from such errors.

When using a connection pool, there are generally two possibilities how a network error happens:

1) There is an unused, but open connection in the pool. When there is a network error, the connection fails. The failure will be discovered as soon as someone tries to use the connection. Connection pools can be configured to test the connection before handing them out; in this case, you should always obtain a valid connection from the pool. If the connection pool is not configured to check the connection beforehand, the first operation you'll try to issue on the connection will fail. In this case, the proceeding is identical to the other scenario.

2) The connection is checked out of the pool and actively used. When there is a network error, the connection fails. If there was a command active on the connection, it fails immediately. Otherwise the error will pop out when the next statement is executed. In this case, the entire transaction made so far on the connection is lost; the database should detect the lost connection and should rollback the transaction automatically*). This situation can be generally handled in two ways:

- The transaction is restarted, using a fresh connection from the pool. In this case, if the network has come up again, the application might have recovered seamlessly from the error. However, this might be risky, since re-running the transaction might cause a lost update or other logical error in the processing (as a result of transactions not running in their intended order).

- The transaction is deemed failed and the operation is terminated with an error. If the operation was initiated by a user, you might show him an error screen and let him repeat the operation. If the operation was run by an automatic process (a scheduled task, for example), the things are more complicated, since there isn't anyone to report the error to. Some applications have means of reporting such errors to competent person, for example.

Also, connection pools are usually able to detect errors on connections and close them physically once it happens, so that they should not be serving the same failed connection over and over again. But this might depend on the connection pool you use and its configuration.

What you describe looks like your connection pool is not configured to verify connections before handing them out. If this is the case, your application might come back to life after several failed attempts - when all failed connections get evicted from the pool. It might be a good idea to keep the number of unused connections in the pool low, to limit the number of failed connection you might get.

*) I've experienced situations in which the database didn't detect the connection was lost, and held locks of the unresolved transaction indefinitely. When this happens, it can prevent the recovery of the application.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic