MySQL fails to start when a Plesk server lacks disk space: SQLSTATE[HY000] No space left on device 500 Plesk\Exception\Database

 

Symptoms

• When opening Plesk or exporting/importing a database in Plesk, the operation fails with one of the following error messages:

  ERROR: Plesk\Exception\Database
  DB query failed: SQLSTATE[HY000]: General error: 1021 Disk full (/var/tmp/#sql_3b95_1); waiting for someone to free some space..., <...>

  ---

  Server Error
  500 Plesk\Exception\Database
  DB query failed: SQLSTATE[HY000]: General error: 1 Can't create/write to file '/var/tmp/#sql_9d1_0.MAI' (Errcode: 28), <...>

• Websites with MySQL databases are not accessible with the following error message in a web-browser:

  Error establishing a database connection

• The MySQL service fails to start with the "No space left on device" error in its status:

  # systemctl status mariadb.service
  ...
  systemd[1]: Starting MariaDB database server...
  systemd[1]: mariadb.service failed to run 'start-pre' task: No space left on device
  systemd[1]: Failed to start MariaDB database server.
  systemd[1]: mariadb.service failed.

  or with the following error message in /var/log/mariadb/mariadb.log: 

  [ERROR] InnoDB: Could not set the file size of './ibtmp1'. Probably out of disk space

Cause

The MySQL service cannot create temporary files when there is no free disk space limit has been reached on the root partition (or tmp partition if separated):

# df -h /var/lib/mysql /tmp
Filesystem  Size  Used  Avail  Use%  Mounted on
/dev/sda3   50G   50G   20K    100%  /

Resolution

First, to bring services back it is required to free-up some space:

1. Connect to a Plesk server via SSH as root.
2. Delete temporary files that are older than 14 days:

   # find /tmp -type f -mtime +14 -exec rm {} \;
   # find /var/tmp -type f -mtime +14 -exec rm {} \;

   as well as Plesk temporary files:

   # rm -rf /usr/local/psa/PMM/tmp/* /usr/local/psa/tmp/*

3. Next, if disk space is still shows 100% you may want to remove the oldest Plesk backup file. First get the list of backup files by copy/pasting below command:

   # /usr/local/psa/admin/bin/pmm-ras --get-dump-list --type=server | grep 'message' | grep -v [0-9]_[0-9]
   <message>backup_info_2010230005.xml: </message>
   <message>backup_info_2010300005.xml: </message>

   If you have just one backup, it is better not to remove it. If you have several then the first one in the output is the oldest one. Remove it using its name:

   # /usr/local/psa/admin/bin/pmm-ras --verbose --debug --delete-dump --dump-specification=backup_info_2010230005.xml --session-path=/var/log/plesk/PMM

4. If steps 2 and 3 did not help try to find all files bigger than 200 MB size. 

   # find / -type f -size +200M -exec du -h {} + 2>/dev/null | sort -r -h
   248M /var/log/plesk-php74-fpm/error.log
   218M /var/lib/mysql/ibdata1

   In case there will be a log files among them like error.log in above example, you may clean them up:

   # echo > /var/log/plesk-php74-fpm/error.log 
   # service plesk-php74-fpm restart

5. Restart services:

   # service psa restart

6. Once MySQL and Plesk running back the best way to find what takes space is to use Diskpace Usage Viewer extension.

Note: If you did not find what data to remove likely all files are required and server just running out of space. In such a case it is needed to add/buy additional space to the server. This can be done with the help of hosting provider - company you purchased server itself.