How to fix EE_WRITE (3): Error writing file in MySQL

-- View the tmpdir setting
SHOW VARIABLES LIKE 'tmpdir';

-- Also check related temp settings
SHOW VARIABLES LIKE '%temp%';
SHOW VARIABLES LIKE '%tmp%';

# Replace /tmp with your tmpdir path from step 1
ls -la /tmp/

# Check specific permissions
stat /tmp

# Verify it's not a dead symlink
cd /tmp
pwd  # Should show actual directory, not symlink target

# Create the directory (using /tmp as example)
sudo mkdir -p /tmp

# Set proper permissions for MySQL
sudo chmod 1777 /tmp

# Or if you have a custom tmpdir path
sudo mkdir -p /custom/mysql/tmpdir
sudo chmod 1777 /custom/mysql/tmpdir

# Verify permissions were applied
ls -la /custom/mysql/tmpdir

# Find MySQL user and group (usually 'mysql')
id mysql

# Check current ownership
ls -la /tmp | head -20

# Fix ownership (important if directory was manually created or changed)
sudo chown -R mysql:mysql /custom/mysql/tmpdir

# Ensure write permissions exist
sudo chmod -R 755 /custom/mysql/tmpdir

# Verify
ls -la /custom/mysql/tmpdir

# Create a new tmpdir location (example: on a larger partition)
sudo mkdir -p /var/lib/mysql/tmp
sudo chown mysql:mysql /var/lib/mysql/tmp
sudo chmod 700 /var/lib/mysql/tmp

# Edit MySQL configuration
sudo nano /etc/mysql/mysql.cnf
# or /etc/mysql/my.cnf

# Add or modify the tmpdir setting under [mysqld]
[mysqld]
tmpdir = /var/lib/mysql/tmp

# Save and restart MySQL
sudo systemctl restart mysql

# Verify the setting took effect
mysql -u root -p -e "SHOW VARIABLES LIKE 'tmpdir';"

-- Check InnoDB temp tablespace path
SHOW VARIABLES LIKE 'innodb_temp_data_file_path';

-- Check other relevant InnoDB paths
SHOW VARIABLES LIKE '%innodb%path%';

-- View temp tablespace status
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;

# Verify the path exists and is writable
ls -la /path/to/innodb/temp/dir

# If not, create it
sudo mkdir -p /path/to/innodb/temp/dir
sudo chown mysql:mysql /path/to/innodb/temp/dir
sudo chmod 700 /path/to/innodb/temp/dir

# Check disk space on all partitions
df -h

# Check inode usage (could be full even with space available)
df -i

# Check for filesystem errors in system logs
sudo journalctl -xe | grep -i filesystem

# If using LVM, check its status
sudo pvs
sudo lvs
sudo vgs

# Check mount options (tmpdir should not be mounted read-only)
mount | grep tmpdir
# Should NOT show 'ro' (read-only)

-- Simple test that uses tmpdir
SELECT * FROM your_table ORDER BY column1 LIMIT 1000;

-- Create a test temporary table
CREATE TEMPORARY TABLE test_tmp AS
SELECT * FROM information_schema.tables LIMIT 100;

-- Check if it succeeded
SELECT COUNT(*) FROM test_tmp;

-- Cleanup
DROP TABLE test_tmp;

# Restart MySQL service
sudo systemctl restart mysql

# Verify MySQL is running
sudo systemctl status mysql

# Check that new tmpdir setting is active
mysql -u root -p -e "SHOW VARIABLES LIKE 'tmpdir';"

# Monitor error log for any startup errors
tail -f /var/log/mysql/error.log &

# Run a workload test
# (run your typical queries)

# Kill the tail process when done
pkill tail