Differences

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

--- mysql:problemresolution [2013/07/25 09:13] – rlunaro
+++ mysql:problemresolution [2013/10/04 10:10] (current) – removed rlunaro
@@ Line 1: / Line 1: @@
-====== Problem Resolution ======
-===== Problem =====
-<code>
-/20 01:38:26 hibernate.util.JDBCExceptionReporter     - Communications link failure
-The last packet successfully received from the server was 7.202.618 milliseconds ago.  The last packet sent successfully to the server was 7.202.618 milliseconds ago.
-</code>
-===== Solution =====
-After several days investigating the problem I've found out that other database worked smoothly with the application, so the problem was in the mysql.
-I've copied several configuration values (max threads, log file size, cache size, etc..) from the "good" database to the old, and it's working now.
-The lesson: **too ambitious values (i.e. big caches, big memory comsumption, big threads can spoil your database performance)**.
-===== Some tools =====
-This will show you the current connections:
-<code>
-show status like '%onn%';
-</code>
-Example of output:
-<code>
-mysql> show status like '%onn%';
-+--------------------------+-------+
-| Variable_name            | Value |
-+--------------------------+-------+
-| Aborted_connects         | 0     |
-| Connections              | 50    |
-| Max_used_connections     | 9     |
-| Ssl_client_connects      | 0     |
-| Ssl_connect_renegotiates | 0     |
-| Ssl_finished_connects    | 0     |
-| Threads_connected        | 8     |
-+--------------------------+-------+
-rows in set (0.00 sec)
-</code>
-Pay attention to the data "Threads_connected", which is similar to the result of the ''show processlist''.
-===== What are the maximum connections that mysql can have =====
-<code>
-show variables like "max_connections";
-</code>
-===== What are the maximum timeout that a connection will be kept open =====
-<code>
-mysql> show variables like '%imeout%';
-+----------------------------+----------+
-| Variable_name              | Value    |
-+----------------------------+----------+
-| connect_timeout            | 10       |
-| delayed_insert_timeout     | 300      |
-| innodb_lock_wait_timeout   | 50       |
-| innodb_rollback_on_timeout | OFF      |
-| interactive_timeout        | 28800    |
-| lock_wait_timeout          | 31536000 |
-| net_read_timeout           | 30       |
-| net_write_timeout          | 60       |
-| slave_net_timeout          | 3600     |
-| wait_timeout               | 28800    |
-+----------------------------+----------+
-rows in set (0.00 sec)
-</code>
-===== Maybe c3p0 has the solution =====
-Eventually I've implemented a configuration in the connection pooling mechanism to:
-  * verify the connection when it enters into the pool
-  * put a timeout to this connection, releasing it after this period
-This will prevent the case when a connection is held but the JDBC driver drops out.
-My config parameters:
-<code xml>
-    <property name="minPoolSize" value="2"/>
-    <property name="maxPoolSize" value="4"/>
-    <property name="initialPoolSize" value="1"/>
-    <!-- good for mysql. For oracle, select * from dual could be good enough -->
-    <property name="preferredTestQuery" value="select 1"/>
-    <property name="testConnectionOnCheckin" value="true"/>
-    <property name="idleConnectionTestPeriod" value="60"/>
-</code>
-And where I've taken them:
-http://www.mchange.com/projects/c3p0/
-===== Checking the log files =====
-I've put the status of the log to "DEBUG", and I've seen this:
-<code>
-/24 12:09:14 hibernate.jdbc.ConnectionManager         - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!
-</code>
-It seems that the hibernate continues to use their connection manager as he wants, ignoring completely the configuration I have for C3P0....
-Reading this:
-http://www.mkyong.com/hibernate/how-to-configure-the-c3p0-connection-pool-in-hibernate/
-===== Issues about configuration of C3P0 and Hibernate =====
-I've discovered that, **the configuration for C3P0 goes into the configuration of hibernate**.
-I have this:
-<code xml>
-  <bean id="dataSource"
-        class="com.mchange.v2.c3p0.ComboPooledDataSource"
-        destroy-method="close"
-        scope="singleton">
-    <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}"/>
-  </bean>
-   <bean id="sessionFactory"
-          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
-          scope="singleton">
-        <property name="dataSource" ref="dataSource"/>
-        <property name="annotatedClasses">
-    [....]
-</code>
-**And it's incorrect. This is the proper configuration:**
-<code xml>
-    <bean id="sessionFactory"
-          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
-          scope="singleton">
-        <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>
-		<prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>
-		<prop key="hibernate.connection.url">jdbc:mysql://${mysql-server}:${mysql-port}/${mysql-database}</prop>
-		<prop key="hibernate.connection.username">${mysql-user}</prop>
-		<prop key="hibernate.connection.password">${mysql-password}</prop>
-                 <!--
-                  Configuration for fixing the problem:
-                  The last packet successfully received from
-                  the server was 7.202.618 milliseconds ago.
-                  The last packet sent successfully to the
-                  server was 7.202.618 milliseconds ago.
-                  -->
-                <prop key="hibernate.c3p0.idle_test_period">60</prop>
-                <!--
-                In mysql, this value MUST be lower than wait_timeout.
-                You can check this out with the mysql command "show variables like '%ait%';"
-                -->
-                <prop key="hibernate.c3p0.timeout">60</prop>
-                <prop key="hibernate.c3p0.min_size">2</prop>
-                <prop key="hibernate.c3p0.max_size">5</prop>
-		<!-- good for mysql. For oracle, select * from dual could be good enough -->
-		<prop key="hibernate.c3p0.preferredTestQuery">select 1</prop>
-		<prop key="hibernate.c3p0.testConnectionOnCheckin">true</prop>
-                <prop key="hibernate.c3p0.idleConnectionTestPeriod">250</prop>
-            </props>
-        </property>
-    </bean>
-</code>
-In other words, Hibernate must take care of configuring C3P0. I was so obsesed with Spring that I've forgot that some libraries do things in their own way.