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

Symptoms

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

  ERROR: PleskExceptionDatabase
  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 PleskExceptionDatabase
  DB query failed: SQLSTATE[HY000]: General error: 1 Can't create/write to file '/var/tmp/#sql_9d1_0.MAI' (Errcode: 28), <...>

  ---

  Server Error
  500
  Zend_Db_Adapter_Exception
  SQLSTATE[HY000][2002] No such file or directory

  ---

  This page isn’t working
  203.0.113.2 is currently unable to handle this request.
  HTTP ERROR 500

• 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% /

• There is enough disk space available but there are not enough inodes available:

  # df -i
  Filesystem Inodes IUsed IFree IUse% Mounted on
  tmpfs 483902 1 483901 1% /dev/shm
  /dev/sda3 3276800 3276800 0 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. Check the size and clean package cache (cache of previously downloaded packages) with the following command for Ubuntu:

   # du -sh /var/cache/apt/
   # sudo apt-get clean

   and for Centos:

   # yum clean all

4. 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

5. 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-php…