How to fix CR_ALREADY_CONNECTED (2058): Handle already connected in MySQL

// WRONG - attempting to connect twice without closing
MYSQL *conn = mysql_init(NULL);
mysql_real_connect(conn, host, user, password, db, 0, NULL, 0);
// ... do work ...
mysql_real_connect(conn, host2, user2, password2, db2, 0, NULL, 0);  // ERROR 2058!

// CORRECT - close before reconnecting
MYSQL *conn = mysql_init(NULL);
mysql_real_connect(conn, host, user, password, db, 0, NULL, 0);
// ... do work ...
mysql_close(conn);  // Close first

conn = mysql_init(NULL);  // Allocate new handle
mysql_real_connect(conn, host2, user2, password2, db2, 0, NULL, 0);  // Now safe

// WRONG - sharing one handle across threads
MYSQL *shared_conn = mysql_init(NULL);
mysql_real_connect(shared_conn, host, user, password, db, 0, NULL, 0);

// Thread 1
mysql_query(shared_conn, "SELECT ...");  // May race with thread 2

// Thread 2 (concurrent)
mysql_query(shared_conn, "SELECT ...");  // DATA RACE!

// CORRECT - one handle per thread/connection
void* thread_func(void* arg) {
    MYSQL *conn = mysql_init(NULL);
    mysql_real_connect(conn, host, user, password, db, 0, NULL, 0);
    
    // Safe - this thread owns this connection
    mysql_query(conn, "SELECT ...");
    
    mysql_close(conn);
    return NULL;
}

# Python example with proper pooling
from mysql.connector import pooling

dbconfig = {
    "host": "localhost",
    "user": "myuser",
    "password": "mypassword",
    "database": "mydb"
}

# Create pool (not connecting yet)
pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,  # Number of connections
    **dbconfig
)

# Each call gets a fresh connection
conn1 = pool.get_connection()  # Gets connection 1
conn2 = pool.get_connection()  # Gets connection 2 (different handle)

# Use connections
conn1.cursor().execute("SELECT ...")
conn2.cursor().execute("SELECT ...")

# Return to pool (ready for reuse)
conn1.close()
conn2.close()

// Node.js example with mysql2/promise pooling
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'myuser',
    password: 'mypassword',
    database: 'mydb'
});

// Each getConnection() returns a separate connection
const conn1 = await pool.getConnection();
const conn2 = await pool.getConnection();

await conn1.query("SELECT ...");
await conn2.query("SELECT ...");

conn1.release();  // Return to pool
conn2.release();  // Return to pool

// Check if connection is still alive
if (mysql_ping(conn) != 0) {
    // Connection is dead, close and reconnect
    mysql_close(conn);
    conn = mysql_init(NULL);
    mysql_real_connect(conn, host, user, password, db, 0, NULL, 0);
} else {
    // Connection is alive, safe to use
    mysql_query(conn, "SELECT ...");
}

# Python - check connection status
try:
    cursor = conn.cursor()
    cursor.execute("SELECT 1")  # Lightweight query to verify
except mysql.connector.Error:
    # Connection dead, close and reconnect
    conn.close()
    conn = mysql.connector.connect(**config)

# WRONG - pool doesn't track connection state
class BrokenPool:
    def __init__(self):
        self.conn = mysql.connector.connect(**config)  # Single shared connection
    
    def get_connection(self):
        return self.conn  # Returns same handle every time!

# CORRECT - pool manages handles properly
class ProperPool:
    def __init__(self, size=5):
        self.available = []
        self.in_use = set()
        
        # Create pool of connections
        for _ in range(size):
            conn = mysql.connector.connect(**config)
            self.available.append(conn)
    
    def get_connection(self):
        if not self.available:
            raise Exception("No available connections")
        
        conn = self.available.pop()
        self.in_use.add(id(conn))
        return conn
    
    def return_connection(self, conn):
        # Verify connection state
        if conn.is_connected():
            self.in_use.discard(id(conn))
            self.available.append(conn)
        else:
            # Dead connection, create new one
            self.in_use.discard(id(conn))
            conn.close()
            self.available.append(
                mysql.connector.connect(**config)
            )
    
    def close_all(self):
        for conn in self.available:
            conn.close()
        for conn_id in self.in_use:
            # Try to close in-use connections
            pass

# WRONG - attempts to reconnect on same handle
def execute_with_retry(query, retries=3):
    for attempt in range(retries):
        try:
            cursor = conn.cursor()
            cursor.execute(query)
            return cursor.fetchall()
        except mysql.connector.Error as e:
            if attempt < retries - 1:
                conn.reconnect()  # ERROR 2058 if already connected!

# CORRECT - closes before reconnecting
def execute_with_retry(query, retries=3):
    global conn
    for attempt in range(retries):
        try:
            cursor = conn.cursor()
            cursor.execute(query)
            return cursor.fetchall()
        except mysql.connector.Error as e:
            if attempt < retries - 1:
                conn.close()  # Close first!
                conn = mysql.connector.connect(**config)  # Fresh connection
            else:
                raise