[feature]Supports Multiple host configuration.

[tabdelbari]

Is your feature request related to a problem? Please describe. The probleme is that tve driver doesn't support multiple host configuration

Describe the solution you'd like support multiple hosts configuration, for example: r2dbc:mysql//host1,host2/db

[jchrys]

Hello, @tabdelbari. Thank you for bringing this feature request to our attention. I'll be taking up the task to work on implementing multiple host configurations.

Considering that the R2DBC SPI already supports multiple hosts, it would be beneficial for our project to have this feature as well.

Similar requests have been made in the past, and there's a clear demand for this feature

• https://github.com/mirromutth/r2dbc-mysql/issues/169
• https://github.com/mirromutth/r2dbc-mysql/issues/183

[jchrys]

reference

• https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-multi-host-connections.html
• https://r2dbc.io/spec/1.0.0.RELEASE/spec/html/#overview.connection.url

[tabdelbari]

Thank you so much @jchrys

[mirromutth]

It requires following tasks:

1. Connection strategy when creating the connection
   • https://dev.mysql.com/doc/connector-j/en/connector-j-multi-host-connections.html
   • https://mariadb.com/kb/en/failover-and-high-availability-with-mariadb-connector-j
2. DNS SRV Records support: https://dev.mysql.com/doc/refman/8.0/en/connecting-using-dns-srv.html
3. Reconnection strategy: autoReconnect/maxReconnects/reconnectAtTxEnd/initialTimeout option. It should be reconnected only if the connection is in auto-commit mode (not in a transaction).
4. If secondaryBeforeRetrySource or requestsBeforeRetrySource is set, the default (no name) high availability mode will attempt to return the first host/address even if no communication exception occurs
5. retriesAllDown option or alternative. If the connection fails for all hosts/addresses, whether to retry them all

These we should not consider:

• Replication mode: io.r2dbc.spi.Connection cannot be set to a read-only connection, so this mode will always use the first host/address
• failOverReadOnly option: same reason as above
• autoReconnectForPools option: force reconnect even the connection is in a transaction or autoCommit=false. Not sure it is necessary
• retriesAllDown default value 120: Too big? The interval between all retries is 250ms, so it takes at least 30s to fail