Symptoms

The following appears in the atlassian-confluence.log:

2009-03-17 09:39:46,951 FATAL [http-9080-13] [user.provider.jdbc.JDBCCredentialsProvider] handles Could not see if [<Username>] 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)

Diagnosis

This applies to the following condition:

• Using MySQL database
• Using JIRA User management
• When logging into Confluence, the users are randomly/intermittently denied entry with an invalid username/password error. The same user can log in successfully into JIRA at the same time
• The user can be locked out/denied login intermittently, with the time ranging from a few minutes to up to 2 hours

Troubleshooting

1. Checked that the ?autoconnect=true parameter has been set for both Confluence and Jira connection URL, but the problem persists
2. This is not a session timeout problem, as the user should be allowed to log in after the first login for the day
3. Adding the Validation query parameter, to help survive closed DB connections, does not help

Cause

MySQL connections are getting terminated by MySQL before they have been released by the connection pool. Then the next time you try to get a connection from the pool, you'll get a dead connection which will give a "broken pipe" error, as noted in the stack trace above.

Resolution

Evict the idle session before MySQL does. Retrying the connection will simply create a new connection. The following parameters can be added to the JIRA datasource connection URL in Confluence's server.xml for the same:

minEvictableIdleTimeMillis=21600000 (evict after 6 hours of inactivity, given that MySQL has a default timeout of 8 hours for idle database connections after which it closes the connection)
timeBetweenEvictionRunsMillis=1800000 (run the evicter every 30 minutes)
numTestsPerEviction=-1 (check every connection)