How to fix ERROR 1023: Error on close of file in MySQL

Use df to check how much disk space is available on the partition containing your MySQL data directory:

df -h

Look for the partition containing your MySQL datadir (typically /var/lib/mysql or /usr/local/mysql/data). If used space is above 85% or available space is less than 1GB, you must free space immediately. This is the most common cause of error 1023.

Stop MySQL to safely free disk space:

sudo systemctl stop mysql

Delete old backups, logs, or temporary files on the same partition:

du -sh /path/to/mysql/datadir/*

This shows the size of each database. Remove old databases or archived logs. Alternatively, add a new disk or mount a larger volume. Once you've freed space, restart MySQL:

sudo systemctl start mysql

Check that the MySQL data directory is owned by the mysql user and has correct permissions:

ls -ld /var/lib/mysql

Expected output: drwx------ 28 mysql mysql ...

If ownership is incorrect, fix it:

sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 700 /var/lib/mysql

If individual table files have wrong permissions, MySQL cannot close them properly.

Check the current file descriptor limit:

ulimit -n

For a busy MySQL server, this should be at least 10,000. If it's lower, increase it in /etc/security/limits.conf:

sudo bash -c 'echo "mysql soft nofile 10000" >> /etc/security/limits.conf'
sudo bash -c 'echo "mysql hard nofile 10000" >> /etc/security/limits.conf'

Restart MySQL for the change to take effect:

sudo systemctl restart mysql

Check the MySQL error log for more information about which file failed to close:

tail -100 /var/log/mysql/error.log

Or use MySQL to show the log location:

SHOW VARIABLES LIKE 'log_error';

Look for the specific filename mentioned in the error 1023 messages and any preceding OS-level errors (errno values). This helps identify if the problem is with a specific table or the temp directory.

If errors persist after checking space and permissions, the filesystem itself may be corrupted. Check for errors:

sudo fsck -n /dev/sdXY

Replace /dev/sdXY with your actual partition. The -n flag does a read-only check without making changes. If errors are found, you may need to run fsck without -n (requires unmounting and downtime) or replace the disk. Consider backing up all data first.

After resolving the underlying filesystem issue, check if any tables became corrupted:

CHECK TABLE your_table_name;

If a table is marked as crashed, repair it:

REPAIR TABLE your_table_name;

For InnoDB tables, restart MySQL in recovery mode or use percona-xtrabackup to restore from backup if REPAIR doesn't work.