How to fix ERROR 2013 (HY000): Lost connection to MySQL server during query in MySQL

-- Check current settings:
SHOW VARIABLES LIKE 'net_read_timeout';
SHOW VARIABLES LIKE 'net_write_timeout';

-- Set temporarily (until server restart):
SET GLOBAL net_read_timeout = 120;
SET GLOBAL net_write_timeout = 120;

-- Set permanently in my.cnf or my.ini:
[mysqld]
net_read_timeout = 120
net_write_timeout = 120

# Linux:
sudo systemctl restart mysql

# macOS:
brew services restart [email protected]

-- Check current values:
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- Set temporarily (affects new connections only):
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

-- Set permanently in my.cnf:
[mysqld]
wait_timeout = 600
interactive_timeout = 600

-- Check current setting:
SHOW VARIABLES LIKE 'max_allowed_packet';

-- Set temporarily on the server (affects new connections):
SET GLOBAL max_allowed_packet = 67108864;  -- 64MB in bytes

[mysqld]
max_allowed_packet = 64M

-- Also set on client side (mysqldump, mysql CLI):
[mysqldump]
max_allowed_packet = 64M

[mysql]
max_allowed_packet = 64M

-- Enable slow query log to find culprits:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;  -- Log queries taking >5 seconds

-- Check the log:
-- Location: /var/log/mysql/slow.log (Linux) or MySQL datadir

-- SLOW (full table scan without index):
SELECT * FROM orders WHERE customer_id = 123;

-- FAST (with index, selecting only needed columns):
CREATE INDEX idx_customer_id ON orders(customer_id);
SELECT id, amount FROM orders WHERE customer_id = 123;

# Check system resources:
top
free -h
df -h

# Check MySQL process:
ps aux | grep mysqld

-- Show active processes:
SHOW PROCESSLIST;

-- Kill a specific stuck query (use carefully):
KILL QUERY 12345;  -- replace 12345 with the thread/connection id from SHOW PROCESSLIST

-- Check server status:
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Questions';

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

// Pool options like waitForConnections/connectionLimit/queueLimit
// are valid on createPool(), NOT on createConnection().
const pool = mysql.createPool({
  host: 'localhost',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  // Per-query connect/timeout (ms):
  connectTimeout: 30000,
});

// Retry logic for lost-connection errors:
async function queryWithRetry(sql, params = [], maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      // pool.query() acquires, uses, and releases a connection automatically
      return await pool.query(sql, params);
    } catch (err) {
      const retryable = ['PROTOCOL_CONNECTION_LOST', 'ECONNRESET', 'ETIMEDOUT'];
      if (retryable.includes(err.code) && i < maxRetries - 1) {
        console.log(`Connection lost, retrying... (${i + 1}/${maxRetries})`);
        await new Promise((r) => setTimeout(r, 1000));
      } else {
        throw err;
      }
    }
  }
}

import os
import mysql.connector
from time import sleep

def query_with_retry(sql, params=None, max_retries=3):
    for i in range(max_retries):
        try:
            connection = mysql.connector.connect(
                host='localhost',
                user=os.environ['DB_USER'],
                password=os.environ['DB_PASSWORD'],
                database='mydb',
                connection_timeout=30,
            )
            cursor = connection.cursor()
            cursor.execute(sql, params or ())
            rows = cursor.fetchall()
            cursor.close()
            connection.close()
            return rows
        except mysql.connector.Error as err:
            # 2006 = MySQL server has gone away, 2013 = Lost connection during query
            if err.errno in (2006, 2013) and i < max_retries - 1:
                print(f"Connection lost, retrying... ({i + 1}/{max_retries})")
                sleep(1)
            else:
                raise

# Test connectivity:
ping mysql.example.com

# Check for packet loss:
ping -c 100 mysql.example.com

# Monitor the route with MTR:
mtr mysql.example.com

-- Require an encrypted connection for this user (MySQL 8.0):
CREATE USER 'appuser'@'%' IDENTIFIED BY 'use-a-strong-secret'
  REQUIRE SSL;

-- Stronger: require a client certificate signed by your CA:
-- REQUIRE X509;

mysql --ssl-ca=/path/to/ca.pem \
      --ssl-mode=VERIFY_IDENTITY \
      -h mysql.example.com -u appuser -p