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

[renecannao]

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.

Surface	Source
runtime_mcp_target_profiles	target_profiles_ — the raw, unjoined snapshot (MCP_Thread.cpp:889 project_target_profiles_to_runtime_view)
list_targets / all query tools	target_auth_map — the joined map (Query_Tool_Handler.cpp:535 refresh_target_registry() → GloMCPH->get_all_target_auth_contexts() at :550)

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.