MySQL/MariaDB service fails to start on Plesk server or other operations fail due to corrupted database

Symptoms

• MySQL/MariaDB service fails to start with the following errors:

  • in Plesk for Linux

    In MySQL/MariaDB log file /var/log/mysql/error.log, /var/log/mysqld.log, or /var/log/mariadb/mariadb.log:

    CONFIG_TEXT: [ERROR] InnoDB: innodb-page-size mismatch in tablespace ./example_db/table_name.ibd (table example_db/table_name)
    ...
    InnoDB: Error: could not open single-table tablespace file ./example_db/table_name.ibd

    In journalctl:

    CONFIG_TEXT: InnoDB: space header page consists of zero bytes in tablespace ./example_db/table_name.ibd (table example_db/table_name)

    CONFIG_TEXT: InnoDB: Operating system error number 2 in a file operation.

  • in Plesk for Windows

    In MySQL/MariaDB log file %plesk_dir%Databases\MySQL\Data\server_hostname.err:

    CONFIG_TEXT: [ERROR] InnoDB: checksum mismatch in tablespace .\mysql\slave_relay_log_info.ibd (table mysql/slave_relay_log_info)

• Unable to access a MySQL/MariaDB database via phpMyAdmin in Domains > example.com > Databases:

  CONFIG_TEXT: #2002 - The server is not responding (or the local server's socket is not correctly configured).

  At the same time, one the following errors is found in the MySQL/MariaDB log file:

  CONFIG_TEXT: [ERROR] Table table_name contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

  OR

  CONFIG_TEXT: InnoDB: MySQL and InnoDB data dictionaries are out of sync.

• The error below occurs on attempt to open database overview in Domains > example.com > Databases:

  PLESK_ERROR: Internal error ;-P
  {"status":"error","statusMessages":[{"status":"error","class":"","content":"SQLSTATE[HY000] [2002] No such file or directory<br><a href='http://kb.plesk.com/plesk-error/search?metaId=0faa7e4d9d6e1d1c8182d642d419756f&messageId=2eb8d2699e722503bf79ae1c7819dc4e&file=Abstract.php&line=144&type=Zend_Db_Adapter_Exception&version=17.8.11&message=SQLSTATE%5BHY%5D+%5B%5D+No+such+file+or+directory' target='_blank'>Search for related Knowledge Base articles</a>","source":null,"title":"Error"}]}

• Plesk backup fails with the error:

  PLESK_ERROR: Database example_db is skipped from backup due to error: Unable to connect to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/psa/PMM/agents/shared/Db/MysqlDbiBackend.pm line 62.

• Repairing the database using mysqlcheck fails:

  # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -u admin example_db
  status : Operation failed
  table_name
  Error : Table 'table_name' doesn't exist

  OR

  # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -u admin --all-databases
  mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '
  example_db.mytable
  warning : Table is marked as crashed
  error : Can't read key from filepos: 8936448
  Error : Incorrect key file for table './example_db/table_name.MYI'; try to repair it
  error : Corrupt

  Dumping database may also fail with one of above errors.

Cause

One or more MySQL/MariaDB databases is corrupted.

Resolution

If there is a backup of a corrupted database, restore it using the instructions from the article How to restore a database from a Plesk backup

If there is no backup of the corrupted database, recover it using InnoDB force recovery scenario:

• How to fix InnoDB corruption cases for the MySQL/MariaDB databases on Plesk for Linux?

• How to fix InnoDB corruption cases for the MySQL/MariaDB databases on Plesk for Windows?