about summary refs log tree commit diff
path: root/src
diff options
context:
space:
mode:
authorPjotr <wrk@bosa.lan>2026-11-08 12:29:22 +0000
committerPjotr <wrk@bosa.lan>2026-11-08 12:29:22 +0000
commitc008bdeca126062b2ab87bb82e7fc9ae05060e5f (patch)
tree329b595a9e18733ac559c446c11d3b320c7a31b2 /src
parentee939a246a5b67f6650e0759e23fe2731124f8c3 (diff)
downloadgenenetwork4-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.rs9
-rw-r--r--src/db.rs127
-rw-r--r--src/error.rs25
-rw-r--r--src/lib.rs9
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"
+        },
     }))
 }