After switching our JSF-Hibernate application to a MySQL backend we are severely suffering problems with the connection. We use a c3p0 connection pool which starts to log broken pipe errors after 1 to 1.5 hours of operation.
I have setup connection testing which works fine according to the logs, but there appears to be something else. Here is a piece of my logs:
17:37:13,203 DEBUG GooGooStatementCache:457 - cxnStmtMgr.statementSet( com.mysql.jdbc.ConnectionImpl@c5dbb ).size(): 17
17:37:13,205 DEBUG GooGooStatementCache:196 - checkoutStatement: com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 42; checked out: 1; num connections: 3; num keys: 42
17:37:13,207 DEBUG NewPooledConnection:430 - com.mchange.v2.c3p0.impl.NewPooledConnection@1ddc3ea handling a throwable.
com.mysql.jdbc.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)
...
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
... 81 more
17:37:13,211 DEBUG DefaultConnectionTester:126 - Testing a Connection in response to an Exception:
com.mysql.jdbc.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)
...
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
... 81 more
17:37:13,213 DEBUG DefaultConnectionTester:80 - Connection com.mysql.jdbc.ConnectionImpl@c5dbb failed Connection test with an Exception! [query=SELECT 1]
com.mysql.jdbc.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)
...
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
... 85 more
17:37:13,217 DEBUG NewPooledConnection:449 - com.mchange.v2.c3p0.impl.NewPooledConnection@1ddc3ea invalidated by Exception.
com.mysql.jdbc.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
...
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
... 81 more
17:37:13,219 WARN NewPooledConnection:486 - [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
17:37:13,221 WARN NewPooledConnection:487 - [c3p0] Another error has occurred [ com.mysql.jdbc.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago. ] which will not be reported to listeners!
com.mysql.jdbc.CommunicationsException: Communications link failure
Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)
...
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1818)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
... 76 more
17:37:13,223 DEBUG GooGooStatementCache:271 - checkinStatement(): com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 42; checked out: 0; num connections: 3; num keys: 42
17:37:13,225 WARN JDBCExceptionReporter:77 - SQL Error: 0, SQLState: 08S01
17:37:13,226 ERROR JDBCExceptionReporter:78 - Communications link failure
Last packet sent to the server was 0 ms ago.
These errors do not show within the first hour of my app operation, but after that randomly, sometimes once in an hour, sometimes every 5 minutes. As a result, the requested page shows an error.
Is there any way that I can solve this problem? I have looked at this issue for 4 days but was unable to solve this one :((
Thanks a lot
Steven