jdbc error with mysql backend database even with autoReconnect=true

[Etienne Grignon]

Hello Rundeck Community,

I m running rundeck 1.5.2 with a mysql backend. I m using the following jdbc settings.

mysql://localhost:3306/rundeck?autoReconnect=true&autoCommit=true

However, I m experiencing the the following error in the web interface:

"Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost."

In the log file, I m getting the following stackstrace:

        at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:539)

        at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)

        at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:405)

        at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)

        at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2540)

        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2990)

        ... 123 more

ERROR GrailsExceptionResolver: Exception occurred when processing request: [GET] /jobs

Stacktrace follows:

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2540)

        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2990)

        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)

        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4997)

        at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)

        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)

        at $Proxy7.setAutoCommit(Unknown Source)

        at rundeck.services.UserService$$EnhancerByCGLIB$$b12774bf.findOrCreateUser(<generated>)

        at rundeck.services.UserService$findOrCreateUser.call(Unknown Source)

        at rundeck.controllers.MenuController$_closure9.doCall(MenuController.groovy:134)

        at rundeck.controllers.MenuController$_closure9.call(MenuController.groovy)

Is there any other setting to enable on the rundeck side?

The connectivity is good between the rundeck server and the mysql database after testing using mysql client and making tcpdump traces on both sides.

Thanks,

Etienne

[Etienne Grignon]

The fix was to add some settings to jdbc datasource properties:

Found on the thread:

https://groups.google.com/forum/#!msg/rundeck-discuss/R-2tHGXQzk0/gqFzeZ6WOLsJ

Just setting the autoReconnect=true parameter wasn't enough for me. I still experienced exceptions after the database connection timed out. I ended up setting the following properties in rundeck-config.properties and that appears to have worked for me: 

dataSource.properties.minEvictableIdleTimeMillis=1800000 
dataSource.properties.timeBetweenEvictionRunsMillis=1800000 
dataSource.properties.numTestsPerEvictionRun=3 
dataSource.properties.testOnBorrow=true 
dataSource.properties.testWhileIdle=true 
dataSource.properties.validationQuery=SELECT 1