Add Option to set Netty ConnectionProvider directly

[paul-kraftlauget]

Feature Request

Please add the possibility to configure Netty's ConnectionProvider directly

Is your feature request related to a problem? Please describe

I have experienced this issue when using the r2dbc-mssql client:

io.netty.channel.unix.Errors$NativeIoException: readAddress(..) failed: Connection reset by peer

I believe this is the same problem as described on: reactor/reactor-netty#1774
In the comments, configuring the ConnectionProvider in such a way solves this issue (indeed, we had this same problem for WebClient and the suggested ConnectionProvider configuration solved the exceptions occurring in the Netty framework).

Describe the solution you'd like

Add the option to set the ConnectionProvider:

ConnectionProvider provider = ConnectionProvider.builder("fixed")
    .maxConnections(500)
    .maxIdleTime(Duration.ofSeconds(20))
    .maxLifeTime(Duration.ofSeconds(60))
    .pendingAcquireTimeout(Duration.ofSeconds(60))
    .evictInBackground(Duration.ofSeconds(120))
    .build();

ConnectionFactoryOptions options = builder()
    .option(DRIVER, "sqlserver")
    ...
    .connectionProvider(CONNECTION_PROVIDER, connectionProvider)
    .build();

Describe alternatives you've considered

It is very difficult to configure this without being supported due to the package-private and final classes in the library. I have managed to add classes in my project located in the io.r2dbc.mssql package to expose and set the connection provider, but the solution is really quite hacky.

I'll create a PR to add the option.

Teachability, Documentation, Adoption, Migration Strategy

Documentation will be included in the pull request

[mp911de]

Thanks for reaching out. If I understand correctly, the linked Reactor Netty ticket discusses a problem when using pooled connections that are broken but the broken connection goes unnoticed until it is being actually used.

The driver here uses ConnectionProvider.newConnection() that isn't pooled so each time you connect you get a fresh connection. While configuring ConnectionProvider can be useful when not using R2DBC Pool, I doubt it fixes Connection reset by peer.

From what I've seen, Connection reset by peer happens either caused by network components terminating traffic or connection resets caused by TCPSND/TCPRECV buffer overflows on the SQL server if a connection has too much ongoing concurrent requests.

I'd like to understand whether the changes actually fix something.

[paul-kraftlauget]

Thank you for your insight. Yes, I have perhaps been trying to solve this incorrectly. We indeed use the R2DBC pool.

Do you have a recommended connection pool configuration for solving this in the same way as the Netty pool configuration (max-idle, max-life-time, eviction-interval)? With WebClient, when we used the suggested config, the whole problem went away and everything just works, no errors at all.

Any insights why the r2dbc pooling would be better to use than netty connection pooling? From an API perspective, I can understand that perhaps not all r2dbc drivers use netty, so it provides a common way to configure connection pools, but could there be applications that benefit from netty pooling instead? I'd be curious if using netty instead of r2dbc pooling would solve issues like #273 which we have experienced as well.