MySQL error 1030 with “Got error 28 from storage engine” means the operating system returned errno 28 (ENOSPC): the filesystem MySQL is trying to write to has no usable space. The storage engine is usually InnoDB, and the full location may be the operating system’s temporary directory, the InnoDB temporary tablespace file ibtmp1, the data directory, or a log filesystem—not necessarily the directory containing your tables.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
Recover in this order: identify the exact path from MySQL’s settings and error log, confirm which mount is full, free or relocate space safely, then control the query or temporary-tablespace growth that caused the incident. Do not delete files from the data directory or remove ibtmp1 while the server is running.
As an Amazon Associate I earn from qualifying purchases.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
What the message means
The operating system error is the important part:
| Message or code | Layer | Meaning |
|---|---|---|
28 / ENOSPC |
Operating system | A write could not be completed because the target filesystem had no space available. The same code can appear when an ordinary process writes to a full directory such as /tmp. |
ERROR 1030 (HY000) |
MySQL server | MySQL’s wrapper around a storage-engine failure. |
| “Got error 28 from storage engine” | InnoDB or another engine | The engine attempted to create or extend a file and the operating system rejected the write. |
This is a capacity problem, not a malformed SQL statement. It can occur during an index build, sort, join, import, dump, table repair, optimization, or an ordinary insert that needs to extend a tablespace. A command can fail even when the database partition appears to have free space if its temporary directory is on a different, full mount.
Before changing anything: protect the server
- Stop repeatedly retrying the failing job. Every retry may create another temporary file or extend a temporary tablespace.
- Do not run
rmagainst the MySQL data directory, binary-log directory, oribtmp1by pattern. Removing an active database file can corrupt the instance. - Keep a small amount of emergency space available for the server to write an error log and shut down cleanly. If the host is completely full, use an approved operational procedure to remove an identified, non-database artifact first.
- Before purging binary or relay logs, check replication, backup, and point-in-time-recovery requirements. A log that looks old may still be needed by a replica or backup process.
Find the filesystem that is actually full
1. Ask MySQL which paths it uses
Run these statements in the affected instance. They reveal the data directory, temporary directory, InnoDB home, error-log location, and temporary-tablespace definition.
#1 Best Overall
- THE SSD ALL-STAR: The latest 870 EVO has indisputable performance, reliability and compatibility built upon Samsung's pioneering technology.Computer Platform:PC.Encryption : Class 0 (AES 256) TCG/Opal v2.0, MS eDrive (IEEE1667), Environmental Specs - Shock : 1,500 G & 0.5 ms (Half sine).
- EXCELLENCE IN PERFORMANCE: Enjoy professional level SSD performance with 870 EVO, which maximizes the SATA interface limit to 560/530 MB/s sequential speeds, Accelerates write speeds and maintains long term high performance with a larger variable buffer
- INDUSTRY DEFINING RELIABILITY: Meet the demands of every task from everyday computing to 8K video processing, with up to 2,400 TBW
- MORE COMPATIBLE THAN EVER: 870 EVO has been compatibility tested for major host systems and applications, including chipsets, motherboards, NAS, and video recording devices. Interface- SATA 6GB/s, compatible with SATA 3GB/s and SATA 1.5GB/s interfaces
SHOW VARIABLES WHERE Variable_name IN ('datadir','tmpdir','innodb_data_home_dir','innodb_temp_data_file_path','log_error');
SELECT @@datadir, @@tmpdir, @@innodb_data_home_dir, @@innodb_temp_data_file_path;
SELECT @@innodb_temp_data_file_path;Record the absolute path for each variable. A relative tmpdir or tablespace filename is resolved according to MySQL’s startup and data-directory rules; do not assume it is under the directory you expect.
2. Check each mount, not just the data directory
On Linux, substitute the paths returned above:
df -h /tmp /var/lib/mysql
df -h /path/from/@@tmpdir
df -h /path/from/@@datadir
df -h /path/containing/ibtmp1Use the real paths rather than copying /var/lib/mysql if your installation stores data elsewhere. If a path is a symlink, resolve it before interpreting the result. The mount containing the failing path is the one that needs relief.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteA useful operational rule is to investigate well before a temporary filesystem reaches full utilization; one troubleshooting guide uses roughly 80% as a warning point, but the correct threshold depends on workload, table sizes, and how much headroom large operations require.
3. Locate large directories and files
After identifying the full mount, measure its top-level consumers without crossing onto other filesystems:
du -xhd1 /tmp | sort -h
du -xhd1 /var/lib/mysql | sort -h
ls -lh /var/lib/mysql/ibtmp1Run du as a user that can read the directories, or use the system’s approved privilege mechanism. The largest directory is a lead, not permission to delete its contents. Confirm ownership, age, and whether a process still has a file open before removing anything.
Rank #2
- Upgrade your laptop or desktop computer and feel the difference with super-fast OS boot times and application loads
- Exceptional performance offering up to 535MB/s seq. Read and 500MB/s seq. Write speeds
- Superior performance as compared to traditional hard drives (HDD)
- Ultra-low power consumption
- Backwards compatible with SATA II 3GB/sec
4. Read the MySQL error log
Get the configured path with:
SHOW VARIABLES LIKE 'log_error';Inspect entries around the failure. They often identify the operation and the path that could not be created or extended. A message mentioning /tmp, a temporary file, or a particular tablespace narrows the remedy considerably.
5. Measure the global InnoDB temporary tablespace
By default, InnoDB uses a global temporary tablespace whose main file is normally named ibtmp1. You can inspect its definition and the metadata MySQL reports:
SELECT @@innodb_temp_data_file_path;
SELECT TABLESPACE_NAME, FILE_NAME, TOTAL_EXTENTS, FREE_EXTENTS,
INITIAL_SIZE, MAXIMUM_SIZE
FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME = 'innodb_temporary';Also inspect the file on disk after confirming the data-directory path. The SQL view and filesystem listing may show different aspects of allocation, so use both when diagnosing an unexpectedly large file.
Where the space usually went
| Location | What grows there | Operations that commonly trigger growth | Safe first response |
|---|---|---|---|
tmpdir |
Per-operation temporary files and, in some versions and cases, temporary table files. | Large sorts and joins, online DDL, REPAIR TABLE, OPTIMIZE TABLE, and LOAD DATA INFILE. |
Identify and remove confirmed stale artifacts, or move tmpdir to a larger filesystem. |
Global InnoDB temporary tablespace (ibtmp1) |
On-disk internal temporary tables. The file auto-extends and retains its enlarged size for reuse. | Queries that spill large internal temporary tables, including long-running sorts, joins, and schema changes. | End or redesign the workload, then restart during a maintenance window to recreate the file; configure a version-appropriate cap if suitable. |
| Data directory | Table data, indexes, per-table tablespaces, and database metadata. | Normal data growth, index creation, imports, and table copies. | Extend or redesign storage. Never remove database files manually. |
| Binary or relay-log filesystem | Replication and recovery logs retained until their configured purge process removes them. | Long retention, a stopped replica, or a backup policy that prevents purging. | Verify recovery and replica needs, then use MySQL’s log-purge procedure rather than deleting files from the directory. |
/tmp leftovers |
Old SQL dumps, compressed backups, and abandoned temporary files. | Interrupted exports, backup scripts, or manual copies left on the host. | Confirm that a file is not in use and is covered by a backup; delete or move only the identified artifact. |
Fast ways to create breathing room
Remove confirmed dump and temporary-file leftovers
Use the directory listing and file timestamps to identify artifacts that belong to completed jobs. Check with the job owner or backup system before deleting a dump. If a process still has a deleted file open, space may not return until that process closes the descriptor; restarting a production service solely for this reason requires a change window.
Purge binary logs through MySQL
First review what exists and why it is retained:
SHOW BINARY LOGS;If replication and recovery checks show that older logs are no longer required, purge to a specific log boundary, for example:
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Rank #3
- SPEED UP COMPUTER: The fanxiang 1TB SSD 2.5 Inch SATA SSD achieves blazing read and write speeds of 520MB/s, facilitating rapid file and data transfers
- UPGRADE YOUR COMPUTER: Compared to HDDs, the 1TB SATA SSD boots up at least 50% faster, enabling instant productivity or gaming sessions
- LONG-LASTING DURABILITY: The 2.5 SATA SSD 1TB incorporates 3D NAND TLC chips, offering a longer lifespan in writes compared to QLC, ensuring a more reliable data storage solution
- EXTENSIVE COMPATIBILITY: The S101 1TB SATA III SSD is compatible with desktops, laptops, all-in-one PCs, supporting various operating systems like Windows, Linux, and Mac OS, meeting the needs of diverse devices
- 3-Year Service: Fanxiang S101 1TB SSD solid state drive provides 3 years after-sales service and lifetime technical support. If you have any questions, please contact us and we will sincerely and professionally solve the problem for you
PURGE BINARY LOGS TO 'binlog.000123';Use the actual filename and your organization’s retention procedure. Do not delete binary-log files with a shell command. Relay logs on a replica have separate safety considerations and should be managed by the replication configuration and replica state.
Reduce or stop the operation consuming temporary space
Cancel a runaway report, import, or schema change when doing so is safer than allowing the mount to reach zero. A failed operation may leave a temporary file that can be removed only after the server or operating-system process has released it. Once space is available, run the job in smaller batches or at a less busy time rather than immediately repeating the same workload.
Understand and recover ibtmp1
The global InnoDB temporary tablespace is normally a single auto-extending file, initially a little over 12 MB under the default definition. Internal temporary tables can make it grow substantially. Dropping those temporary tables returns blocks for reuse inside the tablespace, but the file itself keeps the larger size; ordinary query completion therefore does not shrink the file on disk.
Reclaim the file with a controlled restart
- Confirm that the growth is in the global temporary tablespace rather than a permanent table or log.
- Stop the workload that is generating large temporary tables and ensure you have enough free space for a clean shutdown.
- Schedule a normal MySQL restart. During startup, InnoDB removes and recreates the global temporary tablespace, reclaiming the old file’s disk allocation.
- After startup, verify the new file size, repeat the
INFORMATION_SCHEMA.FILESquery, and check the error log for startup errors. - Run the workload with monitoring and avoid launching several large temporary-table operations concurrently.
Do not delete ibtmp1 while mysqld is running. A restart is also not a substitute for fixing a query that immediately rebuilds a huge temporary tablespace.
Recommended Free Tools
Set a maximum size where the version supports it
The MySQL 5.7 Reference Manual documents the innodb_temp_data_file_path setting and gives this form as an example:
Rank #4
- THE SSD ALL-STAR: The latest 870 EVO has indisputable performance, reliability and compatibility built upon Samsung's pioneering technology. S.M.A.R.T. Support: Yes.Specific uses: Business, personal
- EXCELLENCE IN PERFORMANCE: Enjoy professional level SSD performance which maximizes the SATA interface limit to 560 530 MB/s sequential speeds,* accelerates write speeds and maintains long term high performance with a larger variable buffer
- INDUSTRY-DEFINING RELIABILITY: Meet the demands of every task — from everyday computing to 8K video processing, with up to 600 TBW** under a 5-year limited warranty***
- MORE COMPATIBLE THAN EVER: The 870 EVO has been compatibility tested**** for major host systems and applications, including chipsets, motherboards, NAS, and video recording devices
- UPGRADE WITH EASE: Using the 870 EVO SSD is as simple as plugging it into the standard 2.5 inch SATA form factor on your desktop PC or laptop; The renewed migration software takes care of the rest
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500MThis configuration requires a restart. Choose a limit from measured workload requirements, not from the example value. In the 5.7 behavior documented for this setting, once the file reaches its maximum, operations that need more temporary space fail with a “table is full” style error. That is preferable to exhausting the host only when you have planned for, monitored, and handled the resulting query failures.
The supplied MySQL 8.0 manual excerpt confirms the temporary-tablespace setting but does not establish the same cap behavior in that excerpt. Check the documentation for your exact 8.0 release before applying a production limit. A path change for ibtmp1 is also separate from tmpdir and has version-specific relative-path rules; do not infer its syntax from a tmpdir change.
Move tmpdir to a larger filesystem
Moving the operating-system temporary directory can solve failures caused by sort files, join files, online-DDL work files, or other temporary artifacts. It does not move the global InnoDB temporary tablespace.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →- Choose a filesystem with enough sustained free space and appropriate performance. Confirm its mount will be present before MySQL starts.
- Create a dedicated directory and assign it to the account that runs MySQL. For a conventional Linux service account, an administrator might use:
sudo install -d -o mysql -g mysql -m 1770 /mnt/mysql-tmpUse your actual service account and a permission policy approved for the host.
- Add or update the startup option in the MySQL server configuration:
[mysqld]
tmpdir=/mnt/mysql-tmp- Restart MySQL during a maintenance window, then run
SHOW VARIABLES LIKE 'tmpdir';and verify that the directory is writable by the server. - Repeat the failing operation while watching both the new temporary mount and the data filesystem. If
ibtmp1is the consumer, configure or relocate that tablespace separately.
In MySQL 5.7, compressed temporary tables are stored in tmpdir as file-per-table tablespaces, which is another reason a larger temporary filesystem can matter for that version. The exact mix of files depends on the operation and server version.
Best Value
- Upgrade your laptop or desktop computer and feel the difference with super-fast OS boot times and application loads
- Exceptional performance offering up to 550MB/s seq. Read and 500MB/s seq. Write speeds
- Superior performance as compared to traditional hard drives (HDD)
- Ultra-low power consumption
- Backwards compatible with SATA II 3GB/sec
Fix the workload that caused the growth
Freeing space restores service but does not prevent recurrence. Review the statement and operation named in the error log:
- Large sorts or joins: reduce the result set, add an appropriate index, or process the report in bounded batches. Avoid running several resource-heavy reports at once.
ALTER TABLEor online DDL: schedule the change when the temporary filesystem has room for a second copy or large sort intermediates. Check the expected table and index sizes before starting.OPTIMIZE TABLEorREPAIR TABLE: treat the command as a space-intensive maintenance job, not a routine low-cost action. Run one table at a time and monitor the target mount.LOAD DATA INFILEor imports: split the input, remove unnecessary duplicate staging copies, and ensure the destination and temporary paths have independent headroom.- Long-running queries: identify statements that create large on-disk temporary tables. They can regrow
ibtmp1after every restart unless the execution plan or workload is changed.
Server-wide temporary-table limits can be useful as part of capacity planning, but lowering a limit alone may simply cause more operations to spill to disk or fail earlier. Test changes against representative queries and monitor both memory and disk usage.
Free tools Windows power users keep installed
One-click scans. No signup required.
Version and configuration distinctions that prevent common mistakes
| Situation | Correct interpretation |
|---|---|
Changing tmpdir |
Redirects MySQL’s operating-system temporary files after the configuration is applied and the server is restarted. It does not relocate ibtmp1. |
Changing innodb_temp_data_file_path |
Controls the global InnoDB temporary tablespace definition. A restart is required, and the syntax and documented behavior must match the exact MySQL version. |
Restarting after ibtmp1 growth |
Recreates the global temporary tablespace and can reclaim its enlarged file, provided the full space was actually in that file and the instance is restarted normally. |
| MySQL 5.7 cap example | The 5.7 manual documents ibtmp1:12M:autoextend:max:500M and describes “table is full” failures at the limit. |
| MySQL 8.0 | The available 8.0 excerpt confirms the temporary-tablespace setting but does not establish cap behavior. Verify the manual for the installed 8.0 release before relying on a limit. |
When the obvious fix does not work
df shows free space, but the statement still returns error 28
- Check every path returned by
@@tmpdir,@@datadir, and@@innodb_temp_data_file_path; you may have checked the wrong mount. - Read the error log for the exact failed pathname. A temporary directory, data directory, and log directory can be separate filesystems.
- Measure
ibtmp1directly. A large retained file can consume the mount even when current temporary tables have been dropped. - Confirm that the running server is the instance whose variables you queried. Multiple MySQL instances can use different data and temporary directories.
Restarting did not reclaim space
Verify that the restart reached the intended instance and that the file you measured belongs to it. If another directory or log mount is full, recreating ibtmp1 will not change that mount. Check the post-start error log and rerun the path and size queries.
Moving tmpdir changed nothing
The failing allocation may be in ibtmp1 or the data directory. Compare the error-log pathname with the configured paths. A tmpdir change cannot move an InnoDB temporary tablespace.
The server now reports “table is full” after a cap was added
That is the expected protection when the configured temporary-tablespace maximum is reached in the documented MySQL 5.7 behavior. Remove the workload pressure, increase the limit only after capacity planning, or redesign the query so it needs less temporary space. Do not respond by deleting ibtmp1 while the server is online.
The new temporary directory produces permission errors
Check the directory owner, group, mode, mount availability at boot, and any operating-system security policy that restricts the service. Confirm the effective tmpdir value after restart and test file creation as the MySQL service account using your normal administrative procedure.
Post-incident checklist
- Identify and record the full filesystem and the path named in the error log.
- Document whether the consumer was
tmpdir,ibtmp1, permanent data, logs, or an abandoned dump. - Remove only verified, nonessential files; use MySQL commands for binary-log purging.
- Record any restart, configuration change, or temporary-filesystem move in the change log.
- Review the statement or maintenance job that created the temporary demand.
- Set monitoring on each relevant mount and on
ibtmp1growth, with alerts that leave enough room for cleanup. - Test the recovery procedure and confirm that backups and replication still meet their retention requirements.
Once the full path is known, the remedy is usually straightforward: restore headroom, reclaim or relocate the correct temporary area, and change the operation that consumed it. Treating every error 28 as a generic “database is full” event is what causes unsafe deletions and repeated outages.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
