Differences

This shows you the differences between two versions of the page.

--- java:thelastpacketsuccessfullyreceivedfromserver [2013/09/30 17:03] – [Second Approach: don't relax yourself] rlunaro
+++ java:thelastpacketsuccessfullyreceivedfromserver [2022/12/02 22:02] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== The last packet successfully received from the server was X milliseconds ago ======
-===== This is a work in progress =====
-I've detected that this page receives many visits. Unfortunately, I am still struggling with this problem and nothing that I have tried before have worked, so **read the rest of the page at your own risk**.
 ===== Intro =====
-This problem is driving me mad since more than three months. I've tried many things to find out a solution, but at the present moment (30/09/2013) nothing is working.
+==== This crap will be useful for you if... ====
-However, **I am creating this page to register all the solutions and as an advice for other people.**
-==== This crap is useful if you... ====
   * Have tried the well known solutions: [[http://stackoverflow.com/questions/16175398/java-communications-link-failure-error-after|solution1]], [[http://stackoverflow.com/questions/15752667/warn-sqlexceptionhelper143-sql-error-0-sqlstate-08s01-sqlexceptionhelper|solution2]]
@@ Line 22: / Line 13: @@
   * Mysql worked perfectly
   * There wasn't a network error
-  * So... what is the problem???
+  * So... were the problem is???
 ==== My architecture ====
@@ Line 28: / Line 19: @@
 Before going on, I think it's important to describe what's my architecture. If it doesn't match yours, probably the solutions described here won't work in your case.
-  * Mysql as persistence engine (I love this: persistence engine. In my times this was simple a RDBMS, but now... wow! it's a persistence engine!)
+  * Mysql as database
   * C3P0 as connection pooling mechanism
   * Hibernate as data access layer
@@ Line 34: / Line 25: @@
   * A batch application that runs for hours and then stops. It only have one thread: starts, do thounsands of queries and then stops.
-PENDIENTE: INDICAR AQUÍ LOS DETALLES DE LA CONFIGURACION HIBERNATE Y C3P0
+If you have the same problem, but in a web application, this please read on; some of the things described here are still useful or at least you should check them out.
+===== First approach: retention of Session objects =====
-===== First approach: I am a nerd =====
 Yes, I am new in hibernate and I miss out this:
@@ Line 258: / Line 248: @@
 </code>
-===== Second Approach: don't relax yourself =====
+===== Second Approach: be careful with your open sessions =====
 After this, **sometimes I have the same problem, others I get this error**.
@@ Line 271: / Line 261: @@
 at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
 </code>
+==== HibernateTemplate must be a singleton ====
+Catch it!!! I had this:
+<code>
+    <bean id="hibernateTemplate"
+          class="org.springframework.orm.hibernate3.HibernateTemplate"
+          scope="prototype">
+        <property name="sessionFactory" ref="sessionFactory"/>
+    </bean>
+</code>
+**An this is wrong.** The [[http://docs.spring.io/spring/docs/2.5.x/api/org/springframework/orm/hibernate3/HibernateTemplate.html|HibernateTemplate]] object must be declared singleton (maybe "session" in a web application to avoid multithreading issues). This saves lots of session objects and keep them alive because they are always been called across the application.
+However, I am starting to think that HibernateTemplate doesn't close properly it's Session objects. If this were the case, it will explain why when I release my HibernateTemplate objects the corresponding session objects weren't released and closed.
+==== You can configure also when Hibernate release its session objects ====
 I've discovered these things that I think they could help in solving the problem:
@@ Line 276: / Line 286: @@
   * [[http://docs.jboss.org/hibernate/stable/core.old/reference/en/html/transactions-connection-release.html|configuring when hibernate releases its connections]] (and don't forget [[http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html|this]] and [[http://stackoverflow.com/questions/5978613/spring-3-mvc-hibernate-3-5-4-hibernatetemplate-not-closing-connections-non-tran|this]])
-Ater trying the parameter [[http://www.mchange.com/projects/c3p0/#debugUnreturnedConnectionStackTraces|debugUnreturnedConnectionStackTraces]] in the application, I get this error:
+You can se the parameter "releaseMode" to the value "AFTER_STATEMENT" and let C3P0 get this open sessions to mysql back. With this, the open sessions that hibernate gets, are released in the quickest way. This is discouraged in the documentation, but bear in mind that if you have put a pooling mechanism, this could act as a safety net catching this connections and reusing them.
-But I am more and more confident that the problem is that **Hibernate is starving the available connections**.
+<code xml>
+<!--
+Possible values for this are:
+     ON_CLOSE : legacy behavior. The session gets the connection when
+                the first time it requires it and release it when it's
+                destroyed
+     AFTER_TRANSACTION :
+                session is released after Transaction issues a commit or
+                rollback
+     AFTER_STATEMENT :
+                connection is released after execution of every statement,
+                BUT it will keep the connection open if the object
+ -->
+<prop key="hibernate.connection.release_mode"></prop>
-I've checked the sourcecode of the find() method of Hibernate and I've found this:
+</code>
+==== You can configure C3P0 to reopen used connections ====
+For me, this have been the silver bullet that can fix a broken application whihout the need to go back to development to fix the issue.
+The configuration parameter [[http://www.mchange.com/projects/c3p0/#unreturnedConnectionTimeout|unreturnedConnectionTimeout]] does the following:
+  - When C3P0 gives a connection to the application, it starts a counter
+  - When this counter reaches this limit, C3P0 silently replaces this connection and gives another fresh open connection
+  - This parameter can avoid the problem that I had that C3P0 gave a connection and it went closed because it was retained open for days
+However, it is discouraged because it hides problems in the application. But if it can save your day, take it into account.
+You have also this parameter: [[http://www.mchange.com/projects/c3p0/#debugUnreturnedConnectionStackTraces|debugUnreturnedConnectionStackTraces]] that can help to debug those connection that the application stores and doesn't return back until it's too late.
+===== My final configuration of Spring =====
+First, define C3P0 datasource:
+<code>
+	  <!--
+	  A better datasource: it gives a new connection each time
+	  is requested.
+	  -->
+	  <bean id="dataSource"
+	        class="com.mchange.v2.c3p0.ComboPooledDataSource"
+	        destroy-method="close"
+	        scope="singleton">
+      <!--
+      To test a connection:
+      mysql -h HOSTNAME -u USERNAME -p
+      and aftwerwards introduce the password when requested
+       -->
+      <property name="driverClass" value="com.mysql.jdbc.Driver"/>
+      <property name="jdbcUrl"     value="jdbc:mysql://${mysql-server}:${mysql-port}/${mysql-database}"/>
+      <property name="user"        value="${mysql-user}"/>
+      <property name="password"    value="${mysql-password}"/>
+      <!--
+      Recommended values for maxPoolSize and minPoolSize:
+. get value of max available connections of your
+             database issuing command 'show variables like "max_connections";'
+. this will be the upper limit for maxPoolSize: you shouldn't
+             reach never this limit
+. check out with show processlist the active connections
+             to the database: this will help to determine the available
+             connections for your application:
+             (show processlist) - (show variables like max_connections)
+. it is advisable to identify how many DAO objects will be
+             issuing sentences to the database at the same time. Ok,
+             make a rough stimation. Say that this value will be
+. A good and steady value for maxPoolSize can
+             be 40 (four times).
+       -->
+			<property name="maxPoolSize"         value="10"/>
+			<property name="minPoolSize"         value="5"/>
+			<!--
+			checkoutTimeout mits how long a client will wait for a
+			Connection, if all Connections are checked out and one
+			cannot be supplied immediately. In milliseconds.
+			http://www.mchange.com/projects/c3p0/#checkoutTimeout
+			-->
+			<property name="checkoutTimeout"     value="10000"/>
+			<property name="acquireIncrement"    value="1"/>
+			<property name="idleConnectionTestPeriod" value="100"/>
+			<!-- property name="preferredTestQuery"  value="select 1"/ -->
+			<!--
+			testConnectionOnCheckout is not recommended because it
+			checks the connection before give it to hibernate. In a
+			paranoid environment could fix some problems
+			-->
+			<!-- property name="testConnectionOnCheckout" value="true"/-->
+			<!--
+			debugUnreturnedConnectionStackTraces prints a stack trace when
+			a connection that is given to hibernate reachs its timeout value.
+			This could help to debug cases when a connection is taken and
+			not released in a certain amount of time
+			http://www.mchange.com/projects/c3p0/#debugUnreturnedConnectionStackTraces
+			-->
+			<!-- property name="debugUnreturnedConnectionStackTraces" value="true"/-->
+			<!--
+			unreturnedConnectionTimeout is the silver bullet for a problem in
+			a greedy application (in terms of sessions). It will count how long
+			hibernate has taken a connection, and if reachs a timeout, c3p0 will
+			replace this connection with a fresh session, avoiding the problem
+			of the database may close opened for much time
+			http://www.mchange.com/projects/c3p0/#unreturnedConnectionTimeout
+			-->
+			<!--property name="unreturnedConnectionTimeout" value="100"/-->
+	  </bean>
-<code java>
-public List find(final String queryString, final Object... values) throws DataAccessException {
-	return executeWithNativeSession(new HibernateCallback<List>() {
-		public List doInHibernate(Session session) throws HibernateException {
-			Query queryObject = session.createQuery(queryString);
-			prepareQuery(queryObject);
-			if (values != null) {
-				for (int i = 0; i < values.length; i++) {
-					queryObject.setParameter(i, values[i]);
-				}
-			}
-			return queryObject.list();
-		}
-	});
-}
 </code>
-See [[http://grepcode.com/file/repository.springsource.com/org.springframework/org.springframework.orm/3.0.4/org/springframework/orm/hibernate3/HibernateTemplate.java#HibernateTemplate.find%28java.lang.String%2Cjava.lang.Object%5B%5D%29|this]] for the reference.
+Second, the hibernateTemplate and jdbctemplate objects of spring will be available accross the application.
+**Beware!!! If you are developing a web application, these should be session instead of singleton!!!**
-I'm not an expert on this code, but I am starting to think that HibernateTemplate doesn't close the connections it uses. If it were the case, many things will be explained:
-  * at the firs stages of the application, everything went well
+<code xml>
-  * the more and more time the application is running, the more connections are open
+	  <bean id="simpleJdbcTemplate"
-  * until it starves the available connections and rejects creating more connections
+	        class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate"
+	        scope="singleton">
+	    <constructor-arg ref="dataSource"/>
+	  </bean>
-==== If you are using HibernateTemplate, configura Release afterstatement ====
+    <bean id="hibernateTemplate"
+          class="org.springframework.orm.hibernate3.HibernateTemplate"
+          scope="singleton">
+        <property name="sessionFactory" ref="sessionFactory"/>
+    </bean>
-I've configured release after_statement to see if this fixes the problem.
+</code>
+Finally, the sessionFactory:
+<code xml>
+    <bean id="sessionFactory"
+          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
+          scope="singleton">
+        <property name="dataSource" ref="dataSource"/>
+        <property name="annotatedClasses">
+            <list>
+                <value>com.mapfre.dga.mercurio.entity.Detail</value>
+                <value>com.mapfre.dga.mercurio.entity.Indicator</value>
+                <value>com.mapfre.dga.mercurio.entity.Period</value>
+                <value>com.mapfre.dga.mercurio.entity.Struct</value>
+                <value>com.mapfre.dga.mercurio.entity.Valoration</value>
+                <value>com.mapfre.dga.mercurio.entity.Version</value>
+            </list>
+        </property>
+        <property name="hibernateProperties">
+            <props>
+                <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
+                <!-- for debug, put these values to "true" -->
+                <prop key="hibernate.show_sql">${show-sql-in-hibernate}</prop>
+                <prop key="hibernate.format_sql">${format-sql-in-hibernate}</prop>
+                <!--
+                Possible values for this are:
+                     on_close : legacy behavior. The session gets the connection when
+                                the first time it requires it and release it when it's
+                                destroyed
+                     after_transaction :
+                                session is released after Transaction issues a commit or
+                                rollback
+                     after_statement :
+                                connection is released after execution of every statement,
+                                BUT it will keep the connection open if the object
+                 -->
+                <!-- prop key="hibernate.connection.release_mode">after_statement</prop -->
+            </props>
+        </property>
+    </bean>
+</code>
-PENDIENTE VER SI ESTA EJECUCION RESUELVE EL PROBLEMA
+I've put plenty of comments of useful configuration parameters and lots of information, just in case.
+===== Resources =====
-===== Third approach: paranoia approach =====
+http://www.mchange.com/projects/c3p0/
-All these tests have been made on corporate Windows machines. I am starting to think that some kind of security policy is imposed in my user that limits the activity of my windows machine. To be studied...
 ~~DISQUS~~