How to Fix MySQL Error 1114: The Table Is Full

ERROR 1114 (HY000): The table 'tablename' is full is raised by MySQL when it cannot allocate additional storage for a table. The trigger varies by storage engine and configuration: InnoDB can exhaust a fixed-size data file, MyISAM has a per-table size limit, and internal temporary tables spill to disk when they hit tmp_table_size / max_heap_table_size.

Impact

The failing statement is rolled back. Depending on context, this can mean a lost write, a failed migration, or an aborted ALTER TABLE. The error often surfaces unexpectedly — the table may have grown slowly for months and then hit a hard limit during a peak traffic period or a scheduled batch job.

Common Causes

1. InnoDB system tablespace exhausted: ibdata1 (or innodb_data_file_path) was created with a fixed size and autoextend was not specified, or the disk is full
2. Disk full: The filesystem hosting MySQL's datadir or tmpdir has run out of space
3. innodb_data_home_dir / per-table tablespace file is full: In innodb_file_per_table=ON mode (the default since MySQL 5.6), each table has its own .ibd file; if the filesystem is full, new extents cannot be allocated
4. Internal temporary table overflow: A complex GROUP BY, ORDER BY, UNION, or subquery that builds an in-memory MEMORY table exceeds tmp_table_size or max_heap_table_size; MySQL spills to a disk-based temp table in tmpdir, and if tmpdir is full, error 1114 is raised for the temp table
5. MyISAM MAX_ROWS / AVG_ROW_LENGTH exceeded: A MyISAM table created with an explicit row count or row length limit hits that ceiling
6. Partitioned table with a partition on a separate filesystem that is full
7. Quota on the MySQL OS user that limits total file size

Troubleshooting and Resolution Steps

1. Check disk space on the datadir filesystem:

   df -h /var/lib/mysql
   du -sh /var/lib/mysql/*
   

   A full filesystem is the most common cause and is immediately visible.

2. Check the InnoDB system tablespace configuration:

   SHOW VARIABLES LIKE 'innodb_data_file_path';
   SHOW VARIABLES LIKE 'innodb_data_home_dir';
   SHOW VARIABLES LIKE 'innodb_file_per_table';
   

   A value like ibdata1:10M (without :autoextend) means the system tablespace cannot grow. ibdata1:10M:autoextend allows growth limited by disk space.

3. Fix a fixed-size InnoDB system tablespace by adding an autoextend file:

   # my.cnf — add a second file that can grow
   [mysqld]
   innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend
   

   This requires a MySQL restart. The new ibdata2 file is created on startup and will grow as needed.

4. Free disk space when the filesystem is full:

   • Rotate and purge binary logs: PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;
   • Remove old slow query logs, error logs, and general query logs
   • Run OPTIMIZE TABLE on heavily fragmented MyISAM tables (reduces file size)
   • For InnoDB, reclaim space by dropping tables and running innodb_file_per_table (each table has its own .ibd that shrinks on DROP TABLE)

5. Check and expand tmpdir for temp-table overflow:

   SHOW VARIABLES LIKE 'tmpdir';
   SHOW VARIABLES LIKE 'tmp_table_size';
   SHOW VARIABLES LIKE 'max_heap_table_size';
   

   df -h $(mysql -N -e "SHOW VARIABLES LIKE 'tmpdir'" | awk '{print $2}')
   

   The limit for in-memory temp tables is MIN(tmp_table_size, max_heap_table_size). Raise both to allow larger in-memory temps before spilling:

   SET GLOBAL tmp_table_size = 134217728;        -- 128 MB
   SET GLOBAL max_heap_table_size = 134217728;    -- 128 MB
   SET PERSIST tmp_table_size = 134217728;
   SET PERSIST max_heap_table_size = 134217728;
   

6. Identify which query is generating the large temp table:

   SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
   SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
   

   The ratio Created_tmp_disk_tables / Created_tmp_tables indicates what fraction of temp tables spill to disk. Then find the queries:

   SELECT DIGEST_TEXT, SUM_CREATED_TMP_DISK_TABLES, SUM_CREATED_TMP_TABLES
   FROM performance_schema.events_statements_summary_by_digest
   WHERE SUM_CREATED_TMP_DISK_TABLES > 0
   ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
   LIMIT 10;
   

7. Optimize queries that create large temp tables. The most common causes are:

   • GROUP BY on a column without an index: add a covering index
   • UNION of two large result sets: use UNION ALL where duplicate removal is not needed
   • Large TEXT / BLOB columns in the result set force disk temp tables regardless of size (MySQL 8.0 uses the new TempTable engine which handles BLOBs in memory, but pre-8.0 always spills):

     -- Use SUBSTRING to avoid BLOB/TEXT in temp tables
     SELECT SUBSTRING(content, 1, 255) AS content_preview, ...
     FROM articles GROUP BY author_id;
     

8. Fix MyISAM MAX_ROWS limit:

   -- Check current row count and limit
   SHOW TABLE STATUS LIKE 'mytable'\G
   
   -- Raise the limit
   ALTER TABLE mytable MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 200;
   

   Consider migrating MyISAM tables to InnoDB, which does not have this artificial limit:

   ALTER TABLE mytable ENGINE = InnoDB;
   

9. Reclaim space in InnoDB per-table tablespaces:

   -- Rebuild the table to reclaim space from deleted rows
   OPTIMIZE TABLE mytable;
   -- or
   ALTER TABLE mytable ENGINE = InnoDB;  -- triggers a full rebuild
   

   Note: for large tables this locks the table (with ALGORITHM=COPY) or requires gh-ost / pt-online-schema-change for zero-downtime reclaim.

10. Monitor disk usage proactively:

    -- Top tables by data + index size
    SELECT
        table_schema,
        table_name,
        ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb,
        ROUND(data_free / 1024 / 1024, 1) AS fragmented_mb
    FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
    ORDER BY size_mb DESC
    LIMIT 20;
    

Additional Information

• innodb_file_per_table=ON (the default since MySQL 5.6) isolates each table's data to its own .ibd file. Space from dropped rows stays in that file (shown as data_free in information_schema.tables) until OPTIMIZE TABLE reclaims it. The file size never automatically shrinks.
• The InnoDB system tablespace (ibdata1) accumulates the undo log, the data dictionary (MySQL 5.7), and the doublewrite buffer. In MySQL 8.0, the data dictionary moved to mysql.ibd and undo logs can be moved to separate tablespaces, reducing ibdata1 growth.
• Partitioned tables can spread data across multiple filesystems. If a single partition's filesystem fills, error 1114 fires for writes to that partition only.
• tmpdir can be set to a comma-separated list of directories; MySQL round-robins temp file creation across them. Add a fast SSD path here for I/O-intensive sort/group workloads.

Frequently Asked Questions

Q: I deleted millions of rows but the table file is still large. How do I reclaim the space? A: Run OPTIMIZE TABLE tablename. InnoDB marks deleted rows as reclaimable but does not shrink the .ibd file until a full rebuild is triggered. OPTIMIZE TABLE rebuilds the table in place.

Q: The disk is 80% full. Should I add more disk or purge data first? A: Purge unnecessary data first (old binary logs, large temp files, unused tables). Then add disk if still needed. Reaching 95%+ on a MySQL filesystem causes intermittent errors across multiple operations simultaneously, not just 1114.

Q: How do I move tmpdir to a larger disk? A: Set tmpdir = /path/to/larger/disk in my.cnf and restart MySQL. The directory must exist and be writable by the MySQL OS user.

Q: Can I shrink ibdata1? A: Not in-place. To shrink the system tablespace you must: (1) export all data with mysqldump, (2) stop MySQL and delete the ibdata files, (3) restart MySQL (it creates a fresh small ibdata1), (4) restore from the dump. This is disruptive — plan a maintenance window.

Q: Error 1114 appears on a query, not an INSERT. Why? A: That query is probably creating an internal temp table that overflows. A complex SELECT with ORDER BY or GROUP BY on columns that cannot use an index will materialize an intermediate result set. If tmpdir is full or the result exceeds temp table limits, error 1114 fires on the SELECT.