Troubleshooting MySQL & MariaDB Error 1114 'The table is full': Complete Diagnostic & tmpdir Runbook

MySQL and MariaDB Error 1114 (SQLSTATE HY000, symbolic name ER_RECORD_FILE_FULL) with the message “The table is full” is one of the most deceptively named errors in relational database management. To junior administrators and webmasters, the error text implies that a physical database table has hit some arbitrary record limit or maximum row ceiling.

In reality, in modern high-throughput relational systems, Error 1114 rarely stems from an explicit base table exhausting its physical limits. Instead, it overwhelmingly indicates that MySQL or MariaDB attempted to construct an internal temporary table during complex query processing, and the storage engine exhausted its assigned memory quota, disk partition capacity, or tablespace allocation ceiling.

When transactional systems—such as high-volume WooCommerce shops, Magento catalogs, analytics engines, or SaaS reporting backends—execute unindexed multi-table JOIN, GROUP BY, ORDER BY, or DISTINCT queries, the database engine must stage intermediate row data in memory. If that intermediate dataset exceeds strict engine boundaries, the engine spills to disk. When the disk partition or temporary tablespace cannot accommodate the write, the query aborts with Error 1114.

This diagnostic runbook covers the internal execution mechanics of temporary tables, investigates the 4 root causes of Error 1114, and provides a step-by-step production runbook to permanently resolve it.

---

Internal Query Execution Mechanics: How Temporary Tables Are Formed

To understand why Error 1114 occurs, we must trace how relational query planners evaluate complex SQL statements.

┌────────────────────────────────────────────────────────────────────────┐
│                   QUERY TEMPORARY TABLE PIPELINE                       │
├────────────────────────────────────────────────────────────────────────┤
│  Client Query (GROUP BY, ORDER BY, DISTINCT, Window Functions)         │
│                               │                                        │
│                               ▼                                        │
│  Phase 1: In-Memory Temporary Table                                    │
│  - Storage Engine: MEMORY (Heaps) or TempTable (MySQL 8.0+)            │
│  - Hard Ceilings: MIN(tmp_table_size, max_heap_table_size)             │
│                               │                                        │
│             Did dataset exceed in-memory limit?                        │
│                     /                  \                               │
│                   No                    Yes                            │
│                  /                        \                            │
│                 ▼                          ▼                           │
│     Result Returned To Client    Phase 2: On-Disk Conversion           │
│                                  - MySQL 8.0+: InnoDB temp tablespace  │
│                                  - MariaDB: Aria / MyISAM disk engine  │
│                                  - Stored in: @@tmpdir (/tmp)          │
│                                            │                           │
│                          Did disk write hit full partition/tablespace? │
│                                  /                  \                  │
│                                No                    Yes               │
│                               /                        \               │
│                              ▼                          ▼              │
│                  Result Returned To Client    ERROR 1114: TABLE FULL   │
└────────────────────────────────────────────────────────────────────────┘

When MySQL or MariaDB executes an aggregation or sorting operation that cannot be resolved via an existing B-tree index, it creates an internal temporary table.

1. In-Memory Creation

Initially, the intermediate table is held entirely in system RAM:

• MySQL 5.7 and MariaDB: Handled by the MEMORY storage engine.
• MySQL 8.0+: Handled by default by the TempTable storage engine (controlled by temptable_max_ram).
• Memory Ceiling: The maximum in-memory size is governed by the lesser of two session/global parameters: tmp_table_size and max_heap_table_size.

2. On-Disk Conversion

If the intermediate dataset exceeds this threshold, or if the query extracts data columns of type BLOB or TEXT (which the classic MEMORY storage engine cannot store in memory without converting to fixed-width allocations), the query optimizer converts the temporary table into an on-disk table:

• In MySQL 5.7 and earlier, on-disk temporary tables defaulted to the MyISAM engine written into the directory defined by tmpdir (typically /tmp).
• In MySQL 8.0+, on-disk internal temporary tables use the InnoDB storage engine and reside either in the session temporary tablespace pool or the global temporary tablespace file (ibtmp1).
• In MariaDB 10.x+, on-disk temporary tables default to the crash-safe Aria storage engine, which streams temporary disk rows to /tmp as .MAD and .MAI files.

If the underlying /tmp filesystem runs out of free blocks, exhausts its inodes, or hits the maximum autoextend boundary configured on ibtmp1, the storage engine writes a failure response: Error 1114: The table is full.

---

The 4 Underlying Root Causes of Error 1114

Pinpointing the exact operational bottleneck requires isolating which layer reached saturation.

Root Cause 1: Operating System /tmp or tmpdir Filesystem Capacity Exhaustion

On standard Linux enterprise installations (RHEL, AlmaLinux, Rocky Linux, Ubuntu Server), /tmp is mounted as a dedicated virtual partition or tmpfs (RAM-backed filesystem). In many default hosting images and cPanel/WHM configurations, /tmp is restricted to between 1 GB and 4 GB to prevent unprivileged users from monopolizing physical server disk space.

When an unindexed analytical query or an e-commerce reporting dashboard joins 100,000 product rows against 2,000,000 order item rows:

1. The dataset instantly overflows the in-memory buffer (e.g., 64 MB).
2. The engine writes temporary data to /tmp.
3. If the spilled temporary dataset requires 4.5 GB of scratch space, the /tmp partition reaches 100% capacity.
4. The operating system kernel rejects further write() syscalls with ENOSPC (No space left on device), which MySQL surfaces to the application client as Error 1114.

Root Cause 2: Memory Engine / Heap Allocation Caps (tmp_table_size vs max_heap_table_size)

If an application explicitly uses memory-backed staging tables via CREATE TABLE ... ENGINE=MEMORY, or if the database is running legacy workloads that depend on the MEMORY engine:

• The maximum permissible size of a MEMORY table is bounded by max_heap_table_size.
• Unlike internal temporary tables that automatically transition to disk upon saturation, an explicit user-created table using ENGINE=MEMORY does not spill to disk. When it reaches max_heap_table_size, inserts will immediately fail with Error 1114.
• In MariaDB and MySQL 5.7, if tmp_table_size is increased to 512 MB but max_heap_table_size remains at the system default of 16 MB, internal temporary tables will spill to disk at 16 MB because MySQL enforces the mathematical minimum:

$$\text{Effective In-Memory Cap} = \min(\text{tmp_table_size}, \text{max_heap_table_size})$$

Root Cause 3: InnoDB Temporary Tablespace Autoextend Capping (innodb_temp_data_file_path)

In MySQL 5.7 and MySQL 8.0, the global temporary tablespace ibtmp1 stores rollback segments and intermediate table data for internal temporary tables. By default, ibtmp1 is set to auto-extend. However, many production configuration templates constrain file growth to protect server storage:

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:4G

If multiple concurrent worker threads execute heavy sorting operations simultaneously, the cumulative size of ibtmp1 expands rapidly. Once ibtmp1 reaches the max:4G threshold, InnoDB refuses additional page allocations. Because the tablespace is explicitly capped, MySQL returns Error 1114, even if the underlying physical NVMe filesystem has terabytes of free space.

Root Cause 4: Inode Exhaustion on the /tmp Partition

A filesystem can run out of space even when df -h shows gigabytes of remaining storage. Every individual file on a POSIX filesystem requires an inode data structure.

If long-running PHP sessions, orphaned lock files, or broken cron jobs flood /tmp with hundreds of thousands of zero-byte session files (sess_*), the filesystem exhausts its inode allocation table. When MySQL calls open() or creat() to allocate a temporary file handle for an Aria or MyISAM disk spill, the kernel returns ENOSPC because no free inodes remain.

---

Production Diagnostic Runbook

When a production application begins throwing Error 1114, follow this ordered triage sequence to pinpoint the bottleneck.

Step 1: Inspect Storage and Inode Utilization on /tmp

Execute df from an administrative SSH terminal to inspect filesystem blocks and inode availability:

# Check physical disk capacity
df -h /tmp

# Check inode capacity
df -i /tmp

Diagnostic Evaluation

• If Use% in df -h /tmp is at 100% (or 99%), /tmp has run out of physical disk blocks due to runaway temporary query spills.
• If IUse% in df -i /tmp is at 100%, the partition has run out of inodes. Clean up abandoned session files:

# Safely inspect and purge expired PHP sessions older than 24 hours
find /tmp -type f -name "sess_*" -mtime +1 -delete

Step 2: Measure Temporary Table Spill Health in MySQL

Log into the MySQL CLI and inspect the global counters that track temporary table creation:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Example output:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 148209   |
| Created_tmp_files       | 1204     |
| Created_tmp_tables      | 389140   |
+-------------------------+----------+

Calculate the Disk Spill Ratio:

$$\text{Disk Spill Ratio} = \left(\frac{\text{Created_tmp_disk_tables}}{\text{Created_tmp_tables}}\right) \times 100$$

Using the values above:

$$\left(\frac{148209}{389140}\right) \times 100 \approx 38.08%$$

Evaluation Benchmark

In an optimized OLTP database, less than 10% to 15% of temporary tables should spill to disk. If your disk spill ratio exceeds 25%, either your in-memory buffers are severely undersized or your application queries lack proper covering indexes for GROUP BY and ORDER BY operations.

Step 3: Pinpoint the Exact Offending SQL Queries

Do not guess which queries are exhausting your temporary storage. Query performance_schema to identify the specific query digests generating the highest volume of on-disk temporary tables:

SELECT 
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    SUM_CREATED_TMP_TABLES AS total_tmp_tables,
    SUM_CREATED_TMP_DISK_TABLES AS total_disk_tmp_tables,
    ROUND((SUM_CREATED_TMP_DISK_TABLES / SUM_CREATED_TMP_TABLES) * 100, 2) AS disk_spill_pct,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_latency_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10\G

Common culprits in e-commerce applications (such as WooCommerce and Magento) include:

• Unindexed multi-attribute faceted product filters (wp_postmeta joins without composite indexes).
• Analytical queries using SELECT DISTINCT across serialized options tables.
• Window functions (ROW_NUMBER(), RANK()) evaluating multi-million-row ledger tables without index pushdown.

---

Permanent Remediation Strategies

Once the immediate incident is stabilized, implement the following architectural fixes to prevent recurrence.

1. Safely Sizing tmp_table_size and max_heap_table_size

To prevent small queries from unnecessarily spilling to disk, increase the in-memory thresholds. However, you must budget memory carefully to prevent the Linux kernel’s Out-Of-Memory (OOM) killer from terminating the mysqld process.

The Memory Budgeting Formula

Unlike the global innodb_buffer_pool_size, tmp_table_size is allocated per-thread as needed. The potential maximum thread memory consumption is calculated as:

$$\text{Max Thread RAM} = \text{max_connections} \times (\text{read_buffer_size} + \text{sort_buffer_size} + \text{tmp_table_size})$$

To configure safe limits, set both variables to matching values in your MySQL/MariaDB configuration file (/etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf):

[mysqld]
# Sizing in-memory temporary tables (matching pair)
tmp_table_size         = 64M
max_heap_table_size    = 64M

For MySQL 8.0 installations utilizing the TempTable engine:

# Cap on RAM consumed by TempTable engine before spilling to disk or MMAP
temptable_max_ram      = 1G
temptable_use_mmap     = ON

Apply runtime changes immediately without restarting the daemon:

SET GLOBAL tmp_table_size = 67108864;      -- 64MB
SET GLOBAL max_heap_table_size = 67108864; -- 64MB

2. Relocating tmpdir to High-Speed NVMe Storage

Leaving tmpdir pointing to a tiny 2 GB root or tmpfs partition /tmp is the leading cause of catastrophic Error 1114 failures. Relocate the database temporary scratch directory to dedicated NVMe storage.

Step-by-Step Migration Guide

1. Create a dedicated directory on your primary fast storage volume:

mkdir -p /var/lib/mysql-tmp
chown -R mysql:mysql /var/lib/mysql-tmp
chmod 1777 /var/lib/mysql-tmp

2. Update /etc/my.cnf under the [mysqld] section:

[mysqld]
tmpdir = /var/lib/mysql-tmp

3. Configure Mandatory Linux Security Modules:

For Ubuntu / Debian (AppArmor): Add the new path to /etc/apparmor.d/local/usr.sbin.mysqld:

# Allow MySQL to read, write, and lock within the new directory
/var/lib/mysql-tmp/ r,
/var/lib/mysql-tmp/** rwk,

Reload AppArmor:

sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

For RHEL / AlmaLinux / Rocky Linux (SELinux): Set the correct security context on the directory:

semanage fcontext -a -t mysqld_db_t "/var/lib/mysql-tmp(/.*)?"
restorecon -Rv /var/lib/mysql-tmp

4. Restart the MySQL/MariaDB service:

systemctl restart mysql || systemctl restart mariadb

5. Verify the active configuration:

SHOW GLOBAL VARIABLES LIKE 'tmpdir';

3. Reconfiguring InnoDB Temporary Tablespace Autoextension

If your database uses MySQL 5.7 or MySQL 8.0 and fails due to ibtmp1 saturation, check the current setting:

SHOW GLOBAL VARIABLES LIKE 'innodb_temp_data_file_path';

If it includes an explicit cap (such as max:2G or max:4G), increase the ceiling or remove the restriction to permit continuous autoextension:

[mysqld]
# Allow ibtmp1 to start at 12M and expand up to 64GB as required
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:64G

Operational Note: In MySQL 5.7 and early 8.0 releases, ibtmp1 does not shrink automatically after disk-spilling queries complete. Disk space is reclaimed only upon a service restart. Ensure your hosting storage volume has sufficient headroom to hold peak transient tablespaces.

---

Infrastructure Bottlenecks: When Virtualization Hits the Wall

While database configuration tuning resolves modest memory mismatches and temporary path constraints, high-concurrency enterprise workloads eventually hit the physical limitations of virtualized and shared hosting infrastructure.

In multi-tenant cloud environments or shared VPS nodes:

• Virtual disks share underlying SAN or NAS IOPS channels. When complex reporting queries generate hundreds of megabytes of temporary disk tables, I/O wait (wa) spikes across the entire node.
• Virtual disk quotas enforce strict root volume boundaries that cannot be dynamically expanded without service downtime.
• Noisy neighbors on the same physical hypervisor consume CPU cache and storage bus throughput, causing query execution times to expand and temporary tables to linger on disk.

For mission-critical e-commerce platforms, SaaS multi-tenant databases, and enterprise data warehousing, migrating to high-performance bare-metal infrastructure is the definitive solution. Deploying on Dedicated Servers provides:

• Dedicated NVMe Storage in Hardware RAID: Uncontended PCIe Gen4/Gen5 NVMe storage arrays offering sustained write throughput exceeding 7,000 MB/s, allowing massive temporary disk tables to serialize without query timeout errors.
• Dedicated Physical RAM: Compute nodes with 128 GB to 512 GB of unshared ECC DDR5 RAM, allowing database administrators to allocate 2 GB+ to temptable_max_ram and massive InnoDB buffer pools without risk of hypervisor memory ballooning.
• Kernel-Level Tuning: Full root control to tune Linux virtual memory management (vm.swappiness, vm.dirty_ratio, hugepages) and mount custom enterprise filesystems (XFS with noatime).

For enterprises serving regional audiences, choosing Dedicated Servers in Pakistan delivers sub-10ms latency over direct domestic routing exchanges, compliance with local data residency mandates, and resilient bare-metal hardware designed for high-concurrency database workloads.

---

Summary Checklist for MySQL & MariaDB Error 1114

Root Cause	Immediate Diagnostic Command	Permanent Solution
/tmp Partition Full	df -h /tmp shows 100%	Relocate tmpdir to /var/lib/mysql-tmp on a spacious NVMe drive.
Inode Exhaustion	df -i /tmp shows 100%	Purge orphaned sess_* files; adjust automated cron session garbage collection.
Memory Cap Mismatch	SHOW VARIABLES LIKE '%heap%';	Equalize tmp_table_size and max_heap_table_size to 64M–256M.
InnoDB ibtmp1 Cap	Check innodb_temp_data_file_path	Set ibtmp1:12M:autoextend:max:64G in my.cnf.
Inefficient Queries	Check events_statements_summary_by_digest	Add missing composite indexes for GROUP BY and ORDER BY operations.

---