mariadb-cli 0.1.1

One-shot agent-native command line client for MariaDB servers
Documentation
//! Who waits for whom, for how long, and the oldest transaction still open.
//!
//! A statement that sits still looks the same whether it is slow or blocked,
//! and the two need opposite answers. This command tells them apart.
//!
//! An open transaction nobody remembers is the usual cause, and it is
//! invisible in a listing of connections, because the connection holding it
//! may be doing nothing at all. So the oldest one is always reported, even
//! when nothing is waiting.
//!
//! No dispute at all is an empty listing with success, never a failure.

use super::{cell, fetch, text_of, ServerSettings};
use crate::error::{Failure, Result};
use crate::output::{Envelope, Gap, Row};
use crate::sql::strip;
use mysql_async::{Conn, Params};
use serde_json::{json, Value};

/// The transactions open right now, with the state of the session holding each.
///
/// The join is outer on purpose. A credential that sees fewer sessions than
/// transactions would lose whole transactions to an inner join, and losing a
/// transaction is the opposite of what this command is for. An unseen session
/// arrives as an absent command word, which reads as unknown rather than as
/// busy.
const OPEN_TRANSACTIONS: &str = "SELECT t.trx_id, t.trx_state, t.trx_started, \
     t.trx_wait_started, t.trx_mysql_thread_id, t.trx_rows_locked, \
     t.trx_rows_modified, t.trx_query, p.COMMAND AS session_command, \
     TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS open_seconds \
     FROM information_schema.INNODB_TRX t \
     LEFT JOIN information_schema.PROCESSLIST p ON p.ID = t.trx_mysql_thread_id \
     ORDER BY t.trx_started ASC";

/// The same reading without the session beside it.
///
/// Kept for the server or the credential that refuses the join, so the command
/// answers with the transactions it can see and declares what it could not add
/// to them, rather than failing whole.
const OPEN_TRANSACTIONS_ALONE: &str = "SELECT trx_id, trx_state, trx_started, trx_wait_started, \
     trx_mysql_thread_id, trx_rows_locked, trx_rows_modified, trx_query \
     FROM information_schema.INNODB_TRX ORDER BY trx_started ASC";

/// The word the server uses for a session that is sitting still.
const ASLEEP: &str = "Sleep";

/// The pairs of waiting and blocking transactions, where the server has them.
const WAITS: &str = "SELECT w.requesting_trx_id, w.blocking_trx_id, \
     w.requested_lock_id, w.blocking_lock_id, \
     r.trx_mysql_thread_id AS waiting_thread, b.trx_mysql_thread_id AS blocking_thread, \
     r.trx_wait_started, r.trx_query \
     FROM information_schema.INNODB_LOCK_WAITS w \
     JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id \
     JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id";

/// Reports the disputes and the oldest open transaction.
///
/// # Errors
///
/// Returns a permission failure naming the right that is missing when the
/// credential may not observe other connections, and the mapped driver failure
/// for any other condition.
pub async fn run(connection: &mut Conn, _settings: &ServerSettings) -> Result<Envelope> {
    let mut gaps = Vec::new();
    let transactions = match fetch(connection, OPEN_TRANSACTIONS, Params::Empty).await {
        Ok(joined) => joined,
        Err(failure) if super::is_permission(&failure) => {
            return Err(observation_failure(failure));
        }
        Err(failure) => {
            gaps.push(Gap::new(
                "idle_in_transaction",
                "unreadable",
                format!(
                    "the session behind each transaction could not be read, so a \
                     transaction held open by an idle session is not told apart from \
                     one still working: {}",
                    failure.message()
                ),
            ));
            fetch(connection, OPEN_TRANSACTIONS_ALONE, Params::Empty)
                .await
                .map_err(observation_failure)?
        }
    };

    let (rows, gap) = match fetch(connection, WAITS, Params::Empty).await {
        Ok(waits) => (shape_waits(&waits), None),
        Err(failure) if super::is_absent_structure(&failure) => (
            waiting_without_blockers(&transactions),
            Some(Gap::new(
                "blocking_transaction",
                "unsupported",
                "this server does not publish which transaction blocks which, \
                 so each waiting transaction is reported without its blocker"
                    .to_owned(),
            )),
        ),
        Err(failure) => return Err(observation_failure(failure)),
    };

    let envelope = Envelope::new(
        "server locks",
        json!({
            "open_transactions": transactions.len(),
            "oldest_open_transaction": oldest(&transactions),
            "idle_in_transaction": idle_in_transaction(&transactions),
            "rows": rows,
        }),
    );
    gaps.extend(gap);
    Ok(gaps.into_iter().fold(envelope, Envelope::with_gap))
}

/// The transactions nobody is working on, which are the expensive ones.
///
/// A transaction whose session is asleep holds its locks and will go on holding
/// them until somebody remembers it. That is a different problem from a
/// transaction that is slow, and it is the one the reader has to act on first,
/// so it is counted apart rather than left inside the total.
///
/// A transaction whose session could not be read counts as neither, because
/// guessing either way would report a state nobody observed.
#[must_use]
pub fn idle_in_transaction(transactions: &[Row]) -> Value {
    let asleep: Vec<&Row> = transactions
        .iter()
        .filter(|row| cell(row, "session_command").is_some_and(|word| word == ASLEEP))
        .collect();
    let unknown = transactions
        .iter()
        .filter(|row| matches!(cell(row, "session_command"), None | Some(Value::Null)))
        .count();

    let longest = asleep
        .iter()
        .filter_map(|row| cell(row, "open_seconds").and_then(Value::as_i64))
        .max();

    json!({
        "count": asleep.len(),
        "longest_open_seconds": longest,
        "session_unknown": unknown,
    })
}

/// Names the right that is missing when the transactions cannot be observed.
fn observation_failure(failure: Failure) -> Failure {
    super::observation_failure(failure, "PROCESS", "other-transactions")
}

/// Shapes each pair of a waiting and a blocking transaction.
#[must_use]
pub fn shape_waits(source: &[Row]) -> Vec<Row> {
    source
        .iter()
        .map(|row| {
            let mut shaped = Row::new();
            shaped.insert(
                "waiting_transaction".to_owned(),
                copy(row, "requesting_trx_id"),
            );
            shaped.insert("waiting_connection".to_owned(), copy(row, "waiting_thread"));
            shaped.insert(
                "blocking_transaction".to_owned(),
                copy(row, "blocking_trx_id"),
            );
            shaped.insert(
                "blocking_connection".to_owned(),
                copy(row, "blocking_thread"),
            );
            shaped.insert("disputed_object".to_owned(), copy(row, "requested_lock_id"));
            shaped.insert("blocking_lock".to_owned(), copy(row, "blocking_lock_id"));
            shaped.insert("waiting_since".to_owned(), copy(row, "trx_wait_started"));
            shaped.insert("waiting_statement".to_owned(), redact(row, "trx_query"));
            shaped
        })
        .collect()
}

/// Shapes the waiting transactions when the server names no blocker.
///
/// Half an answer that says it is half is worth more than no answer, because
/// the waiting side alone already tells a caller this is a block and not
/// slowness.
#[must_use]
pub fn waiting_without_blockers(transactions: &[Row]) -> Vec<Row> {
    transactions
        .iter()
        .filter(|row| text_of(cell(row, "trx_state")).eq_ignore_ascii_case("lock wait"))
        .map(|row| {
            let mut shaped = Row::new();
            shaped.insert("waiting_transaction".to_owned(), copy(row, "trx_id"));
            shaped.insert(
                "waiting_connection".to_owned(),
                copy(row, "trx_mysql_thread_id"),
            );
            shaped.insert("blocking_transaction".to_owned(), Value::Null);
            shaped.insert("blocking_connection".to_owned(), Value::Null);
            shaped.insert("disputed_object".to_owned(), Value::Null);
            shaped.insert("blocking_lock".to_owned(), Value::Null);
            shaped.insert("waiting_since".to_owned(), copy(row, "trx_wait_started"));
            shaped.insert("waiting_statement".to_owned(), redact(row, "trx_query"));
            shaped
        })
        .collect()
}

/// The transaction that has been open longest, when one is open at all.
///
/// The listing arrives ordered by the instant each transaction started, so the
/// oldest is the first one.
#[must_use]
pub fn oldest(transactions: &[Row]) -> Value {
    match transactions.first() {
        None => Value::Null,
        Some(row) => json!({
            "transaction": copy(row, "trx_id"),
            "connection": copy(row, "trx_mysql_thread_id"),
            "state": copy(row, "trx_state"),
            "started": copy(row, "trx_started"),
            "rows_locked": copy(row, "trx_rows_locked"),
            "rows_modified": copy(row, "trx_rows_modified"),
            "statement": redact(row, "trx_query"),
        }),
    }
}

/// Copies one cell out of a row by name.
fn copy(row: &Row, name: &str) -> Value {
    cell(row, name).cloned().unwrap_or(Value::Null)
}

/// Copies one statement out of a row with every literal blanked.
fn redact(row: &Row, name: &str) -> Value {
    match cell(row, name) {
        None | Some(Value::Null) => Value::Null,
        Some(other) => json!(strip::strip(&text_of(Some(other))).text().to_owned()),
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    fn transaction(id: &str, state: &str, started: &str, query: Value) -> Row {
        let mut row = Row::new();
        row.insert("trx_id".to_owned(), json!(id));
        row.insert("trx_state".to_owned(), json!(state));
        row.insert("trx_started".to_owned(), json!(started));
        row.insert("trx_wait_started".to_owned(), Value::Null);
        row.insert("trx_mysql_thread_id".to_owned(), json!(4711));
        row.insert("trx_rows_locked".to_owned(), json!(3));
        row.insert("trx_rows_modified".to_owned(), json!(1));
        row.insert("trx_query".to_owned(), query);
        row
    }

    /// A server with nothing in dispute answers with an empty listing and
    /// success, because no dispute is a fact and not a failure.
    #[test]
    fn no_dispute_is_an_empty_listing_and_not_a_failure() {
        assert!(shape_waits(&[]).is_empty());
        assert_eq!(oldest(&[]), Value::Null);
    }

    /// The transaction open longest is reported even when nothing is waiting,
    /// because a forgotten transaction is the usual cause and is otherwise
    /// invisible.
    #[test]
    fn the_oldest_open_transaction_is_reported_with_nothing_waiting() {
        let transactions = vec![
            transaction("A", "RUNNING", "2026-01-10 09:00:00", Value::Null),
            transaction("B", "RUNNING", "2026-01-10 09:05:00", Value::Null),
        ];
        let oldest = oldest(&transactions);
        assert_eq!(oldest["transaction"], json!("A"));
        assert_eq!(oldest["started"], json!("2026-01-10 09:00:00"));
    }

    /// A statement observed on another connection loses its values here too.
    #[test]
    fn an_observed_statement_loses_its_values() {
        let transactions = vec![transaction(
            "A",
            "RUNNING",
            "2026-01-10 09:00:00",
            json!("UPDATE t SET secret = 'hunter2'"),
        )];
        let statement = text_of(Some(&oldest(&transactions)["statement"]));
        assert!(
            !statement.contains("hunter2"),
            "a literal survived: {statement}"
        );
        assert!(statement.contains("UPDATE"));
    }

    /// A server that names no blocker still reports who is waiting, which is
    /// already enough to tell a block from slowness.
    #[test]
    fn a_server_naming_no_blocker_still_reports_who_waits() {
        let transactions = vec![
            transaction("A", "RUNNING", "2026-01-10 09:00:00", Value::Null),
            transaction("B", "LOCK WAIT", "2026-01-10 09:05:00", Value::Null),
        ];
        let rows = waiting_without_blockers(&transactions);
        assert_eq!(rows.len(), 1);
        assert_eq!(rows[0]["waiting_transaction"], json!("B"));
        assert_eq!(rows[0]["blocking_transaction"], Value::Null);
    }

    /// A missing right names the right rather than surfacing as a bare server
    /// condition.
    #[test]
    fn a_missing_right_names_the_right() {
        let failure = observation_failure(Failure::permission("permission.denied", "refused"));
        assert_eq!(failure.exit_number().code(), 7);
        assert!(failure.hint().is_some_and(|hint| hint.contains("PROCESS")));
    }
}