Articles in this section

See more

Plesk is not accessible: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file or directory

Avatar
Ivan Kamnev
Updated
Follow
kb: how-to Plesk for Linux ABT: Group A FR:PPM-2077

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

  • Unable to start MySQL/MariaDB service with the following error in /var/log/mysql/error.log, /var/log/mysqld.log, or /var/log/mariadb/mariadb.log:

    CONFIG_TEXT: InnoDB: Waiting for the background threads to start
    InnoDB: Error: tablespace size stored in header is 3712 pages, but
    InnoDB: the sum of data file sizes is only 3072 pages
    InnoDB: Cannot start InnoDB. The tail of the system tablespace is
    InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
    InnoDB: inappropriate way, removing ibdata files from there?
    InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
    InnoDB: a startup if you are trying to recover a badly corrupt database.

  • 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/MariaDB log file /var/log/mysql/error.log, /var/log/mysqld.log, or /var/log/mariadb/mariadb.log:

    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

Solution via Plesk GUI

Note: The solution below is available starting from Plesk 18.0.63 version. If Plesk version is below 18.0.63 check the Manual SSH solution subsection

  1. Open the Plesk Repair Kit by accessing the following URL:

    CONFIG_TEXT: https://203.0.13.2:8443/repair

    Note: in the link above replace the 203.0.13.2 with the IP address/hostname of your Plesk server

  2. Input your Plesk admin credentials in the corresponding Username and Password fields and press the Sign in button.
  3. Under the MariaDB/MySQL server section press the Check and after the Continue button:
  4. When the checking procedure will be finished press the Repair button.
  5. Verify that the server has enough disk space for creating a backup for databases and press the Continue button to start the InnoDB repair procedure.

    Note: this procedure could cause data loss because the corrupted databases will be removed:

Note: The location of the log file for this procedure is /var/log/plesk/repairkit.log
The backup of the /var/lib/mysql directory will be created in /var/lib/psa/dumps/mysql.repair-innodb.$date.d/mysql/. This depends on the configured value for DUMP_D in /var/lib/psa/dumps file.

Manual SSH solution

  1. Log into the server using SSH.
  2. Stop the affected MySQL/MariaDB service and the service plesk-web-socket to prevent it from attempting to start MySQL/MariaDB:

    # service mysql stop || service mariadb stop && service plesk-web-socket stop

  3. Back up all the MySQL/MariaDB data storage files. By default, they are located in the directory /var/lib/mysql/.
    For example:

    # cp -a /var/lib/mysql /root/mysql_backup

  4. Add the parameterinnodb_force_recovery to the section[mysqld] of the MySQL/MariaDB configuration file. This option allows starting MySQL/MariaDB service in the recovery mode and try creating dumps of databases.
    For example:

    # vi /etc/my.cnf
    [mysqld]
    innodb_force_recovery = 2

  5. Start the MySQL/MariaDB service.
    • If the service fails to start, set the value of the parameter innodb_force_recovery to a greater value and try starting MySQL/MariaDB again.
      Value of the parameter innodb_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/MariaDB 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 by using steps from Restore databases from backups subsection of the this article

  6. 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 switch to Restore databases from backups subsection of the this article and do not apply further steps from this subsection

  7. Remove all files and directories from the MySQL data directory. For example:

    # rm -rf /var/lib/mysql/*

  8. Remove the option innodb_force_recoveryfrom the MySQL configuration file.

  9. Create an empty mysqldatabase 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.

  10. Set correct ownership for the MySQL data directory:

    # chown -R mysql:mysql /var/lib/mysql

  11. Add the parameter skip-grant-tables to the MySQL configuration file and restart MySQL to apply the change:

    # service mysqld restart

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

  13. Remove the parameter skip-grant-tables from the MySQL configuration file and restart MySQL to apply the change:

    # service mysqld restart

  14. Start the service plesk-web-socket:

    # service plesk-web-socket start 

Restore databases from backups 
  1. Log into the server via SSH.

    Note: in case you have already created backup for /var/lib/mysql directory in subsection Manual SSH solution, skip steps №2 and №3

  2. Stop the affected MySQL/MariaDB service and the service  plesk-web-socket  to prevent it from attempting to start MySQL/MariaDB:

    # service mysql stop || service mariadb stop && service plesk-web-socket stop

  3. Back up all the MySQL/MariaDB data storage files. By default, they are located in the directory /var/lib/mysql/.

    # cp -a /var/lib/mysql /root/mysql_backup_dir

  4. Remove all files and directories from the MySQL data directory.

    # rm -rf /var/lib/mysql/*

  5. Verify that the innodb_force_recovery is removed from the MySQL/MariaDB configuration file.
  6. 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. Set correct ownership for the MySQL data directory:

    # chown -R mysql:mysql /var/lib/mysql

  8. Add the parameter skip-grant-tables to the MySQL configuration file and restart MySQL to apply the change:

    # service mysqld restart

  9. Restore the database mysql and Plesk system databases (psa, apsc, roundcubemail, horde, phpmyadmin) from a daily/pre-upgrade dump.

  10. Remove the parameter skip-grant-tables from the MySQL configuration file (if it was added in step 8) and restart MySQL to apply the changes:

    # service mysqld restart

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

Comments

2 comments

Sort by Date Votes
  • Avatar
    Ehud Ziegelman

    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:

    service mysql restart

     

    May I ask is that so, and when is mysqld used, and when is mysql ?

     

    0
    Comment actions Permalink
  • Avatar
    Fabian Lücker

    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.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles