标题:mysql, wait_timeout, java报错:Could not open JPA EntityManager for transaction

发表于

坑爹的阿里云mysql数据库,设置的wait_timeout只有90秒,导致tomcat java连接数据库频繁报错:

java.lang.RuntimeException: org.apache.jasper.JasperException: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is java.lang.IllegalStateException: Transaction already active

步骤:
1、检查,在mysql中执行:show global variables like "%timeout%";
得到结果

1 connect_timeout 15
2 delayed_insert_timeout 300
3 innodb_lock_wait_timeout 50
4 innodb_rollback_on_timeout OFF
5 interactive_timeout 28800
6 net_read_timeout 30
7 net_write_timeout 60
8 slave_net_timeout 3600
9 table_lock_wait_timeout 50
10 wait_timeout 90

2、在应用的配置文件applicationContext.xml中修改以下配置,把

<property name="timeBetweenEvictionRunsMillis" value="1200000" />
<property name="minEvictableIdleTimeMillis" value="1200000" />
<property name="testWhileIdle" value="true" />
<property name="validationQuery" value="select 1" />

修改为

<property name="timeBetweenEvictionRunsMillis" value="89000" />
<property name="minEvictableIdleTimeMillis" value="89000" />
<property name="testWhileIdle" value="true" />
<property name="validationQuery" value="select 1" />
<property name="testOnBorrow" value="true" />

参考资料:
https://stackoverflow.com/questions/20556172/could-not-roll-back-jpa-transaction-javax-persistence-persistenceexception-une
https://blog.csdn.net/wangfayinn/article/details/24623575