Login After a Long Inactivity on Confluence Fails with 'com.mysql.jdbc.CommunicationsException Communications link failure' CommunicationsException Error
症状
When using MySQL, the first person that tries to login to confluence in the morning can't (after a 8 hour period of inactivity). After about 15 minutes, everybody can login without problem. The following appears in the atlassian-confluence.log:
2007-10-11 09:01:33,925 FATAL [TP-Processor8] [user.provider.jdbc.JDBCCredentialsProvider] handles Could not see if [fver] is handled
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Broken pipe
STACKTRACE:
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2692)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2621)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at com.opensymphony.user.provider.jdbc.JDBCCredentialsProvider.handles(JDBCCredentialsProvider.java:131)
at bucket.user.providers.ChainedUserProvider.handles(ChainedUserProvider.java:125)
原因
MySQL's JDBC drivers usually close a connection that remains idle for a certain amount of time (normally eight hours). Since Confluence uses a connection pool, this means that pooled connections will be terminated if they are not used within a certain time period.
ソリューション
Using Direct JDBC connection
Append '?autoReconnect=true' to the end of your database's JDBC URL (without the quotes).
Using a Tomcat data source connection
The Commons DBCP (Database Connection Pool) which is used by the Tomcat application server can validate connections (by running a simple SQL query) before issuing them, and if a broken connection is detected, a new one is created to replace it. To do this you will need to set the "validationQuery" option on the database connection pool.
Add the 'validationQuery' and other parameter when configuring the datasource in the catalina/localhost/confluence.xml file (or <confluence install>/conf/server.xml if using standalone installation):
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>20</value>
</parameter>
<parameter>
<name>timeBetweenEvictionRunsMillis</name>
<value>300000</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
<parameter>
<name>testWhileIdle</name>
<value>true</value>
</parameter>
If you are using JNDI connection please add the following lines into your Resource:
maxActive="20"
validationQuery="select 1"
removeAbandonedTimeout="60"
testWhileIdle=true
timeBetweenEvictionRunsMillis=300000