CockroachDB SQL error rate: XX000 internal errors and error-code triage

sql_failure_count is a single Prometheus counter with no error-code labels. A serialization conflict (40001) is expected noise in a contended workload. An internal error (XX000) is a database fault that needs immediate escalation. Treating the aggregate counter as one signal guarantees the wrong response.

This article covers how to break that aggregate apart by error code, when XX000 internal errors warrant paging, and how to triage the other common error classes.

What this means

sql_failure_count increments every time a SQL statement results in a planning or runtime error. It does not break down by error code natively. To understand what is failing, correlate the counter with per-statement statistics, application logs, or the DB Console Insights page.

CockroachDB follows the PostgreSQL error code standard. The distribution across codes reveals the failure type:

XX000 is the code that warrants paging. It means the database hit an unexpected internal condition: an assertion failure, a corrupted data structure, or a software bug. These errors are not client-retriable. A sustained nonzero rate means the database is actively malfunctioning.

The other codes represent workload or configuration issues. They need attention but do not indicate database faults.

Triage flow when sql_failure_count spikes:

flowchart TD
    A["sql_failure_count rising"] --> B["Break down by error code"]
    B --> C{"XX000 internal error?"}
    C -->|Yes| D["PAGE: database fault"]
    D --> D1["Capture stack trace from logs"]
    D1 --> D2["Check version for known bugs"]
    D2 --> D3["Escalate to Cockroach Labs support"]
    C -->|No| E{"40001 serialization?"}
    E -->|Yes| F["Check contention signals"]
    F --> F1["txn_restarts by cause"]
    E -->|No| G{"53200 out of memory?"}
    G -->|Yes| H["Check sql_mem_root_current"]
    H --> H1["Increase budget or optimize queries"]
    G -->|No| I{"57014 query canceled?"}
    I -->|Yes| J["Check timeout configuration"]
    I -->|No| K["Check connection errors"]

Common causes

Quick checks

All commands are read-only and safe to run during an active incident.

# Check the overall SQL failure rate
curl -s http://localhost:8080/_status/vars | grep 'sql_failure_count'

# Check connection failure rate separately
curl -s http://localhost:8080/_status/vars | grep 'sql_conn_failures'

-- Identify which statement fingerprints are failing most often (last_error_code is set on failures)
SELECT key, count, last_error_code FROM crdb_internal.node_statement_statistics
WHERE last_error_code IS NOT NULL
ORDER BY count DESC LIMIT 20;

# Check transaction restart rate and cause breakdown
curl -s http://localhost:8080/_status/vars | grep 'txn_restarts'

# Check SQL memory pressure (53200 precursor)
curl -s http://localhost:8080/_status/vars | grep 'sql_mem_root_current'

# Check SQL throughput to calculate error rate as fraction of total
curl -s http://localhost:8080/_status/vars | grep -E 'sql_(select|insert|update|delete)_count'

# Search logs for XX000 internal errors with stack traces
# Adjust the path to match your actual cockroach-data location
grep -r "internal error" /path/to/cockroach-data/logs/

Note that crdb_internal tables are version-sensitive, require admin privileges, and should be used for manual diagnosis only. Do not build automated monitoring pipelines on them.

How to diagnose it

1. Confirm the error rate is real. Check sql_failure_count rate over the last 5-10 minutes. A single transient error is noise. A sustained rate over more than 1 minute is actionable.

2. Determine the error code distribution. The sql_failure_count counter does not break down by code. Use the DB Console Insights page (Failed Execution view), which shows the error code for each failed statement, or application-level error logging to identify which statements are failing and what error codes they return. The crdb_internal.node_statement_statistics table records last_error and last_error_code for failing statements; use the Insights page or application logs to map those fingerprints to specific SQLSTATE codes.

3. If XX000: treat as a database fault. XX000 errors include a stack trace in the error DETAIL. If the error message does not contain a stack trace, it may not be a true XX000 internal error. Capture the full error text including the stack trace. Check your CockroachDB version against known issues on GitHub.

4. If 40001: check contention depth. Pull txn_restarts broken down by cause. writetooold indicates write-write contention on hot keys. readwithinuncertainty indicates clock skew. txnpush indicates transaction conflicts. Each cause points to a different layer of the stack.

5. If 53200: check memory pressure. Verify sql_mem_root_current against the configured --max-sql-memory. A single large query (hash join, sort) can exhaust the per-node budget and reject all concurrent queries.

6. If 57014: check timeout configuration. These are client-initiated cancellations or server-side statement timeouts. They indicate queries running longer than allowed, which may point to plan regression, missing indexes, or storage degradation.

7. If 08006/08001: check connectivity. These are connection-level errors, not statement execution errors. Check certificate validity, node liveness, and network reachability. Connection errors increment sql_conn_failures, not sql_failure_count.

Metrics and signals to monitor

Fixes

XX000 internal errors

XX000 errors indicate a database bug or corruption. You cannot fix them in the moment. The operational response is:

• Capture the full error and stack trace from logs or the Insights page. This is essential for support.
• Check your CockroachDB version against release notes and GitHub issues. XX000 bugs are often fixed in patch releases.
• Upgrade if a fix exists. Do not attempt workarounds for assertion failures or vectorized engine panics.
• Escalate to Cockroach Labs support with the stack trace, query fingerprint, and version number.
• If corruption is suspected, look for explicit “consistency check failed” or “checksum mismatch” messages from the consistency checker subsystem. Do not act on loose log matches that merely contain the word “corrupt” in an unrelated context.

40001 serialization failures

These indicate contention, not a database fault. Responses include:

• Identify hot keys via crdb_internal.transaction_contention_events.
• Check for sequential primary key patterns causing hot ranges.
• Ensure the application implements proper retry logic using SAVEPOINT cockroach_restart or client-side retry with exponential backoff.
• Consider schema changes to reduce write conflicts (hash-prefixed keys, smaller transaction scopes).

53200 resource exhaustion

• Increase --max-sql-memory if the node has available RAM.
• Identify and optimize the query consuming disproportionate memory (large sorts, hash joins on unindexed columns).
• Ensure the application is not issuing full table scans without proper predicates.

57014 query canceled

• Review statement timeout settings.
• Investigate why queries exceed the timeout: plan regression, storage latency, or lock contention.
• Do not simply increase the timeout without understanding the root cause.

08006/08001 connection failures

• Verify certificate validity on all nodes and clients.
• Check that the node is live and accepting connections.
• Verify network connectivity and firewall rules.
• Ensure clients connect through a load balancer using /health?ready=1 for health checks, not plain TCP.

Prevention

• Alert on error code class, not just the aggregate counter. A single sql_failure_count alert tells you something is wrong but not what. Correlate with the Insights page or application logs to identify the dominant error code.
• Gate XX000 alerts on sustained duration. A single XX000 event may be a transient bug trigger. Page only on sustained nonzero XX000 rate for more than 1 minute.
• Track your CockroachDB version against known issues. XX000 bugs are version-specific. Knowing your version narrows the diagnosis immediately.
• Ensure client retry logic handles 40001 correctly. Serialization failures are expected in CockroachDB’s serializable isolation model. Clients that do not retry 40001 errors will surface them as application errors.
• Monitor sql_conn_failures alongside sql_failure_count. Connection failures are tracked separately. Monitoring only sql_failure_count creates a blind spot for connectivity issues.
• Use /health?ready=1 for load balancer health checks, not plain TCP checks. This prevents routing traffic to draining or impaired nodes that will generate connection errors.

How Netdata helps

• Netdata collects sql_failure_count and sql_conn_failures per second, so you see error rate changes immediately rather than waiting for a longer scrape interval.
• Correlating sql_failure_count spikes with txn_restarts by cause, sql_mem_root_current, and node liveness in a single view shortens error-code triage from minutes to seconds.
• ML anomaly detection flags unusual error rate patterns, including subtle increases in serialization failures that a fixed threshold would miss.
• Per-node breakdowns reveal whether errors are concentrated on one node (hot range, storage issue) or cluster-wide (schema problem, version bug).
• Node uptime gating suppresses false-positive error alerts during rolling restarts and cold starts.

Netdata’s CockroachDB monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• CockroachDB clock_offset_meannanos high: catching clock drift before self-termination
• CockroachDB clock skew cascade: how shared NTP drift causes quorum loss
• CockroachDB clock synchronization error: this node is more than 500ms away from at least half of the known nodes
• CockroachDB compaction backlog growing: when Pebble can’t keep pace with writes
• CockroachDB context deadline exceeded: timeouts, slow ranges, and overload
• CockroachDB CPU saturation: Raft ticking, SQL execution, and the per-node ceiling
• CockroachDB detecting hot ranges: per-range QPS, CPU asymmetry, and the Hot Ranges page
• CockroachDB disk space running out: capacity_available trends and the 20% rule
• CockroachDB disk stall detected: storage_disk_stalled and node self-termination
• CockroachDB file descriptor exhaustion: SSTables, connections, and ulimit
• CockroachDB Go GC pauses high: when garbage collection threatens Raft heartbeats
• CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles