Comprehensive Analysis and Solutions for MySQL Errcode 28: No Space Left on Device

Error Phenomenon and Initial Diagnosis

During MySQL database operations, users may encounter the following error message:

Query3 failed: Error writing file '/tmp/MY1fnqpm' (Errcode: 28) ... INSERT MailList...

This error indicates that MySQL encountered a system-level obstacle when attempting to write temporary files. From error code 28, it can be preliminarily determined that this is likely related to storage space issues.

Deep Analysis of Error Code

To accurately understand the meaning of Errcode 28, you can use MySQL's provided perror diagnostic tool. Execute the following command in Linux/Unix systems:

$ perror 28
OS error code  28:  No space left on device

This output clearly identifies the nature of the problem: there is not enough available space on the target device. Error code 28 is an operating system-level error number that remains consistent across different Unix-like systems, all indicating exhausted storage space.

Technical Principles Deep Dive

When MySQL executes complex queries, particularly scenarios involving large dataset sorting, grouping, or temporary table operations, it creates temporary files in the file system. These temporary files are typically stored in the system temporary directory, which by default is the /tmp directory.

When MySQL needs to process data exceeding memory capacity, it employs a disk-based temporary table strategy. Here is a code example simulating temporary table creation:

CREATE TEMPORARY TABLE temp_user_stats (
    user_id INT,
    login_count INT,
    last_login TIMESTAMP
) ENGINE=MEMORY;

When the memory engine cannot accommodate the data, MySQL automatically converts to disk storage:

-- When data volume exceeds tmp_table_size or max_heap_table_size
-- MySQL automatically converts temporary tables to MyISAM or InnoDB disk tables
SET SESSION tmp_table_size = 16*1024*1024;  -- 16MB
SET SESSION max_heap_table_size = 16*1024*1024;

Problem Diagnosis and Verification Methods

To confirm disk space status, use system commands for detailed checking:

Check the space usage of the entire file system:

df -h

Specifically check the space status of the partition where the /tmp directory is located:

df -h /tmp

View the actual usage of MySQL temporary files:

ls -la /tmp/MY* | wc -l
du -sh /tmp

Solutions and Optimization Strategies

Immediate Mitigation Measures

Cleaning unnecessary temporary files can quickly free up space:

# Clean MySQL temporary files (ensure MySQL service is stopped)
rm -f /tmp/MY*

# Clean system temporary files
find /tmp -type f -name \"*\" -mtime +1 -delete

Configuration Optimization Solutions

Modify the MySQL configuration file to point the temporary directory to a partition with more space:

# Add in my.cnf or my.ini
tmpdir = /var/tmp

# Or use a custom directory with sufficient space
tmpdir = /home/mysql/tmp

Adjust temporary table related parameters:

# Increase memory temporary table size
tmp_table_size = 64M
max_heap_table_size = 64M

Preventive Monitoring Script

The following Python script can periodically monitor disk space usage:

import shutil
import logging

def check_disk_usage(path='/tmp', threshold=90):
    """
    Check disk usage for specified path
    """
    total, used, free = shutil.disk_usage(path)
    usage_percent = (used / total) * 100
    
    logging.info(f"Path {path} usage: {usage_percent:.1f}%")
    
    if usage_percent > threshold:
        logging.warning(f"Warning: {path} usage exceeds {threshold}%")
        return False
    return True

# Monitor MySQL temporary directory
if not check_disk_usage('/tmp', 80):
    print("Immediate temporary file cleanup or configuration adjustment required")

Advanced Troubleshooting Techniques

Identifying Space Consumption Sources

Use the following command to find the files occupying the most space:

find /tmp -type f -exec du -h {} + | sort -rh | head -10

MySQL Internal Diagnostics

Query MySQL's current temporary file usage status:

SHOW STATUS LIKE 'Created_tmp%';

-- Output example:
-- Created_tmp_disk_tables: Number of temporary tables created on disk
-- Created_tmp_files: Number of temporary files created
-- Created_tmp_tables: Total number of temporary tables created

Best Practice Recommendations

To prevent recurrence of Errcode 28 errors, the following preventive measures are recommended:

1. Regular Maintenance Schedule

# Set up scheduled task to clean temporary files
0 2 * * * find /tmp -type f -mtime +1 -delete

2. Monitoring and Alerting System

# Use monitoring tools to set disk space alerts
# Send alerts when /tmp partition usage exceeds 85%

3. Query Optimization

-- Avoid unnecessary sorting in queries
-- Use appropriate indexes to reduce temporary table creation
-- Process large data operations in batches

Conclusion

The MySQL Errcode 28 error is essentially an operating system-level storage space insufficiency problem. Through systematic diagnostic methods, reasonable configuration optimization, and preventive monitoring, such problems can be effectively resolved and avoided. The key lies in understanding MySQL's temporary file working mechanism and establishing comprehensive disk space management strategies.