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

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

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

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

# 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:
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:
SET GLOBAL max_allowed_packet = 64M;

-- Set permanently in my.cnf:
[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 (without index):
SELECT * FROM orders WHERE customer_id = 123;

-- FAST (with index):
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 long-running queries (dangerous, use carefully):
KILL QUERY {thread_id};

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

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

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
  enableTimeout: true,
  queueLimit: 0
});

// Retry logic:
async function queryWithRetry(sql, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await connection.query(sql);
    } catch (err) {
      if (err.code === 'PROTOCOL_CONNECTION_LOST' && i < maxRetries - 1) {
        console.log('Connection lost, retrying...');
        await new Promise(r => setTimeout(r, 1000));
      } else {
        throw err;
      }
    }
  }
}

import mysql.connector
from time import sleep

def query_with_retry(sql, max_retries=3):
    for i in range(max_retries):
        try:
            connection = mysql.connector.connect(
                host='localhost',
                user='root',
                password='password',
                database='mydb',
                connection_timeout=30
            )
            cursor = connection.cursor()
            cursor.execute(sql)
            return cursor.fetchall()
        except mysql.connector.Error as err:
            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 network with MTR:
mtr mysql.example.com

# Linux socket options (in application):
tcp_keepalives_idle = 600
tcp_keepalives_interval = 60
tcp_keepalives_count = 5

-- Create user with SSL requirement:
CREATE USER 'user'@'host' IDENTIFIED BY 'password' REQUIRE SSL;