MySQL connection creation should not have default behavior

[daiguadaidai]

Version: 0.6.2

mysql connection create have default behavior.
default execute

SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION')),time_zone='+00:00',NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

The above settings will change the default behavior of the database, which is undesirable for programmers and DBAs. From the perspective of DBAs, the default parameters of the database are optimized and fixed Programmers only need to use the default one You can customize it if you really need it For example, use after_connect function.

Describe the solution you'd like
You can add new function to provide options Whether to use default behavior.
such as

        let pool = MySqlPoolOptions::new()
            .max_connections(4)
            .min_connections(0)
            .connect_timeout(Duration::from_secs(5))
            .max_lifetime(Duration::from_secs(3600))
            .idle_timeout(Duration::from_secs(600))
            // .after_connect_use_default_behavior(true) // you can add new function, like this
            .after_connect(|conn| {
                Box::pin(async move {
                    let _ = conn
                        .execute("SET time_zone='SYSTEM'; SET system_time_zone='CST'")
                        .await;
                    Ok(())
                })
            })
            .connect("mysql://HH:oracle12@127.0.0.1:3306/employees")
            .await?;

[srinivasmohan]

+1 for this ({ version = "0.7.1", features = ["mysql", "runtime-tokio-rustls"] } ) - while setting 'SET SQL_MODE=(SELECT CONCAT(@@SQL_MODE, ',NO_ENGINE_SUBSTITUTION')),TIME_ZONE='+00:00',NAMES... appears sane for most mysqld, its not ideal for things that look like mysql but are not (e.g. backends using mysqld proto but not a full fledged mysqld server itself so may not parse all such sql). It would be nice to be able to turn this off as needed (but retain as default the current behaviour).

[abonander]

All of these options are set by default for specific reasons:

• PIPES_AS_CONCAT

Makes MySQL behave closer to ANSI SQL which is necessary for sane operation of the Any driver. Why we don't just set the ANSI combination mode, I'm not sure. I vaguely recall that we set some of the related options but then had to disable them for compatibility with third-party databases.

Either way, we already have an option to disable this: https://docs.rs/sqlx-mysql/0.7.2/sqlx_mysql/struct.MySqlConnectOptions.html#method.pipes_as_concat

• NO_ENGINE_SUBSITUTION

This is a great sanity check because if you go so far as to choose a specific storage engine for a table, you'd probably be really annoyed if MySQL chose a different one instead of telling you it doesn't have the one you requested.

It's also set by default in MySQL 8, so it appears that the maintainers of MySQL agree with this sentiment. Thus, it's also good to set this for consistency between versions.

• time_zone='+00:00'

This is required for the sane encoding and decoding of timestamps (chrono::DateTime<Utc>, time::OffsetDateTime), otherwise they'll be in whatever time zone is set on the server.

We're strongly of the persuasion that time-zone conversions should be handled at the presentation layer and not in the data layer of an application. Otherwise, that's a breeding ground for bugs. UTC is love, UTC is life.

For manual management, set the timezone in whatever client you use to access your database.

• NAMES utf8mb4

This ensures that the server accepts and returns string values (including statements themselves) in UTF-8, otherwise we'd have to support all the different character sets that MySQL supports and check which one is set on startup.

• COLLATE utf8mb4_unicode_ci

Specifying utf8mb4_unicode_ci as the default collation... that one I'm personally not married to, but it's a relatively sane default.

As for third-party databases, none of these options is particularly obscure. I personally believe that if a vendor is going to advertise "compatible with the MySQL/PostgreSQL/etc. protocol" then not supporting a feature of the that protocol is more their problem than ours. Managing idiosyncrasies with third-party vendors like that is a massive game of whack-a-mole that I'm getting tired of playing. Sometimes it gets rather ridiculous, like not properly supporting prepared statements.

[srinivasmohan]

@abonander - I completely agree that these are useful for standard mysqlds (whatever variant). However, in a large controlled environment (like ours), these can be enforced in other ways in a managed client. I understand what sqlx developers want to do here. However, it needs to account for that fact that there will be non-standard endpoints to connect to (e.g. binlog servers which may only implement a subset of mysql set sql_mode options and purely for replication related activities; opaque to "end user" connections) and thats what I am asking for an exclusion for. The exclusion here being a Mysql Options flag that lets us skip this exec of sets (https://github.com/launchbadge/sqlx/blob/6ecb5831f49bbc5b1b693c1b223a2a3b7e346b69/sqlx-mysql/src/options/connect.rs#L49-64) with the caveat that "i know what I am doing coz I really don't need this". With the default being to apply these always.

[abonander]

I would prefer to provide setters for individual options. Which of those are not supported?

[srinivasmohan]

I would prefer to provide setters for individual options. Which of those are not supported?

None at all (no sql here, just binary binlogs). so "SET SQL_MODE..." is never handled (which is why I was requesting a global do-not-attempt-set-sql-mode option). Issue with per option setter is that if a new option is added, then it could break (for us) connections rather dramatically while global flag in conn options will not. I can send a PR if you prefer.

[abonander]

closed by #3037