MCP Tables

ProxySQL v4.0.10 provides editable MCP query rules and backend profiles in the Admin schema, persistent copies in the configuration database, and live runtime_mcp_* projections of module state.

Table Inventory

mcp_query_rules

The mcp_query_rules table defines policy for SQL execution through the MCP Query handler. In v4.0.10 the rules are evaluated by run_sql_readonly and explain_sql; they are not a universal filter for /mcp/stats, /mcp/config, or every other endpoint.

CREATE TABLE mcp_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    target_id VARCHAR,
    schemaname VARCHAR,
    tool_name VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagIN INT NOT NULL DEFAULT 0,
    flagOUT INT CHECK (flagOUT >= 0),
    replace_pattern VARCHAR,
    timeout_ms INT CHECK (timeout_ms >= 0),
    error_msg VARCHAR,
    OK_msg VARCHAR,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK (apply IN (0,1)) NOT NULL DEFAULT 1,
    comment VARCHAR
);

• active: only active rules participate after they are loaded.
• username, target_id, schemaname, and tool_name: request selectors.
• match_pattern, negate_match_pattern, and re_modifiers: argument/SQL matching.
• flagIN and flagOUT: rule-chain state.
• replace_pattern: rewritten SQL/action when set.
• timeout_ms: per-rule Query-handler timeout override when greater than zero.
• error_msg: blocks the request with the supplied error.
• OK_msg: returns the supplied success without executing SQL.
• log: requests logging; the released Query handler contains a pending logging branch, so this is not a complete audit guarantee.
• apply: stops further rule evaluation when set.

Load and persist rules with the LOAD MCP QUERY RULES ... and SAVE MCP QUERY RULES ... command family.

runtime_mcp_query_rules

runtime_mcp_query_rules has the same columns as mcp_query_rules. It is a read-only, chassis-projected view of the module’s in-memory snapshot: each Admin SELECT refreshes its rows through the plugin callback. Do not write to it directly.

mcp_auth_profiles

Authentication profiles keep backend credentials on the server so MCP clients select a logical target without sending database usernames or passwords.

CREATE TABLE mcp_auth_profiles (
    auth_profile_id VARCHAR PRIMARY KEY NOT NULL,
    db_username VARCHAR NOT NULL,
    db_password VARCHAR NOT NULL,
    default_schema VARCHAR DEFAULT '',
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    ssl_mode VARCHAR DEFAULT '',
    comment VARCHAR DEFAULT ''
);

• auth_profile_id: identifier referenced by target profiles.
• db_username and db_password: backend credentials applied internally by the Query executor.
• default_schema: default backend schema.
• use_ssl and ssl_mode: stored backend TLS intent.
• comment: operator metadata.

runtime_mcp_auth_profiles

runtime_mcp_auth_profiles has the same schema, including db_password, as mcp_auth_profiles. It is refreshed on each Admin SELECT from the in-memory MCP authentication-profile snapshot and has no independently persistent rows.

mcp_target_profiles

Target profiles map a client-visible logical target to a backend protocol, ProxySQL hostgroup, authentication profile, and policy metadata.

CREATE TABLE mcp_target_profiles (
    target_id VARCHAR PRIMARY KEY NOT NULL,
    protocol VARCHAR NOT NULL CHECK (protocol IN ('mysql','pgsql')),
    hostgroup_id INT CHECK (hostgroup_id >= 0) NOT NULL,
    auth_profile_id VARCHAR NOT NULL,
    description VARCHAR DEFAULT '',
    max_rows INT CHECK (max_rows > 0) NOT NULL DEFAULT 200,
    timeout_ms INT CHECK (timeout_ms >= 0) NOT NULL DEFAULT 2000,
    allow_explain INT CHECK (allow_explain IN (0,1)) NOT NULL DEFAULT 1,
    allow_discovery INT CHECK (allow_discovery IN (0,1)) NOT NULL DEFAULT 1,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    comment VARCHAR DEFAULT ''
);

• target_id: value accepted by Query tools and returned by list_targets.
• protocol: mysql or pgsql.
• hostgroup_id: ProxySQL backend hostgroup used for routing.
• auth_profile_id: server-side credential profile to join at load time.
• description: client-visible/operator description.
• max_rows: positive per-target row-limit metadata, default 200.
• timeout_ms: non-negative per-target timeout metadata, default 2000 ms.
• allow_explain and allow_discovery: per-target capability-policy metadata.
• active: whether the target is available to the runtime registry.
• comment: operator metadata.

MCP clients should call list_targets and pass a target_id; they must not submit backend credentials as tool arguments.

runtime_mcp_target_profiles

runtime_mcp_target_profiles has the same schema as mcp_target_profiles. It is a read-only per-SELECT projection of the in-memory target snapshot. The Query handler joins the loaded target and authentication snapshots into its target registry.

Loading and Saving Profiles

Authentication and target profiles form one runtime mapping and are loaded together:

LOAD MCP PROFILES TO RUNTIME atomically installs both editable tables and rebuilds the joined target/auth map. Selecting a runtime table then asks the chassis to project the corresponding in-memory snapshot; LOAD does not copy rows directly into the runtime table.

stats_mcp_query_rules

This stats-schema table reports hit counts from the loaded MCP rule set.

CREATE TABLE stats_mcp_query_rules (
    rule_id INTEGER PRIMARY KEY NOT NULL,
    username VARCHAR,
    target_id VARCHAR,
    hits INTEGER NOT NULL
);

Other MCP invocation and digest counters are documented under MCP Stats.