How to Fix MySQL Error 1021: Disk Full

ERROR 1021 (HY000): Disk full (/tmp/#sql_xxxx.MAI); waiting for someone to free some space... (errno: 28 "No space left on device") is raised when MySQL cannot complete a write operation because the underlying filesystem has no available space. The error symbol is ER_DISK_FULL.

Impact

When disk space is exhausted, MySQL cannot write data, update indexes, or flush buffers. Any INSERT, UPDATE, DELETE, or DDL statement that requires writing to disk will fail with this error. For InnoDB tables, the current transaction is rolled back; for MyISAM, the partial write may leave the table in a corrupt state.

In practice, developers most often encounter this during large bulk loads, ALTER TABLE operations (which create temporary files), or when binary logs or slow query logs grow unbounded. Application-level ORMs such as ActiveRecord, SQLAlchemy, or Hibernate will surface this as a generic database exception or connection error, so always check the MySQL error log (/var/log/mysql/error.log) or the application server's stderr to find the underlying 1021 code.

Common Causes

Temporary files from large queries or ALTER TABLE. MySQL writes sort buffers and intermediate result sets to the tmpdir directory (often /tmp). A large ORDER BY, GROUP BY, or an ALTER TABLE that rebuilds the table can produce temporary files that fill the filesystem.
InnoDB tablespace growth. The shared tablespace (ibdata1) or per-table .ibd files grow as rows are inserted. When innodb_file_per_table=ON (the default since MySQL 5.6), each table's data file grows but does not shrink after large deletes without a table rebuild.
Binary log accumulation. Binary logs (binlog.XXXXXX) accumulate indefinitely unless expire_logs_days (MySQL 5.x) or binlog_expire_logs_seconds (MySQL 8.0+) is set, or old logs are not being purged by a replica or backup process.
General, slow query, or error log growth. If verbose logging is enabled without log rotation, these files can grow to tens of gigabytes on busy servers.
Insufficient disk provisioned for workload growth. Data volumes simply outgrow the allocated disk, especially in cloud environments with fixed volume sizes.
Undo log / undo tablespace expansion. Long-running transactions (including abandoned ones) cause the InnoDB undo log to grow, potentially filling the disk.

Troubleshooting and Resolution Steps

Confirm the disk is full and identify which filesystem.

df -h
# Look for a filesystem at 100% use, typically the one containing /var/lib/mysql or /tmp

Find the largest files under the MySQL data directory.

du -sh /var/lib/mysql/* | sort -rh | head -20
du -sh /tmp/* | sort -rh | head -20

Check and purge binary logs.

-- List binary logs and their sizes
SHOW BINARY LOGS;

-- Purge logs older than 7 days (adjust as needed)
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Or purge up to a specific log file
PURGE BINARY LOGS TO 'binlog.000042';

In MySQL 8.0, set a retention policy so logs are purged automatically:

SET PERSIST binlog_expire_logs_seconds = 604800;  -- 7 days

Reclaim space from InnoDB tables with large amounts of deleted rows.

-- Check how much space could be reclaimed
SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
       ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.tables
WHERE data_free > 0
ORDER BY data_free DESC
LIMIT 20;

-- Reclaim space (rebuilds the table; acquires metadata lock)
OPTIMIZE TABLE your_table;
-- Or equivalently:
ALTER TABLE your_table ENGINE=InnoDB;

Identify and truncate or rotate oversized log files.

# MySQL error log
ls -lh /var/log/mysql/

# Rotate using logrotate or truncate safely (after flushing)
mysql -u root -p -e "FLUSH LOGS;"
> /var/log/mysql/error.log   # truncate — do NOT delete while mysqld holds it open

Check for long-running transactions holding undo log space.
```
-- In MySQL 5.7+
SELECT trx_id, trx_started, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds
FROM information_schema.innodb_trx
ORDER BY trx_started;
```
Kill any abandoned transactions, then verify undo space is released. In MySQL 8.0 with dedicated undo tablespaces, you can also ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE; to trigger a purge cycle.
Expand disk capacity or move the data directory.

If the above steps do not free enough space, resize the volume (e.g., in AWS: resize the EBS volume then grow the filesystem with resize2fs or xfs_growfs), or move the MySQL data directory to a larger volume and update datadir in my.cnf.

Additional Information

The SQLSTATE for this error is HY000 (general error), so it cannot be distinguished from other general errors by SQLSTATE alone — always parse the error number or message text.
errno: 28 is the Linux kernel code for "No space left on device" and appears alongside the MySQL error message in the server log.
On Windows, the equivalent OS error is ERROR_DISK_FULL (error code 112).
If tmpdir is on a separate, smaller filesystem from /var/lib/mysql, disk-full errors can occur even when the data directory has free space. Check both with df -h.
MySQL will retry writes to tmpdir for up to wait_timeout seconds while logging "waiting for someone to free some space", giving you a window to free space without restarting the server.
Related errors: ER_OUTOFMEMORY (1037) for memory exhaustion; ER_CANT_CREATE_FILE (1004) when MySQL cannot create a file for other reasons.

Frequently Asked Questions

Why does MySQL show "waiting for someone to free some space" instead of failing immediately? MySQL retries disk writes for a configurable period before giving up. This behavior gives operators a chance to free space (delete old logs, clear /tmp) without the server crashing. If space is freed while the retry loop is running, the operation completes normally. The server logs a message every 10 seconds during the wait.

Can I safely delete files in /var/lib/mysql to free space? No — never delete .ibd, ibdata1, ib_logfile*, or .frm files while the server is running; doing so will corrupt the database. You can safely remove binary log files only via PURGE BINARY LOGS inside MySQL, not by deleting them from the shell.

My table has billions of deleted rows but the .ibd file is still huge — why? InnoDB marks deleted rows as "free" internally but does not return space to the filesystem until the table is rebuilt. Run OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB to compact the file. On very large tables, consider pt-online-schema-change (from Percona Toolkit) to avoid locking the table during the rebuild.

After freeing space, do I need to restart MySQL? Usually not. Once disk space is available, in-progress operations that were in the retry loop will complete automatically. If MySQL has already returned the error to the client and the transaction was rolled back, simply reissue the query. A restart is only necessary if the server entered a crashed state (rare with InnoDB).