mariadb-cli 0.1.1

One-shot agent-native command line client for MariaDB servers
Documentation
//! The statements that cost this server the most, grouped by signature.
//!
//! Statements are grouped by the shape the server itself derives, in which
//! every literal has already been replaced by a placeholder. That is what
//! makes the answer safe to read and safe to keep: two calls of the same
//! statement with different values land in one group, and no value from the
//! application ever appears.
//!
//! This command stays on the surface even where the collection is switched
//! off. Switching it off is a decision on the server, and the answer names it
//! rather than pretending the server is idle.

use super::{cell, fetch, number_of, text_of, ServerSettings};
use crate::cli::server::{QueryRanking, TopArgs};
use crate::error::{Failure, Result};
use crate::output::{Envelope, Row};
use mysql_async::{params, Conn, Params};
use serde_json::{json, Value};

/// How the server measures the time it spends: in millionths of a microsecond.
const PICOSECONDS_PER_MILLISECOND: f64 = 1_000_000_000.0;

/// The ranking, up to the ordering the caller asked for.
const RANKING: &str = "SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT, \
     AVG_TIMER_WAIT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT \
     FROM performance_schema.events_statements_summary_by_digest \
     WHERE DIGEST_TEXT IS NOT NULL \
       AND (:database IS NULL OR SCHEMA_NAME = :database) \
     ORDER BY ";

/// The ranking ordered by one of the four criteria, and by nothing else.
///
/// The column is written into the statement rather than bound as a parameter,
/// because no server binds an `ORDER BY`. That is safe here and only here: the
/// text comes from [`QueryRanking::column`], which is four fixed words chosen by
/// a closed domain the parser already refused anything else for. Nothing a
/// caller typed reaches this string.
///
/// The ordering belongs in the statement and not in the output pipeline: the
/// ceiling on rows is applied by the server, so ordering afterwards would keep
/// the ten costliest by TIME and then rearrange those ten by calls — an answer
/// that is wrong while looking exactly like the right one.
fn ranking(order: QueryRanking) -> String {
    format!("{RANKING}{} DESC LIMIT :top", order.column())
}

/// Ranks the costliest statements.
///
/// # Errors
///
/// Returns a validation failure when the server collects no statement
/// statistics, naming what to switch on; a permission failure when the
/// credential may not read the collection; and the mapped driver failure for
/// any other condition.
pub async fn run(
    connection: &mut Conn,
    args: &TopArgs,
    database: Option<&str>,
    settings: &ServerSettings,
) -> Result<Envelope> {
    refuse_when_collection_is_off(connection).await?;

    let top = args.top.unwrap_or(settings.default_top);
    let source = fetch(
        connection,
        &ranking(args.rank_by),
        params! {
            "database" => database.map(ToOwned::to_owned),
            "top" => u64::try_from(top).unwrap_or_default(),
        },
    )
    .await
    .map_err(observation_failure)?;

    Ok(Envelope::new(
        "server queries",
        json!({
            "top": top,
            // The word the command line accepts, so a caller reading this back
            // learns what to write to ask for the same ranking again. It used
            // to read `total_time` while the manual published `total-time`,
            // which is two spellings of an ordering that did not exist.
            "sorted_by": args.rank_by.as_str(),
            "database": database,
            "rows": shape_all(&source),
        }),
    ))
}

/// Refuses when the server keeps no statement statistics at all.
///
/// An empty ranking would read as a quiet server, which is the opposite of
/// what a switched-off collection means.
async fn refuse_when_collection_is_off(connection: &mut Conn) -> Result<()> {
    let rows = fetch(connection, "SELECT @@performance_schema", Params::Empty)
        .await
        .map_err(|_| collection_off())?;
    let on = rows
        .first()
        .and_then(|row| row.values().next())
        .and_then(|value| number_of(Some(value)))
        .is_some_and(|value| value != 0);
    if on {
        Ok(())
    } else {
        Err(collection_off())
    }
}

/// The failure a server that collects nothing produces.
#[must_use]
pub fn collection_off() -> Failure {
    Failure::validation(
        "validation.statement_statistics_off",
        "this server keeps no statement statistics, so there is nothing to rank",
    )
    .with_hint(
        "set performance_schema=ON in the server configuration and restart it, \
         then enable the statements_digest consumer",
    )
}

/// Names the right that is missing when the collection cannot be read.
fn observation_failure(failure: Failure) -> Failure {
    super::observation_failure(
        failure,
        "SELECT on performance_schema",
        "statement-statistics",
    )
}

/// Shapes every ranked signature.
#[must_use]
pub fn shape_all(source: &[Row]) -> Vec<Row> {
    source.iter().map(shape_one).collect()
}

/// Shapes one ranked signature, with times a caller can read.
fn shape_one(row: &Row) -> Row {
    let calls = number_of(cell(row, "COUNT_STAR")).unwrap_or_default();
    let mut shaped = Row::new();
    shaped.insert(
        "database".to_owned(),
        cell(row, "SCHEMA_NAME").cloned().unwrap_or(Value::Null),
    );
    shaped.insert(
        "signature".to_owned(),
        json!(text_of(cell(row, "DIGEST_TEXT"))),
    );
    shaped.insert("calls".to_owned(), json!(calls));
    shaped.insert(
        "total_ms".to_owned(),
        milliseconds(number_of(cell(row, "SUM_TIMER_WAIT"))),
    );
    shaped.insert(
        "average_ms".to_owned(),
        milliseconds(number_of(cell(row, "AVG_TIMER_WAIT"))),
    );
    shaped.insert(
        "rows_examined".to_owned(),
        cell(row, "SUM_ROWS_EXAMINED")
            .cloned()
            .unwrap_or(Value::Null),
    );
    shaped.insert(
        "rows_returned".to_owned(),
        cell(row, "SUM_ROWS_SENT").cloned().unwrap_or(Value::Null),
    );
    shaped
}

/// Turns the server's own time unit into milliseconds.
#[must_use]
pub fn milliseconds(picoseconds: Option<i64>) -> Value {
    match picoseconds {
        // Allowed: the answer is a duration rounded to three decimal places, so
        // it is approximate by construction and the exactness the lint protects
        // was already given up by the shape being asked for. A server would have
        // to hold a counter past two to the fifty-third picoseconds, which is
        // over a hundred days of accumulated time in a single statement, before
        // the reading and the counter parted company at that precision.
        #[allow(clippy::cast_precision_loss)]
        Some(value) => {
            json!((value as f64 / PICOSECONDS_PER_MILLISECOND * 1000.0).round() / 1000.0)
        }
        None => Value::Null,
    }
}

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

    fn ranked(signature: &str, calls: i64, total: i64) -> Row {
        let mut row = Row::new();
        row.insert("SCHEMA_NAME".to_owned(), json!("sales"));
        row.insert("DIGEST_TEXT".to_owned(), json!(signature));
        row.insert("COUNT_STAR".to_owned(), json!(calls));
        row.insert("SUM_TIMER_WAIT".to_owned(), json!(total));
        row.insert("AVG_TIMER_WAIT".to_owned(), json!(total / calls.max(1)));
        row.insert("SUM_ROWS_EXAMINED".to_owned(), json!(9000));
        row.insert("SUM_ROWS_SENT".to_owned(), json!(3));
        row
    }

    /// A signature the server derived carries a placeholder where the value
    /// was, so nothing from the application travels out.
    #[test]
    fn a_signature_carries_no_value_from_the_application() {
        let rows = shape_all(&[ranked(
            "SELECT * FROM `users` WHERE `id` = ?",
            2,
            4_000_000_000,
        )]);
        let signature = text_of(Some(&rows[0]["signature"]));
        assert!(
            signature.contains('?'),
            "the shape must survive: {signature}"
        );
        assert!(!signature.contains("4711"));
    }

    /// The server's own time unit is turned into one a caller can read.
    #[test]
    fn the_server_time_unit_becomes_milliseconds() {
        assert_eq!(milliseconds(Some(1_000_000_000)), json!(1.0));
        assert_eq!(milliseconds(Some(2_500_000_000)), json!(2.5));
        assert_eq!(milliseconds(None), Value::Null);
    }

    /// A switched-off collection is refused with what to switch on, never
    /// answered with an empty ranking that reads as a quiet server.
    #[test]
    fn a_switched_off_collection_says_what_to_switch_on() {
        let failure = collection_off();
        assert_eq!(failure.exit_number().code(), 3);
        assert!(failure
            .hint()
            .is_some_and(|hint| hint.contains("performance_schema")));
    }

    /// FR-127. Every criterion orders on the SERVER, and each one orders by a
    /// different column.
    ///
    /// The manual promised a criterion the command never had, and the global
    /// output ordering accepted a field the answer does not carry, which ended
    /// in success having sorted nothing. What makes the repair real is that the
    /// word reaches the statement: a criterion that changed only the reported
    /// `sorted_by` would be the same no-op wearing a label.
    #[test]
    fn every_criterion_reaches_the_statement_and_orders_by_its_own_column() {
        let mut seen = std::collections::BTreeSet::new();
        for order in [
            QueryRanking::Total,
            QueryRanking::Calls,
            QueryRanking::Average,
            QueryRanking::Rows,
        ] {
            let statement = ranking(order);
            assert!(
                statement.contains(&format!("ORDER BY {} DESC", order.column())),
                "{order:?} did not reach the statement: {statement}"
            );
            assert!(
                statement.ends_with("LIMIT :top"),
                "the ceiling must stay on the server, after the ordering: {statement}"
            );
            assert!(
                seen.insert(order.column()),
                "{order:?} shares a column with another criterion, so one of \
                 them is unreachable"
            );
        }
    }

    /// FR-127. The word the answer declares is the word the command line
    /// accepts, so a caller can ask for the same ranking again.
    ///
    /// This pair was the defect: the answer said `total_time` while the manual
    /// published `total-time`, and neither spelling ordered anything. One word
    /// per criterion is what keeps the two ends from drifting again.
    #[test]
    fn the_reported_criterion_is_the_word_the_caller_would_write() {
        for (order, word) in [
            (QueryRanking::Total, "total"),
            (QueryRanking::Calls, "calls"),
            (QueryRanking::Average, "average"),
            (QueryRanking::Rows, "rows"),
        ] {
            assert_eq!(order.as_str(), word);
            assert!(
                !word.contains('_') && !word.contains('-'),
                "{word} is spelled two ways the moment it carries a separator"
            );
        }
    }

    /// A missing right is a permission failure naming the right, never 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("performance_schema")));
    }

    /// Every published field of a ranked signature is present.
    #[test]
    fn a_ranked_signature_carries_every_published_field() {
        let rows = shape_all(&[ranked("SELECT ?", 4, 8_000_000_000)]);
        for field in [
            "database",
            "signature",
            "calls",
            "total_ms",
            "average_ms",
            "rows_examined",
            "rows_returned",
        ] {
            assert!(rows[0].contains_key(field), "{field} is missing");
        }
        assert_eq!(rows[0]["calls"], json!(4));
    }
}