How to fix CR_SERVER_GONE_ERROR (2006): MySQL server has gone away in MySQL

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

-- Typical default is 28800 seconds (8 hours)
-- For applications with connection pooling, consider:
SET GLOBAL wait_timeout = 86400; -- 24 hours
SET GLOBAL interactive_timeout = 86400; -- 24 hours

-- For persistent changes, add to my.cnf/my.ini:
[mysqld]
wait_timeout = 86400
interactive_timeout = 86400

# Python example with mysql-connector-python
import mysql.connector
from mysql.connector import Error

def get_connection():
    try:
        # Try to ping existing connection
        if hasattr(get_connection, 'cached_conn'):
            try:
                get_connection.cached_conn.ping(reconnect=True, attempts=3, delay=1)
                return get_connection.cached_conn
            except Error:
                # Connection failed, create new one
                pass
        
        # Create new connection
        conn = mysql.connector.connect(
            host='localhost',
            user='user',
            password='password',
            database='mydb',
            connection_timeout=30,
            pool_size=10,
            pool_reset_session=True
        )
        get_connection.cached_conn = conn
        return conn
    except Error as e:
        print(f"Connection failed: {e}")
        raise

// Java example with HikariCP
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000); // 30 seconds
config.setIdleTimeout(600000); // 10 minutes
config.setMaxLifetime(1800000); // 30 minutes
config.setConnectionTestQuery("SELECT 1");
config.setValidationTimeout(5000); // 5 seconds

// Important: Set MySQL-specific properties
config.addDataSourceProperty("autoReconnect", "true");
config.addDataSourceProperty("maxReconnects", "3");
config.addDataSourceProperty("initialTimeout", "2");

HikariDataSource dataSource = new HikariDataSource(config);

-- Check current value
SHOW VARIABLES LIKE 'max_allowed_packet';

-- Increase (default is 4MB-64MB depending on version)
SET GLOBAL max_allowed_packet = 67108864; -- 64MB

-- For persistent changes, add to my.cnf:
[mysqld]
max_allowed_packet = 64M

-- Also check client-side setting if using certain connectors
[mysql]
max_allowed_packet = 64M

-- Find slow queries
SHOW PROCESSLIST;

-- Enable slow query log
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2; -- queries longer than 2 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Check for queries being killed
SHOW GLOBAL STATUS LIKE 'aborted_connects';
SHOW GLOBAL STATUS LIKE 'aborted_clients';

-- Analyze and optimize slow queries
EXPLAIN SELECT * FROM large_table WHERE unindexed_column = 'value';

-- Consider adding indexes
CREATE INDEX idx_column ON large_table(unindexed_column);

// Node.js example with exponential backoff
async function executeWithRetry(query, params, maxRetries = 3) {
  let lastError;
  
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const connection = await pool.getConnection();
      try {
        const [rows] = await connection.execute(query, params);
        connection.release();
        return rows;
      } catch (err) {
        connection.release();
        throw err;
      }
    } catch (err) {
      lastError = err;
      
      // Check if it's a "server gone away" error
      if (err.code === 'PROTOCOL_CONNECTION_LOST' || 
          err.errno === 2006 || 
          err.message.includes('server has gone away')) {
        
        // Exponential backoff: 100ms, 200ms, 400ms
        const delay = Math.pow(2, attempt - 1) * 100;
        await new Promise(resolve => setTimeout(resolve, delay));
        
        // Try to re-establish pool connections
        if (attempt < maxRetries) {
          console.log(`Retrying query (attempt ${attempt}/${maxRetries})...`);
          continue;
        }
      }
      
      // Not a retryable error or out of retries
      break;
    }
  }
  
  throw lastError;
}