Diagnosing Vitess Query Routing Failures: 'No Tablets Available'

The Problem: Queries Failing Despite Active Databases

A common failure mode in Vitess occurs when a client receives a No tablets available or Tablet not found error, yet the underlying MySQL instances are healthy and running. This indicates a routing failure between vtgate (the proxy layer) and vttablet (the tablet server). The issue is almost always a mismatch between the actual state of the database and the metadata stored in the Topology Service (typically etcd or ZooKeeper).

Routing Failure Diagnostics

When queries fail to route, use the following table to identify the most likely cause based on the observed behavior.

Symptom	Likely Cause	Diagnostic Indicator
Immediate failure for all shards	Topology Connection Loss	vtgate logs show connection timeouts to etcd/ZooKeeper
Failure for specific shards/keyspaces	Stale Topology Cache	vtctlClient GetTopology shows correct state, but vtgate logs show old tablet IDs
Intermittent routing failures	Tablet State Mismatch	Tablet is flapping between PRIMARY and OFFLINE
'No tablets available' for a new shard	Missing Shard Mapping	Topology service lacks a mapping for the requested shard ID

Step-by-Step Routing Verification

Follow these checks in order to isolate where the routing chain is broken. These steps assume you are using vtctlClient with the necessary administrative permissions to access the Topology Service.

1. Validate Tablet State

If a tablet is marked as OFFLINE in the topology, vtgate will ignore it, even if the MySQL process is healthy. Run the following command to check the current state of tablets for a specific keyspace:

# Run on a management node with vtctlClient configured
vtctlClient GetTopology --keyspace <keyspace_name>

What to look for: Ensure the tablet assigned to the shard is in the PRIMARY or REPLICA state. If it is listed as OFFLINE, it will not receive traffic.

2. Verify Topology Service Connectivity

If vtgate cannot reach the Topology Service, it relies on a cached version of the routing table. If that cache expires or is corrupted, routing fails. Check the vtgate logs for errors related to the topology provider.

Risk: Do not restart all vtgate instances simultaneously if you suspect a topology issue; this can create a "thundering herd" effect, overloading the Topology Service with simultaneous refresh requests.

3. Check for Shard Misplacement

Verify that the query is being routed to the expected shard. If you recently performed a resharding operation, the vtgate may still be attempting to route queries to the old shard mapping.

Applying Fixes

Depending on the findings above, apply the corresponding fix. Warning: Never manually edit the etcd or ZooKeeper backend. Always use vtctlClient to ensure metadata consistency.

Fix A: Restore Tablet State

If the tablet is healthy but marked OFFLINE, move it back to the active state:

# Move a tablet to PRIMARY state
vtctlClient TabletState <cell> <tablet_id> PRIMARY

Fix B: Force Topology Refresh

If the Topology Service is correct but vtgate is using stale data, you can trigger a refresh. While vtgate usually polls for changes, a manual restart of the vtgate process is the most reliable way to clear the internal routing cache.

# Example: Restarting a vtgate systemd service
sudo systemctl restart vtgate

Verification and Validation

To confirm the routing path is restored, perform these three checks:

1. Functional Test: Execute a simple SELECT 1; through the vtgate endpoint for the affected keyspace.
2. Topology Check: Run vtctlClient GetTopology again to ensure the state persists.
3. Metric Analysis: Check the vtgate Prometheus metrics for topology_update_failures. A rising counter here indicates that vtgate is failing to sync with the Topology Service.

Escalation Criteria

If the following conditions persist after the fixes above, escalate to the database infrastructure team:

• vtctlClient commands time out when attempting to reach the Topology Service.
• Tablets automatically revert to OFFLINE state immediately after being set to PRIMARY (indicates a health-check failure).
• Routing failures occur only for specific key ranges despite correct shard mappings (indicates a potential VReplication or keyspace configuration error).