How to fix CR_FETCH_CANCELED (2050): Row retrieval canceled in MySQL

// C API - Correct pattern
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, query, strlen(query));
mysql_stmt_execute(stmt);
mysql_stmt_bind_result(stmt, bind);

// Fetch ALL rows before closing
while ((status = mysql_stmt_fetch(stmt)) == 0) {
    // Process row data
    printf("Row data: %s\n", column_data);
}

// Check for errors after loop
if (status != MYSQL_NO_DATA) {
    fprintf(stderr, "Fetch error: %s\n", mysql_stmt_error(stmt));
}

// NOW it's safe to close
mysql_stmt_close(stmt);

// PHP mysqli - Correct pattern
$stmt = $mysqli->prepare("SELECT id, name FROM users WHERE active = ?");
$active = 1;
$stmt->bind_param("i", $active);
$stmt->execute();
$stmt->bind_result($id, $name);

// Fetch all rows
while ($stmt->fetch()) {
    echo "ID: $id, Name: $name\n";
}

// All rows fetched, now close
$stmt->close();

# Python mysql-connector - Correct pattern
import mysql.connector

conn = mysql.connector.connect(host='localhost', user='root', password='pass', database='mydb')
cursor = conn.cursor(prepared=True)

query = "SELECT id, name FROM users WHERE active = %s"
cursor.execute(query, (1,))

# Fetch all rows
for (id, name) in cursor:
    print(f"ID: {id}, Name: {name}")

# All rows consumed, safe to close
cursor.close()
conn.close()

// C API - Buffered approach
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, query, strlen(query));
mysql_stmt_bind_param(stmt, bind_params);
mysql_stmt_execute(stmt);
mysql_stmt_bind_result(stmt, bind_results);

// Buffer complete result set on client
if (mysql_stmt_store_result(stmt)) {
    fprintf(stderr, "Store result error: %s\n", mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    return;
}

// Get total row count
unsigned long long num_rows = mysql_stmt_num_rows(stmt);
printf("Total rows: %llu\n", num_rows);

// Now fetch rows - they're already buffered
while (mysql_stmt_fetch(stmt) == 0) {
    // Process row
}

// Free buffered results before closing
mysql_stmt_free_result(stmt);
mysql_stmt_close(stmt);

// PHP mysqli - Store result
$stmt = $mysqli->prepare("SELECT id, email FROM users");
$stmt->execute();

// Buffer complete result set
$stmt->store_result();

printf("Total rows: %d\n", $stmt->num_rows);

$stmt->bind_result($id, $email);

while ($stmt->fetch()) {
    echo "ID: $id, Email: $email\n";
}

// Free buffered results
$stmt->free_result();
$stmt->close();

// WRONG - Closes statement before fetching completes
try {
    $stmt = $mysqli->prepare("SELECT id, data FROM large_table");
    $stmt->execute();
    $stmt->bind_result($id, $data);
    
    while ($stmt->fetch()) {
        if (someErrorCondition($data)) {
            throw new Exception("Data validation failed");
            // Statement closes in finally before fetch completes!
        }
        processData($id, $data);
    }
} catch (Exception $e) {
    log_error($e->getMessage());
} finally {
    if (isset($stmt)) $stmt->close();  // ERROR 2050 here!
}

// CORRECT - Drain remaining rows before closing
try {
    $stmt = $mysqli->prepare("SELECT id, data FROM large_table");
    $stmt->execute();
    $stmt->bind_result($id, $data);
    
    $hasError = false;
    $errorMsg = null;
    
    while ($stmt->fetch()) {
        if (someErrorCondition($data)) {
            $hasError = true;
            $errorMsg = "Data validation failed for ID: $id";
            // Don't throw - continue fetching to drain rows
            continue;
        }
        processData($id, $data);
    }
    
    if ($hasError) {
        throw new Exception($errorMsg);
    }
} catch (Exception $e) {
    log_error($e->getMessage());
} finally {
    if (isset($stmt)) $stmt->close();  // Now safe - all rows fetched
}

// C API - Drain rows on error
int error_occurred = 0;
char error_msg[256];

while ((status = mysql_stmt_fetch(stmt)) == 0) {
    // Validation check
    if (validate_data(column_data) != 0) {
        error_occurred = 1;
        snprintf(error_msg, sizeof(error_msg), "Invalid data: %s", column_data);
        // Don't break - continue fetching!
        continue;
    }
    
    process_data(column_data);
}

// All rows drained, now handle error
if (error_occurred) {
    log_error(error_msg);
}

mysql_stmt_close(stmt);  // Safe - fetch loop completed

// C API - Free result to discard remaining rows
MYSQL_STMT *stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, "SELECT * FROM huge_table", 30);
mysql_stmt_execute(stmt);
mysql_stmt_bind_result(stmt, bind);

int rows_processed = 0;
int max_rows = 100;

// Process only first 100 rows
while (mysql_stmt_fetch(stmt) == 0 && rows_processed < max_rows) {
    process_row(column_data);
    rows_processed++;
}

// Discard remaining rows (if unbuffered)
mysql_stmt_free_result(stmt);

// Now safe to close
mysql_stmt_close(stmt);

// PHP mysqli - Free result when done early
$stmt = $mysqli->prepare("SELECT id, name FROM users");
$stmt->execute();
$stmt->bind_result($id, $name);

// Process only first 10 rows
$count = 0;
while ($stmt->fetch() && $count < 10) {
    echo "ID: $id, Name: $name\n";
    $count++;
}

// Free remaining results
$stmt->free_result();

// Safe to close now
$stmt->close();

# Python mysql-connector - Close cursor properly
import mysql.connector

conn = mysql.connector.connect(host='localhost', user='root', password='pass', database='mydb')
cursor = conn.cursor(prepared=True)

query = "SELECT id, data FROM large_table"
cursor.execute(query)

# Process only first 50 rows
for i, row in enumerate(cursor):
    if i >= 50:
        break
    process_row(row)

# Consume remaining rows before closing
for _ in cursor:
    pass  # Drain remaining rows

cursor.close()
conn.close()

// Node.js mysql2 - WRONG (asynchronous issue)
const mysql = require('mysql2/promise');

async function queryDataWrong() {
    const connection = await mysql.createConnection(config);
    
    const [rows] = await connection.execute(
        'SELECT id, data FROM users WHERE active = ?',
        [1]
    );
    
    // This closes connection immediately
    await connection.end();
    
    // Processing rows after connection closed - potential issues
    rows.forEach(row => {
        processDataAsync(row);  // If this is async, connection may be gone
    });
}

// CORRECT - Keep connection alive during processing
async function queryDataCorrect() {
    const connection = await mysql.createConnection(config);
    
    try {
        const [rows] = await connection.execute(
            'SELECT id, data FROM users WHERE active = ?',
            [1]
        );
        
        // Process all rows before closing
        for (const row of rows) {
            await processDataAsync(row);
        }
    } finally {
        // Close only after all processing complete
        await connection.end();
    }
}

// PHP - Streaming with generator (avoid premature close)
function streamResults($mysqli) {
    $stmt = $mysqli->prepare("SELECT id, data FROM large_table");
    $stmt->execute();
    $stmt->bind_result($id, $data);
    
    while ($stmt->fetch()) {
        yield ['id' => $id, 'data' => $data];
    }
    
    // Close after iteration completes
    $stmt->close();
}

// Usage
foreach (streamResults($mysqli) as $row) {
    processRow($row);
}  // Statement closes automatically when generator finishes

# Python - Context manager ensures proper cleanup
import mysql.connector
from contextlib import closing

conn = mysql.connector.connect(host='localhost', user='root', password='pass', database='mydb')

with closing(conn.cursor(prepared=True)) as cursor:
    cursor.execute("SELECT id, name FROM users WHERE active = %s", (1,))
    
    # Process all rows
    for row in cursor:
        process_row(row)
    
    # Cursor automatically closed after with block
    # But only after all rows consumed