警告SqlExceptionHelper：143 - SQL错误：0，SQLState：08S01- SqlExceptionHelper：144 - 通信链接失败 [英] WARN SqlExceptionHelper:143 - SQL Error: 0, SQLState: 08S01- SqlExceptionHelper:144 - Communications link failure

查看：3076
本文介绍了警告SqlExceptionHelper：143 - SQL错误：0，SQLState：08S01- SqlExceptionHelper：144 - 通信链接失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有Hibernate的问题（ hibernate-core-4.1.9.Final.jar ）

当我在for循环中测试hibernate时所有工作正常： 

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

当我在 Thread.sleep（）中添加for循环

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

1分钟后我收到异常： 

  00：20：06,447 WARN SqlExceptionHelper：143  -  SQL错误：0，SQLState：08S01 
 00：20：06,448错误SqlExceptionHelper：144  - 通信链接失败
 
从服务器成功接收到的最后一个数据包是120,017毫秒前。成功发送到服务器的最后一个数据包是9毫秒前。 
线程main中的异常org.hibernate.exception.JDBCConnectionException：通信链接失败
 
从服务器成功接收的最后一个数据包是120,017毫秒前。成功发送到服务器的最后一个数据包是9毫秒前。 
 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）
 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）
 org.hibernate.loader.Loader.getResultSet（Loader.java:2031）
 at org.hibernate.loader.Loader .executeQueryStatement（Loader.java:1832）
在org.hibernate.loader.Loader.executeQueryStatement（Loader.java:1811）
在org.hiber nate.loader.Loader.doQuery（Loader.java:899）
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections（Loader.java:341）
 at org.hibernate.loader.Loader.doList（ Loader.java:2516）
在org.hibernate.loader.Loader.doList（Loader.java:2502）
在org.hibernate.loader.Loader.listIgnoreQueryCache（Loader.java:2332）$ b $ org.hibernate.loader.Loader.list（Loader.java:2327）$ b $ org.hibernate.loader.criteria.CriteriaLoader.list（CriteriaLoader.java:124）
 org.hibernate .internal.SessionImpl.list（SessionImpl.java:1621）
在org.hibernate.internal.CriteriaImpl.list（CriteriaImpl.java:374）
在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）
导致：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException：通信链接失败
 
最后一个数据包成功r从服务器上获得的是120,017毫秒前。成功发送到服务器的最后一个数据包是9毫秒前。 
 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。 （com.mysql.jdbc.MysqlIO.reuseAndReadPacket（MysqlIO.java:3603）
在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）$ b $ 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 
引起：java.io.EOFException：无法读取服务器的响应。预计会读取4个字节，在连接意外丢失之前读取0个字节。 
 at com.mysql.jdbc.MysqlIO.readFully（MysqlIO.java:3052）
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket（MysqlIO.java:3503）
 ... 29更多

这是我的 hibernate.cfg.xml 

 <？xml version ='1.0'encoding ='UTF-8'？> 
<！DOCTYPE hibernate-configuration PUBLIC 
 -  // Hibernate / Hibernate配置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>更新< / 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 class =com.xxx.model.xxxxx/> 
< mapping class =com.xxx.model.xxxxx/> 
< mapping class =com.xxx.model.xxxxx/> 
 
< / session-factory> 
< / hibernate-configuration>

如何解决此问题？

更新：
我想我解决了问题。



我把这个放在hibernate.cfg.xml中。

 <！ - 通过c3p0的配置池 - > 
< property name =c3p0.acquire_increment> 1< / property> 
< property name =c3p0.idle_test_period> 100< / property> <！ - 秒 - > 
< 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> <！ - 秒 - >


解决方案

$ c> time_out var在mysql服务器上。



在我的情况下，time_out被设置为1分钟。
使用C3PO池化机制，我们可以优化JDBC。



下载 c3p0 - > http://sourceforge.net/projects/c3p0/



我正在使用 hibernate 3.0 。


$ b hibernate.cfg.xml 

 <？xml version ='1.0'encoding ='UTF-8'？> 
<！DOCTYPE hibernate-configuration PUBLIC 
 -  // Hibernate / Hibernate配置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>更新< / property> 
 
< property name =show_sql> false< / property> 
 
 <！ -  
 hibernate c3p0设置
  - > 
 
< 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 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; 
导入org.hibernate.cfg.Configuration; 
 
 public class PersistenceManager 
 {
 private static SessionFactory sessionFactory = null; 
 
 private static PersistenceManager singleton = null; 
 $ b $ public static PersistenceManager getInstance（）
 {
 if（singleton == null）
 {
 singleton = new PersistenceManager（）; 
} 
 
返回单身人士; 
 
 $ b $ public SessionFactory getSessionFactory（）
 {
 if（sessionFactory == null）
 createSessionFactory（）; 
 return sessionFactory; 
 
 
 protected void createSessionFactory（）
 {
 sessionFactory = new AnnotationConfiguration（）。configure（）
 .buildSessionFactory（）; 
 
 $ b $ public void destroySessionFactory（）
 {
 if（sessionFactory！= null）
 {
 sessionFactory.close（）; 
 sessionFactory = null; 
 
 
 
 code 
 $ b $ p $例子1：   
 
 
  import org.hibernate.HibernateException; 
 import org.hibernate.Query; 
 import org.hibernate.Session; 
 
 public Users Login（String username，String password）
 {
 Session session = null; 
尝试
 {
 String hql =从用户u中选择u，其中u.username如：p1和u.password，如：p2; 
 session = PersistenceManager.getInstance（）。getSessionFactory（）。openSession（）; 
 
 Query q = session.createQuery（hql）
 .setParameter（p1，username）
 .setParameter（p2，password）; 
 
 if（q.list（）。size（）== 0）
 {
 session.close（）; 
返回新用户（）; 
} 
 
用户user =（用户）q.list（）。get（0）; 
 session.close（）; 
返回用户; 
} 
 catch（Exception e）
 {
 session.close（）; 
 
 
 
 
 $ b 
 示例2： 
 
 $  
  import org.hibernate.HibernateException; 
 import org.hibernate.Query; 
 import org.hibernate.Session; 
 public String Registration（Users u）{
 
 Session session = null; 
尝试
 {
 String hql =从用户u中选择u，其中u.username像：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（）; 
返回新的布尔值（true）.toString（）; 
} 
 
 session.close（）; 
 
 return new Boolean（false）.toString（）; 
} 
 catch（Exception e）
 {
 return e.toString（）; 
} 
} 
  
 
I have problem with Hibernate (hibernate-core-4.1.9.Final.jar)



When i testing hibernate in  for loop all working fine:
for(int i=0;i<1500;i++){

    UserDAO.getInstance.getById(1);
}

When i add Thread.sleep() in for loop
 for(int i=0;i<1500;i++){


        UserDAO.getInstance.getById(1);
        Thread.sleep(60000);
 }

After 1 minute i get 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 resolve this problem?



UPDATE:
I think i solve 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 --> 



 解决方案 
The problem was happening because of small value time_out var on mysql server.



In my situation time_out was set to 1 minute.
Using C3PO pooling mechanism we can optimize JDBC.



Download c3p0 -> http://sourceforge.net/projects/c3p0/



I'm using hibernate 3.0.



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();
    }
  }



                        
这篇关于警告SqlExceptionHelper：143  -  SQL错误：0，SQLState：08S01- SqlExceptionHelper：144  - 通信链接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆