Analysis and Solutions for MySQL Temporary File Write Error: Understanding 'Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2)'

Error Phenomenon and Context

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

PreparedStatementCallback; SQL [ /*long sql statement here*/ ]; 
Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2); 
nested exception is java.sql.SQLException: Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2)

This error typically manifests as application failure to access the database, while the MySQL service itself may remain operational and queryable via command-line tools. The filename #sql_3c6_0.MYI in the error message is an internally generated temporary file name by MySQL, where the # character is a special prefix used by MySQL to identify temporary files, making manual creation of such files typically impossible for users.

Deep Analysis of Error Causes

Based on technical community consensus, this error primarily stems from MySQL's inability to create or write temporary files in the /tmp directory. The following are key contributing factors:

Insufficient Disk Space

The most common root cause is exhaustion of space in the /tmp partition. When MySQL executes complex queries, particularly those involving sorting, grouping, or temporary table operations, it creates temporary files in the /tmp directory. These files can become extremely large, especially when processing substantial data volumes. For instance, a single query might require creating multiple gigabyte-sized temporary tables, and concurrent execution of several such queries can rapidly deplete available space.

Below is a simplified example demonstrating how to check disk space:

# Check disk usage for /tmp directory
df -h /tmp
# Examine size distribution of files in /tmp
du -sh /tmp/* | sort -rh | head -10

Permission Issues

The MySQL service process (typically running as the mysql user) may lack sufficient permissions to write to the /tmp directory. This could result from improper directory permissions or restrictions imposed by security modules like SELinux. In certain system configurations, particularly in environments managed by systemd, MySQL may operate within private namespaces, complicating the manifestation of permission issues.

Basic commands for permission verification:

# View permissions of /tmp directory
ls -ld /tmp
# Check user identity of MySQL process
ps aux | grep mysqld

System Configuration Problems

In some Linux distributions (e.g., Fedora), systemd creates private /tmp directories for MySQL services. These private directories reside at paths like /tmp/systemd-namespace-XXXXXX/private and become visible to MySQL processes through mounting. System maintenance tasks (such as tmpwatch or systemd-tmpfiles) may periodically clean these directories. If private directories remain unaccessed during cleanup cycles, they get deleted, rendering MySQL unable to write temporary files.

The following code example illustrates how to inspect MySQL's private namespace:

# Find MySQL process ID
pgrep mysqld
# View mount information for the process (assuming process ID 1234)
cat /proc/1234/mountinfo | grep /tmp

Systematic Solutions

Addressing the aforementioned causes requires a multi-layered approach:

Basic Checks and Fixes

Begin with fundamental verifications:

1. Ensure the /tmp partition has adequate space (maintain at least 10-20% free space).
2. Verify /tmp directory permissions are set to 1777 (drwxrwxrwt), ensuring all users (including mysql user) can create files.
3. Temporarily restart MySQL service to restore temporary file creation capability: systemctl restart mysqld or /etc/init.d/mysqld restart.

Configuration Adjustments

If basic checks prove insufficient, proceed with configuration modifications:

1. Explicitly Specify Temporary Directory: Add tmpdir setting in MySQL configuration file (typically /etc/my.cnf or /etc/mysql/my.cnf) under the [mysqld] section. For example:

[mysqld]
tmpdir = /var/tmp/mysql

Ensure the specified directory exists and MySQL process has write permissions. Selecting a partition with ample space (such as /var or a dedicated data partition) can prevent root partition space shortages.

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

Advanced System Configuration

For systemd-managed systems, prevent accidental cleanup of private /tmp directories:

1. Exclude from Cleanup Rules: Edit the /etc/tmpfiles.d/privatetmp.conf file (create if nonexistent) and add:

x   /tmp/systemd-namespace-*
x   /tmp/systemd-namespace-*/private

This instructs the systemd-tmpfiles tool to skip cleaning these directories.

/usr/sbin/tmpwatch "$flags" -x /tmp/.X11-unix -x /tmp/.XIM-unix \
        -x /tmp/.font-unix -x /tmp/.ICE-unix -x /tmp/.Test-unix \
        -X '/tmp/systemd-namespace*' \
        -X '/tmp/hsperfdata_*' 10d /tmp

Query Optimization

Fundamentally reduce temporary file usage:

1. Analyze slow query logs to identify queries creating large temporary tables.
2. Optimize query statements by adding appropriate indexes to minimize full table scans.
3. Consider archiving historical data to reduce data volume processed by individual queries.

Below is a simple query optimization example using indexes to avoid temporary table creation:

-- Original query potentially creating temporary table
SELECT user_id, COUNT(*) FROM orders WHERE order_date > '2023-01-01' GROUP BY user_id;

-- Optimization: Ensure indexes exist on user_id and order_date
ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);

Preventive Measures and Best Practices

To prevent recurrence of such errors, implement the following preventive strategies:

1. Monitoring Systems: Set up disk space monitoring alerts to notify when /tmp or other critical partitions exceed 80% usage.
2. Regular Maintenance: Periodically clean unnecessary temporary files while ensuring exclusion of MySQL's private directories.
3. Configuration Reviews: Validate MySQL's tmpdir settings and system cleanup policies after new system deployments or upgrades.
4. Capacity Planning: Allocate separate and sufficient partitions for temporary files to avoid competition with system-critical partitions.

Conclusion

The "Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2)" error, while appearing straightforward, may originate from multiple layers including disk space, permissions, system configuration, and query design. Through systematic diagnosis and layered solutions, this issue can be effectively resolved and prevented. Database administrators are advised to combine monitoring, optimization, and proper configuration to ensure stable MySQL database operations.