translate_exception never maps "DBPROCESS is dead or not enabled" to a connection error

[bhenn]

---

Versions

• activerecord-sqlserver-adapter 8.0.5
• tiny_tds 2.1.7
• rails 8.0.2
• FreeTDS 1.4.26
• SQL Server (on-prem, TLS-encrypted TDS connection)

Summary

When the TCP/TLS connection underlying a TinyTds::Client is killed while idle (no in-flight query), tiny_tds/FreeTDS has no way to know until the next query is attempted on it. That query fails with:

TinyTds::Error: DBPROCESS is dead or not enabled

SQLServerAdapter#translate_exception doesn't match this message against any of its patterns, so it falls through to StatementInvalid — a plain "your SQL was bad" error, not a connection error. This has two compounding effects, one already known, one that as far as we can tell is new:

1. (Previously reported, still unfixed) No reconnect happens

Because the exception isn't ConnectionNotEstablished, Rails has no signal that the connection itself is broken. The pool checks the same dead connection back in, and the next request that happens to check it out fails identically. This repeats until the process is restarted. This exact symptom has been reported and closed without a landed fix at least 9 times since 2013: #267, #402, #510, #525, #623, #668, #970 (and probably others we didn't find).

2. (New, as far as we can tell) Rails 8's own self-healing mechanism can't save this either

Rails 8's AbstractAdapter#with_raw_connection added exactly the kind of resiliency this needs — it marks a connection @verified = false after any non-retryable query error, and calls verify! (which calls active?) before the connection's next use, reconnecting if it's not active. On paper this should already fix the cascade, no adapter change needed.

In practice it doesn't help here, because:

# tiny_tds ext/tiny_tds/client.c
static VALUE rb_client_dead_p(VALUE self) {
  ...
  return dbdead(cwrap->client) ? Qtrue : Qfalse;
}

dead? (and therefore active?, and therefore verify!) is backed by FreeTDS's dbdead(), which is a passive flag — it only flips to true after an I/O operation fails and the error handler marks the connection dead. It cannot detect that an idle connection was silently killed (e.g. by a TCP RST from the server, or a stateful device between client and server dropping the session) without actually attempting I/O on it. So verify! calls active?, gets a stale "true", and hands the same broken connection right back out — Rails' own opt-in retry path (retryable_connection_error? checking for ConnectionNotEstablished/ConnectionFailed) never triggers because the exception was never classified as one in the first place.

Put together: even on Rails 8, without the translate_exception fix, there is currently no path — old or new — that recovers from this automatically.

Production evidence

We've spent several months chasing this in a production Rails 8 app against SQL Server and have fairly complete forensic evidence:

• Confirmed via packet capture: a clean TCP RST originating from the SQL Server host itself, killing 2–3 app-side connections simultaneously, at the exact moment failures begin.
• Every incident shows one connection dying once, then the identical query being retried on that same dead handle dozens to hundreds of times before a full process restart — never a genuine reconnect.
• Failure-rate math checks out with pool poisoning: one dead connection out of a pool of N produces almost exactly a 1/N request failure rate, sustained for 45–90 minutes until restart, matching what we've measured in production.

Proposed fix

Add the message to the existing pattern list in translate_exception:

# lib/active_record/connection_adapters/sqlserver_adapter.rb
when /(SQL Server client is not connected)|(failed to execute statement)|(DBPROCESS is dead or not enabled)/i
  ConnectionNotEstablished.new(message, connection_pool: @pool)

Questions for maintainers

1. Would a PR adding this pattern (and any other known-fatal TinyTds::Error messages you're aware of) be welcome?
2. Given point 2 above, is there any appetite for making tiny_tds's active?/dead? do a real liveness probe (e.g. a trivial round trip) rather than relying purely on FreeTDS's passive dbdead() flag — or is that considered out of scope / too costly to do on every checkout?

[bhenn]

Apparently this PR fixes something very similar to this

#1393

[aidanharan]

@bhenn Thanks for this.

1. Yes PR welcome for this pattern and any other known-fatal messages.
2. It appears from first glance that the Rails MySQL, PostgreSQL and Trilogy database adapters perform roundtrips to perform the liveness check. If that is the case then SQL Server adapter should follow the same pattern. MySQL/Trilogy call a ping on the raw connection and PostgreSQL does an empty query. PR welcome.