This PR implements MySQL's COM_RESET_CONNECTION command (introduced in MySQL 5.7.3) and integrates it with connection pools to automatically reset connection state when connections are released.

This addresses 7-year-old issue #934 and resolves issues #1087, #1328, and #1469.

Motivation

Problem

When connections are reused from a pool, session state persists between different users:

• User variables remain set
• Temporary tables persist
• Session variables stay modified
• Locks (GET_LOCK) aren't released
• Active transactions continue

This creates:

• Security issues: Session state leakage between users
• Data integrity issues: Unexpected state in "fresh" connections
• Hard-to-debug bugs: State pollution across requests

Current Workarounds (Suboptimal)

1. Call changeUser() with no params: Requires full re-authentication (slow, extra roundtrip)
2. Destroy and recreate connections: Wasteful, defeats pool purpose
3. Manually clean state: Error-prone, easy to miss something
4. Avoid pools for safety: Defeats the purpose of pooling

Solution

Implements COM_RESET_CONNECTION (0x1F) - a lightweight command that:

• Resets all session state (variables, temp tables, locks, transactions, etc.)
• Does NOT re-authenticate (faster than changeUser)
• Does NOT close TCP connection
• ~3-5x faster than changeUser for state reset

Changes

1. Core Command Implementation (Commit 1)

• ✅ Add ResetConnection command class
• ✅ Add ResetConnection packet class
• ✅ Add connection.reset(callback) method
• ✅ Add promise wrapper await connection.reset()
• ✅ Clear prepared statements cache on reset
• ✅ TypeScript definitions

2. Pool Integration (Commit 2)

• ✅ Add resetOnRelease pool config option (default: true)
• ✅ Modify releaseConnection() to call reset() when enabled
• ✅ Handle reset failures gracefully (destroy connection, create new one)
• ✅ Add _handleSuccessfulRelease() helper method
• ✅ TypeScript pool option definition

3. Comprehensive Tests

• ✅ 6 connection reset tests (test/integration/connection/test-reset-connection.test.mts):

  • Basic reset functionality
  • User variable cleanup
  • Temporary table cleanup
  • Prepared statements cache clearing
  • Transaction rollback
  • Promise wrapper support

• ✅ 7 pool integration tests (test/integration/pool/test-pool-reset-on-release.test.mts):

  • Default behavior (resetOnRelease: true)
  • Opt-out behavior (resetOnRelease: false)
  • Error handling for failed resets
  • Prepared statements cleanup
  • Promise pool support
  • Queued connection reset behavior

API Usage

Connection API (added)

// Callback style
connection.reset((err) => {
  if (err) throw err;
  console.log('Connection state reset');
});

// Promise style
await connection.reset();

Pool API (enhanced)

// Default: automatic reset on release (RECOMMENDED)
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test',
  resetOnRelease: true, // Default
});

// Opt-out for performance-critical scenarios
const fastPool = mysql.createPool({
  // ... config
  resetOnRelease: false, // Skip reset (use with caution!)
});

What Gets Reset

When connection.reset() is called (or triggered by pool):

Breaking Changes

⚠️ Pool Behavior Change (Breaking)

Before: Connections retained all state when returned to pool

// BEFORE: State persists
pool.getConnection((err, conn1) => {
  conn1.query("SET @var = 'value1'");
  conn1.release();
  
  pool.getConnection((err, conn2) => {
    // conn2 sees @var = 'value1' (SECURITY ISSUE!)
  });
});

After (with resetOnRelease: true, the new default):

// AFTER: State is cleared
pool.getConnection((err, conn1) => {
  conn1.query("SET @var = 'value1'");
  conn1.release(); // Triggers reset
  
  pool.getConnection((err, conn2) => {
    // conn2 sees @var = NULL (SAFE!)
  });
});

Migration Guide

If you rely on state persistence (rare, not recommended):

const pool = mysql.createPool({
  // ... config
  resetOnRelease: false, // Restore old behavior
});

Best practice for legitimate state needs:

// BAD: Relying on pool state persistence
pool.getConnection((err, conn) => {
  conn.query("SET @user_id = 123");
  // ... later queries use @user_id
  conn.release(); // Hope next user doesn't see it!
});

// GOOD: Manage state explicitly
pool.getConnection((err, conn) => {
  const userId = 123;
  conn.query("SELECT * FROM orders WHERE user_id = ?", [userId]);
  conn.release(); // Clean slate for next user
});

Performance Impact

Benchmarks (preliminary)

When to Disable Reset

Consider resetOnRelease: false only if:

1. ✅ You have zero session state (no variables, no temp tables)
2. ✅ Performance gain (1-2ms per connection release) is critical
3. ✅ You're willing to sacrifice security guarantees
4. ✅ You can guarantee no state pollution bugs

Default recommendation: Keep resetOnRelease: true (security > 1-2ms)

MySQL Version Compatibility

Error handling: If reset fails (e.g., old MySQL version), the connection is destroyed and removed from pool, and a new connection is created for the next request.

Issues Resolved

This PR directly addresses:

• Feature request: mysql_reset_connection #1328 (May 2021): Feature request for mysql_reset_connection - ✅ EXACT DUPLICATE
• Connection state is not reset on return to pool #934 (March 2019): Connection state is not reset on return to pool - ✅ FIXED (7 years old!)
• What happens when a connection is released? #1087 (Oct 2023): What happens when a connection is released? - ✅ FIXED
• poolConnection.changeUser changing for the whole Pool.getConnection() #1469 (Dec 2021): poolConnection.changeUser changing for the whole Pool - ✅ HELPED

Also helps with:

• Recommendation on connection recycling #1103 (June 2023): Recommendation on connection recycling
• Event on release connections? #586 (Jan 2021): Event on release connections?

Related PRs

• Implement CMD_RESET_CONNECTION #1437 (Oct 2021): Implement CMD_RESET_CONNECTION (draft by @testn) - This PR incorporates and completes that work

Testing

Run Tests

# Run all tests
npm test

# Run only reset tests
npm test -- test/integration/connection/test-reset-connection.test.mts
npm test -- test/integration/pool/test-pool-reset-on-release.test.mts

Manual Testing

# Start MySQL with Docker
docker-compose up -d

# Run example
node examples/reset-connection-demo.js

Documentation Updates

• Add to README.md pool options section
• Add to website documentation
• Add migration guide for pool behavior change
• Add security best practices guide

Checklist

• Implementation complete
• Tests added and passing
• TypeScript definitions added
• Linting passes
• No breaking changes to existing code paths
• Documentation updated
• Changelog entry added
• Approved by maintainers

Backward Compatibility

✅ Mostly backward compatible

The only behavior change is:

• Pool connections now reset by default (can be disabled with resetOnRelease: false)
• Direct connection.reset() calls are new API, don't affect existing code

Security Considerations

Before This PR

• ❌ Session state leaks between pool users
• ❌ User variables visible across requests
• ❌ Temp tables persist between users
• ❌ Locks can cause deadlocks for other users
• ❌ Active transactions can corrupt data

After This PR (with resetOnRelease: true)

• ✅ Clean state for each pool user
• ✅ No session variable leakage
• ✅ No temp table conflicts
• ✅ Locks automatically released
• ✅ Transactions properly rolled back

Recommendation: Keep default resetOnRelease: true for security

Community Quotes

From issue discussions:

"For transactional workloads... I tend to assume the worst rather than risk committing a partially complete transaction." - @joeyhub (#1087)

"Anyone expecting the opposite [no reset], is working in some extreme case where there is zero session and wants to gain 1-2% more in perf." - @danielgindi (PR #1437)

"I believe traditionally this is meant for reusing connections safely in a pool" - @joeyhub (#1087)

Future Enhancements

Possible follow-up PRs:

1. Add connection.resetAsync() with better performance for multiple resets
2. Add pool statistics for reset failures
3. Add connection lifecycle events (beforeReset, afterReset)
4. Add automatic version detection with graceful fallback for MySQL < 5.7.3

Credits

• Original PR Implement CMD_RESET_CONNECTION #1437 by @testn
• Issue reports and discussion by @aikar, @joeyhub, @danielgindi, @TheVoid-0
• MySQL protocol documentation

Ready for review! 🎉

This PR represents a complete solution to a 7-year-old issue that affects security, data integrity, and reliability for all connection pool users.