Connection auto-healing issue

[Chrys4lisfag]

It seems like there is the some case when the connection to the database is ruined and it never auto-heals,

It is related to broken ssl stream (bad internet connection) on my specific test PC, maybe this exception does not trigger disconnected state due to occurring on specific request and is treated like only it fails, but in fact from that moment the whole connection is ruined.

All my future requests will fail. Reconnection does not happen.

The exception I get:
Decryption failed or bad record mac (SSL routines) [asio.ssl:167772441]
If I restart the app everything is fine but without restart recon never happens.

If some more verbose logs needed I can provide them.

[anarthal]

Hi! Thanks for reporting.

Are you using return_without_reset(), or returning your connections using pooled_connection destructor?

[Chrys4lisfag]

return_without_reset

Yes, I use con.return_without_reset();

[anarthal]

The symptoms you're describing are compatible with calling return_without_reset unconditionally. You should call it only if the operation that you executed finished successfully, and you know that it didn't modify session state anyhow (e.g. you didn't leave an uncommitted transaction).

Once you return a connection to the pool using pooled_connection destructor, the pool will issue a request to the server to reset the connection. This is lightweight, but cleans up any leftovers (e.g. uncomitted transactions) and ensures that the connection is healthy.

This check is completely skipped when you use return_without_reset. Essentially, you're telling the pool "this connection works and I didn't mutate session state, so it's safe to skip the reset". It's an optimization, but should only be applied when you know that the connection is OK.

TL;DR: can you please avoid calling return_without_reset on error and see if it solves the problem?

Thanks,
Ruben.

[anarthal]

Reference:

https://www.boost.org/doc/libs/latest/libs/mysql/doc/html/mysql/connection_pool.html#mysql.connection_pool.connection_lifecycle

Also, #254 is a proposal to make this work even if you use return_without_reset. I'm not convinced that this is a good idea tho- imagine that you got an error in the middle of a transaction (e.g. because of a duplicate unique constraint violation). This is not a network error, but you should definitely not call return_without_reset here, or subsequent operations will end up in a transaction they weren't supposed to be.

[Chrys4lisfag]

I forgot to state that I have all operations wrapped in try catch so con.return_without_reset(); only called in try and never called in catch.
And ofc I dont use ec for completion token when await`ing ops, so they throw on errors

[anarthal]

I see. Then please provide any extra logs you have, as I don't see any obvious reason for this. Boost version, compiler version and compiler flags could be useful.

Also, could you please double-check that the issue happens under -fsanitize=address and the sanitizer log is clean? Just to rule out possibilities.

Thanks,
Ruben.

[Chrys4lisfag]

Seems like I found the the explanation to this behavior. I have thread pool of 50 connections by default and when the connection breaks all these connection do not automatically heal in the background. Seems like healing happens only on the next request (which will cause query error and only after that the connection will reconnect).

As I have pool of 50 connections, every connection gets broken and every request it does:
-> pop broken con -> try query -> throw error due to this conn disconnected -> reconnect this con
-> pop next con which is broken and so on

So seems like two bugs:

1. Autohealing triggers only on the next query only on the con where we execute query
2. Con popping pops dead cons (probably issue with states ) even tho there is alive cons.

I could easily repeat this behavior by running single query every 3s in coro and reconnecting to my VPN to simulate network down.

with single connection in pool i get -> pops dead con -> throws error -> next query ok (autoheal). With 50 cons it has to pop every con in the list before query succeeds.

using conn.return_without_reset() or removing does not matter. If you need, I can create repo with test code, just tell me

[anarthal]

Thanks for your explanation. Yes, connection healing only happens when a request is executed, because that's the only way to know whether a connection is healthy or not.

The connection pool has a health check feature, where all connections will ping the server when they have been unused for some time. If the ping isn't answered, connection heal occurs. This time can be configured using pool_params::ping_interval. By default, it is 1h (which is probably an unfortunate default on my side). Could you please try changing it to a more reasonable value (like 5s) and see the effect? Doing this should allow connections to detect a dead server and heal within a maximum of 5s, without you executing any requests on your side.

Would this be satisfactory for your use case? I can look into changing this default for the next release.

Thanks,
Ruben.

[Chrys4lisfag]

That solved the issue and I am totally fine with this solution as I understand that the only other way to beat this issue without pining is to constantly run network reader which will require to rework a lot and complicate some things.

Thanks for the help!