CommunicationsException when using cursor-based fetch of large dataset
Hi folks,
We are using Connector/J version 5.1 in our app to read a large number of rows (~7M) and fairly large dataset (~27 GB). We were hitting timeouts with streaming results row-by-row:
val conn = DriverManager.getConnection(
connectionConfig.connectUrl.toStr,
connectionConfig.userName.toStr,
connectionConfig.password.toStr
)
val stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
val rs: ResultSet = stmt.executeQuery(selectQuery)
The error we received:
The last packet successfully received from the server was 4,311 milliseconds ago. The last packet sent successfully to the server was 4,311 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3434)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3334)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3774)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432)
at com.twitter.scalding_internal.db.jdbc.JdbcToHdfsCopier$$anonfun$2.apply(JdbcToHdfsCopier.scala:65)
at com.twitter.scalding_internal.db.jdbc.JdbcToHdfsCopier$$anonfun$2.apply(JdbcToHdfsCopier.scala:51)
at scala.util.Success$$anonfun$map$1.apply(Try.scala:237)
at scala.util.Try$.apply(Try.scala:192)
at scala.util.Success.map(Try.scala:237)
at com.twitter.scalding_internal.db.jdbc.JdbcToHdfsCopier$.apply(JdbcToHdfsCopier.scala:51)
... 76 more
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:2926)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3344)
... 89 more
So we switched to using cursor-based fetch by providing the useCursorFetch=true config property and setting fetch size = 50.
stmt.setFetchSize(50)
This has eliminated the EOFExceptions, which we thought was due to the fact that we are now making requests to the database more frequently to fetch results using the database cursor. However, we started seeing a new error:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 900,055 milliseconds ago. The last packet sent successfully to the server was 900,055 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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3434)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3334)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3774)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1288)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:794)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1193)
at com.mysql.jdbc.StatementImpl.createResultSetUsingServerFetch(StatementImpl.java:666)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1394)
... 76 more
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:210)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2923)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3344)
... 90 more
This was observed when issuing select statements for smaller chunks of our data (~2 million rows at a time).
Since it is a shared MySQL cluster used at our company, I don't believe changing the server-side configuration 'wait_timeout' is an option for us. We also tried to add the autoReconnect=true parameter to the connection string, but the errors still occasionally occur.
Are there some debugging steps we could take here to understand the cause? Does cursor based fetch not issue several requests to the database that should prevent us from hitting the timeout? Should we be issuing even more requests in smaller chunks so that we reduce the risk of hitting this timeout?
I'm also wondering if autoReconnect is doing anything here. With cursor fetch, if a request fails and the driver attempts to reconnect with the next request, does it try to re-run the existing SELECT statement with cursor fetch, or will it try to fetch the next set of data after advancing the cursor? I am not sure I have observed either behavior, but definitely want to avoid dropping data in case of connection failure.
Thank you,
Mark