How to fix CR_OUT_OF_MEMORY (2008): MySQL client ran out of memory in MySQL

# Linux - Check available RAM
free -h
free -m

# Detailed memory breakdown
cat /proc/meminfo

# Check process memory limits
ulimit -a
ulimit -v  # Virtual memory limit
ulimit -m  # Physical memory limit

# macOS
vm_stat
memory_pressure

# Windows
wmic OS get TotalVisibleMemorySize,FreePhysicalMemory

-- Get row count and average row size
SELECT 
  COUNT(*) as total_rows,
  AVG(LENGTH(CAST(CONCAT_WS(',', col1, col2, col3) AS CHAR))) as avg_row_bytes,
  COUNT(*) * AVG(LENGTH(CAST(CONCAT_WS(',', col1, col2, col3) AS CHAR))) / 1024 / 1024 as approx_result_mb
FROM your_large_table;

-- Or use information_schema for BLOB columns
SELECT 
  table_name,
  COUNT(*) as row_count,
  ROUND(SUM(octet_length) / 1024 / 1024, 2) as size_mb
FROM (
  SELECT 
    'table_name' as table_name,
    OCTET_LENGTH(blob_column) as octet_length
  FROM your_table
) t
GROUP BY table_name;

-- For tables with BLOB columns, check max/min sizes
SELECT 
  MIN(OCTET_LENGTH(blob_column)) as min_size_bytes,
  MAX(OCTET_LENGTH(blob_column)) as max_size_bytes,
  AVG(OCTET_LENGTH(blob_column)) as avg_size_bytes,
  COUNT(*) as row_count,
  COUNT(*) * AVG(OCTET_LENGTH(blob_column)) / 1024 / 1024 as total_approx_mb
FROM your_table;

-- Instead of:
SELECT * FROM large_table;

-- Use:
SELECT * FROM large_table LIMIT 1000;

-- For pagination, use OFFSET:
SELECT * FROM large_table LIMIT 1000 OFFSET 0;    -- First page
SELECT * FROM large_table LIMIT 1000 OFFSET 1000; -- Second page
SELECT * FROM large_table LIMIT 1000 OFFSET 2000; -- Third page

// PHP example - pagination loop
$pageSize = 1000;
$offset = 0;

while (true) {
    $query = "SELECT * FROM large_table LIMIT $pageSize OFFSET $offset";
    $result = $mysqli->query($query);
    
    if ($result->num_rows === 0) {
        break; // No more rows
    }
    
    while ($row = $result->fetch_assoc()) {
        // Process one row at a time
        process_row($row);
    }
    
    $offset += $pageSize;
}

# Python example
import mysql.connector

db = mysql.connector.connect(...)
cursor = db.cursor()

page_size = 1000
offset = 0

while True:
    query = f"SELECT * FROM large_table LIMIT {page_size} OFFSET {offset}"
    cursor.execute(query)
    
    rows = cursor.fetchall()
    if not rows:
        break
    
    for row in rows:
        process_row(row)
    
    offset += page_size

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

const connection = await mysql.createConnection({...});

const pageSize = 1000;
let offset = 0;

while (true) {
    const [rows] = await connection.execute(
        'SELECT * FROM large_table LIMIT ? OFFSET ?',
        [pageSize, offset]
    );
    
    if (rows.length === 0) break;
    
    for (const row of rows) {
        processRow(row);
    }
    
    offset += pageSize;
}

// PHP mysqli - unbuffered query
$mysqli->real_query("SELECT * FROM large_table");
$result = $mysqli->use_result(); // Unbuffered fetch

while ($row = $result->fetch_assoc()) {
    // Process one row at a time - memory efficient
    process_row($row);
}

// Compare to buffered (default):
$result = $mysqli->query("SELECT * FROM large_table"); // Buffered
while ($row = $result->fetch_assoc()) {
    // All rows loaded in memory before loop
    process_row($row);
}

# Python MySQLdb - SSCursor (Server-Side Cursor)
import MySQLdb
from MySQLdb.cursors import SSCursor

db = MySQLdb.connect(...)

# Standard cursor - buffered (may cause memory error)
# cursor = db.cursor()

# SSCursor - unbuffered (memory efficient)
cursor = db.cursor(SSCursor)

cursor.execute("SELECT * FROM large_table")

for row in cursor:
    # Fetches rows one at a time
    process_row(row)

// Node.js mysql2 - streaming
const mysql = require('mysql2');
const connection = mysql.createConnection({...});

const query = "SELECT * FROM large_table";

connection
    .query(query)
    .on('result', function(row) {
        // Handle each row as it arrives
        process_row(row);
    })
    .on('error', function(err) {
        console.error(err);
    })
    .on('end', function() {
        // Done
        console.log('All rows processed');
    });

# Ruby mysql2 - streaming
db = Mysql2::Client.new(host: 'localhost', username: 'root')

db.query("SELECT * FROM large_table").each do |row|
    # Stream_each_row processes one row at a time
    process_row(row)
end

# Linux - Increase virtual memory limit for current session
ulimit -v unlimited

# Or set permanent limit in /etc/security/limits.conf
# Add line:
# username soft memlock unlimited
# username hard memlock unlimited
# Then restart shell

# For system services, set in systemd unit
sudo systemctl edit service_name

# Add:
[Service]
MemoryLimit=4G
MemoryMax=4G

sudo systemctl daemon-reload
sudo systemctl restart service_name

# For PHP
# Edit php.ini
php_value memory_limit 512M

# Or set at runtime
ini_set('memory_limit', '512M');

# Check current limit
echo ini_get('memory_limit');

# For Java applications using MySQL JDBC
# Set JVM heap size
java -Xmx2G -Xms1G -jar application.jar

# Or in JAVA_OPTS environment variable
export JAVA_OPTS="-Xmx2G -Xms1G"

# For Python
# Run with explicit memory
python script.py

# Or use resource module
import resource
resource.setrlimit(resource.RLIMIT_AS, (4*1024*1024*1024, 4*1024*1024*1024))

-- Only select needed columns
SELECT id, name, email FROM large_table;
-- Instead of SELECT *

-- Add WHERE clause to filter
SELECT * FROM large_table WHERE status = 'active';
-- Instead of all records

-- Use appropriate indexes
CREATE INDEX idx_status ON large_table(status);
CREATE INDEX idx_created ON large_table(created_at);

-- Check query plan
EXPLAIN SELECT * FROM large_table WHERE status = 'active' LIMIT 10;

-- Filter before JOINing large tables
SELECT a.*, b.* 
FROM table_a a
JOIN table_b b ON a.id = b.a_id
WHERE a.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Instead of joining all rows then filtering

-- Avoid SELECT * with BLOB columns
SELECT id, name FROM large_table;
-- Instead of SELECT * when you don't need the BLOB column

-- For aggregations, reduce rows
SELECT category, COUNT(*) FROM table GROUP BY category;
-- Instead of fetching all rows to count in application

-- Enable query profiling
SET profiling = 1;
SELECT * FROM large_table LIMIT 10;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- Check slow query log
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# mysqldump with chunking
mysqldump --single-transaction --quick --lock-tables=false \
  -h localhost -u root -p database table_name > backup.sql

# For very large tables, use --where to chunk:
mysqldump --single-transaction --quick \
  -h localhost -u root -p database table_name \
  --where="id >= 1 AND id < 100000" > chunk1.sql

mysqldump --single-transaction --quick \
  -h localhost -u root -p database table_name \
  --where="id >= 100000 AND id < 200000" > chunk2.sql

# Using mysql command with pager for streaming output
mysql -h localhost -u root -p -e "SELECT * FROM large_table" | \
  sed '1d' | \
  split -l 10000 - output_
# This streams output instead of buffering

# mysqlsh (MySQL Shell) supports streaming
mysqlsh --uri user@localhost/database \
  --execute "SELECT * FROM large_table LIMIT 1000"

# Use load_infile for imports (faster, less memory)
FLOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

# Application level with streaming
with open('large_file.csv', 'r') as f:
    rows = []
    for i, line in enumerate(f):
        rows.append(parse_csv_line(line))
        
        if len(rows) == 1000:
            insert_batch(rows)
            rows = []
    
    if rows:
        insert_batch(rows)