csvn项目采用grails开发,自定义数据库连接为mysql,之后出现连接数据库的狂刷问题,日志一天高达40多G,问题原因为:
mysql如果连接超过8小时,即8小时内没有进行数据库之间的操作,mysql就会断开连接,而grails程序必须连接才可以。
(注:查看mysql超时设置:show global variables like '%timeout%';,设置 set global wait_timeout=28800;(8个小时))
最后修改如下:grails-app/conf/DataSource.groovy中,修改
dataSource { pooled = true driverClassName =selfConfig.driverClassName username = selfConfig.DbUsername password = selfConfig.DbPassword dbCreate="none" }
为:
dataSource { pooled = true driverClassName =selfConfig.driverClassName username = selfConfig.DbUsername password = selfConfig.DbPassword dbCreate="none" properties { maxActive = -1 //同一时间可以从池分配的最多连接数量。设置为0时表示无限制。 minEvictableIdleTimeMillis=1800000 //连接保持空闲而不被驱逐的最长时间。 timeBetweenEvictionRunsMillis=1800000 //毫秒秒检查一次连接池中空闲的连接, numTestsPerEvictionRun=3 testOnBorrow=true //表示从连接池中获取连接前是否运行validationQuery,true=运行[默认],false=不运行 testWhileIdle=true testOnReturn=true //表示将连接归还连接池前是否运行validationQuery,true=运行,false=不运行[默认]; validationQuery="SELECT 1" } }
其他信息为:
hibernate { cache.use_second_level_cache=true cache.use_query_cache=true //cache.provider_class='net.sf.ehcache.hibernate.EhCacheProvider' //cache.provider_class='net.sf.ehcache.hibernate.SingletonEhCacheProvider' cache.provider_class='net.sf.ehcache.hibernate.SingletonEhCacheProvider' }
environments { development { dataSource { // url = "jdbc:mysql://10.2.5.83:3306/public" url = selfConfig.dataSourceUrl } }
...
}
报错信息:
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 86,385,398 milliseconds ago. The last packet sent successfully to the server was 86,385,401 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.
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3851)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2471)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2310)
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:96)
at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3780)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.retryExecuteInNonManagedTXLock(JobStoreSupport.java:3742)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggeredJobComplete(JobStoreSupport.java:3039)
at org.quartz.core.QuartzScheduler.notifyJobStoreJobComplete(QuartzScheduler.java:1804)
at org.quartz.core.JobRunShell.run(JobRunShell.java:269)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3832)
... 13 more