update_sql_transformation: batch str_replace silently succeeds when search strings not found

[beckavac]

Bug description

When calling update_sql_transformation with multiple str_replace operations in a single batch, the tool returns success: true even when none of the search strings were found and no changes were made.

Steps to reproduce

1. Fetch transformation config SQL from dev branch
2. Build a batch of 40+ str_replace operations (e.g. removing AS from table aliases)
3. Call update_sql_transformation with all operations in one payload
4. Tool returns {"success": true, "version": N} (version unchanged or incremented)
5. Re-fetch the SQL — no changes were applied

Expected behavior

• Each failing str_replace operation (search string not found) should be reported individually
• OR the batch should fail with a list of which operations had no match
• The version should NOT increment if nothing changed

Actual behavior

• Tool returns success: true with a new version number
• No replacements were actually made
• Only when running single operations does it explicitly report "search string not found"

Workaround used

Had to abandon batch approach entirely. Used direct Keboola Storage API (PUT /v2/storage/branch/{branch_id}/components/.../configs/{config_id}) with a locally-prepared JSON payload to push the full updated configuration.

Impact

AI agents iterating on SQL code cannot trust batch str_replace results. Silent no-ops with a success response make it impossible to detect when transformations failed without re-fetching and diffing the entire config after every batch.

Context

• kbagent version: 0.9.0
• Operation: removing 182 AS alias occurrences from Products code (config 746772863, Slevomat project 226)
• Dev branch: 1285731

[padak]

Unable to reproduce

We attempted to reproduce this issue with keboola-mcp-server v1.48.0 (the issue was originally reported against kbagent v0.9.0). The batch str_replace behavior appears to work correctly now -- batches are atomic and properly report errors.

Test setup

• Created a dev branch in project 10539 (test project)
• Target: existing Snowflake transformation 01km4215thchx0dnzmkcptfb7p with 3 SQL code blocks
• Used kbagent tool call update_sql_transformation --branch <id> to invoke the MCP tool

Test results

#	Scenario	Result	Version changed?
1	3x non-existent str_replace	isError: true, "search string not found" on first op	No
2	1 valid + 2 non-existent	isError: true, valid replace not applied (atomic rollback)	No
3	3x valid str_replace	success: true, all 3 replacements applied, change_summary lists each	Yes (+1)
4	42x non-existent (large batch)	isError: true on first op, nothing applied	No
5	1 valid + 1 non-existent (in order)	isError: true, valid replace not applied	No

Key finding

The batch behaves atomically: if any str_replace operation fails (search string not found), the entire batch is rolled back and no changes are saved. This is the correct behavior.

Verification commands used

# Setup
mkdir /tmp/issue_66 && cd /tmp/issue_66
kbagent init
kbagent project add --project test66 --url https://connection.keboola.com --token <token>
kbagent branch create --project test66 --name "test-issue-66"

# Test: all non-existent (expected: error)
kbagent --json tool call update_sql_transformation --project test66 --branch 1287575 --input '{
  "configuration_id": "01km4215thchx0dnzmkcptfb7p",
  "change_description": "Test batch str_replace with non-existent strings",
  "parameter_updates": [
    {"op": "str_replace", "search_for": "THIS_DOES_NOT_EXIST_AAA", "replace_with": "replaced_aaa"},
    {"op": "str_replace", "search_for": "THIS_DOES_NOT_EXIST_BBB", "replace_with": "replaced_bbb"},
    {"op": "str_replace", "search_for": "ANOTHER_NONEXISTENT_CCC", "replace_with": "replaced_ccc"}
  ]
}'
# Result: isError: true, "Search string ... not found in the transformation"

# Test: valid then invalid (expected: error, no changes)
kbagent --json tool call update_sql_transformation --project test66 --branch 1287575 --input '{
  "configuration_id": "01km4215thchx0dnzmkcptfb7p",
  "change_description": "Test: valid replace followed by invalid",
  "parameter_updates": [
    {"op": "str_replace", "search_for": "Acme Corp", "replace_with": "Acme Corp CHANGED"},
    {"op": "str_replace", "search_for": "NONEXISTENT_STRING_XYZ", "replace_with": "nope"}
  ]
}'
# Result: isError: true, first valid replace NOT applied (atomic rollback)
# Verified via Storage API: version unchanged, SQL still contains "Acme Corp"

# Test: all valid (expected: success)
kbagent --json tool call update_sql_transformation --project test66 --branch 1287575 --input '{
  "configuration_id": "01km4215thchx0dnzmkcptfb7p",
  "change_description": "Test all valid replaces",
  "parameter_updates": [
    {"op": "str_replace", "search_for": "Acme Corp", "replace_with": "Acme Corporation"},
    {"op": "str_replace", "search_for": "Globex Inc", "replace_with": "Globex International"},
    {"op": "str_replace", "search_for": "approximate rate", "replace_with": "rough estimate"}
  ]
}'
# Result: success: true, version incremented, change_summary lists all 3 replacements

Request

Could you provide more details about the original failure? Specifically:

1. Exact MCP server version at the time of the bug (the kbagent version alone doesn't determine update_sql_transformation behavior -- that logic lives in keboola-mcp-server)
2. Exact JSON payload sent to update_sql_transformation (or the kbagent tool call command)
3. Exact JSON response received (the success: true with version number mentioned in the issue)
4. Before/after SQL diff showing the SQL didn't change despite the success response

Without these details it's hard to determine whether this was a bug that has since been fixed in a newer MCP server version, or a specific edge case we're not reproducing.

[beckavac]

Thanks for the detailed investigation and test results!

Clarification on original conditions

MCP server version at time of bug: Unknown — I didn't log it. Current installed version is keboola-mcp-server v1.49.0. The bug was observed in a prior session so it may have been an older version (likely pre-1.48.0 based on your testing).

More precise description of what happened

The failing scenario was specifically:

• 182 str_replace operations in a single batch
• Each operation removed the keyword AS from table aliases in JOIN clauses:
  • Search: JOIN "some_table" AS alias
  • Replace: JOIN "some_table" alias
• The search strings were extracted from the already-fetched SQL — so they should have matched exactly
• Tool returned {"success": true} with a new version number (version DID increment, not a no-op)
• Re-fetching the config confirmed: zero replacements applied

This is different from your test scenario #4 (42x non-existent strings → isError: true). In our case the strings existed in the SQL but the replacements still weren't applied, yet the tool reported success and created a new version.

Possible link with issue #67 (payload size)

The 182-operation batch payload was approximately 682 KB — which is the same size that hit the CLI input limit (issue #67). My hypothesis: at that payload size, the CLI or MCP server may have silently processed an empty or truncated batch, saved a no-op version increment, and returned success: true.

Smaller batches (10–20 operations) worked correctly in the same session.

Suggested next steps

1. Test with a large batch (150+ operations) on a real-world SQL file — the test setup used small scripts; the issue may only surface with large payloads
2. Check if a payload above a certain size threshold causes silent truncation rather than an explicit error

If you cannot reproduce with large payloads either, I'm happy to close this as "fixed in newer version" or "cannot reproduce". The workaround (direct Storage API via Python) is working fine for now.

[padak]

Root cause identified and reported upstream

We reproduced the issue with keboola-mcp-server v1.49.0 using a stress test with 2000 str_replace operations (~510 KB payload) on a 682 KB SQL transformation.

Finding

The bug is in the MCP server's event logging, not in the replacement logic itself:

1. All str_replace operations are applied correctly, config version increments
2. MCP server tries to log the full change_summary (2000 lines) to POST /v2/storage/events
3. Storage Events API rejects the request: 400 Bad Request: "Request too large"
4. MCP server propagates this as isError: true to the caller

This means changes are persisted but the tool reports failure -- a dangerous inconsistency for AI agents that rely on the return value to determine success.

Test matrix

Operations	Payload	Result	Changes applied?
200	22 KB	success: true	Yes (200/200)
600	148 KB	success: true	Yes (600/600)
2000	510 KB	isError: true (400 on /events)	Yes (2000/2000)

Correlation with @beckavac's report

The original scenario (182 ops, 682 KB payload) likely hit the same event size limit. The MCP server version at the time may have handled the error differently (returning success: true instead of isError: true), or the agent interpreted the response differently after retry.

Actions taken

1. Upstream bug filed: update_sql_transformation: large batch str_replace returns isError despite changes being applied mcp-server#442 -- with full reproduction steps and suggested fixes
2. Workaround documented: added batch size limit guidance (max 50 ops) to kbagent plugin gotchas
3. CLI input fix already shipped: v0.15.4 supports --input @file for large payloads (tool call --input: payload size limit makes large config updates impossible via CLI #67)

Recommendation

Until keboola/mcp-server fixes the event logging, agents should split large str_replace batches into chunks of ~50 operations. This stays well under the Storage Events API size limit.

Leaving this issue open until the upstream fix is confirmed.