🚨 MySQL Error: 'Got error 28 from storage engine' — Cause, Fix, and Troubleshooting Guide

Overview

Got error 28 from storage engine means the operating system returned ENOSFC / errno 28 — “No space left on device” — to MySQL:

ERROR 1030 (HY000): Got error 28 from storage engine

Error number 28 is a raw OS errno, not a MySQL logic error. Either the data directory volume (/var/lib/mysql) is full, or — very commonly — the temporary directory (tmpdir) filled up while MySQL wrote an on-disk temp table for a big sort/join. It usually appears under load on SELECTs with large GROUP BY/ORDER BY, or on any write once the data volume is exhausted.

Symptoms

• Queries and writes fail with “error 28 from storage engine” or “No space left on device”.
• Large SELECTs (sorts, joins, GROUP BY) fail while small ones succeed — a full tmpdir.
• The error log shows write failures; df shows a volume at 100%.
• Replication stops because the relay log or binary log volume filled.

Common Root Causes

1. Data volume full

/var/lib/mysql (ibdata, per-table .ibd, binlogs) has consumed the disk.

df -h /var/lib/mysql

Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme1n1    100G  100G    0G 100% /var/lib/mysql

2. tmpdir full from on-disk temp tables

A big sort/join spills to tmpdir (often /tmp, a small root partition) and exhausts it mid-query.

df -h /tmp

3. Binary/relay logs never purged

Long binlog_expire_logs_seconds or a stuck replica keeps binlogs forever until the disk fills.

4. Runaway growth — no monitoring

A large import, an unbounded log table, or a missing PURGE job silently ate the space.

How to diagnose

Find what filled up:

df -h                       # which mount is at 100%
du -sh /var/lib/mysql/* | sort -h | tail    # biggest consumers

Check MySQL’s tmpdir and binlog settings:

SHOW VARIABLES LIKE 'tmpdir';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW BINARY LOGS;

Inspect the error log for the first “No space left” line to time the onset:

journalctl -u mysql --no-pager | grep -i "no space left" | head

Fixes

Free space immediately. Purge old binary logs once you confirm replicas have consumed them:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

Set an expiry so they self-clean:

[mysqld]
binlog_expire_logs_seconds = 259200   # 3 days

Move tmpdir to a larger volume if temp tables are the cause:

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

Reclaim space from a bloated InnoDB table (rebuilds and shrinks the .ibd):

OPTIMIZE TABLE big_log_table;

Then grow the volume (cloud disk resize + resize2fs/xfs_growfs) so there is durable headroom. For a full data disk where MySQL will not start, expand the volume first, then start MySQL.

What to watch out for

• Clearing space by deleting files under /var/lib/mysql by hand corrupts InnoDB — only use PURGE BINARY LOGS, DROP/OPTIMIZE, or grow the disk.
• tmpdir on a small root partition is a classic trap; give it real space or point it at the data volume.
• OPTIMIZE TABLE/ALTER needs free space equal to the table size to rebuild — do not run it on a nearly full disk.
• Reducing tmp_table_size/max_heap_table_size sends more temp tables to disk; tune queries to avoid huge sorts rather than just enlarging tmpdir.
• Alert at ~80% disk on the data and tmp volumes so error 28 never surprises you.

Related

• ERROR 1114: The table is full — a related capacity ceiling (tablespace/temp-table limits).
• ERROR 1194: Table marked as crashed — a full disk mid-write is a leading cause of MyISAM corruption.
• ERROR 1040: Too many connections — another resource-exhaustion failure to distinguish under load.