mysql: runtime `SET time_zone` silently skews `TIMESTAMP` for `chrono::DateTime<Utc>` and others

[meng-xu-cs]

I have found these related issues/pull requests

N.A.

Description

sqlx's MySQL support establishes a UTC-session invariant at connect time, but the instant-like Rust types for MySQL TIMESTAMP rely on that invariant forever even though safe user SQL can later break it.

In current implementation:

• MySqlConnectOptions defaults to time_zone = '+00:00' and connect() sends that to the server.
• chrono::DateTime<Utc>, chrono::DateTime<Local>, and time::OffsetDateTime encode/decode TIMESTAMP as if the session time zone is always UTC.
• MySQL itself converts TIMESTAMP values from the session time zone to UTC for storage, and from UTC back to the session time zone for retrieval.

That means a plain runtime statement like SET time_zone = '+05:00' on a live MySqlConnection silently breaks the invariant. After that, SQLx can write the wrong UTC instant to a TIMESTAMP column or decode the wrong UTC instant from one.

This is a silent data-corruption bug for TIMESTAMP specifically. (DATETIME is not subject to MySQL's session-time-zone conversion.)

Reproduction steps

This uses a single connection and changes the session time zone at runtime to demonstrate both write skew and read skew.

use chrono::{TimeZone, Utc};
use sqlx::mysql::MySqlConnection;
use sqlx::{Connection, Executor};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let database_url = std::env::var("DATABASE_URL")?;
    let mut conn = MySqlConnection::connect(&database_url).await?;

    conn.execute("DROP TABLE IF EXISTS sqlx_tz_bug").await?;
    conn.execute("CREATE TABLE sqlx_tz_bug (ts TIMESTAMP NOT NULL)").await?;

    let dt = Utc.with_ymd_and_hms(2024, 1, 2, 3, 4, 5).unwrap();

    // --- write skew ---
    // Break SQLx's UTC-session invariant.
    conn.execute("SET time_zone = '+05:00'").await?;

    sqlx::query("INSERT INTO sqlx_tz_bug (ts) VALUES (?)")
        .bind(dt)
        .execute(&mut conn)
        .await?;

    // Switch back to UTC and read the stored row.
    conn.execute("SET time_zone = '+00:00'").await?;

    let written_back: (chrono::DateTime<Utc>,) =
        sqlx::query_as("SELECT ts FROM sqlx_tz_bug LIMIT 1")
            .fetch_one(&mut conn)
            .await?;

    println!("expected write value: {dt}");
    println!("actual stored instant: {}", written_back.0);
    // expected: 2024-01-02 03:04:05 UTC
    // actual:   2024-01-01 22:04:05 UTC

    // --- read skew ---
    conn.execute("TRUNCATE TABLE sqlx_tz_bug").await?;
    conn.execute("SET time_zone = '+00:00'").await?;

    sqlx::query("INSERT INTO sqlx_tz_bug (ts) VALUES (?)")
        .bind(dt)
        .execute(&mut conn)
        .await?;

    // Break the invariant again before decoding.
    conn.execute("SET time_zone = '+05:00'").await?;

    let read_back: (chrono::DateTime<Utc>,) =
        sqlx::query_as("SELECT ts FROM sqlx_tz_bug LIMIT 1")
            .fetch_one(&mut conn)
            .await?;

    println!("expected read value:  {dt}");
    println!("actual decoded value: {}", read_back.0);
    // expected: 2024-01-02 03:04:05 UTC
    // actual:   2024-01-02 08:04:05 UTC

    Ok(())
}

After a safe runtime SET time_zone = ... on the same connection:

• writing a chrono::DateTime<Utc> to a TIMESTAMP can store the wrong UTC instant;
• reading a TIMESTAMP as chrono::DateTime<Utc> can return the wrong UTC instant;
• the failure is silent because the value is still returned as a well-typed UTC/offset-aware Rust value.

The same underlying row can decode differently depending only on the current session time_zone of the connection performing the read.

SQLx version

main branch

Enabled SQLx features

default

Database server and version

MySQL

Operating system

MacOS

Rust version

1.94.0 (4a4ef493e 2026-03-02)