com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operati

MySQL报异常:

1:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

2:org.hibernate.exception.JDBCConnectionException: The last packet successfully received from the server was 74,359,500 milliseconds ago.  The last packet sent successfully to the server was 74,359,515 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

 

解决方案在报错信息中已经很明确了:

1:增加服务器端对客户端的连接超时时间

<1>:Linux系统下的配置文件为/etc/my.cnf,windows中的my.ini,单位为秒,默认为28800秒,即8小时

# 服务器关闭交互式连接前等待活动的秒数

interactive_timeout=28800

# 服务器关闭非交互连接之前等待活动的秒数

wait_timeout=28800

设置完重启.

<2>:在命令行里设置:

会话变量设置/查询:
show variables;

show variables like 'wait_timeout';
select @@session.wait_timeout;

set wait_timeout=10;
set session wait_timeout=10;
----------------------------------------------------------

全局变量设置/查询:
set global wait_timeout=60;
show global variables like 'wait_timeout'
select @@global.wait_timeout;
-------------------------------------------------
interactive_timeout 也一样设置和查询;

 

 

2:在使用连接前验证连接(tomcat jdbc pool属性设置)

<!-- 数据源配置,使用应用内的Tomcat JDBC连接池 -->
		<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
			<!-- Connection Info -->
			<property name="driverClassName" value="${jdbc.driver}" />
			<property name="url" value="${jdbc.url}" />
			<property name="username" value="${jdbc.username}" />
			<property name="password" value="${jdbc.password}" />
			<!-- 初始化连接数,默认为10  -->
			<property name="initialSize" value="${jdbc.initialSize}"/>
			<!-- 没有可用连接时,抛出异常前最长等待时间(单位:毫秒,默认30000/30秒) -->
			<property name="maxWait" value="${jdbc.maxWait}"/>
			<!-- 同一时间可从连接池中分配的最大连接数 -->
			<property name="maxActive" value="${jdbc.maxActive}" />
			<!-- 连接池中应保持的最小连接数,默认和initialSize的值一样 -->
			<property name="minIdle" value="${jdbc.minIdle}"/>
			<!-- 连接池中应保持的最大连接数 -->
			<property name="maxIdle" value="${jdbc.maxIdle}" />
			<!-- 运行"检查已经超时/废弃的连接"的线程频率(毫秒/次),默认为5000/5秒 -->
			<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
			<!-- 连接超时的最小时间(单位:毫秒,默认为60000/60秒) -->
			<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />
			<!-- 取连接前使用validationQuery验证,验证失败则从连接池中去除且尝试取出另一个 -->
			<property name="testOnBorrow" value="${jdbc.testOnBorrow}"/>
			<!-- 当连接被放回到连接池中时,使用validationQuery验证连接,无效则去除 -->
			<property name="testOnReturn" value="${jdbc.testOnReturn}"/>
			<!-- 每隔timeBetweenEvictionRunsMillis时长使用validationQuery去验证非活动的连接 -->
			<property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
			<!-- 验证的SQL -->
			<property name="validationQuery" value="${jdbc.validationQuery}"/>
			<!-- 验证SQL超时时间 -->
			<property name="validationQueryTimeout" value="${jdbc.validationQueryTimeout}"/>
			<!-- 连接最大存活时间,0为禁用此选项 -->
			<property name="maxAge" value="${jdbc.maxAge}"></property>
			<!-- 不默认提交 -->
			<property name="defaultAutoCommit" value="false" />
		</bean>

 

 

3:增加在mysql连接url字符串后面增加连接失败后自动重新连接属性:autoReconnect=true,详见mysql-5.6-en.pdf官方文档.

例如:jdbc.url=jdbc:mysql://localhost:3306/SHAM?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true&autoReconnect=true