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.

Sign up for GitHub

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

Jump to bottom

R2dbcNonTransientResourceException: Connection validation failed #209

Open
menakaprabu opened this issue Apr 5, 2024 · 13 comments
Open

R2dbcNonTransientResourceException: Connection validation failed #209

menakaprabu opened this issue Apr 5, 2024 · 13 comments

Comments

@menakaprabu
Copy link

Hi,
I am getting this issue "org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection nested exception. Caused by io.r2dbc.spi.R2dbcNonTransientResourceException: Connection validation failed" in MSSQL. I am using below libraries(Version).

io.r2dbc r2dbc-pool 1.0.0.RELEASE io.r2dbc r2dbc-mssql 1.0.0.RELEASE I am seeing this issue in both pre-prod and production environment intermittently. Could you please help me with this? Thanks.
@mp911de
Copy link
Member

mp911de commented Apr 9, 2024

Is there more to the stack trace? Typically, the cause shows the reason for the validation failure.

@menakaprabu
Copy link
Author

menakaprabu commented Apr 9, 2024 via email

@MBecM
Copy link

MBecM commented Apr 10, 2024 •
edited
Loading

Maybe I will add more information, because I'm fighting with the same problem for the last couple of days.

Versions

  • Driver: r2dbc-postgres
  • Database: postgres
  • Java: 21
  • io.r2dbc:r2dbc-spi:1.0.0.RELEASE
  • io.r2dbc:r2dbc-pool:1.0.1.RELEASE

Stacktrace

io.r2dbc.spi.R2dbcNonTransientResourceException: Connection validation failed
  at io.r2dbc.pool.Validation.lambda$validate$2(Validation.java:45)
  at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:179)
  at reactor.core.publisher.MonoSupplier$MonoSupplierSubscription.request(MonoSupplier.java:145)
  at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.request(FluxHandleFuseable.java:260)
  at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onSubscribe(MonoIgnoreElements.java:72)
  at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onSubscribe(FluxHandleFuseable.java:164)
  at reactor.core.publisher.MonoSupplier.subscribe(MonoSupplier.java:48)

Pool configuration

default

Additional context

This issue happens in my case 2-3 times a day in different day time. It is totally random when will it happen. What is more interesting, that after some time (10-20sec) everything goes back to normal work. So it "heals" itself, but during this 10-20sec non of my queries are executed. During my investigation I found that validation is performed 2 times.

  1. During connectionFactory.create()

    r2dbc-pool/src/main/java/io/r2dbc/pool/ConnectionPool.java

    Line 135 in 3ee034a

    conn = getValidConnection(allocateValidation, connection);
  2. After query during connectionFactory.close()

    r2dbc-pool/src/main/java/io/r2dbc/pool/PooledConnection.java

    Line 66 in 3ee034a

    return Validation.validate(this, ValidationDepth.LOCAL).then(Mono.defer(() -> {

In my case it happens only on 1 case, that's why query is not executed, because connection is not acquired from connection pool. Stacktrace is also not very helpful, because it points only to 45 line in Validation.class

r2dbc-pool/src/main/java/io/r2dbc/pool/Validation.java

Line 45 in 3ee034a

sink.error(new R2dbcNonTransientResourceException("Connection validation failed"));

Steps to reproduce

Restart database server between queries.

  1. Start application
  2. Execute query
  3. Restart database server: sudo pg_ctl restart / Ubuntu: sudo pg_ctlcluster 14 main restart
  4. Execute query (should FAIL with provided stacktrace)

Related issues

#138
#107

Possible Solution
The only way to mitigate this problem I found was to increase default acquireRetry property from 1 to 10, which is maxSize value.

In that case the following Mono<Connection> create will retry until it find valid connection.

r2dbc-pool/src/main/java/io/r2dbc/pool/ConnectionPool.java

Line 172 in 3ee034a

this.create = configuration.getAcquireRetry() > 0 ? create.retry(configuration.getAcquireRetry()) : create;

But I'm afraid that this only hides/delays the problem and makes it even more unpredictable.
@mp911de correct me if I'm wrong. With my solution, connection pool will try 10 times to acquire connection until it founds valid connection, so basically "searches" through entire pool to find what it needs. I assume that if none of connection is valid then this exception would be fired.

@menakaprabu
Copy link
Author

menakaprabu commented Apr 10, 2024 via email

@menakaprabu
Copy link
Author

Hi @mp911de ,

Do you have any update for me. This issue is acting like a blocker, please let me know if you need more details from me.
-Thanks.

@menakaprabu
Copy link
Author

menakaprabu commented Apr 30, 2024 via email

@CennyMo
Copy link

CennyMo commented Jun 24, 2024

@MBecM we met same problem and decided to change max retryTimes to 10. It seems that when r2dbc pool get an invalid connection and validate query failed, the connection will be invalidated:

src/main/java/io/r2dbc/pool/ConnectionPool.java 140:
conn = conn.onErrorResume(throwable -> ref.invalidate().then(Mono.error(throwable)));

So theoretically all the connections will recovery in the end. Have you encountered any other problems after changing it to 10?

@CennyMo
Copy link

CennyMo commented Jun 24, 2024

@menakaprabu
Under what circumstances would this issue be triggered in your scenario? Restart database or just suddenly break down when system running in long term?
We always met this problem when databases was crashed, and the fault will disappear as time and new requests increase.
But if database is normal, considering set the maxLifeTime(Connection will never timeout when maxLifeTime is not configured) and test if it's useful. Cause it's possibly that sql server release the connection, however client still use it.

@CennyMo
Copy link

CennyMo commented Jul 8, 2024 •
edited
Loading

@menakaprabu Under what circumstances would this issue be triggered in your scenario? Restart database or just suddenly break down when system running in long term? We always met this problem when databases was crashed, and the fault will disappear as time and new requests increase. But if database is normal, considering set the maxLifeTime(Connection will never timeout when maxLifeTime is not configured) and test if it's useful. Cause it's possibly that sql server release the connection, however client still use it.

2024/07/08 update. We solve our problem by setting minIdle to 0.

@menakaprabu
Copy link
Author

Hi @CennyMo , We are getting this issue when database is normal. We are having below config.
poolInitialSize=10
poolMaxSize=30
maxIdleTime=60 mins (1hr)
I don't have maxLifeTime and minIdle set.

@CennyMo
Copy link

CennyMo commented Jul 9, 2024 •
edited
Loading

Hi @CennyMo , We are getting this issue when database is normal. We are having below config. poolInitialSize=10 poolMaxSize=30 maxIdleTime=60 mins (1hr) I don't have maxLifeTime and minIdle set.

I suggest config maxLifeTime to 20min, or smaller, cause database server also has a "timeout" when it holds a connection established from client, in mysql(we use mysql now), it's 30min. You can check this config in MSSql too.
The maxLifeTime config in client should always be smaller than the timeout config of database server.

As for maxIdleTime, in fact, the actual idleTime of connection is calculated by System.currentMills() - latest active timestamp, and the latest active timestamp of connection is refreshed in every time it has been used. So, if your pool is always busy, your connection may never idle, and it will never be evicted.

You can also set minIdle to 0 to prevent broken connections from remaining unhandled.

@Abdulkhakimov
Copy link

All suggested pool configuration tunings didn't help. Any suggestions?

@JohnNiang JohnNiang mentioned this issue Oct 16, 2024
Closed
@rputhiyapurayil
Copy link

The fail over functionality in r2dbc mariadb is not working when we add connection pooling. It is working without that. Could you please confirm

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
6 participants
@mp911de @rputhiyapurayil @MBecM @menakaprabu @CennyMo @Abdulkhakimov