diagnostic-db-transients

Diagnostic: Transient Buildup Detection

You analyze the WordPress options table for transient accumulation — particularly expired transients that have not been cleared — and report aggregate counts with ratio-based severity to identify sites where transient buildup is degrading database performance.

Overview

WordPress uses transients as a lightweight caching mechanism: plugins store temporary data in the wp_options table using set_transient(). Each transient consists of two rows:

1. _transient_{name} — the cached value
2. _transient_timeout_{name} — the expiry timestamp

When a transient expires, WordPress removes it the next time it is accessed (lazy cleanup). On sites with low traffic to specific cached pages, or on sites using plugins that write transients but never clean them up, expired transients accumulate. This causes:

• Database bloat: Expired transients consume disk space in the options table
• Query slowdown: Larger tables mean slower full-table scans
• Autoload contamination: Some plugins incorrectly mark transients as autoload, loading dead data on every request

Important: wp transient list does NOT have an --expired flag. The only reliable way to count expired transients is via direct SQL comparison of the timeout value against UNIX_TIMESTAMP(). This skill uses that approach exclusively.

How It Works

Prerequisites

Before running checks, you need:

• Site connection profile from sites.json (loaded by CoWork or /diagnose)
• WP_CLI_PREFIX set by the /diagnose command's Source-Type Routing section
• WP_CLI_AVAILABLE=true (this skill requires WP-CLI for all checks)

If WP_CLI_AVAILABLE is false, return a single Warning finding and stop.

Step 1: Dynamic Table Prefix Retrieval

Never hardcode wp_. Always retrieve the prefix dynamically before running any queries:

# Primary method: wp db prefix (most authoritative — reflects active DB handler)
TABLE_PREFIX=$($WP_CLI_PREFIX db prefix 2>/dev/null | tr -d '[:space:]')

# Fallback: wp config get table_prefix
if [ -z "$TABLE_PREFIX" ]; then
  TABLE_PREFIX=$($WP_CLI_PREFIX config get table_prefix 2>/dev/null | tr -d '[:space:]')
fi

# If both fail, cannot proceed
if [ -z "$TABLE_PREFIX" ]; then
  echo '[{"id":"DBHL-TRANS-ERR","severity":"Warning","category":"Database Health","title":"Could not determine table prefix","summary":"The transient analysis could not run because the database table prefix could not be retrieved.","detail":"Both wp db prefix and wp config get table_prefix returned empty results. This may indicate a database connection failure or WP-CLI misconfiguration.","location":"wp_options table","fix":"Verify that WP-CLI can connect to the database by running: wp db check. Check the site connection profile and ensure WP-CLI has valid database credentials."}]'
  exit 0
fi