MCP Autodiscovery

ProxySQL v4.0 provides a two-phase discovery workflow that allows LLMs to work from a captured database structure.

How it works

The discovery process is typically triggered by an AI agent using the discovery.run_static tool. The tool resolves one released MCP target profile to one ONLINE MySQL or PostgreSQL backend and populates the MCP Catalog.

Phase 1: Static Harvesting

ProxySQL connects to the backend selected for that target and extracts:

• Schema names.
• Table and View definitions.
• Column metadata (types, nullability, defaults).
• Index definitions.
• Foreign key relationships.

Phase 2: Metadata Enrichment

Once the basic structure is harvested, ProxySQL (optionally with LLM assistance) can:

• Infer relationships between tables.
• Generate summaries for complex views or tables.
• Track “Agent Runs” to maintain context between different LLM sessions.

Discovery Run IDs

Every successful discovery execution is assigned an integer run_id. This allows ProxySQL to maintain a history of the schema state. AI agents can bind themselves to a specific run_id to ensure consistency even if the schema changes while they are processing a request.

Storage (The MCP Catalog)

The harvested metadata is stored in a local SQLite database named mcp_catalog.db, located in ProxySQL’s data directory. This catalog powers the search and retrieval tools used by the MCP server.

For a detailed look at the catalog structure, see the MCP Catalog documentation.

Configuration

Discovery requires a loaded MCP target profile and authentication profile. The target maps a protocol and hostgroup to server-side credentials; clients obtain its target_id from list_targets and never send backend credentials as tool arguments. See MCP configuration tables.

• MySQL backends: The target references a hostgroup defined in mysql_servers; its MCP authentication profile must have the metadata privileges required by the harvester.
• PostgreSQL backends: The target references a hostgroup defined in pgsql_servers; its MCP authentication profile must be able to read information_schema or pg_catalog.

The released target resolver selects one ONLINE backend from the target hostgroup. The harvester connects directly to that selected endpoint and queries INFORMATION_SCHEMA or the PostgreSQL catalogs.

Performance Considerations

Discovery queries metadata on the selected target endpoint. This can take significant time on large schemas:

• Databases with thousands of tables may take tens of seconds to fully harvest.
• Discovery is a one-off (or infrequent) operation and does not affect query routing performance once complete.
• During discovery, the harvester opens its own connection to the selected endpoint rather than borrowing a normal routed backend connection.
• It is recommended to run discovery during low-traffic periods if the backend has a very large number of objects, since INFORMATION_SCHEMA queries can themselves be resource-intensive on MySQL.

Example: Triggering Discovery and Querying the Catalog

The following illustrates a typical workflow using the MCP tools via an AI agent or MCP client:

Step 1 — Trigger discovery:

{
  "tool": "discovery.run_static",
  "target_id": "mysql-main"
}

Response:

{
  "run_id": 42,
  "target_id": "mysql-main",
  "protocol": "mysql",
  "objects": { "table": 142, "view": 8 },
  "columns": 1087,
  "indexes": 96,
  "foreign_keys": 31
}

Step 2 — Search the catalog for a relevant table:

{
  "tool": "catalog.search",
  "target_id": "mysql-main",
  "run_id": "42",
  "query": "customer orders"
}

Response:

[
  {
    "object_key": "shop.orders",
    "schema_name": "shop",
    "object_name": "orders",
    "object_type": "table",
    "tags": "sales,customer",
    "score": -4.72
  },
  {
    "object_key": "shop.customers",
    "schema_name": "shop",
    "object_name": "customers",
    "object_type": "table",
    "tags": "customer",
    "score": -3.91
  }
]

Step 3 — Inspect the table definition:

{
  "tool": "catalog.get_object",
  "target_id": "mysql-main",
  "run_id": "42",
  "object_key": "shop.orders"
}

Step 4 — Run a read-only query:

{
  "tool": "run_sql_readonly",
  "target_id": "mysql-main",
  "sql": "SELECT COUNT(*) FROM shop.orders WHERE status = 'pending'",
  "schema": "shop"
}

The catalog persists between sessions; you do not need to re-run discovery unless the schema has changed.