Crate mysql_async

Expand description

Tokio based asynchronous MySql client library for The Rust Programming Language.

§Installation

The library is hosted on crates.io.

[dependencies]
mysql_async = "<desired version>"

§Crate Features

By default there are only two features enabled:

flate2/zlib — choosing flate2 backend is mandatory
derive — see “Derive Macros” section in mysql_common docs

§List Of Features

minimal – enables only necessary features (at the moment the only necessary feature is flate2 backend). Enables:
- `flate2/zlib“
Example:
```
[dependencies]
mysql_async = { version = "*", default-features = false, features = ["minimal"]}
```
minimal-rust - same as minimal but with rust-based flate2 backend. Enables:
- flate2/rust_backend
default – enables the following set of features:
- flate2/zlib
- derive
default-rustls – default set of features with TLS via rustls/aws-lc-rs

default-rustls-ring – default set of features with TLS via rustls/ring

Example:

[dependencies]
mysql_async = { version = "*", default-features = false, features = ["default-rustls"] }

native-tls-tls – enables TLS via native-tls

Example:

[dependencies]
mysql_async = { version = "*", default-features = false, features = ["minimal", "native-tls-tls"] }

rustls-tls - enables rustls TLS backend with no provider. You should enable one of existing providers using aws-lc-rs or ring features:

Example:
```
[dependencies]
mysql_async = { version = "*", default-features = false, features = ["minimal-rust", "rustls-tls", "ring"] }
```
tracing – enables instrumentation via tracing package.

Primary operations (query, prepare, exec) are instrumented at INFO level. Remaining operations, incl. get_conn, are instrumented at DEBUG level. Also at DEBUG, the SQL queries and parameters are added to the query, prepare and exec spans. Also some internal queries are instrumented at TRACE level.

Example:
```
[dependencies]
mysql_async = { version = "*", features = ["tracing"] }
```
binlog - enables binlog-related functionality. Enables:
- `mysql_common/binlog“

§Proxied features (see `mysql_common`` fatures)

derive – enables mysql_common/derive feature
chrono = enables mysql_common/chrono feature
time = enables mysql_common/time feature
bigdecimal = enables mysql_common/bigdecimal feature
rust_decimal = enables mysql_common/rust_decimal feature
frunk = enables mysql_common/frunk feature

§TLS/SSL Support

SSL support comes in two flavors:

Based on native-tls – this is the default option, that usually works without pitfalls (see the native-tls-tls crate feature).
Based on rustls – TLS backend written in Rust (see the rustls-tls crate feature).

Please also note a few things about rustls:
- it will fail if you’ll try to connect to the server by its IP address, hostname is required;
- it, most likely, won’t work on windows, at least with default server certs, generated by the MySql installer.

§Connection URL parameters

There is a set of url-parameters supported by the driver (see documentation on Opts).

§Example

use mysql_async::prelude::*;

#[derive(Debug, PartialEq, Eq, Clone)]
struct Payment {
    customer_id: i32,
    amount: i32,
    account_name: Option<String>,
}

#[tokio::main]
async fn main() -> Result<()> {
    let payments = vec![
        Payment { customer_id: 1, amount: 2, account_name: None },
        Payment { customer_id: 3, amount: 4, account_name: Some("foo".into()) },
        Payment { customer_id: 5, amount: 6, account_name: None },
        Payment { customer_id: 7, amount: 8, account_name: None },
        Payment { customer_id: 9, amount: 10, account_name: Some("bar".into()) },
    ];

    let database_url = /* ... */

    let pool = mysql_async::Pool::new(database_url);
    let mut conn = pool.get_conn().await?;

    // Create a temporary table
    r"CREATE TEMPORARY TABLE payment (
        customer_id int not null,
        amount int not null,
        account_name text
    )".ignore(&mut conn).await?;

    // Save payments
    r"INSERT INTO payment (customer_id, amount, account_name)
      VALUES (:customer_id, :amount, :account_name)"
        .with(payments.iter().map(|payment| params! {
            "customer_id" => payment.customer_id,
            "amount" => payment.amount,
            "account_name" => payment.account_name.as_ref(),
        }))
        .batch(&mut conn)
        .await?;

    // Load payments from the database. Type inference will work here.
    let loaded_payments = "SELECT customer_id, amount, account_name FROM payment"
        .with(())
        .map(&mut conn, |(customer_id, amount, account_name)| Payment { customer_id, amount, account_name })
        .await?;

    // Dropped connection will go to the pool
    drop(conn);

    // The Pool must be disconnected explicitly because
    // it's an asynchronous operation.
    pool.disconnect().await?;

    assert_eq!(loaded_payments, payments);

    // the async fn returns Result, so
    Ok(())
}

§Pool

The Pool structure is an asynchronous connection pool.

Please note:

Pool is a smart pointer – each clone will point to the same pool instance.
Pool is Send + Sync + 'static – feel free to pass it around.
use Pool::disconnect to gracefuly close the pool.
⚠️ Pool::new is lazy and won’t assert server availability.

§Transaction

Conn::start_transaction is a wrapper, that starts with START TRANSACTION and ends with COMMIT or ROLLBACK.

Dropped transaction will be implicitly rolled back if it wasn’t explicitly committed or rolled back. Note that this behaviour will be triggered by a pool (on conn drop) or by the next query, i.e. may be delayed.

API won’t allow you to run nested transactions because some statements causes an implicit commit (START TRANSACTION is one of them), so this behavior is chosen as less error prone.

§`Value`

This enumeration represents the raw value of a MySql cell. Library offers conversion between Value and different rust types via FromValue trait described below.

§`FromValue` trait

This trait is reexported from mysql_common create. Please refer to its crate docs for the list of supported conversions.

Trait offers conversion in two flavours:

from_value(Value) -> T - convenient, but panicking conversion.

Note, that for any variant of Value there exist a type, that fully covers its domain, i.e. for any variant of Value there exist T: FromValue such that from_value will never panic. This means, that if your database schema is known, than it’s possible to write your application using only from_value with no fear of runtime panic.

Also note, that some convertions may fail even though the type seem sufficient, e.g. in case of invalid dates (see sql mode).
from_value_opt(Value) -> Option<T> - non-panicking, but less convenient conversion.

This function is useful to probe conversion in cases, where source database schema is unknown.

§MySql query protocols

§Text protocol

MySql text protocol is implemented in the set of Queryable::query* methods and in the prelude::Query trait if query is prelude::AsQuery. It’s useful when your query doesn’t have parameters.

Note: All values of a text protocol result set will be encoded as strings by the server, so from_value conversion may lead to additional parsing costs.

§Binary protocol and prepared statements.

MySql binary protocol is implemented in the set of exec* methods, defined on the prelude::Queryable trait and in the prelude::Query trait if query is QueryWithParams. Prepared statements is the only way to pass rust value to the MySql server. MySql uses ? symbol as a parameter placeholder.

Note: it’s only possible to use parameters where a single MySql value is expected, i.e. you can’t execute something like SELECT ... WHERE id IN ? with a vector as a parameter. You’ll need to build a query that looks like SELECT ... WHERE id IN (?, ?, ...) and to pass each vector element as a parameter.

§Named parameters

MySql itself doesn’t have named parameters support, so it’s implemented on the client side. One should use :name as a placeholder syntax for a named parameter. Named parameters uses the following naming convention:

parameter name must start with either _ or a..z
parameter name may continue with _, a..z and 0..9

Note: this rules mean that, say, the statment SELECT :fooBar will be translated to SELECT ?Bar so please be careful.

Named parameters may be repeated within the statement, e.g SELECT :foo, :foo will require a single named parameter foo that will be repeated on the corresponding positions during statement execution.

One should use the params! macro to build parameters for execution.

Note: Positional and named parameters can’t be mixed within the single statement.

§Statements

In MySql each prepared statement belongs to a particular connection and can’t be executed on another connection. Trying to do so will lead to an error. The driver won’t tie statement to its connection in any way, but one can look on to the connection id, contained in the Statement structure.

§LOCAL INFILE Handlers

Warning: You should be aware of Security Considerations for LOAD DATA LOCAL.

There are two flavors of LOCAL INFILE handlers – global and local.

I case of a LOCAL INFILE request from the server the driver will try to find a handler for it:

It’ll try to use local handler installed on the connection, if any;
It’ll try to use global handler, specified via OptsBuilder::local_infile_handler, if any;
It will emit LocalInfileError::NoHandler if no handlers found.

The purpose of a handler (local or global) is to return InfileData.

§Global LOCAL INFILE handler

See prelude::GlobalHandler.

Simply speaking the global handler is an async function that takes a file name (as &[u8]) and returns Result<InfileData>.

You can set it up using OptsBuilder::local_infile_handler. Server will use it if there is no local handler installed for the connection. This handler might be called multiple times.

Examles:

WhiteListFsHandler is a global handler.
Every T: Fn(&[u8]) -> BoxFuture<'static, Result<InfileData, LocalInfileError>> is a global handler.

§Local LOCAL INFILE handler.

Simply speaking the local handler is a future, that returns Result<InfileData>.

This is a one-time handler – it’s consumed after use. You can set it up using Conn::set_infile_handler. This handler have priority over global handler.

Worth noting:

impl Drop for Conn will clear local handler, i.e. handler will be removed when connection is returned to a Pool.
Conn::reset will clear local handler.

Example:

let pool = mysql_async::Pool::new(database_url);

let mut conn = pool.get_conn().await?;
"CREATE TEMPORARY TABLE tmp (id INT, val TEXT)".ignore(&mut conn).await?;

// We are going to call `LOAD DATA LOCAL` so let's setup a one-time handler.
conn.set_infile_handler(async move {
    // We need to return a stream of `io::Result<Bytes>`
    Ok(stream::iter([Bytes::from("1,a\r\n"), Bytes::from("2,b\r\n3,c")]).map(Ok).boxed())
});

let result = r#"LOAD DATA LOCAL INFILE 'whatever'
    INTO TABLE `tmp`
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    LINES TERMINATED BY '\r\n'"#.ignore(&mut conn).await;

match result {
    Ok(()) => (),
    Err(Error::Server(ref err)) if err.code == 1148 => {
        // The used command is not allowed with this MySQL version
        return Ok(());
    },
    Err(Error::Server(ref err)) if err.code == 3948 => {
        // Loading local data is disabled;
        // this must be enabled on both the client and the server
        return Ok(());
    }
    e @ Err(_) => e.unwrap(),
}

// Now let's verify the result
let result: Vec<(u32, String)> = conn.query("SELECT * FROM tmp ORDER BY id ASC").await?;
assert_eq!(
    result,
    vec![(1, "a".into()), (2, "b".into()), (3, "c".into())]
);

drop(conn);
pool.disconnect().await?;

§Testing

Tests uses followin environment variables:

DATABASE_URL – defaults to mysql://root:password@127.0.0.1:3307/mysql
COMPRESS – set to 1 or true to enable compression for tests
SSL – set to 1 or true to enable TLS for tests

You can run a test server using doker. Please note that params related to max allowed packet, local-infile and binary logging are required to properly run tests (please refer to azure-pipelines.yml):

docker run -d --name container \
    -v `pwd`:/root \
    -p 3307:3306 \
    -e MYSQL_ROOT_PASSWORD=password \
    mysql:8.0 \
    --max-allowed-packet=36700160 \
    --local-infile \
    --log-bin=mysql-bin \
    --log-slave-updates \
    --gtid_mode=ON \
    --enforce_gtid_consistency=ON \
    --server-id=1

Modules§

consts
futures: Futures used in this crate
params
prelude: Traits used in this crate

Macros§

params: This macro is a convenient way to pass named parameters to a statement.

Structs§

BinaryProtocol: Phantom struct used to specify MySql binary protocol.
ChangeUserOpts: COM_CHANGE_USER options.
Column: Represents MySql Column (column packet).
Compression: When compressing data, the compression level can be specified by a value in this struct.
Conn: MySql server connection.
Connection: Some connection.
Deserialized: Use it to parse T: Deserialize from Value.
FromRowError: FromRow conversion error.
FromValueError: FromValue conversion error.
GnoInterval: GnoInterval. Stored within Sid
Gtids: This tracker type indicates that GTIDs are available and contains the GTID string.
Metrics
OkPacket: Represents MySql’s Ok packet.
Opts: Mysql connection options.
OptsBuilder: Provides a way to build Opts.
Pool: Asynchronous pool of MySql connections.
PoolConstraints: Connection pool constraints.
PoolOpts: Connection pool options.
QueryResult: Result of a query or statement execution.
QueryWithParams: Representation of a prepared statement query.
ResultSetStream: Rows stream for a single result set.
Row: Client side representation of a MySql row.
Schema: This tracker type indicates that the default schema has been set.
Serialized: Use it to pass T: Serialize as JSON to a prepared statement.
ServerError: This type represents MySql server error.
SessionStateInfo: Represents change in session state (part of MySql’s Ok packet).
Sid: SID is a part of the COM_BINLOG_DUMP_GTID command. It’s a GtidSet whose has only one Uuid.
SslOpts: Ssl Options.
Statement: Prepared statement.
SystemVariable: This tracker type indicates that one or more tracked session system variables have been assigned a value.
TextProtocol: Phantom struct used to specify MySql text protocol.
Transaction: This struct represents MySql transaction.
TransactionCharacteristics: This tracker type indicates that transaction characteristics are available.
TransactionState: This tracker type indicates that transaction state information is available.
TxOpts: Transaction options.
Unsupported: This tracker type is unknown/unsupported.
WhiteListFsHandler: Handles LOCAL INFILE requests from filesystem using an explicit whitelist of paths.

Enums§

DriverError: This type enumerates driver errors.
Error: This type enumerates library errors.
IoError: This type enumerates IO errors.
IsolationLevel: Transaction isolation level.
LocalInfileError
Params: Representations of parameters of a prepared statement.
ParseError: Errors that can occur during parsing.
SessionStateChange: Represents a parsed change in a session state (part of MySql’s Ok packet).
ToConnectionResult: Result of a ToConnection::to_connection call.
UrlError: This type enumerates connection URL errors.
Value: Client side representation of a value of MySql column.

Constants§

DEFAULT_INACTIVE_CONNECTION_TTL: Default inactive_connection_ttl of a pool.
DEFAULT_POOL_CONSTRAINTS: Default pool constraints.
DEFAULT_STMT_CACHE_SIZE: Each connection will cache up to this number of statements by default.
DEFAULT_TTL_CHECK_INTERVAL: Default ttl_check_interval of a pool.

Functions§

from_row: Will panic if could not convert row to T.
from_row_opt: Will return Err(row) if could not convert row to T
from_value: Will panic if could not convert v to T
from_value_opt: Will return Err(FromValueError(v)) if could not convert v to T

Type Aliases§

InfileData: LOCAL INFILE data is a stream of std::io::Result<Bytes>.
Result: Result type alias for this library.

Crate mysql_asyncCopy item path