Applicable to:
- Plesk for Linux
Symptoms
-
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_INFO: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file or directory
PLESK_INFO: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] Connection refused:
0: /usr/local/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php:144 -
Domain overview page on Domains > example.com is not accessible:
CONFIG_TEXT: 500 Server Error
Type SB_Facade_Exception_Generic
Message
File generic.php
Line 33 -
Opening WP Toolkit results in the following error:
PLESK_INFO: Server Error 500 Zend_Db_Adapter_Exception
Type Zend_Db_Adapter_Exception
Message SQLSTATE[HY000] [2002]
No such file or directory
File Abstract.php Line 144 -
Plesk upgrade fails with the following error:
PLESK_INFO: DATABASE ERROR!!!
Database psa database found, but version undefined -
A table cannot be properly queried with the
SELECT
statement:MYSQL_LIN: select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm' -
The following information can be found in the MySQL log file
/var/log/mysql/error.log
,/var/log/mysqld.log
, or/var/log/mariadb/mariadb.log
: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: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
CONFIG_TEXT: InnoDB: Assertion failure in thread 140154354255616 in file trx0purge.c line 848
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
CONFIG_TEXT: [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace database/table uses space ID: 882 at filepath
CONFIG_TEXT: [Note] InnoDB: Starting crash recovery.
[ERROR] InnoDB: Tablespace 11904 was not found at ./example_db/example1.ibd.
[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
[ERROR] InnoDB: Tablespace 11905 was not found at ./example_db/example2.ibd.
[ERROR] InnoDB: Cannot continue operation.
CONFIG_TEXT: InnoDB: Database page corruption on disk or a failed file read of page 660. A table cannot be properly queried with the SELECT statement - additional possible output: MariaDB [psa]> select * from db_example.misc;
ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
CONFIG_TEXT: [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 4083146667 between the checkpoint 4083146667 and the end 4083146240.
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[Note] InnoDB: Starting shutdown...
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
CONFIG_TEXT: [Note] InnoDB: Starting crash recovery from checkpoint LSN=353990497915
[Warning] InnoDB: Ignoring a doublewrite copy of page [page id: space=0, page number=9679] with future log sequence
Cause
InnoDB corruption.
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.
Resolution
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 article for additional information regarding MySQL on different operating systems
[Linux] Local MySQL server for all databases
For additional information, check your operating system and MySQL/MariaDB documentation.
Note: for Plesk on Windows, see the following article: How to fix InnoDB corruption cases for the MySQL databases on Plesk for Windows?
-
Connect to the server using SSH.
-
Stop the affected MySQL service and the service
plesk-web-socket
to prevent it from attempting to start MySQL:# service mysql stop || service mariadb stop && service plesk-web-socket stop
-
Back up all the MySQL data storage files. By default, they are located in the directory
/var/lib/mysql/
.For example:
# cp -a /var/lib/mysql /root/mysql_backup
-
Add the parameter
innodb_force_recovery
to the section[mysqld]
of the MySQL configuration file. This option allows starting MySQL service in the recovery mode and try creating dumps of databases.For example:
# vi /etc/my.cnf
[mysqld]
innodb_force_recovery = 2 -
-
If the service fails to start, set the value of the parameter
innodb_force_recovery
to a greater value and try starting MySQL again.
Value of the parameterinnodb_force_recovery
can be from 1 to 6.Warning: Only set
innodb_force_recovery
to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump databases. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. See more details in the official MySQL Documentation. -
If the service fails to start with an error like:
InnoDB: Waiting for the background threads to start
Then add the parameter
innodb_purge_threads
as described in the following article: Unable to start MySQL service: InnoDB: Waiting for the background threads to start. -
If the service fails to start even with
innodb_force_recovery
set to the highest possible value of 6, restore the databases from backups/dumps using steps from the below section Restore databases from backups and do not apply steps 6-8.
Restore databases from backups-
Check which engine (MyISAM or InnoDB) is used by the database
mysql
and proceed based on the output of this command:# ls -l /var/lib/mysql/mysql/*.ibd
-
If the output is empty (MyISAM engine is used):
1.1.1. Remove all the MySQL data storage files except the
mysql
folder. For example:# rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
1.1.2. Remove the option
innodb_force_recovery
from the MySQL configuration file.1.1.3. Restart the MySQL service:
# service mysqld restart
-
If the output is not empty and contains .ibd files (InnoDB engine is used):
1.2.1. Remove all files and directories from the MySQL data directory. For example:
# rm -rf /var/lib/mysql/*
1.2.2. Remove the option
innodb_force_recovery
from the MySQL configuration file.1.2.3. Create an empty
mysql
database using ONE of the below commands:# mysql_install_db --force
# mysqld --initialize
# mysql_secure_installation
Note: the exact command depends on the installed MySQL/MariaDB version, check MySQL/MariaDB documentation for information on this.
1.2.4. Set correct ownership for the MySQL data directory:
# chown -R mysql:mysql /var/lib/mysql
1.2.5. Add the parameter
skip-grant-tables
to the MySQL configuration file and restart MySQL to apply the change:# service mysqld restart
-
-
Restore the database
mysql
and Plesk system databases (psa
,apsc
,roundcubemail
,horde
,phpmyadmin
) from a daily/pre-upgrade dump. -
Remove the parameter
skip-grant-tables
from the MySQL configuration file (if it was added in step 1.2.5) and restart MySQL to apply the change:# service mysqld restart
-
Restore websites' databases from a Plesk backup (instructions can be found either in the documentation or in the knowledge base article) or from manually created dumps.
Note: If timeouts are encountered when restoring databases, set the
wait_timeout
value in the MySQL configuration file and restart the MySQL service. For example:# vi /etc/my.cnf
[mysqld]
wait_timeout = 1800
-
-
Try dumping all databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -Ns -uadmin psa -Ne"show databases" | grep -v information_schema | grep -v performance_schema > /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" --routines --databases > /root/db_backup/"$i".sql; echo $i; sleep 5; done-
If dumping fails with an error like:
Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"
then increase the value of
innodb_force_recovery
, 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 creating a dump of some or all databases fails, then the only remaining method is to restore databases as described in the section Restore databases from backups in step 5, do not apply steps 7-8 in this case.
-
-
Check which engine (MyISAM or InnoDB) is used by the database
mysql
and proceed based on the output of this command:# ls -l /var/lib/mysql/mysql/*.ibd
If the output is empty (MyISAM engine is used)7.1. Remove all the MySQL data storage files except the
mysql
folder. For example:# rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
7.2. Remove the option
innodb_force_recovery
from the MySQL configuration file.7.3. Restart the MySQL service:
# service mysqld restart
7.4. Check the MySQL log file for any errors.
7.5. Restore databases from the dumps made in step 6. For example:
# for db in `cat /root/db_list.txt`; do echo -e "Importing $db..."; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/db_backup/$db.sql; done
If the output is not empty and contains .ibd files (InnoDB engine is used)7.1. Remove all files and directories from the MySQL data directory. For example:
# rm -rf /var/lib/mysql/*
7.2. Remove the option
innodb_force_recovery
from the MySQL configuration file.7.3. Create an empty
mysql
database using ONE of the below commands:# mysql_install_db --force
# mysqld --initialize
# mysql_secure_installation
Note: the exact command depends on the installed MySQL/MariaDB version, check MySQL/MariaDB documentation for information on this.
7.4. Set correct ownership for the MySQL data directory:
# chown -R mysql:mysql /var/lib/mysql
7.5. Add the parameter
skip-grant-tables
to the MySQL configuration file and restart MySQL to apply the change:# service mysqld restart
7.6. Restore databases from the dumps made in step 6. For example:
# for db in `cat /root/db_list.txt`; do echo -e "Importing $db..."; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/db_backup/$db.sql; done
7.7. Remove the parameter
skip-grant-tables
from the MySQL configuration file and restart MySQL to apply the change:# service mysqld restart
-
Start the service
plesk-web-socket
:# service plesk-web-socket start
Comments
2 comments
Hi Taras Ermoshin,
On step number (7.3), to the best of my understanding, is could be a different CLI, without the letter 'd' at the end of the service name:
May I ask is that so, and when is mysqld used, and when is mysql ?
How can i restore the Userdatabases from the files from "/var/lib/mysql"?
After i copy this files from this folder to the other, i can´t restore this.
And i found no working tutorial to do this.
Please sign in to leave a comment.