diff options
| author | Pjotr <wrk@bosa.lan> | 2026-11-08 12:29:22 +0000 |
|---|---|---|
| committer | Pjotr <wrk@bosa.lan> | 2026-11-08 12:29:22 +0000 |
| commit | c008bdeca126062b2ab87bb82e7fc9ae05060e5f (patch) | |
| tree | 329b595a9e18733ac559c446c11d3b320c7a31b2 /src | |
| parent | ee939a246a5b67f6650e0759e23fe2731124f8c3 (diff) | |
| download | genenetwork4-c008bdeca126062b2ab87bb82e7fc9ae05060e5f.tar.gz | |
db: recover from a connection the server closed, and report the database
Every database-backed endpoint 500'd with `CodecError { IO error: Broken
pipe }` while the process answered /version quite happily. The cause was
ours: `pool()` turns off the driver's `check_health` (a ping at checkout,
dead connections replaced) to save a round trip -- sound against a
database 130 ms away, worth nothing against loopback, and it costs the
pool its self-healing. One server-side close of an idle session then
becomes one failed request per dead socket: ten in a row, until a
restart.
* src/db.rs: `SQL_CHECK_HEALTH` (default true) decides the ping;
`is_connection_loss` recognises the errors that mean the peer went away
(broken pipe, reset, went away, lost connection, closed);
`forget_pool` drops the cached pool so the next call rebuilds it;
`health` probes reachability, cached ten seconds. One pool static shared
by pool()/forget_pool (they had drifted into two).
* src/error.rs (ApiError::from_db): on a connection loss, forget the pool
-- the one place every database error passes through, so recovery does
not depend on each caller.
* src/lib.rs (/version): `"database": "reachable" | "unreachable"`; a
service that answers while nothing behind it works is now visible.
* src/config.rs: the switch, with the trade written down.
* tests/db_recovery.rs: kills every pooled session from a second
connection, then asserts the next twelve requests all succeed -- the
scenario that produced the outage, now a regression test.
* tests/...: unit test for the classifier (its own IO error text and the
"this is a bad query, do not retry" cases).
* README.md ("Database connections"), docs/www/operator-checklist.md:
the knob, why it defaults on, and what /version reports.
Diffstat (limited to 'src')
| -rw-r--r-- | src/config.rs | 9 | ||||
| -rw-r--r-- | src/db.rs | 127 | ||||
| -rw-r--r-- | src/error.rs | 25 | ||||
| -rw-r--r-- | src/lib.rs | 9 |
4 files changed, 151 insertions, 19 deletions
diff --git a/src/config.rs b/src/config.rs index d832bbb..6a390f1 100644 --- a/src/config.rs +++ b/src/config.rs @@ -132,6 +132,12 @@ pub struct Config { pub genotype_files_dir: String, /// Interpreter used to run the plotter scripts (`PLOT_PYTHON`, /// default `python3`). + /// Whether the connection pool pings a connection taken from it and + /// replaces it when the ping fails (`SQL_CHECK_HEALTH`, default + /// `true`). Costs one round trip per pooled checkout; turning it off + /// means a connection the server closed surfaces as a failed request + /// instead of being replaced. + pub sql_check_health: bool, pub plot_python: String, /// `scripts/plot-rqtl.py`: turns a /compute/rqtl response into an SVG /// Manhattan plot (`RQTL_PLOTTER`). @@ -182,6 +188,7 @@ impl Config { "http://127.0.0.1:5003/api/".to_string(), ); values.insert("GN3_TIMEOUT_SECS".to_string(), "30".to_string()); + values.insert("SQL_CHECK_HEALTH".to_string(), "true".to_string()); values.insert("PLOT_PYTHON".to_string(), "python3".to_string()); values.insert( "RQTL_PLOTTER".to_string(), @@ -269,6 +276,7 @@ impl Config { "GN3_URL", "GN3_TIMEOUT_SECS", "GENOTYPE_FILES", + "SQL_CHECK_HEALTH", "PLOT_PYTHON", "RQTL_PLOTTER", "GEMMA_PLOTTER", @@ -328,6 +336,7 @@ impl Config { rqtl_wrapper_path: values["RQTL_WRAPPER_PATH"].clone(), rqtl_cache_dir: values["RQTL_CACHE_DIR"].clone(), genotype_files_dir: values["GENOTYPE_FILES"].clone(), + sql_check_health: parse_switch(&values["SQL_CHECK_HEALTH"]), plot_python: values["PLOT_PYTHON"].clone(), rqtl_plotter: values["RQTL_PLOTTER"].clone(), gemma_plotter: values["GEMMA_PLOTTER"].clone(), diff --git a/src/db.rs b/src/db.rs index 35d3fb2..4919264 100644 --- a/src/db.rs +++ b/src/db.rs @@ -48,6 +48,9 @@ impl From<mysql::Error> for DbError { } } +/// The shared connection pool (see `pool()` and `forget_pool()`). +static POOL: OnceLock<Mutex<Option<Pool>>> = OnceLock::new(); + /// The shared connection pool, built once and reused by every query /// function below. mysql::Pool::new eagerly opens PoolConstraints:: /// DEFAULT's 10 connections before returning (about 7s against this @@ -63,35 +66,101 @@ impl From<mysql::Error> for DbError { /// letting a failed attempt be retried on the next call instead of /// wedging into a permanent error. /// -/// Two defaults are turned off because each costs a full round trip to -/// the database per checkout, on top of the query: `check_health` (a -/// ping when a connection is taken from the pool) and -/// `reset_connection` (a COM_RESET_CONNECTION when it is returned). -/// Together they tripled the cost of every database call against a -/// database about 130 ms away (390 ms instead of 130 ms). Nothing here -/// needs the reset (queries are read-only and keep no session state), -/// and the ping only protects against a connection the server closed -/// while idle -- which would now surface as a database error on the -/// next query using it (the pool then drops that connection). +/// `reset_connection` is off because it costs a full round trip per +/// checkout (a COM_RESET_CONNECTION on return) and nothing here needs it: +/// the queries are read-only and keep no session state. Against a +/// database about 130 ms away that round trip tripled the cost of a +/// query. +/// +/// `check_health` is a different trade, and on by default +/// (`SQL_CHECK_HEALTH`): it pings a connection taken from the pool and +/// replaces it if the ping fails, which is what keeps one server-side +/// close of an idle session from turning into a failed request per dead +/// socket. Turning it off saves a round trip -- worth it against a +/// remote database, worth nothing against loopback -- but then a closed +/// connection surfaces as `database_error` on the next request that +/// happens to draw it, which is exactly what happened here on +/// 2026-11-07: ten pooled connections went away and the next ten +/// requests each 500'd with `CodecError { IO error: Broken pipe }`. fn pool() -> Result<Pool, DbError> { - static POOL: OnceLock<Mutex<Option<Pool>>> = OnceLock::new(); let mut guard = POOL.get_or_init(|| Mutex::new(None)).lock().unwrap(); if let Some(pool) = guard.as_ref() { return Ok(pool.clone()); } + let config = crate::config::Config::load(); let opts = OptsBuilder::from_opts( Opts::from_url(&sql_uri()).map_err(|e| DbError::Unavailable(e.to_string()))?, ) .pool_opts( PoolOpts::default() .with_reset_connection(false) - .with_check_health(false), + .with_check_health(config.sql_check_health), ); let new_pool = Pool::new(opts).map_err(|e| DbError::Unavailable(e.to_string()))?; *guard = Some(new_pool.clone()); Ok(new_pool) } +/// Does this error mean the *connection* went away, rather than the query +/// being wrong? +/// +/// The driver reports a peer that closed under it as an IO error nested +/// in its own wrapper (`CodecError { IO error: Broken pipe }`), or as +/// `io::ErrorKind::{ConnectionReset, ConnectionAborted, UnexpectedEof}` +/// depending on where it surfaces. These are the cases where retrying on +/// a fresh connection is the right answer and the query is not at fault. +pub fn is_connection_loss(error: &DbError) -> bool { + let DbError::Query(error) = error else { + return false; + }; + let text = error.to_string().to_lowercase(); + [ + "broken pipe", + "connection reset", + "connection aborted", + "unexpected eof", + "server has gone away", + "lost connection", + "connection was closed", + ] + .iter() + .any(|marker| text.contains(marker)) +} + +/// Drop the cached pool, so the next call builds a fresh one. +/// +/// Called when a request reports a connection loss: the pool may be full +/// of sockets the server has closed, and rebuilding it is the only way to +/// stop the next requests drawing them one by one. Cheap when it happens +/// (a rebuild opens 10 connections) and never when it does not. +pub fn forget_pool() { + if let Some(guard) = POOL.get() { + if let Ok(mut slot) = guard.lock() { + *slot = None; + } + } +} + +/// Database reachability, cached for a few seconds: what `/version` +/// reports, so "the service answers but nothing behind it works" is +/// visible without trying a data endpoint. +pub fn health() -> bool { + static LAST: OnceLock<Mutex<Option<(std::time::Instant, bool)>>> = OnceLock::new(); + let cache = LAST.get_or_init(|| Mutex::new(None)); + if let Ok(guard) = cache.lock() { + if let Some((checked_at, healthy)) = *guard { + if checked_at.elapsed() < std::time::Duration::from_secs(10) { + return healthy; + } + } + } + let healthy = probe().is_ok(); + if let Ok(mut guard) = cache.lock() { + *guard = Some((std::time::Instant::now(), healthy)); + } + healthy +} + /// Startup reachability probe: builds the shared pool (connecting to /// `SQL_URI`) and runs `SELECT 1`. `Ok(())` means the database is /// reachable; an `Err` is exactly what makes DB-backed routes answer @@ -1365,3 +1434,37 @@ pub fn get_dataset_probeset_values( ) .map_err(DbError::from) } + +#[cfg(test)] +mod tests { + use super::*; + + fn query_error(text: &str) -> DbError { + DbError::Query(mysql::Error::IoError(std::io::Error::other( + text.to_string(), + ))) + } + + #[test] + fn a_peer_that_went_away_is_a_connection_loss_and_a_bad_query_is_not() { + for text in [ + "CodecError { IO error: `Broken pipe (os error 32)' }", + "Connection reset by peer", + "connection aborted", + "unexpected eof", + "MySQL server has gone away", + "Lost connection to MySQL server during query", + ] { + assert!(is_connection_loss(&query_error(text)), "{text}"); + } + for text in [ + "Table 'db_webqtl.Nope' doesn't exist", + "You have an error in your SQL syntax", + ] { + assert!(!is_connection_loss(&query_error(text)), "{text}"); + } + assert!(!is_connection_loss(&DbError::Unavailable( + "bad uri".to_string() + ))); + } +} diff --git a/src/error.rs b/src/error.rs index 7daa103..a9b01bd 100644 --- a/src/error.rs +++ b/src/error.rs @@ -128,13 +128,24 @@ impl ApiError { detail, operation_id, ), - crate::db::DbError::Query(error) => ApiError::new( - StatusCode::INTERNAL_SERVER_ERROR, - "database_error", - "Database error", - error.to_string(), - operation_id, - ), + crate::db::DbError::Query(error) => { + // A connection that died under the query is not the + // query's fault. Forget the pool so the next request + // cannot draw the same dead sockets -- this is the one + // place every database error passes through, so the + // recovery does not depend on each caller remembering it. + let api_error = ApiError::new( + StatusCode::INTERNAL_SERVER_ERROR, + "database_error", + "Database error", + error.to_string(), + operation_id, + ); + if crate::db::is_connection_loss(&crate::db::DbError::Query(error)) { + crate::db::forget_pool(); + } + api_error + } } } } diff --git a/src/lib.rs b/src/lib.rs index 2bba755..c3efcca 100644 --- a/src/lib.rs +++ b/src/lib.rs @@ -154,6 +154,10 @@ struct VersionInfo { source_digest: &'static str, /// The branch the build's HEAD was on, or "HEAD" when detached. branch: &'static str, + /// Whether the database answers, checked at most every ten seconds. + /// A service that answers `/version` while every data endpoint fails + /// is the state this makes visible. + database: &'static str, } #[utoipa::path(get, path = "/version", summary = "Build information: the git commit the binary was built from, and a source digest", responses( @@ -176,5 +180,10 @@ async fn version(ctx: Ctx) -> Json<Envelope<VersionInfo>> { dirty: git.ends_with("-dirty"), source_digest: env!("GN4_SOURCE_DIGEST"), branch: env!("GN4_GIT_BRANCH"), + database: if crate::db::health() { + "reachable" + } else { + "unreachable" + }, })) } |
