MySQL ERROR 1021 occurs when the server runs out of disk space while writing data or temporary files. The server will pause operations and wait for space to become available. Fix it by freeing disk space, managing binary logs, redirecting the temp directory, or expanding storage.
MySQL ERROR 1021 (ER_DISK_FULL) is thrown when the MySQL server cannot write to disk because there is insufficient space available. This error typically occurs during write operations like INSERT, UPDATE, ALTER TABLE, or when MySQL creates temporary files for sorting and joining data. The error message usually includes a file path, such as "/tmp/#sql_xxx.MAI" or the MySQL data directory path, indicating where the server was attempting to write when it ran out of space. When this error occurs, the server will pause and wait for disk space to become available before retrying the operation. This is a blocking error that prevents the affected query from completing. The critical issue is that MySQL needs continuous disk space for data files, temporary tables during query execution, binary replication logs, InnoDB transaction logs, and sort operations. If any of these operations consume all available disk space, ERROR 1021 will occur.
First, identify which partition is full using the df command. This shows disk usage across all mounted filesystems and helps identify whether the problem is the main data partition, /tmp, or another location.
df -hLook for any partition showing 100% usage or very close to it (95%+). The output will show something like:
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 100G 99G 1.0G 99% /
/dev/sda2 50G 50G 0 100% /var/lib/mysql
tmpfs 16G 16G 0 100% /tmpIf multiple partitions are full, you'll need to free space on each one.
Once you know the partition is full, determine which MySQL tables or files are consuming the most space. Log into MySQL and query the information_schema to find the largest tables:
mysql -u root -pThen run:
SELECT table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
ROUND(data_length / 1024 / 1024, 2) AS 'Data (MB)',
ROUND(index_length / 1024 / 1024, 2) AS 'Index (MB)'
FROM information_schema.tables
WHERE table_schema != 'information_schema'
AND table_schema != 'mysql'
AND table_schema != 'performance_schema'
ORDER BY (data_length + index_length) DESC
LIMIT 20;This shows the top 20 tables consuming disk space. Identify tables that may contain old or unnecessary data that can be deleted.
Once you identify large tables, delete old records that are no longer needed. This is the safest way to reclaim space without losing critical data.
For example, if you have logs or archived records:
-- Delete records older than 30 days
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Or clear specific archive tables
TRUNCATE TABLE archived_transactions;
-- After deleting, optimize the table to reclaim space
OPTIMIZE TABLE logs;The OPTIMIZE TABLE command rebuilds the table and removes fragmentation, reclaiming unused space from deleted rows. For InnoDB tables, this temporarily requires additional disk space, so ensure you have some free space first.
If MySQL replication is enabled, binary logs can consume significant disk space. Check the current binary logs and purge old ones:
-- Show current binary logs
SHOW BINARY LOGS;
-- Purge logs older than 7 days
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
-- Or purge before a specific date
PURGE BINARY LOGS BEFORE '2024-01-15';To automatically purge binary logs, add this to your MySQL configuration file (/etc/mysql/my.cnf):
[mysqld]
binlog_expire_logs_seconds = 604800 # 7 days in seconds
# Or use the older directive (deprecated in MySQL 8.0.3)
expire_logs_days = 7Restart MySQL to apply the changes:
sudo systemctl restart mysqlMySQL uses the /tmp directory (or a custom tmpdir) for temporary tables during sort operations and complex queries. If /tmp is full, clear old temporary files:
# Show temp directory usage
du -sh /tmp
# Remove MySQL temporary files (if MySQL is stopped)
sudo rm -rf /tmp/#sql_*
# Or use tmpwatch to safely remove old files (older than 24 hours)
sudo tmpwatch 24 /tmpIf you need to change the temporary directory to a larger partition, update the MySQL configuration:
# Create a new tmpdir with adequate space
sudo mkdir -p /mnt/bigssd/mysql_tmp
sudo chown mysql:mysql /mnt/bigssd/mysql_tmp
sudo chmod 750 /mnt/bigssd/mysql_tmpThen update /etc/mysql/my.cnf:
[mysqld]
tmpdir = /mnt/bigssd/mysql_tmpRestart MySQL:
sudo systemctl restart mysqlIf freeing up space doesn't resolve the issue, or if the database is legitimately large, you need to expand available disk space.
For cloud environments (AWS, Azure, Google Cloud), you can expand the disk volume:
# For AWS EBS, expand the volume in the console, then resize the filesystem
sudo resize2fs /dev/xvda1
# For Azure managed disks, expand in the portal, then resize
sudo parted /dev/sda
# Type: resizepart
# Number: 1
# End: 100%
sudo resize2fs /dev/sda1For physical servers, you can add additional storage or move the MySQL data directory to a larger partition:
# Stop MySQL
sudo systemctl stop mysql
# Move data directory
sudo mv /var/lib/mysql /mnt/large_disk/mysql
# Update MySQL config
# Edit /etc/mysql/my.cnf and change:
# datadir = /mnt/large_disk/mysql
# Fix permissions
sudo chown -R mysql:mysql /mnt/large_disk/mysql
# Restart MySQL
sudo systemctl start mysqlSet up monitoring to catch disk space issues before ERROR 1021 occurs.
Create a cron job to alert when disk usage exceeds 80%:
# Add to crontab (crontab -e)
0 */4 * * * df -h | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output; do
usage=$(echo $output | awk '{ print $1}' | cut -d'%' -f1)
partition=$(echo $output | awk '{ print $2 }')
if [ $usage -ge 80 ]; then
echo "WARNING: $partition is $usage% full" | mail -s "Disk Alert" [email protected]
fi
doneAlternatively, use cloud monitoring (CloudWatch for AWS, Azure Monitor, etc.) to set up automatic alerts.
Keep at least 20% free disk space at all times to allow MySQL sufficient room for temporary operations, index updates, and growth.
InnoDB-specific considerations: If using InnoDB, the tablespace can become fragmented. While you cannot shrink the InnoDB data files without full dump/restore, you can add new data files and InnoDB will balance data across them:
ALTER TABLESPACE innodb_system ADD DATAFILE '/mnt/large_disk/mysql_data2.ibd' FILE_BLOCK_SIZE=16K;Temporary table locations: Some queries generate temporary tables in memory, but complex sorts fall back to disk. Increase tmp_table_size and max_heap_table_size to keep more temporary tables in memory:
SET GLOBAL tmp_table_size = 536870912; -- 512MB
SET GLOBAL max_heap_table_size = 536870912; -- 512MBRunning queries during low disk: If ERROR 1021 occurs while a query is running, MySQL will wait indefinitely. To resume, simply free disk space (delete files, purge logs, etc.), and the query will automatically continue after a few seconds.
Multi-partition setups: If you have separate partitions for data and logs (/var, /tmp, /var/lib/mysql), check all of them. A full /var partition can prevent logging, which can also trigger ERROR 1021 indirectly. The most common culprit is /tmp during large ALTER TABLE operations or complex sorts.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL