Java SQL 错误：0，SQLState：08S01 通信链接失败

SQL Error: 0, SQLState: 08S01 Communications link failure

提问by spring_dev101

I am getting this error (not so frequently):

我收到此错误（不那么频繁）：

2013-05-08 16:44:35,786  WARN (JDBCExceptionReporter.java:100) [org.hibernate.util.JDBCExceptionReporter, logExceptions] - SQL Error: 0, SQLState: 08S01
2013-05-08 16:44:35,786 ERROR (JDBCExceptionReporter.java:101) [org.hibernate.util.JDBCExceptionReporter, logExceptions] - Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
2013-05-08 16:44:35,833 ERROR (AdminDaoImpl.java:297) [com.myapp.admin.db.AdminDaoImpl, createFilePackage] - data-upload: Exception while adding new file package
org.hibernate.exception.JDBCConnectionException: Cannot open connection
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354) [hibernate-core-3.3.2.GA.jar:3.3.2.GA]
    at com.myapp.admin.db.AdminDaoImpl. createFilePackage(AdminDaoImpl.java:182) [AdminDaoImpl.class:na]
    at com.myapp.admin.ctrl.UploadController.processUploadZip(UploadController.java:217) [UploadController.class:na]
    at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source) [na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [na:1.6.0_35]
    at java.lang.reflect.Method.invoke(Method.java:597) [na:1.6.0_35]
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212) [spring-web-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126) [spring-web-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789) [spring-webmvc-3.1.0.RELEASE.jar:3.1.0.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647) [servlet-api.jar:na]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) [servlet-api.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) [catalina.jar:7.0.35]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.35]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) [catalina.jar:7.0.35]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) [catalina.jar:7.0.35]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) [catalina.jar:7.0.35]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) [catalina.jar:7.0.35]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) [catalina.jar:7.0.35]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) [catalina.jar:7.0.35]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) [catalina.jar:7.0.35]
    at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200) [tomcat-coyote.jar:7.0.35]
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) [tomcat-coyote.jar:7.0.35]
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310) [tomcat-coyote.jar:7.0.35]
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [na:1.6.0_35]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [na:1.6.0_35]
    at java.lang.Thread.run(Thread.java:662) [na:1.6.0_35]
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I have two web (spring mvc + hibernate + mysql) applications hosted on shared server:

我在共享服务器上托管了两个 Web（spring mvc + hibernate + mysql）应用程序：

1) main app to show files for download (which is inserted through admin app)

1) 显示要下载的文件的主应用程序（通过管理应用程序插入）

2) admin app with CRUD operations

2) 带有 CRUD 操作的管理应用程序

This error appears sometime, when I am using "admin" application to upload files for "main" app, lets say may be after successfully uploading/processing around 100-120 files/requests and on each request hitting database with 10-20 insert/update queries.

有时会出现此错误，当我使用“管理员”应用程序为“主”应用程序上传文件时，可以说可能是在成功上传/处理大约 100-120 个文件/请求之后，并且在每个请求中使用 10-20 个插入/更新查询。

Hosting Details:

托管详情：

Private tomcat hosting
java 1.6.0_35-b10
tomcat 7.0.35
heap size 288MB
MySQL Server 5
commons-dbcp 1.2.2
mysql-connector-java 5.1.9

Database + Hibernate + DBCP properties:

数据库 + Hibernate + DBCP 属性：

<bean id="dbDataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="username" value="test" />
    <property name="password" value="test" />
    <property name="url" value="jdbc:mysql://localhost/my_database?useUnicode=yes&characterEncoding=UTF-8" />

    <!-- DBCP Connection Pool Settings -->
    <property name="defaultAutoCommit" value="true" />
    <property name="initialSize" value="10" />
    <property name="maxActive" value="500" />
    <property name="maxIdle" value="8" />
    <property name="minIdle" value="0" />
    <property name="maxWait" value="30000" />
    <property name="validationQuery" value="SELECT 1" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="false" />
    <property name="testWhileIdle" value="false" />
    <property name="timeBetweenEvictionRunsMillis" value="-1" />
    <property name="numTestsPerEvictionRun" value="3" />
    <property name="minEvictableIdleTimeMillis" value="180000" />
    <property name="poolPreparedStatements" value="true" />
    <property name="maxOpenPreparedStatements" value="25" />
    <property name="accessToUnderlyingConnectionAllowed" value="false" />
    <property name="removeAbandoned" value="false" />
    <property name="removeAbandonedTimeout" value="300" />
    <property name="logAbandoned" value="false" />
</bean>

hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.hbm2ddl.auto=none
hibernate.jdbc.batch_size=100
hibernate.show_sql=false
hibernate.format_sql=false
hibernate.cache.use_second_level_cache=true
hibernate.cache.use_query_cache=false
hibernate.cache.provider_class=org.hibernate.cache.NoCacheProvider
hibernate.current_session_context_class=thread

Concerned Code:

相关代码：

public String createFilePackage(FilePackage package) {
    String message = "";

    Session session = getSessionFactory().openSession();
    Transaction tx = null;
    try {
        tx = session.beginTransaction();

        // handling package, around 10-14 DB insert, 10 SELECT and 5 update queries on same session

        tx.commit();

    } catch(Exception e) {
        logger.error("file-upload: Exception while adding new font package", e);
        if(tx != null) { tx.rollback(); }
    } finally {
        session.close();
    }
    return message;
}

any advise how to solve this issue?

任何建议如何解决这个问题？

采纳答案by Maciej Cygan

The communication link between the driver and the data source to which the driver was attempting to connect failed before the function completed processing. So usually its a network error. This could be caused by packet drops or badly configured Firewall/Switch.

在函数完成处理之前，驱动程序和驱动程序试图连接的数据源之间的通信链接失败。所以通常是网络错误。这可能是由于数据包丢失或防火墙/交换机配置不当造成的。

回答by morajp2000

Check your server config file /etc/mysql/my.cnf- verify bind_addressis not set to 127.0.0.1. Set it to 0.0.0.0or comment it out then restart server with:

检查您的服务器配置文件/etc/mysql/my.cnf- 验证bind_address未设置为127.0.0.1. 将其设置为0.0.0.0或将其注释掉，然后使用以下命令重新启动服务器：

sudo service mysql restart

回答by Arthur

Could be due to the TCP protocol turned off.

可能是因为关闭了 TCP 协议。

How to check/enable: https://dba.stackexchange.com/questions/11377/cannot-connect-to-ms-sql-2008-r2-by-dbvisualizer-native-sspi-library-not-loade/144097#144097

如何检查/启用：https: //dba.stackexchange.com/questions/11377/cannot-connect-to-ms-sql-2008-r2-by-dbvisualizer-native-sspi-library-not-loade/144097# 144097