How do I fix the MySQL error "The table is full"?

Validated on 27 Jun 2023 • Last edited on 23 May 2024

MySQL may return the error “The table ‘’ is full when executing a query with a GROUP BY clause.

MySQL sometimes creates temporary tables to hold intermediate results when executing these queries. This error indicates that a temporary table has reached its maximum capacity.

To fix this error, try the following methods:

  • Batch or limit the query. Consider splitting the query into smaller batches or limiting the number of rows.
  • Optimize the query. Review the query and verify that it uses appropriate indexes, joins, and conditions to minimize the data processed.
  • Increase the temporary table size. Adjust the configuration parameters for temporary tables in MySQL. Specifically, increase the values of tmp_table_size (for in-memory tables) and max_heap_table_size (for on-disk tables) to enable larger temporary tables. You can update these with the following API call:
How to update the Database Configuration for an Existing Database using the DigitalOcean API

To update the Database Configuration for an Existing Database using the DigitalOcean API, follow these steps:

  1. Create a personal access token, and save it for use with the API.

  2. Send a PATCH request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/config

    cURL

    To update the Database Configuration for an Existing Database with cURL, call:

    
                curl -X PATCH \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"config": {"sql_mode": "ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES","sql_require_primary_key": true}}' \
  "https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/config"

    Python

    
                import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

resp = client.databases.patch_config(database_cluster_uuid="a7aba9d")
  • Change the storage engine. You can set the MySQL variable internal_tmp_mem_storage_engine to use the MEMORY storage engine for internal temporary tables within the current session. You can adjust this variable with set session internal_tmp_mem_storage_engine=Memory;.
Control Panel Blog Pricing Careers Terms Privacy Status API Docs Tutorials Support