Lost connection to MySQL server during query

对于这个MySQL报的异常百度一般解决办法是修改max_allowed_packet和wait_timeout的值，但是也有很多时候不是这两个属性引起的，来看我遇到的情况。

先贴出程序里抛出的异常：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1304)
	at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1296)
	at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:5028)
	at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:328)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:312)
	at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:213)
	at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:192)
	... 93 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 260 milliseconds ago.  The last packet sent successfully to the server was 123 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	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:3673)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4113)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2324)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
	at org.hibernate.loader.Loader.doQuery(Loader.java:802)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
	at org.hibernate.loader.Loader.doList(Loader.java:2533)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
	at org.hibernate.loader.Loader.list(Loader.java:2271)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:921)
	at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:912)
	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
	at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
	at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)
	at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904)
	at com.pcitc.itemonitor.manage.nemonitor.dao.NeMonitorDaoImpl.getStaticValue(NeMonitorDaoImpl.java:161)
	at com.pcitc.itemonitor.manage.nemonitor.server.NeMonitorServiceImpl.getStaticValue(NeMonitorServiceImpl.java:80)
	at com.pcitc.itemonitor.manage.nemonitor.server.NeMonitorServiceImpl$$FastClassByCGLIB$$d2f22621.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	... 88 more
Caused by: java.net.SocketTimeoutException: Read timed out
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.read(SocketInputStream.java:152)
	at java.net.SocketInputStream.read(SocketInputStream.java:122)
	at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
	at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
	at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3573)
	... 121 more

意思就是与数据库连接关闭了，紧接着就是数据读取超时。于是将sql贴到SQLyog执行，就报出以下错误：

select 
	*
FROM
	table
> 2013 - Lost connection to MySQL server during query
> 时间: 0.077s

意思也是连接关闭了，经过一番百度，就是上面说的修改了一番max_allowed_packet和wait_timeout的值也没好。

最后换了一台服务器与数据库进行连接发现好了，不报错了。

经过反思发现根本问题大概是这样产生的：

1. 公司所有服务器都是内网运行，不同区域的服务器相互隔离，互联需要开墙。
2. 数据库和连接数据库的服务器在不同区域，所以需要开墙。
3. 有些服务器之间连接不稳定，网络抖动严重，sql执行时间稍长或者数据包稍大导致连接奔溃（重点）。

所以如果有和我类似问题的小伙伴可以考虑是不是服务器的问题，换一台服务器连接数据库看看问题是否消失。