Node.js MySQL Connection Timeout - Complete Solution Guide

Published: September 25, 2024 | Reading time: 8 minutes

Quick Fix Summary

If you're getting "Connection timeout" or "ETIMEDOUT" errors with MySQL in Node.js, the solution is usually proper connection pooling and timeout configuration. Here's the immediate fix:

Quick Fix

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  connectionLimit: 10,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true
});

The Problem: MySQL Connection Timeouts in Node.js

MySQL connection timeouts in Node.js applications are one of the most common database issues developers face. These errors typically manifest as:

Error: connect ETIMEDOUT
Error: Connection lost: The server closed the connection
Error: Handshake inactivity timeout
Error: Too many connections

Root Causes and Solutions

1. Missing Connection Pooling

The most common cause is creating individual connections for each query instead of using a connection pool.

❌ Wrong Way - Individual Connections

// This will cause timeout issues
const mysql = require('mysql2/promise');

async function getUser(id) {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  });
  
  const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [id]);
  await connection.end(); // This can cause timeouts
  return rows;
}

✅ Correct Way - Connection Pool

const mysql = require('mysql2/promise');

// Create pool once at application startup
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  connectionLimit: 10,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true
});

async function getUser(id) {
  const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
  return rows;
}

2. Incorrect Timeout Configuration

MySQL has multiple timeout settings that need to be configured properly:

Complete Pool Configuration

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'password',
  database: process.env.DB_NAME || 'mydb',
  
  // Connection pool settings
  connectionLimit: 10,
  queueLimit: 0,
  
  // Timeout settings
  acquireTimeout: 60000,    // 60 seconds to get connection
  timeout: 60000,           // 60 seconds query timeout
  reconnect: true,
  
  // Connection settings
  charset: 'utf8mb4',
  timezone: '+00:00',
  
  // SSL settings (for production)
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false
});

3. Error Handling and Reconnection

Proper error handling is crucial for maintaining stable connections:

Robust Error Handling

class DatabaseService {
  constructor() {
    this.pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'mydb',
      connectionLimit: 10,
      acquireTimeout: 60000,
      timeout: 60000,
      reconnect: true
    });

    this.pool.on('connection', (connection) => {
      console.log('New MySQL connection established');
    });

    this.pool.on('error', (err) => {
      console.error('MySQL pool error:', err);
      if (err.code === 'PROTOCOL_CONNECTION_LOST') {
        console.log('Connection lost, will reconnect automatically');
      }
    });
  }

  async query(sql, params = []) {
    try {
      const [rows] = await this.pool.execute(sql, params);
      return rows;
    } catch (error) {
      console.error('Database query error:', error);
      throw error;
    }
  }

  async close() {
    await this.pool.end();
  }
}

4. Production Best Practices

For production applications, consider these additional configurations:

Production Configuration

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  
  // Production pool settings
  connectionLimit: 20,
  queueLimit: 100,
  
  // Aggressive timeout settings for production
  acquireTimeout: 30000,
  timeout: 30000,
  
  // Connection validation
  supportBigNumbers: true,
  bigNumberStrings: true,
  
  // Reconnection strategy
  reconnect: true,
  
  // SSL for secure connections
  ssl: {
    rejectUnauthorized: false,
    ca: process.env.DB_SSL_CA,
    cert: process.env.DB_SSL_CERT,
    key: process.env.DB_SSL_KEY
  }
});

Common Scenarios and Solutions

Scenario 1: High Traffic Applications

For applications with high concurrent users:

High Traffic Configuration

const pool = mysql.createPool({
  // ... other config
  connectionLimit: 50,      // More connections
  queueLimit: 0,           // Unlimited queue
  acquireTimeout: 45000,   // Longer timeout
  timeout: 30000,          // Query timeout
  reconnect: true
});

Scenario 2: Long-Running Queries

For applications with complex queries:

Long Query Configuration

const pool = mysql.createPool({
  // ... other config
  timeout: 300000,         // 5 minutes for complex queries
  acquireTimeout: 60000,   // 1 minute to get connection
});

Monitoring and Debugging

Add monitoring to track connection health:

Connection Monitoring

// Monitor pool statistics
setInterval(() => {
  console.log('Pool stats:', {
    totalConnections: pool._allConnections.length,
    freeConnections: pool._freeConnections.length,
    queuedRequests: pool._connectionQueue.length
  });
}, 30000);

// Handle process termination gracefully
process.on('SIGINT', async () => {
  console.log('Closing database pool...');
  await pool.end();
  process.exit(0);
});

Recommended Tools and Libraries

mysql2

The most popular and reliable MySQL driver for Node.js with excellent connection pooling support.

Install mysql2

Knex.js

A powerful SQL query builder with built-in connection pooling and migration support.

Try Knex.js

Summary

MySQL connection timeouts in Node.js are typically caused by:

Not using connection pooling
Incorrect timeout configuration
Poor error handling
Insufficient connection limits

Use the connection pool configuration above as your starting point, and adjust the settings based on your application's specific needs.

Need More Help?

Still having issues with MySQL connections? Our team of experts can help you implement a robust database solution for your specific use case.

Get Expert Help