Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
Nossiac opened this issue Oct 21, 2019 · 5 comments
Open

Comments

@Nossiac
Copy link

Nossiac commented Oct 21, 2019

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
Copy link
Author

Nossiac commented Oct 21, 2019

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
Copy link

Do we have any fix on this, pro?

@gutmox
Copy link

gutmox commented Jan 22, 2021

Hi, any news about this issue?

@Kaller-LTD
Copy link

+1

@vietj
Copy link
Contributor

vietj commented Jul 27, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

No branches or pull requests

5 participants