java:thelastpacketsuccessfullyreceivedfromserver
Differences
This shows you the differences between two versions of the page.
Previous revision | |||
— | 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 ====== | ||
+ | ===== Intro ===== | ||
+ | |||
+ | ==== This crap will be useful for you if... ==== | ||
+ | |||
+ | * Have tried the well known solutions: [[http:// | ||
+ | * Have [[https:// | ||
+ | |||
+ | __This problem is soooooo common that the solutions described above should work in most of the cases__, however, it wasn't my case: | ||
+ | |||
+ | * My application worked perfectly | ||
+ | * Mysql worked perfectly | ||
+ | * There wasn't a network error | ||
+ | * So... were the problem is??? | ||
+ | |||
+ | ==== My architecture ==== | ||
+ | |||
+ | Before going on, I think it's important to describe what's my architecture. If it doesn' | ||
+ | |||
+ | * Mysql as database | ||
+ | * C3P0 as connection pooling mechanism | ||
+ | * Hibernate as data access layer | ||
+ | * Spring to tie all together | ||
+ | * A batch application that runs for hours and then stops. It only have one thread: starts, do thounsands of queries and then stops. | ||
+ | |||
+ | If you have the same problem, but in a web application, | ||
+ | ===== First approach: retention of Session objects ===== | ||
+ | |||
+ | Yes, I am new in hibernate and I miss out this: | ||
+ | |||
+ | [[http:// | ||
+ | |||
+ | |||
+ | Apparently, the retention of Hibernate Sessions can more time than the value of [[http:// | ||
+ | |||
+ | This could be my case, because it happens that I found one '' | ||
+ | |||
+ | ==== Steps to reproduce the problem ==== | ||
+ | |||
+ | - Configure your [[http:// | ||
+ | | ||
+ | <code ini> | ||
+ | wait_timeout = 60 | ||
+ | # just in case | ||
+ | interactive_timeout = 60 | ||
+ | </ | ||
+ | | ||
+ | - **Make a test.** I've built the following test, which is pretty straightforward: | ||
+ | | ||
+ | <code java> | ||
+ | |||
+ | @Test | ||
+ | public void test() | ||
+ | { | ||
+ | Period p = entityProvider.randomExistingPeriod(); | ||
+ | |||
+ | // issue a query | ||
+ | StringBuilder hql = new StringBuilder(); | ||
+ | |||
+ | hql.append( "from Table where period = : | ||
+ | |||
+ | Query q = session.createQuery( hql.toString() ); | ||
+ | q.setEntity( " | ||
+ | |||
+ | @SuppressWarnings(" | ||
+ | List< | ||
+ | |||
+ | log.info( " | ||
+ | try | ||
+ | { | ||
+ | Thread.sleep( 10 * 60 * 1000 ); // ten minutes | ||
+ | }catch( InterruptedException ex ) | ||
+ | { | ||
+ | log.info( " | ||
+ | } | ||
+ | |||
+ | // issue a second query | ||
+ | // issue a query | ||
+ | StringBuilder hql2 = new StringBuilder(); | ||
+ | |||
+ | hql2.append( "from Table where period = : | ||
+ | |||
+ | Query q2 = session.createQuery( hql.toString() ); | ||
+ | q2.setEntity( " | ||
+ | |||
+ | @SuppressWarnings(" | ||
+ | List< | ||
+ | |||
+ | assertNotNull( result ); | ||
+ | assertNotNull( result2 ); | ||
+ | assertTrue( result.size() > 0 ); | ||
+ | assertTrue( result2.size() > 0 ); | ||
+ | |||
+ | } // test | ||
+ | |||
+ | </ | ||
+ | |||
+ | | ||
+ | In other words; | ||
+ | |||
+ | - issue a valid query to the database | ||
+ | - wait an amount of time **greater than the wait_timeout you have configured** | ||
+ | - issue a valid query to the database. This second query will show up the problem | ||
+ | |||
+ | < | ||
+ | 2013-09-03 15: | ||
+ | com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: | ||
+ | |||
+ | The last packet successfully received from the server was 600.214 milliseconds ago. The last packet sent successfully to the server was 15 milliseconds ago. | ||
+ | at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) | ||
+ | at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) | ||
+ | at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) | ||
+ | at java.lang.reflect.Constructor.newInstance(Unknown Source) | ||
+ | at com.mysql.jdbc.Util.handleNewInstance(Util.java: | ||
+ | at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java: | ||
+ | at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java: | ||
+ | at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java: | ||
+ | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java: | ||
+ | at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java: | ||
+ | [....] | ||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Solution ==== | ||
+ | |||
+ | The solution is as easy as **not keeping the hibernate [[http:// | ||
+ | |||
+ | For myself, it worked the following countermeasures: | ||
+ | |||
+ | * Bear always in mind that the Session object can't be kept in memory too much time, and close as quick as you can | ||
+ | * Avoid saving Session beans as members of classes; use it only in local variables will reduce the exposure to this problem | ||
+ | * Lower the [[http:// | ||
+ | |||
+ | |||
+ | To change the wait_timeout value for a particular session: | ||
+ | |||
+ | <code sql> | ||
+ | set session wait_timeout = 300; | ||
+ | </ | ||
+ | |||
+ | ==== A quick and dirty solution ==== | ||
+ | |||
+ | In my case I have a batch application that longs for 24 hours. So putting a big value in wait_timeout will solve the problem. Here is how to do this programmatically: | ||
+ | |||
+ | <code sql> | ||
+ | mysql> set global interactive_timeout = 172800; | ||
+ | Query OK, 0 rows affected (0.00 sec) | ||
+ | |||
+ | mysql> set global wait_timeout = 172800; | ||
+ | Query OK, 0 rows affected (0.01 sec) | ||
+ | </ | ||
+ | |||
+ | You have to change '' | ||
+ | |||
+ | ==== A way to set lower values of wait_timeout only for our tests ==== | ||
+ | |||
+ | I've developed a bean who issues mysql commands to the database. With this, I've configured it to | ||
+ | set low '' | ||
+ | low values, hence allowing all the possible errors arise before they reach to production. | ||
+ | |||
+ | <code java> | ||
+ | |||
+ | import java.util.ArrayList; | ||
+ | import java.util.List; | ||
+ | import java.util.Map; | ||
+ | |||
+ | import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; | ||
+ | |||
+ | /** | ||
+ | * Allows to set commands to the sql database, like set commands. | ||
+ | * | ||
+ | * @author RLUNARO | ||
+ | * | ||
+ | */ | ||
+ | public class MysqlConfigurator | ||
+ | { | ||
+ | private SimpleJdbcTemplate jdbc; | ||
+ | private List< | ||
+ | |||
+ | public SimpleJdbcTemplate getJdbc() | ||
+ | { | ||
+ | return jdbc; | ||
+ | } | ||
+ | |||
+ | public void setJdbc(SimpleJdbcTemplate jdbc) | ||
+ | { | ||
+ | this.jdbc = jdbc; | ||
+ | } | ||
+ | public List< | ||
+ | { | ||
+ | return initialListOfCommands; | ||
+ | } | ||
+ | |||
+ | public void setInitialListOfCommands(List< | ||
+ | { | ||
+ | this.initialListOfCommands = initialListOfCommands; | ||
+ | } | ||
+ | |||
+ | /** | ||
+ | * This method must be called upon set of the initial properties. | ||
+ | */ | ||
+ | public void init() | ||
+ | { | ||
+ | // traverse list of commands and run them | ||
+ | for( String command : initialListOfCommands ) | ||
+ | { | ||
+ | issueStatement( command ); | ||
+ | } // command : initialListOfCommands | ||
+ | } | ||
+ | |||
+ | public void issueStatement( String command ) | ||
+ | { | ||
+ | jdbc.update( command ); | ||
+ | } | ||
+ | |||
+ | public String queryVariable( String variable ) | ||
+ | { | ||
+ | String sql = "show variables like ? "; | ||
+ | List< | ||
+ | args.add( variable ); | ||
+ | List< | ||
+ | return (String) result.get(0).get( " | ||
+ | } | ||
+ | |||
+ | } | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | And here is the spring configuration I am using (see below). I am using a singleton to load the bean | ||
+ | at the beginning of the program and make sure that the values are set. | ||
+ | |||
+ | <code xml> | ||
+ | |||
+ | <bean id=" | ||
+ | class=" | ||
+ | init-method=" | ||
+ | scope=" | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | </ | ||
+ | </ | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== Second Approach: be careful with your open sessions ===== | ||
+ | |||
+ | After this, **sometimes I have the same problem, others I get this error**. | ||
+ | |||
+ | < | ||
+ | com.mchange.v2.resourcepool.TimeoutException: | ||
+ | at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java: | ||
+ | at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java: | ||
+ | at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java: | ||
+ | at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java: | ||
+ | at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java: | ||
+ | at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java: | ||
+ | </ | ||
+ | |||
+ | ==== HibernateTemplate must be a singleton ==== | ||
+ | |||
+ | Catch it!!! I had this: | ||
+ | |||
+ | < | ||
+ | <bean id=" | ||
+ | class=" | ||
+ | scope=" | ||
+ | < | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | **An this is wrong.** The [[http:// | ||
+ | |||
+ | However, I am starting to think that HibernateTemplate doesn' | ||
+ | |||
+ | |||
+ | ==== 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: | ||
+ | |||
+ | * [[http:// | ||
+ | |||
+ | You can se the parameter " | ||
+ | |||
+ | <code xml> | ||
+ | < | ||
+ | Possible values for this are: | ||
+ | | ||
+ | the first time it requires it and release it when it's | ||
+ | destroyed | ||
+ | | ||
+ | session is released after Transaction issues a commit or | ||
+ | rollback | ||
+ | | ||
+ | connection is released after execution of every statement, | ||
+ | BUT it will keep the connection open if the object | ||
+ | | ||
+ | <prop key=" | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ==== 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:// | ||
+ | |||
+ | - When C3P0 gives a connection to the application, | ||
+ | - 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:// | ||
+ | |||
+ | |||
+ | |||
+ | ===== My final configuration of Spring ===== | ||
+ | |||
+ | First, define C3P0 datasource: | ||
+ | |||
+ | < | ||
+ | <!-- | ||
+ | A better datasource: it gives a new connection each time | ||
+ | is requested. | ||
+ | --> | ||
+ | <bean id=" | ||
+ | class=" | ||
+ | destroy-method=" | ||
+ | scope=" | ||
+ | <!-- | ||
+ | To test a connection: | ||
+ | mysql -h HOSTNAME -u USERNAME -p | ||
+ | and aftwerwards introduce the password when requested | ||
+ | |||
+ | | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | <!-- | ||
+ | Recommended values for maxPoolSize and minPoolSize: | ||
+ | 1. get value of max available connections of your | ||
+ | | ||
+ | 2. this will be the upper limit for maxPoolSize: | ||
+ | reach never this limit | ||
+ | 3. check out with show processlist the active connections | ||
+ | to the database: this will help to determine the available | ||
+ | | ||
+ | (show processlist) - (show variables like max_connections) | ||
+ | 4. it is advisable to identify how many DAO objects will be | ||
+ | | ||
+ | make a rough stimation. Say that this value will be | ||
+ | 10. A good and steady value for maxPoolSize can | ||
+ | be 40 (four times). | ||
+ | |||
+ | | ||
+ | < | ||
+ | < | ||
+ | <!-- | ||
+ | checkoutTimeout mits how long a client will wait for a | ||
+ | Connection, | ||
+ | cannot be supplied immediately. In milliseconds. | ||
+ | http:// | ||
+ | --> | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | <!-- property name=" | ||
+ | <!-- | ||
+ | testConnectionOnCheckout is not recommended because it | ||
+ | checks the connection before give it to hibernate. In a | ||
+ | paranoid environment could fix some problems | ||
+ | --> | ||
+ | <!-- property name=" | ||
+ | <!-- | ||
+ | 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:// | ||
+ | --> | ||
+ | <!-- property name=" | ||
+ | <!-- | ||
+ | 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:// | ||
+ | --> | ||
+ | < | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Second, the hibernateTemplate and jdbctemplate objects of spring will be available accross the application. | ||
+ | **Beware!!! If you are developing a web application, | ||
+ | |||
+ | |||
+ | <code xml> | ||
+ | <bean id=" | ||
+ | class=" | ||
+ | scope=" | ||
+ | < | ||
+ | </ | ||
+ | |||
+ | <bean id=" | ||
+ | class=" | ||
+ | scope=" | ||
+ | < | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | Finally, the sessionFactory: | ||
+ | |||
+ | <code xml> | ||
+ | <bean id=" | ||
+ | class=" | ||
+ | scope=" | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | </ | ||
+ | </ | ||
+ | < | ||
+ | < | ||
+ | <prop key=" | ||
+ | <!-- for debug, put these values to " | ||
+ | <prop key=" | ||
+ | <prop key=" | ||
+ | |||
+ | <!-- | ||
+ | Possible values for this are: | ||
+ | | ||
+ | the first time it requires it and release it when it's | ||
+ | destroyed | ||
+ | | ||
+ | session is released after Transaction issues a commit or | ||
+ | rollback | ||
+ | | ||
+ | connection is released after execution of every statement, | ||
+ | BUT it will keep the connection open if the object | ||
+ | | ||
+ | <!-- prop key=" | ||
+ | </ | ||
+ | </ | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | I've put plenty of comments of useful configuration parameters and lots of information, | ||
+ | |||
+ | ===== Resources ===== | ||
+ | |||
+ | http:// | ||
+ | |||
+ | |||
+ | ~~DISQUS~~ |