Consider providing an option to not re-authenticate on OpenConnection()

[jkuek]

Is your feature request related to a problem? Please describe.

I'm running MySqlConnector 2.4.0 against Aurora MySQL. I have a database cluster with multiple databases. Some queries span multiple databases.

Pooling is used to reduce the number of reconnects.

As mentioned in #1534, when using pooling with connection reset set to true, every call to OpenConnection() triggers a re-authentication. This behaviour is deliberate, to ensure that the selected database is in a default state after OpenConnection().

However, this additional re-authentication adds additional time and load on the database server, and some systems (e.g. Aurora MySQL using IAM DB authentication) have a restriction on authentication requests/sec.

A reset of the default selected database (to that defined by the connection string) seems to me to be mostly unnecessary. In a single database scenario, the default database is not changing. In a multi-database scenario where each database represents a tenant of a multi-tenant platform, then either

• there will be a pool for each tenant, with a database specified in the connection strings (i.e. the default database is unlikely to change)
• common pool for all tenants, where no database is specified in the connection string. In this case the default database can change but the application uses ChangeDatabase(tenant) as appropriate

Describe the solution you'd like
I have struggled to think of why it's necessary to reset the default selected database in most cases, but I do not propose making a breaking change. Rather, a configuration option could be added to set whether a connection reset involves a re-authentication or not.

This could be an extension of the existing ConnectionReset (expanding the options to true/false/authenticate/no-authenticate) or a new setting (ConnectionResetAuthentication = true/false)

Any risks presented by enabling this option can be easily mitigated at design-time by ensuring all queries include the database name with the table name, or by calling ChangeDatabase() after each OpenConnection(). Any new users migrating from MySql.Data are unaffected, given its default behaviour is not to re-authenticate.

Another option would be to add an argument to OpenConnection() to specify the database, i.e OpenConnection(string databaseName). If provided, this would skip re-authentication and instead internally call ChangeDatabase() before returning.

A third option is to replace the existing re-auth with an internal ChangeDatabase(), using the default database from the connection string.

Describe alternatives you've considered
Disabling ConnectionReset by setting it to false has been considered. This has other potential side-effects, as other session states are not cleared.

Additional context
I note that the MySQL API recognises the usefulness for resetting a connection with and without re-authentication, providing the mysql_reset_connection() and mysql_change_user() functions respectively.

MySql.Data does not share this same issue, as it does not reauthenticate during connection reset (but does on a change user). ref:
https://dev.mysql.com/doc/c-api/8.4/en/mysql-reset-connection.html
https://dev.mysql.com/doc/c-api/8.4/en/mysql-change-user.html