How to fix ERROR 2008: MySQL client ran out of memory in MySQL

First, review whether the query is correct and whether you truly need all rows. Run:

SELECT COUNT(*) FROM your_table WHERE your_conditions;

If this returns 100,000+ rows, you likely need to add filters or pagination. Fewer rows = less memory on client side.

The --quick (or -q) flag tells the mysql client to use streaming result retrieval instead of buffering the entire result set:

mysql -h localhost -u user -p --quick < large_query.sql > output.txt

This uses mysql_use_result() which fetches rows on-demand rather than all at once. This dramatically reduces client-side memory usage.

Instead of retrieving results into the client, save directly to the server file system:

SELECT * FROM large_table
INTO OUTFILE '/var/lib/mysql-files/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Then retrieve the file over SSH/SCP. This bypasses client memory entirely by processing on the server. Note: The file must be saved in the MySQL data directory (secure_file_priv setting).

Break large queries into smaller chunks:

SELECT * FROM large_table
WHERE id > 0
LIMIT 10000;

-- Then in next iteration:
SELECT * FROM large_table
WHERE id > 10000
LIMIT 10000;

Or use offset-based pagination:

SELECT * FROM large_table LIMIT 10000 OFFSET 0;
SELECT * FROM large_table LIMIT 10000 OFFSET 10000;

Process results in batches of 10,000-50,000 rows per query.

Check if your MySQL client is 32-bit:

# On Linux
file $(which mysql)
# Output: ELF 32-bit LSB shared object = 32-bit

# On Windows
# Check MySQL Workbench: Help > About

64-bit clients can address much more memory (terabytes vs 2-4 GB). Install the 64-bit version of your MySQL client, Workbench, or database driver:

# Linux
sudo apt install mysql-client # defaults to 64-bit on modern systems

# macOS
brew install mysql-client

# Windows
# Download MySQL Community Server 64-bit installer from dev.mysql.com

If using ODBC (Excel, Power BI, certain BI tools), adjust result caching:

In ODBC Data Source Administrator (Windows) or .odbc.ini (Linux/macOS):

[MySQL ODBC Driver]
CURSOR_TYPE=UseOdbc
USE_RESULT=1
PREFETCH=1000

Or in some drivers, enable:
- "Don't cache results of forward-only cursors"
- "Force use of forward-only cursors"

These settings use streaming instead of buffering the entire result set.

Check and increase the memory limit your OS allows for the mysql process:

# Check current limits
ulimit -a

# Increase heap size (temporary, until reboot)
ulimit -v unlimited

# For persistent changes, edit /etc/security/limits.conf (Linux):
* hard memlock unlimited
* soft memlock unlimited

However, this is a temporary workaround. The root issue is the query/result set being too large.

If backing up or exporting data, mysqldump with --quick streams results:

mysqldump -h localhost -u user -p --quick --single-transaction \
  --routines --triggers large_database > backup.sql

The --quick flag (default with mysqldump) uses mysql_use_result() to avoid buffering. Always include --single-transaction for InnoDB to maintain consistency without table locks.