Session Initialization SQL for Shared Hosting

[caleblloyd]

A user of a shared hosting MySQL service may wish to set default values in their session that are different than the default values of the server. A shared hosting user permission level typically will only allow them to run a SET @@session command, which must be run on every session

MySqlConnector could support this by adding either:

1. A Connection String option with Session variables to set
   a. If the option was a string of SQL, putting SET @@session.sql_mode = 'TRADITIONAL in the string would look odd and we'd need to come up with an easy way to escape the string
   b. Creating an option for every @@session variable in the connection string would be quite work intensive
2. A helper utility function that allows the user to associate a SQL Initialization String with a Connection String
   a. This would look something like SessionInitUtil.setSessionInitSql(connectionString, "SET @@session.sql_mode = 'TRADITIONAL")
   b. This seems cleaner than putting it in the connection string, but still feels kind of hacky

The initialization code could go right after the session is created:

MySqlConnector/src/MySqlConnector/MySql.Data.MySqlClient/MySqlConnection.cs

Line 171 in d35322f

m_session = await CreateSessionAsync(ioBehavior, cancellationToken).ConfigureAwait(false);

[bgrainger]

If ADO.NET exposed a connection pooling API (dotnet/corefx#26714), setting a property on the pool could be a natural to specify this information. For now, it seems like the connection string would have to be used, since that's the only piece of information passed to MySqlConnection.

we'd need to come up with an easy way to escape the string

This should be done by using a property on MySqlConnectionStringBuilder (same issue for escaping special characters in passwords).

[bgrainger]

There is a good suggestion in this SO answer: add an event handler to Connection.StateChange to execute the command when e.CurrentState == ConnectionState.Open. (This will be called right after the line of code @caleblloyd identified, so the effect will be the same as if MySqlConnector handled it directly.)

I think this can be solved adequately in user code and doesn't need to be part of this library.