# Databases (MySQL, PostgreSQL) > Database evidence for every incident, fixes on the database itself, and the right response when the database is the one that's down. Source: https://docs.3am.si/connectors/databases · Markdown: https://docs.3am.si/connectors/databases.md · All docs: https://docs.3am.si/llms.txt 3AM is an on-prem AI on-call engineer for regulated enterprises (https://3am.si). With a database connected, 3AM reads the database directly: whether it's up and why not, who holds the connections, which session is blocking the others, how replication is doing. It uses that evidence in two ways: * **For the database's own incidents.** It ends the session holding a lock, closes a leaking client's idle sessions, makes a primary writable again, or restarts replication that stopped cleanly. Each fix is rehearsed and approved. * **For other incidents.** When an application's log says it can't reach its database, 3AM checks the database itself instead of trusting the log line. When the database itself is down, 3AM proves why before touching anything: | Proven cause | What 3AM does | | ---------------------------- | ------------------------------------------------------------------------ | | Hung, or its pod isn't Ready | Restart its pod (Kubernetes connector), then wait until it answers | | Process down | Restart its pod | | Out of disk | Grow its volume (Kubernetes connector), or ask a person. Never a restart | | Still recovering | Wait. A restart would start the recovery over | | Connections exhausted | Close the leaking client's idle sessions | | Corrupted, or misconfigured | Hand it to a DBA with the error | MySQL and MariaDB, and PostgreSQL, are fully supported. Oracle and SQL Server support reads and approved data changes. **Before you start** * One connection per database instance (or cluster endpoint) you want 3AM to watch. * Two accounts on each: a read account for evidence, and an admin account for approved fixes. * For databases in Kubernetes, the [Kubernetes connector](https://docs.3am.si/connectors/kubernetes) with the database's namespace, so 3AM can restart its pod or grow its volume. ## 1. Create the accounts **MySQL / MariaDB:** ```sql -- evidence: sessions, locks, replication, and the application's tables for packs that read them CREATE USER 'threeam_read'@'%' IDENTIFIED BY '…'; GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'threeam_read'@'%'; GRANT SELECT ON performance_schema.* TO 'threeam_read'@'%'; GRANT SELECT, EXECUTE ON sys.* TO 'threeam_read'@'%'; GRANT SELECT ON orders.* TO 'threeam_read'@'%'; -- the application's schema -- approved fixes: end sessions, read-only off, start replication CREATE USER 'threeam_admin'@'%' IDENTIFIED BY '…'; GRANT PROCESS, REPLICATION CLIENT, CONNECTION_ADMIN, SYSTEM_VARIABLES_ADMIN, REPLICATION_SLAVE_ADMIN ON *.* TO 'threeam_admin'@'%'; GRANT SELECT ON performance_schema.* TO 'threeam_admin'@'%'; GRANT SELECT, EXECUTE ON sys.* TO 'threeam_admin'@'%'; GRANT SELECT ON orders.* TO 'threeam_admin'@'%'; ``` `CONNECTION_ADMIN` also lets the admin account in when every connection slot is taken. MySQL keeps one extra slot for it, which is how 3AM closes a connection leak on a full database. Add `INSERT, UPDATE, DELETE` on the application's schema only if you want approved data changes (`sql.write`). **PostgreSQL:** ```sql -- evidence CREATE ROLE threeam_read LOGIN PASSWORD '…' IN ROLE pg_monitor, pg_read_all_data; -- approved fixes: end sessions (not superusers' sessions) CREATE ROLE threeam_admin LOGIN PASSWORD '…' IN ROLE pg_monitor, pg_signal_backend; GRANT pg_use_reserved_connections TO threeam_admin; -- PostgreSQL 16 and later ``` On PostgreSQL 16 and later, also set `reserved_connections` (for example `3`) so the admin account gets in when applications have taken every other slot. On PostgreSQL 14 and 15, only superusers have reserved slots. ## 2. Add the connector One connector per instance. In `connectors.json`: ```json title="/etc/3am/connectors.json" { "name": "ledger-db", "kind": "sql", "settings": { "engine": "postgres", "host": "ledger-db-0.ledger-db.bank-prod.svc", "database": "ledger", "username": "threeam_admin", "password": { "file": "/etc/3am/secrets/ledger-db-admin" }, "read_username": "threeam_read", "read_password": { "file": "/etc/3am/secrets/ledger-db-read" }, "service": "ledger", "expected_role": "primary" } } ``` `expected_role` matters. 3AM makes a read-only database writable again only if it's declared the primary. A demoted old primary also looks like "not a replica", and making it writable would give you two primaries. When an alert names a database (its `instance` or `host` label), 3AM checks and fixes that database, and no other. If it can't tell which of several databases an alert means, it asks instead of guessing. Connector settings (kind `sql`; actions, alerts in): | Setting | What to enter | Required | Default | | --- | --- | --- | --- | | `engine` | Database engine (mysql, mariadb, postgres, oracle, mssql) | yes | — | | `host` | Host | yes | — | | `port` | Port | no | — | | `database` | Database / service name | yes | — | | `username` | Admin account (approved fixes) | yes | — | | `password` (secret) | Password | yes | — | | `read_username` | Read-only account (evidence and polls) | no | — | | `read_password` (secret) | Its password | no | — | | `service` | The service this database belongs to (as the estate names it) | no | — | | `expected_role` | What this instance should be: primary or replica. 3AM makes a read-only database writable only if it is declared the primary (primary, replica) | no | — | | `tls` | Require TLS | yes | `false` | | `ca_file` | CA certificate (PEM) | no | — | | `max_rows_affected` | Refuse writes that would change more rows than this | yes | `1000` | | `max_sessions_killed` | Most sessions kill_idle_sessions closes at once | yes | `50` | | `protected_users` | Users whose sessions 3AM never closes (replication, monitoring) | no | — | | `down_after_s` | Seconds failing before it's an incident | yes | `60` | | `connections_ratio` | Connections used (of the maximum) before it's an incident | yes | `0.9` | | `long_transaction_s` | Seconds a transaction may stay open | yes | `600` | | `blocked_after_s` | Seconds sessions may wait on a lock | yes | `60` | | `lag_s` | Replication lag before it's an incident | yes | `300` | | `poll` | Watch this database (signals) | yes | `true` | ## 3. Verify ```text title="Test connection" postgres 17.11, primary, 1/30 connections ``` Test connection also tries the admin account, and names any view the read account can't see. ## What it detects | Signal | Meaning | | ------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | `DatabaseDown` | It hasn't answered for `down_after_s` (60 s), with the cause: refused, hung, DNS, credentials, too many connections, starting or recovering, out of disk | | `DatabaseRestarted` | It restarted since the last look, including PostgreSQL's in-place crash recovery | | `DatabaseConnectionsHigh` | Connections used are above `connections_ratio` (90%) of the slots applications can use | | `DatabaseBlockedSessions` | Sessions have waited on a lock for `blocked_after_s` (60 s) | | `DatabaseLongTransaction` | A transaction has been open for `long_transaction_s` (10 min) | | `DatabaseIdleInTransaction` | Sessions sit idle inside an open transaction (PostgreSQL) | | `DatabaseReadOnly` | A primary is read-only, so writes fail | | `DatabaseReplicationStopped`, `DatabaseReplicationLag` | A replica stopped, or is more than `lag_s` (5 min) behind | Alerts from Prometheus exporters (`MySQLDown`, `MySQLTooManyConnections`, `PostgresqlDown`, ...) reach the same causes. ## What it proves and fixes | Cause | How 3AM knows | Fix | | ------------------------------- | ---------------------------------------------------------------------------------------- | ----------------------------------------------------------- | | A lock held by another session | Sessions wait on a lock; the blocking session is named, with its user and last statement | End the blocking session (its transaction rolls back) | | A connection leak | Connections near the limit, most held idle by one user for minutes | Close that user's sessions idle for 2 minutes or more | | A read-only primary | Declared primary, read-only, up | Make it writable again | | Replication stopped cleanly | The replica stopped with no error (MySQL) | Start replication | | Replication stopped on an error | The error, for example a duplicate key | Advice for a DBA: skipping it would make the replica differ | | An inactive replication slot | It holds more than 1 GB of WAL (PostgreSQL) | Advice: drop it once its consumer is gone for good | | Transaction ID wraparound near | `age(datfrozenxid)` above 1.2 billion (PostgreSQL) | Advice: VACUUM (FREEZE) | ## Actions | Action | Parameters | Notes | | ----------------------------------- | ---------------- | --------------------------------------------------------------------------------- | | `kill_session` | `id` | Refuses 3AM's own session, the database's own processes, and `protected_users` | | `cancel_query` | `id` | Cancels the running statement; the session stays | | `kill_idle_sessions` | `user`, `idle_s` | At most `max_sessions_killed` (50) at a time; `idle_s` at least 30 | | `set_read_write` | | A declared primary only, never a replica | | `start_replication` | | MySQL, only when it stopped without an error | | `sql.write` | `statement` | One statement, in a transaction, rehearsed, capped at `max_rows_affected` (1,000) | | `db_status`, `sessions`, `sql.read` | | Read-only evidence; `sql.read` runs in a read-only transaction with a 15 s limit | ## Certification Live on MySQL 8.4 and PostgreSQL 17, with the accounts above and 3AM inside a Kubernetes cluster: **60 of 60 checks passed** (2026-10-05). The oldest supported versions, MySQL 8.0 and PostgreSQL 14, passed the same scenarios. * **The connector.** Both accounts on primaries and replicas; the read account can't write; a wrong password and a name that doesn't resolve are explained. * **Locks.** An idle transaction held a row lock on each engine. 3AM ended the blocking session, and the waiting update went through. * **Connection leaks.** A client leaked connections until none were left. On MySQL, 3AM read and fixed it through the admin account's reserved slot. On both engines it closed exactly the leaking client's idle sessions. * **Read-only primary.** Made writable again and checked with a write. The replica, read-only by design, raised nothing. * **Replication.** Stopped cleanly: started again. Broken by a duplicate key: advice, with the real error in the evidence. * **The database down.** A frozen database and a killed database process were each restarted through Kubernetes and verified. A full disk crashed PostgreSQL, which recovered with writes failing. 3AM proved it from the log and didn't restart it. ## Troubleshooting | You see | Do this | | ------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `it refuses 3AM's credentials` | The password was rotated or expired, or the account is locked. Update the credential file | | `3AM's account may not use the configured database` | Grant the account access to the application's schema (MySQL: `SELECT ON .*`) | | `can't see: other users' sessions` | PostgreSQL: grant `pg_read_all_stats` (part of `pg_monitor`) | | `its name doesn't resolve` for a database in Kubernetes | A headless Service stops publishing a pod that isn't Ready: the database is hung or crashed, not your DNS | | A full disk, but no incident yet | Until PostgreSQL's next checkpoint (up to 5 minutes) a full database still answers reads. Only a disk-space alert from monitoring (for example `KubePersistentVolumeFillingUp`) shows it sooner | | `isn't declared the primary` | Set `expected_role: primary` on the connector of the instance that should take writes |