The pool returns an invalid connection after client idle for a while

[Nossiac]

I'm using vertx-mysql-postgresql-client version 3.8.3.

My client works fine in the beginning. But when the client keeps idle for a while, I obtain an invalid connection from the client.

AIK, Mysql server will close those long idle connections by force. So I guess the client returned a cached connection which is actually invalid since it was already reset by the server. Any query on that connection will raise an exception. Just try getConnection again, a new connection is returned successfully.

Is there any built-in tech to overcome this issue? or, should I handle such exeception on my own?

I post my conf and code below. Did I miss something?

I configured my client as below:

        JsonObject dbConfig = new JsonObject();
        dbConfig.put("host", "xxxx");
        dbConfig.put("port", 3306);
        dbConfig.put("maxPoolSize", 5);
        dbConfig.put("username", "xxxx");
        dbConfig.put("password", "xxxx");
        dbConfig.put("database", "xxxx");
        dbConfig.put("maxConnectionRetries", 10);
        dbConfig.put("connectionRetryDelay", 500);
        dbPool = MySQLClient.createShared(vertx, dbConfig, "mysql-connection-pool");

and query data like this:

    public void execute(String sql, JsonArray params, Handler<AsyncResult<JsonObject>> handler) {
        dbPool.getConnection(res -> {
            if (res.succeeded()) {
                SQLConnection connection = res.result();
                log.debug("connection: {} sql: {}, params: {}", connection, sql, params);
                connection.queryWithParams(sql, params, qryRes -> { // Here I got an exception!
                    if (qryRes.succeeded()) {
                        ResultSet resultSet = qryRes.result();
                        log.info(resultSet.getRows().toString());
                        handler.handle(Future.succeededFuture(new JsonObject().put("data", resultSet.getRows())));
                    } else {
                         handler.handle(Future.failedFuture(qryRes.cause().getMessage()));
                    }
                    connection.close();
                });
            } else {
                handler.handle(Future.failedFuture(res.cause().getMessage()));
            }
        });
    }

[Nossiac]

Just reproduce it, here's the exception log.

java.io.IOException: 远程主机强迫关闭了一个现有的连接。 , the chinese part means "the remote server has reset an existing connection by force".

2019-10-21 15:39:26,082 [vert.x-eventloop-thread-1] DEBUG c.n.j.j.database.DatabaseServiceImpl - connection: io.vertx.ext.asyncsql.impl.MySQLConnectionImpl@46944971 sql: SELECT t_user.id, t_user.username WHERE t_user.id = ? , params: [5240098559942534998]
2019-10-21 15:39:45,291 [vert.x-eventloop-thread-6] ERROR c.g.m.async.db.mysql.MySQLConnection - Transport failure 
java.io.IOException: 远程主机强迫关闭了一个现有的连接。
    at java.base/sun.nio.ch.SocketDispatcher.read0(Native Method)
    at java.base/sun.nio.ch.SocketDispatcher.read(SocketDispatcher.java:43)
    at java.base/sun.nio.ch.IOUtil.readIntoNativeBuffer(IOUtil.java:276)
    at java.base/sun.nio.ch.IOUtil.read(IOUtil.java:233)
    at java.base/sun.nio.ch.IOUtil.read(IOUtil.java:223)
    at java.base/sun.nio.ch.SocketChannelImpl.read(SocketChannelImpl.java:358)
    at io.netty.buffer.UnpooledDirectByteBuf.setBytes(UnpooledDirectByteBuf.java:587)
    at io.netty.buffer.AbstractByteBuf.writeBytes(AbstractByteBuf.java:1140)
    at io.netty.buffer.SwappedByteBuf.writeBytes(SwappedByteBuf.java:828)
    at io.netty.channel.socket.nio.NioSocketChannel.doReadBytes(NioSocketChannel.java:347)
    at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:148)
    at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:697)
    at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:632)
    at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:549)
    at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:511)
    at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:918)
    at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
    at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
    at java.base/java.lang.Thread.run(Thread.java:835)
2019-10-21 15:39:45,297 [vert.x-eventloop-thread-6] DEBUG [connection-handler][mysql-connection-1] - Channel became inactive
2019-10-21 15:39:45,297 [vert.x-eventloop-thread-1] ERROR c.n.j.j.database.DatabaseServiceImpl - 远程主机强迫关闭了一个现有的连接。

[trongtb88]

Do we have any fix on this, pro?

[gutmox]

Hi, any news about this issue?

[Kaller-LTD]

+1

[vietj]

you should use the Vertx SQL client instead of this client that is fully supported by the team.

We will continue to accept PR for this project and review them though.