MySQL/MariaDB 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:

  PLESK_INFO: 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..., <...>

  ---

  PLESK_INFO: 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), <...>

  ---

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

  ---

  PLESK_INFO: 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:

  PLESK_INFO: Error establishing a database connection

• The MySQL/MariaDB 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.

  In rare cases: systemd[1]: mariadb.service: Found left-over process 888 (mysqld) in control group while starting unit. Ignoring.
  systemd[1]: This usually indicates unclean termination of a previous run, or service implementation deficiencies.

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

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

Cause

• The MySQL/MariaDB 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-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

6. Restart services:

   # service psa restart

7. Once MySQL/MariaDB 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.

Additional information

How to remove Plesk backup files

How to clean temporary files

How to find files taking most of the space