Skip to content

fix(mssql): never let a timeout cancel reach a reused connection - #42666

Open
salevine wants to merge 1 commit into
releasefrom
fix/mssql-cancel-before-pool-reuse
Open

salevine wants to merge 1 commit into
releasefrom
fix/mssql-cancel-before-pool-reuse

Conversation

@salevine

@salevine salevine commented Nov 20, 2026 •

Copy link
Copy Markdown
Contributor

Description

When a SQL Server query runs past its timeout, a timer cancels it on SQL Server from another thread (#42659). At the end of the query, the plugin stopped that timer. But stopping the timer could not stop a cancel that the timer had already started. So the connection could go back to the pool while that cancel was still being sent, and the late cancel could stop the next query that used the connection. That query can belong to another app or user of the same datasource.

This PR makes sure a cancel never reaches a connection after it goes back to the pool:

  • MssqlExecuteUtils.cancelAtDeadline now returns a DeadlineCancel. Its cancel and its stop() share a lock.
    • A cancel that has not started never runs after stop().
    • stop() waits for a cancel that is running, up to 5 seconds.
    • stop() returns false when a cancel ran, or is still running after that wait.
  • The connection is removed from the pool (HikariDataSource.evictConnection) and closed, instead of going back to it, when:
    • the timer's cancel ran or may still be on its way, or
    • the query passed its deadline, because the query thread may then have cancelled the query itself.
  • The connection is removed before its statements are closed, so the close cannot wait on a server that stopped answering. If the pool is already closed, the connection is closed directly.
  • An info log line names the pool each time a connection is removed this way.

Only queries that reach their timeout are affected. Their connection is replaced by a new one, which costs one login to SQL Server. Other queries are unchanged.

Found by Hacktron (AIP-FD-39).

Automation

/ok-to-test tags="@tag.All"

Testing

  • Client unit tests

  • Server unit tests

  • Cypress

  • Playwright

  • Deploy preview

  • Not applicable

  • MssqlExecuteUtilsTest (mocked statements):

    • a cancel that is running makes stop() wait, then the connection is not reused;
    • a cancel the timer started, but that has not taken the lock yet, never runs after stop();
    • a cancel still running after the wait, or one that already ran, means the connection is not reused;
    • a timer stopped before the deadline never cancels, and the connection is reused;
    • a connection that is not reusable is removed from the pool before its statement is closed, or closed directly when the pool is closed;
    • if the pool cannot remove it, the connection to SQL Server is closed before the connection goes back to the pool.
  • MssqlDriverBehaviourTest (Testcontainers, SQL Server 2022):

    • a query cancelled at its timeout records its own session. The test checks on SQL Server that this session ends, so the connection was closed, not lent again. This test fails when only the change in MssqlPlugin is reverted.
    • after each timed-out query in the existing timeout test, the next query on the same pool succeeds.
  • With stop() changed back to the old behaviour, 4 of the new unit tests fail.

  • Full SQL Server plugin suite: 133 tests, 0 failures.

Suggested Cypress tags or specs: @tag.Datasource

Communication

Should the DevRel and Marketing teams inform users about this change?

  • Yes
  • No

Release note

A SQL Server query that ran past its timeout could, in rare cases, stop the next query that used the same database connection. Appsmith now closes the connection of a query that reached its timeout, instead of reusing it. The next query opens a new connection.

🤖 Generated with Claude Code

Important

🟣 🟣 🟣 Your tests are running.
Tests running at: https://github.com/appsmithorg/appsmith/actions/runs/44810239714
Commit: debf5d0
Workflow: PR Automation test suite
Tags: @tag.All
Spec: ``


Fri, 20 Nov 2026 20:19:23 UTC

The timer that cancels a query at its deadline could start the cancel just
before the query ended. Stopping the timer could not stop that cancel, so
the connection could go back to the pool and the late cancel could stop the
next query on it.

- The cancel and its stop now share a lock. A cancel that has not started
  never runs after the stop, and the stop waits up to 5 seconds for one
  that is running.
- When a cancel ran, may still be running, or the query passed its
  deadline, the connection is removed from the pool and closed instead of
  reused. It is removed before its statements are closed.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
@salevine
salevine requested a review from a team as a code owner November 20, 2026 20:18
@salevine salevine added ok-to-test Required label for CI release-note: security Include in the release notes under Security. Wording comes from the PR's release-note block. labels Nov 20, 2026
Copilot AI balanced review requested due to automatic review settings November 20, 2026 20:19

Copilot AI left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Copilot was unable to review this pull request because the user who requested the review has insufficient remaining quota. Try requesting a review at a lower effort level.

@coderabbitai

coderabbitai Bot commented Nov 20, 2026 •

Copy link
Copy Markdown
Contributor

Explain Change →

Summary

Summary by CodeRabbit

  • Bug Fixes
    • Timed-out SQL Server queries now safely discard connections that may still be processing cancellation, rather than returning them to the pool.
    • Improved cleanup helps prevent canceled queries from leaving connections in an unusable state.
    • Follow-up queries can continue using the connection pool after a timeout, while connections affected by cancellation are closed.

The SQL Server plugin now coordinates deadline cancellation with connection cleanup. It reuses a connection only when cancellation did not start and the deadline has not passed. Otherwise, cleanup evicts the connection from the pool when possible. Tests cover cancellation races, connection closure, timed-out sessions, and a subsequent query on the same pool.

Estimated code review effort: 3 (Moderate) | ~25 minutes

Sequence Diagram(s)

sequenceDiagram
  participant MssqlPlugin
  participant DeadlineCancel
  participant Statement
  participant MssqlExecuteUtils
  participant HikariDataSource
  DeadlineCancel->>Statement: cancel when deadline expires
  MssqlPlugin->>DeadlineCancel: stop timer and check cancellation status
  DeadlineCancel-->>MssqlPlugin: return whether cancellation did not start
  MssqlPlugin->>MssqlExecuteUtils: clean up with reusability decision
  alt connection is not reusable
    MssqlExecuteUtils->>HikariDataSource: evict connection
  else connection is reusable
    MssqlExecuteUtils->>MssqlExecuteUtils: use existing cleanup path
  end
Loading

Possibly related PRs

  • appsmithorg/appsmith#42659: Adds deadline-based cancellation to the SQL Server plugin and modifies the same query-cancellation path.

Suggested reviewers: wyattwalter, sebastianiv21, sondermanish

Merge Risk: Low · up to debf5

This change stops a SQL Server timeout cancellation from reaching a pooled connection reused by another query. Timed-out queries now close their connection instead of returning it to the pool. One narrow race with datasource shutdown could leave a session open, and a few new tests can be made more reliable. Overall merge risk is low.

Pre-merge checks: Passed 4

Check name Status Explanation
Title check Passed The title names the main change: the MSSQL plugin no longer lets a timeout cancel reach a reused pooled connection. The changeset adds DeadlineCancel, which coordinates cancel and stop, and evicts and…
Description check Passed The description follows the template. It has a Description section with motivation, the race, and the fix. It links the issue number reference (#42659) and the Hacktron finding. It has a Testing secti…
Linked Issues check Passed Check skipped because no linked issues were found for this pull request.
Out of Scope Changes check Passed Check skipped because no linked issues were found for this pull request.
Full details: Title check

Explanation

The title names the main change: the MSSQL plugin no longer lets a timeout cancel reach a reused pooled connection. The changeset adds DeadlineCancel, which coordinates cancel and stop, and evicts and closes non-reusable connections. The title is specific and matches the primary fix.

Full details: Description check

Explanation

The description follows the template. It has a Description section with motivation, the race, and the fix. It links the issue number reference (#42659) and the Hacktron finding. It has a Testing section with server unit tests checked, and a Communication section with an answer. It does not use the 'Fixes #' line, since it references #42659 as the timer context rather than as a closing issue link, and the TL;DR is absent, though the description is under 500 words. These are minor gaps.

Resolution

Optionally add a 'Fixes #<issue>' line, or confirm #42659 is the tracked issue and should be closed by this PR. Optionally add a short TL;DR.

Finishing Touches

Generate unit tests (beta)

  • Commit to this branch
  • Create a new PR
  • Autofix · Keep fixing CodeRabbit findings and required CI, and resolving merge conflicts

@coderabbitai coderabbitai Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Actionable comments posted: 4

Additional comments: 1

app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/utils/MssqlExecuteUtils.java-128-128: 1

128-128: Minor · Stability & Availability · Heavy lift

Unverified finding
Verification ran but could not confirm this finding. It is shown for review, not as a verified issue.

Do not drain JDBC results before the physical connection closes.

HikariCP 5.0.1 removes the pool entry immediately, but schedules the physical close on its close executor. If that executor has not closed the connection yet, this call can make mssql-jdbc 13.6.2 read the remaining response or wait for the cancellation acknowledgement. The entry is no longer borrowable, but the action can still wait on SQL Server before reporting its timeout. The mock ordering test only proves that eviction was called first. (raw.githubusercontent.com)

On the discard path, terminate the physical connection before explicit JDBC cleanup, or arrange for the discard operation to own that cleanup without draining results on the action thread. Keep the existing cleanup order for reusable connections.

Review info

Run configuration

Commits

Reviewing files that changed from the base of the PR and between ecd8582 and debf5d0.

Files selected for processing: 4

  • app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/MssqlPlugin.java
  • app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/utils/MssqlExecuteUtils.java
  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/MssqlDriverBehaviourTest.java
  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/utils/MssqlExecuteUtilsTest.java

Included review availability: This review used your included allowance. Your plan provides up to 10 included reviews per hour; 9 remain after this review.

Review details

Context from checks skipped due to timeout: 3

  • GitHub Check: CodeRabbit
  • GitHub Check: perform-test / client-build / client-build
  • GitHub Check: server-unit-tests / server-unit-tests
Additional context used

Code guidelines: 1

app/server/AGENTS.md — auto-discovered
Path-based instructions: 2

Review server-side changes with a focus on correctness, performance, security, reliability, and maintainability.

CodeRabbit configuration file

Files:

  • app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/MssqlPlugin.java
  • app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/utils/MssqlExecuteUtils.java
  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/MssqlDriverBehaviourTest.java
  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/utils/MssqlExecuteUtilsTest.java
Source excerpt: JUnit 5 (`org.junit.jupiter.api`).

CodeRabbit inference engine (app/server/AGENTS.md)

Files:

  • app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/MssqlPlugin.java
  • app/server/appsmith-plugins/mssqlPlugin/src/main/java/com/external/plugins/utils/MssqlExecuteUtils.java
  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/MssqlDriverBehaviourTest.java
  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/utils/MssqlExecuteUtilsTest.java
Learnings: 2

Learning: 2026-10-31T13:25:08.869Z

Learnt from: CR
Repo: appsmithorg/appsmith PR: 0
File: app/server/AGENTS.md:0-0
Timestamp: 2026-10-31T13:25:08.869Z
Learning: Applies to app/server/** : Source excerpt:
# Java tests

## Shape of a new test

- No `Thread.sleep`. Use `StepVerifier.withVirtualTime` or Awaitility.

Applied to files:

  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/MssqlDriverBehaviourTest.java
Learning: 2026-10-31T13:25:08.869Z

Learnt from: CR
Repo: appsmithorg/appsmith PR: 0
File: app/server/AGENTS.md:0-0
Timestamp: 2026-10-31T13:25:08.869Z
Learning: Applies to app/server/** : Source excerpt:
# Java tests

## Shape of a new test

- Name: `should_<expectedBehavior>_when_<condition>`, for example `should_return401_when_unauthenticated`. Add `DisplayName` only when the name alone cannot carry the scenario, such as a GHSA identifier or a long precondition.

Applied to files:

  • app/server/appsmith-plugins/mssqlPlugin/src/test/java/com/external/plugins/utils/MssqlExecuteUtilsTest.java

try {
// Removed first, while the connection is still open, so Hikari closes it at once and never lends it again.
// Closing the statements afterwards cannot then wait on a server that stopped answering.
pool.evictConnection(connectionFromPool);

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Minor · Stability & Availability · Heavy lift

Preserve physical close when eviction races with pool shutdown.

If Hikari removes the entry and shutdown stops the close executor before Hikari submits the close task, CallerRunsPolicy silently discards the task. Shutdown cannot abort the already-removed entry. Eviction returns normally, and this overload closes only the JDBC children, so the physical SQL Server session can remain open. (raw.githubusercontent.com)

Give the discard path an explicit physical-close fallback for concurrent shutdown. Capture the physical connection while the proxy still has it, and coordinate the fallback with any close already in progress. Do not return the proxy to an open pool.

// When
StepVerifier.create(mssqlPluginExecutor.executeParameterized(
pool, new ExecuteActionDTO(), createDatasourceConfiguration(container), actionConfiguration))
.assertNext(result -> assertThat(result.getIsExecutionSuccess()).isFalse())

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Minor · Functional Correctness · Quick win

Verify that the recorded session ends after a timeout.

If the connection fails after the batch records its session, the plugin can report a failure and Hikari can discard the broken connection. This test would pass even though it did not exercise deadline cancellation. Assert the 3,000 ms timeout message in assertNext before polling for session termination. The existing timeout test already checks that message. (raw.githubusercontent.com)

Comment on lines +138 to +140
MssqlExecuteUtils.cancelAtDeadline(statement, System.nanoTime() + TimeUnit.MILLISECONDS.toNanos(50));
ReentrantLock lock = cancel.lockForTests();
lock.lock();

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Minor · Stability & Availability · Quick win

Gate the cancel worker before testing the lock order.

If CI pauses the test thread after scheduling the 50 ms timer but before lock.lock(), the worker can acquire the lock, cancel, and exit. The test then waits for a queued worker that will never arrive. Use a test-controlled dispatcher or latch to start the worker only after the test owns the lock. Then keep the assertion that stop() wins once both workers are queued.

Comment on lines +222 to +227
boolean reusable = cancel.stop();

// Then: stop gives up after its wait, and the connection is not reused.
assertThat(reusable).isFalse();
assertThat(Duration.ofNanos(System.nanoTime() - start))
.isGreaterThanOrEqualTo(Duration.ofMillis(MssqlExecuteUtils.DeadlineCancel.STOP_WAIT_MILLIS - 100));

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Minor · Functional Correctness · Quick win

Assert that stop() returns while cancellation is still blocked.

If stop() regresses to an unbounded lock wait, this test still passes: the mocked cancel releases itself after 30 seconds, stop() returns false, and the elapsed time exceeds the asserted minimum. Call stop() on a separate worker and require its result within a bound above five seconds and below the mock’s 30-second backstop. Release the latch in finally.

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

ok-to-test Required label for CI release-note: security Include in the release notes under Security. Wording comes from the PR's release-note block.

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants