- Plesk for Linux
The following error is shown in Plesk:
PLESK_INFO: ERROR: PleskMainDBException
MySQL query failed: Incorrect information in file: './psa/misc.frm'
PLESK_INFO: ERROR: PleskDBException: Unable to connect to database: mysql_connect(): No such file or directory /var/run/mysqld/mysqld.sock (Error code: 2002). Please check that database server is started and accessible. (Abstract.php:69)
Plesk upgrade fails with the following error:
PLESK_INFO: DATABASE ERROR!!!
Database psa database found, but version undefined
MySQL service does not start:
# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
mysqlcheckutilities fail with an error message claiming a table does not exist (use the MySQL administrator account to check):
# mysqlcheck -uadmin -p****** db_example
error : Can't find file: 'BackupTasks.MYD' (errno: 2)
A table cannot be properly queried with the
MYSQL_LIN: select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
The table cannot be repaired because the InnoDB engine does not support reparation.
# mysql> repair table misc;
| Table | Op | Msg_type | Msg_text |
| psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
The following information can be found in the MySQL log file:
CONFIG_TEXT: 150704 19:09:27 InnoDB: Waiting for the background threads to start
150704 19:09:28 InnoDB: Error: tablespace size stored in header is 3712 pages, but
150704 19:09:28 InnoDB: the sum of data file sizes is only 3072 pages
150704 19:09:28 InnoDB: Cannot start InnoDB. The tail of the system tablespace is
150704 19:09:28 InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
150704 19:09:28 InnoDB: inappropriate way, removing ibdata files from there?
150704 19:09:28 InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
150704 19:09:28 InnoDB: a startup if you are trying to recover a badly corrupt database.
CONFIG_TEXT: InnoDB: Assertion failure in thread 3876 in file ha_innodb.cc line 17352
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: about forcing recovery.
CONFIG_TEXT: InnoDB: Assertion failure in thread 140154354255616 in file trx0purge.c line 848OR
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
CONFIG_TEXT: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery
Most InnoDB corruptions are hardware-related. Corrupted page writes can be caused by power failures or bad memory. The issue also can be caused by using network attached storage (NAS) and allocating InnoDB databases on it.
Note: Since the MySQL service's control, logs and configuration file's location is different on the different operating systems, this article provides general command examples only. Check the following articles for additional information regarding MySQL on different operating systems:
For additional information check your operating system and MySQL documentation.
There are several ways to recover a failed MySQL database:
I. Force InnoDB Recovery
Stop the affected MySQL service. See the note above regarding service's control.
Back up all the MySQL data storage files. They are located in the following directories:
/var/lib/mysql/- all databases on Linux
%plesk_dir%Databases\MySQL\Data\- customers' databases on Windows
%plesk_dir%MySQL\Data\- Plesk-related databases (psa, apsc, horde) on Windows.
# mkdir /root/mysql_backup
# cp -a /var/lib/mysql/* /root/mysql_backup/
innodb_force_recoveryvalue under the
[PleskSQLServer]for Plesk SQL Server on Windows) in the MySQL configuration file. This option will allow you to start MySQL service and create all databases dump.
# vi /etc/my.cnf
innodb_force_recovery = 1
Warning: Only set
innodb_force_recoveryto a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. Please see more details in the official MySQL Documentation.
Start the MySQL service.
Try to dump all databases:
# mysql -Ns -uadmin -p`cat /etc/psa/.psa.shadow` -e"SHOW DATABASES" > /root/db_list.txt
# mkdir /root/db_backup/
# cat /root/db_list.txt | while read i; do MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin "$i" > /root/db_backup/"$i".sql; done
- If the dump fails with an error like:
CONFIG_TEXT: Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"`
innodb_force_recoveryvalue, restart MySQL service, and try to dump the databases again. It is better to dump databases one by one, separately. In that case, there is no need to go through restore of all databases once again if restore failed for some reason.
If unable to dump the databases, then try using method II (Copy table content) or III (Restore from the backup) which are described below.
Remove all the MySQL data storage files except the
mysqlfolder. For example:
# rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
innodb_force_recoveryoption from the MySQL configuration file.
Restart the MySQL service:
# service mysqld restart
Check the MySQL log file for any errors.
Restore databases from the dumps made on the 5th step. For example:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin < /root/db_backup/[database_name].sql
To perform it in a bunch, try the commands below:
# for db in `cat /root/db_list.txt`; do echo -e "Creating $db..."; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin -e "create database $db"; done
# for db in `cat /root/db_list.txt`; do echo -e "Importing $db..."; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin $db < /root/db_backup/$db.sql; done
II. Copy table contents
Repeat steps #1-4 from the method I to back up all the MySQL data storage files and enable InnoDB recovery mode.
Try to make a copy of a table:
MYSQL_LIN: CREATE TABLE <new_table> LIKE <crashed_table>;
MYSQL_LIN: INSERT INTO <new_table> SELECT * FROM <crashed_table>;
If the copy was created successfully, then replace corrupted table with newly created:
MYSQL_LIN: RENAME TABLE <crashed_table> TO <old_table>;
MYSQL_LIN: RENAME TABLE <new_table> TO <crashed_table>;
MYSQL_LIN: DROP TABLE <old_table>;
Note: Depending on the MySQL version used, it might be necessary to set lower
innodb_force_recoveryvalue or remove it from the MySQL configuration file and restart MySQL service to successfully perform the
RENAMEoperations. Please see more details in the official MySQL Documentation.
III. Restore from a backup
If the instructions above did not help, the only remaining method is to restore the databases from backups. Do not forget to remove the
innodb_force_recovery option from the MySQL configuration file before restore.
To restore Plesk-related databases (psa, apsc, horde) see How to backup/restore a Plesk database dump article. For example:
# ls -tl /var/lib/psa/dumps
-rw------- 1 root root 141960 Aug 8 01:03 mysql.daily.dump.0.gz
-rw------- 1 root root 141925 Aug 7 01:03 mysql.daily.dump.1.gz
# zcat /var/lib/psa/dumps/mysql.daily.dump.0.gz | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa
To restore customer's databases from Plesk backup see the Restoring Data from Backup Archives section in the Administrator's Guide.
Note: If timeouts are encountered when restoring databases, set the
wait_timeoutvalue in the MySQL configuration file and restart the MySQL service. For example:
# vi /etc/my.cnf
wait_timeout = 1800