v4.0/MCP: LOAD MCP PROFILES TO RUNTIME silently drops target profiles, and runtime_mcp_target_profiles disagrees with list_targets

Summary

LOAD MCP PROFILES TO RUNTIME builds the joined target_auth_map that the MCP query endpoint actually uses, and silently discards every target profile that is inactive or whose auth_profile_id does not resolve. The command still returns OK, nothing is logged, and no counter or table records the drop.

At the same time runtime_mcp_target_profiles is projected from the unjoined target_profiles_ vector, so the discarded target is fully visible there.

The result is a state that is very hard to diagnose from the admin interface: runtime_mcp_target_profiles shows the target, LOAD MCP PROFILES TO RUNTIME reported success, and list_targets returns {"targets": []}. The error string the operator gets from other tools in that state is actively misleading — it names the very table that visibly contradicts it.

Affected version / build

Branch v3.0, v4.0 tier (PROXYSQL40=1), verified at 5f9806ea528af5d9d7fffd3900f2810bc6279d41.

Root cause

1. The join drops rows without a word.

plugins/genai/src/MCP_Thread.cpp:494  MCP_Threads_Handler::rebuild_target_auth_map_locked()

for (const auto& t : target_profiles_) {
    if (t.active == 0) continue;
    auto it = by_id.find(t.auth_profile_id);
    if (it == by_id.end()) continue;  // dangling FK; skip silently
    ...
}

Note that mcp_target_profiles.auth_profile_id has no FOREIGN KEY constraint against mcp_auth_profiles (include/ProxySQL_Admin_Tables_Definitions.h:468-480), so a dangling reference is accepted by the INSERT without complaint and only manifests here.

2. The caller cannot report it either. install_profiles_from_admin() (plugins/genai/src/MCP_Thread.cpp:717) calls rebuild_target_auth_map_locked() and returns true unconditionally; the admin verb at plugins/genai/src/plugin_commands.cpp:374-387 therefore always replies MCP profiles loaded to runtime.

3. The two views read different data.

So they legitimately disagree, by design, with no indication anywhere that they can.

4. The fallback error message points at the wrong place.

plugins/genai/src/tool_handlers/Query_Tool_Handler.cpp:675-678
    if (target_registry.empty()) {
        return "No MCP targets loaded in runtime_mcp_target_profiles";
    }

format_target_unavailable_error() has genuinely good diagnostics for a target that loaded but is not executable (empty db_username, no ONLINE backend, per-hostgroup status summary — Query_Tool_Handler.cpp:680-720). None of it is reachable in this scenario, because the target never entered the registry at all. The operator gets told that runtime_mcp_target_profiles has no targets while SELECT * FROM runtime_mcp_target_profiles returns rows.

Reproduction

Two independent ways to trigger it. Build the v4.0 tier with the genai plugin enabled.

(a) dangling auth_profile_id — e.g. a typo, or the auth profile INSERT failed and was not noticed:

INSERT INTO mcp_target_profiles (target_id, protocol, hostgroup_id, auth_profile_id, active)
VALUES ('t1', 'mysql', 10, 'does-not-exist', 1);

LOAD MCP PROFILES TO RUNTIME;      -- OK. No warning in the error log.

SELECT target_id, auth_profile_id, active FROM runtime_mcp_target_profiles;
-- t1 | does-not-exist | 1        <-- present

(b) active = 0:

INSERT INTO mcp_auth_profiles (auth_profile_id, db_username, db_password) VALUES ('a1','u','p');
INSERT INTO mcp_target_profiles (target_id, protocol, hostgroup_id, auth_profile_id, active)
VALUES ('t2', 'mysql', 10, 'a1', 0);

LOAD MCP PROFILES TO RUNTIME;      -- OK
SELECT target_id, active FROM runtime_mcp_target_profiles;
-- t2 | 0                          <-- present

In both cases, on the MCP query endpoint:

{"jsonrpc":"2.0","method":"tools/call","params":{"name":"list_targets"},"id":1}
-> {"targets": [], "default_target_id": ""}

and any tool requiring a target reports No MCP targets loaded in runtime_mcp_target_profiles.

Expected behaviour

An operator must be able to determine, from the admin interface and the error log alone, why a configured target is not usable. Concretely:

• Dropping a row during the profile join is a configuration error and must be logged.
• LOAD MCP PROFILES TO RUNTIME should report how many profiles were installed and how many were skipped.
• The "no targets" error string must not name a table that contradicts it.

Suggested fix

1. Log every skipped row in rebuild_target_auth_map_locked() (MCP_Thread.cpp:494), with the target id and the reason:

   • proxy_warning("MCP: target profile '%s' skipped: active=0\n", ...)
   • proxy_warning("MCP: target profile '%s' skipped: auth_profile_id '%s' not found in mcp_auth_profiles\n", ...) The function runs under the write lock on a cold path (profile install only), so the logging cost is irrelevant.

2. Return counts to the admin caller. Have rebuild_target_auth_map_locked() produce {installed, skipped_inactive, skipped_dangling}, propagate through install_profiles_from_admin(), and let load_mcp_profiles_to_runtime (plugin_commands.cpp:374) return e.g. MCP profiles loaded: 3 target(s) active, 1 skipped (see error log) instead of a bare OK.

3. Fix the misleading message at Query_Tool_Handler.cpp:677. When the registry is empty it should point at the actual mechanism, e.g. No MCP targets in the runtime registry. Rows in mcp_target_profiles are only installed by 'LOAD MCP PROFILES TO RUNTIME', and rows that are inactive or whose auth_profile_id does not resolve are excluded — check the error log.

4. Decide on the runtime_mcp_target_profiles contract (design call for maintainers). Two coherent options:

   • Keep it as a projection of the raw snapshot (current behaviour) and document explicitly that presence there does not imply usability. Cheapest, and keeps inactive rows visible.
   • Or add a derived, read-only column such as effective / skip_reason to the runtime view, computed from the same join. This makes the disagreement self-explaining at the point the operator looks, and is strictly more useful than either extreme.

   Making the view project only joined rows is not recommended: it would hide inactive/broken rows entirely and remove the one surface where the misconfiguration is currently visible at all.

Test coverage to add

TAP coverage in the ai group for both drop reasons: insert a target with a dangling auth_profile_id and one with active=0, run LOAD MCP PROFILES TO RUNTIME, and assert the command reports the skip count, that the proxysql error log contains the per-row warning, and that the list_targets failure message names the real cause.