Query timeouts

Each tool call is limited to query_timeout seconds, enforced in two layers on MySQL, MariaDB, and PostgreSQL:

1. Server timeout. Before each statement, dbmcp sets a session statement timeout to what is left of query_timeout (time spent waiting for a pooled connection counts), so a SET sent through writeQuery can't lift it. The database stops the statement itself, and the connection stays usable. On MySQL and MariaDB this costs one extra round trip per query; PostgreSQL sets it in the same round trip as the session id lookup.
2. Client backstop. If no answer arrives within query_timeout + 5 s (network stall, proxy, or a statement the server timeout doesn't cover), dbmcp returns the timeout error and kills the statement from a separate connection (KILL QUERY / pg_cancel_backend), using the same credentials.

Either way the agent sees Query timed out after …, followed by a hint to check the plan with explainQuery, filter on indexed columns, or limit inner result sets before joining or aggregating. Setting 0 disables both layers. Server defaults and any limit a DBA set then apply, and a server-side timeout still reports as Query timed out.

On SQLite there is no server. Before each statement, dbmcp installs a progress handler that checks the deadline every 1,000 virtual machine instructions and interrupts the statement once query_timeout has passed. The connection stays usable, so the next tool call runs right away. Only the interrupted statement is rolled back: earlier statements of a multi-statement writeQuery stay committed. The client backstop still applies, but there is no session to kill.

On MySQL, a SLEEP() interrupted by the server timeout returns 1 instead of an error, so the call succeeds early rather than timing out.

With read_only: false, writes and DDL are also subject to the timeout. A long migration or bulk UPDATE run through dbmcp is stopped. Raise query_timeout or set it to 0 for such work.

Per-Call TimeoutCopy Anchor Link

readQuery takes an optional timeout argument in seconds. It replaces query_timeout for that call, on every layer above. It must be at least 1 and at most max_query_timeout; any other value is rejected with an error naming the limit. Paging a SELECT with cursor applies timeout per call, so pass it again with each cursor.

max_query_timeout defaults to query_timeout, so an agent can only shorten the limit until you raise the cap:

database:
  query_timeout: 30       # every call
  max_query_timeout: 300  # readQuery may ask for up to 5 minutes

Setting max_query_timeout: 0 removes the cap, up to the hard limit of 2,147,483 seconds (PostgreSQL's statement_timeout maximum). Startup fails if you set a positive max_query_timeout below query_timeout, or any positive max_query_timeout while query_timeout is 0. While query_timeout is 0, readQuery rejects the timeout argument. Other tools always use query_timeout.

Connection Poolers and ProxiesCopy Anchor Link

• PgBouncer transaction mode: not supported for timeouts. The session timeout can leak to other clients, and the backstop kill can hit another client's statement. Use session mode, or skip dbmcp's timeout (query_timeout: 0) and set one on the role: ALTER ROLE dbmcp SET statement_timeout = '30s'.
• RDS Proxy: the session SET pins each connection to one backend, so proxy multiplexing is lost. Timeouts still work. To avoid pinning, use query_timeout: 0 plus a role- or parameter-group-level timeout.
• ProxySQL: supported. It answers CONNECTION_ID() and routes KILL itself.

TroubleshootingCopy Anchor Link

If the session SET fails (wrong backend, value out of range), dbmcp logs a warn on every query and relies on the backstop, which then fires at query_timeout without the extra 5 s.