Geek Answers Handbook

WARN SqlExceptionHelper: 143 - SQL Error: 0, SQLState: 08S01- SqlExceptionHelper: 144 - Communication Failure

I have a problem with Hibernate ( hibernate-core-4.1.9.Final.jar )

When I test hibernate in for loop, everything works fine:

for(int i=0;i<1500;i++){ UserDAO.getInstance.getById(1); }

When I add Thread.sleep () for the loop

  for(int i=0;i<1500;i++){ UserDAO.getInstance.getById(1); Thread.sleep(60000); }

After 1 minute, I get an exception:

 00:20:06,447 WARN SqlExceptionHelper:143 - SQL Error: 0, SQLState: 08S01 00:20:06,448 ERROR SqlExceptionHelper:144 - Communications link failure The last packet successfully received from the server was 120,017 milliseconds ago. The last packet sent successfully to the server was 9 milliseconds ago. Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Communications link failure The last packet successfully received from the server was 120,017 milliseconds ago. The last packet sent successfully to the server was 9 milliseconds ago. at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) at sun.proxy.$Proxy11.executeQuery(Unknown Source) at org.hibernate.loader.Loader.getResultSet(Loader.java:2031) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811) at org.hibernate.loader.Loader.doQuery(Loader.java:899) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) at org.hibernate.loader.Loader.doList(Loader.java:2516) at org.hibernate.loader.Loader.doList(Loader.java:2502) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332) at org.hibernate.loader.Loader.list(Loader.java:2327) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:124) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1621) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374) at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) at com.fit.utilities.BaseDAO.getById(BaseDAO.java:35) at com.fit.test.Testiranje.main(Testiranje.java:51) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 120,017 milliseconds ago. The last packet sent successfully to the server was 9 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:411) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ... 17 more Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503) ... 29 more

Here is my hibernate.cfg.xml

 <?xml version='1.0' encoding='UTF-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://xxxxxx:3306/xxxx</property> <property name="connection.username">xxxx</property> <property name="connection.password">xxxx</property> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <property name="hibernate.hbm2ddl.auto">update</property> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <property name="show_sql">false</property> <property name="hibernate.connection.pool_size">1</property> <!-- Mapping files --> <mapping class="com.xxx.model.xxxxx" /> <mapping class="com.xxx.model.xxxxx" /> <mapping class="com.xxx.model.xxxxx" /> </session-factory> </hibernate-configuration>

How to solve this problem?

UPDATE: I think I'm solving the problem.

I put this in hibernate.cfg.xml

  <!-- configuration pool via c3p0--> <property name="c3p0.acquire_increment">1</property> <property name="c3p0.idle_test_period">100</property> <!-- seconds --> <property name="c3p0.max_size">100</property> <property name="c3p0.max_statements">0</property> <property name="c3p0.min_size">10</property> <property name="c3p0.timeout">100</property> <!-- seconds -->
+5
mysql hibernate
source share
2 answers

The error occurs due to the small value of the time_out variable on the mysql server. In fact, since the jdbc connection is expiring.

In my situation, time_out is set to 1 minute. Using the C3PO join mechanism, we can optimize the JDBC connection.

You can download c3p0 libs from http://sourceforge.net/projects/c3p0/

I am using hibernate 3.0 .

Here is the code:

hibernate.cfg.xml

 <?xml version='1.0' encoding='UTF-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://databasehost:3306/databasename</property> <property name="connection.username">user</property> <property name="connection.password">psw</property> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <property name="hibernate.hbm2ddl.auto">update</property> <property name="show_sql">false</property> <!-- hibernate c3p0 settings --> <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> <property name="hibernate.c3p0.acquire_increment">3</property> <property name="hibernate.c3p0.idle_test_period">10</property> <property name="hibernate.c3p0.min_size">5</property> <property name="hibernate.c3p0.max_size">75</property> <property name="hibernate.c3p0.max_statements">10</property> <property name="hibernate.c3p0.timeout">50</property> <property name="hibernate.c3p0.preferredTestQuery">select 1</property> <property name="hibernate.c3p0.testConnectionOnCheckout">true</property> <!-- Mapping files --> <mapping class="xxx.xxx.xxx.xxx" /> <mapping class="xxx.xxx.xxx.xxx" /> <mapping class="xxx.xxx.xxx.xxx" /> <mapping class="xxx.xxx.xxx.xxx" /> </session-factory> </hibernate-configuration>

PersistenceManager.java

 import java.io.PrintStream; import org.hibernate.SessionFactory; import org.hibernate.cfg.AnnotationConfiguration; import org.hibernate.cfg.Configuration; public class PersistenceManager { private static SessionFactory sessionFactory = null; private static PersistenceManager singleton = null; public static PersistenceManager getInstance() { if (singleton == null) { singleton = new PersistenceManager(); } return singleton; } public SessionFactory getSessionFactory() { if (sessionFactory == null) createSessionFactory(); return sessionFactory; } protected void createSessionFactory() { sessionFactory = new AnnotationConfiguration().configure() .buildSessionFactory(); } public void destroySessionFactory() { if (sessionFactory != null) { sessionFactory.close(); sessionFactory = null; } } }

Example 1:

 import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; public Users Login( String username, String password) { Session session = null; try { String hql = "select u from Users u where u.username like :p1 and u.password like :p2"; session = PersistenceManager.getInstance().getSessionFactory().openSession(); Query q = session.createQuery(hql) .setParameter("p1", username) .setParameter("p2", password); if (q.list().size() == 0) { session.close(); return new Users(); } Users user = (Users)q.list().get(0); session.close(); return user; } catch (Exception e) { session.close(); } }

Example 2:

 import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; public String Registration(Users u) { Session session = null; try { String hql = "select u from Users u where u.username like :p1"; session = PersistenceManager.getInstance().getSessionFactory().openSession(); Query q = session.createQuery(hql).setParameter("p1", u.getUsername()); if (q.list().size() == 0) { session.beginTransaction(); session.persist(u); session.getTransaction().commit(); session.close(); return new Boolean(true).toString(); } session.close(); return new Boolean(false).toString(); } catch (Exception e) { return e.toString(); } }
+10
source share

I had the same problem just because I forgot to bundle my sleeping code in a transaction. Apparently, the connection to the database does not close until you complete the transaction. Here is an example:

 Session session = HibernateUtils.getSession(); Transaction tx = session.beginTransaction(); Category cat = null; try{ cat = (Category)session.get(Category.class, 1); tx.commit(); }catch(Exception ex){ tx.rollback(); }finally{ session.close(); } return cat;
+2
source share

More articles:


All Articles