The server aborts a statement because it requests more memory than the internal capacity limit allows.
MySQL error 3170 ER_CAPACITY_EXCEEDED occurs when a query needs more RAM than the server’s built-in limit. Reduce the data processed or raise tmp_table_size and max_heap_table_size to resolve the issue.
ER_CAPACITY_EXCEEDED
MySQL raises error 3170 (SQLSTATE HY000) when an operation runs out of the in-memory capacity hard limit. The engine stops the statement to protect stability, so queries fail or transactions roll back until memory use is reduced or limits are increased.
The error string shows the number of bytes requested and the component that triggered the allocation, helping you trace the root cause quickly.
Large hash joins, sorts, GROUP BY aggregations, or temporary tables can exceed the allocator threshold, especially when join_buffer_size, sort_buffer_size, or tmp_table_size are too small for the dataset processed.
Operations against the MEMORY storage engine, oversized JSON documents, or heavy use of user variables may also exhaust the server’s memory quota and raise ER_CAPACITY_EXCEEDED.
Locate the failing statement via Performance Schema or the slow query log, then either rewrite it to process fewer rows or raise the memory parameters involved.
If system RAM allows, increase tmp_table_size and max_heap_table_size so MySQL can materialize larger internal tables without failing. Monitor memory status counters to confirm the change works.
For heavy joins and sorts, carefully enlarge join_buffer_size and sort_buffer_size, tracking overall consumption to avoid operating-system OOM.
A reporting query with nested derived tables often spikes memory. Break it into smaller stages or persist intermediate results to on-disk tables to stay below the limit.
Insert activity on user-created MEMORY tables can exceed size caps. Convert those tables to InnoDB or add LIMIT clauses to keep them small.
Monitor memory usage metrics continuously in tools like Galaxy so you detect growth early. Align tmp_table_size and max_heap_table_size with peak load expectations and review them after every data-volume jump.
Write queries with selective WHERE filters, use covering indexes, and avoid SELECT * on large tables. Schedule resource-intensive ETL during off-peak windows.
ER_OUT_OF_SORTMEMORY (1038) arises when sort_buffer_size is too small. ER_TOO_BIG_ROWSIZE (1114) appears when a single row exceeds engine limits. Both errors are resolved by reducing data size or tuning memory variables similarly.
Materialized derived tables and GROUP BY results use MEMORY tables until they reach the lower of these two variables. If the result grows beyond that limit, error 3170 fires.
Complex joins on wide tables allocate large hash structures inside join_buffer_size or sort_buffer_size, quickly breaching the capacity threshold.
User-created MEMORY tables never spill to disk. Continuous inserts can exceed their size cap and trigger ER_CAPACITY_EXCEEDED.
Storing or parsing very large JSON blobs in memory can surpass the allocator’s ceiling within a single statement.
Raised when sort_buffer_size is insufficient during ORDER BY or GROUP BY operations.
Occurs when a single row exceeds the maximum size allowed by the storage engine.
Triggered when an on-disk temporary table exhausts its file-size limit.
Check the operation named in the error text. Memory tables rely on tmp_table_size and max_heap_table_size, while joins and sorts depend on join_buffer_size and sort_buffer_size.
Only if the server has free RAM. Increase gradually and monitor total_allocated_memory to avoid operating-system OOM.
Galaxy highlights memory-heavy queries, profiles their resource use, and suggests optimizations via its AI copilot before they reach production.
A restart frees memory temporarily, but the error returns if the same query still exceeds limits. Fix the query or tune settings instead.
.avif)
.avif){kind=logo}
