Articles in this section

See more

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

Avatar
Leonid Gukhman
Updated
Follow
Plesk for Windows kb: technical ABT: Group A

Applicable to:

  • Plesk for Windows

Symptoms

  • Plesk SQL server cannot be started with the following error:

    CONFIG_TEXT: Incorrect function (Error code 1) at Start service PleskSQLServer at (service::startStopService line 997)

    CONFIG_TEXT: Error 1067: The process terminated unexpectedly, Exception code: 0xc0000005

  • MySQL on Windows is crashing with the following error:

    CONFIG_TEXT: Faulting application name: mysqld.exe, version: 5.6.36.0, time stamp: 0x58ccdb65
    Faulting module name: mysqld.exe, version: 5.6.36.0, time stamp: 0x58ccdb65
    Exception code: 0x80000003
    Fault offset: 0x00000000002bb162
    Faulting process id: 0x1764
    Faulting application start time: 0x01d4639065120bae
    Faulting application path: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe
    Faulting module path: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe
    Report Id: f33b3065-14ac-4735-b77c-3653d732bef3

  • The following can be observed in the %plesk_dir%\databases\mysql\data\*.err file:

    CONFIG_TEXT: InnoDB: Page directory corruption: infimum not pointed to
    InnoDB: Page dump in ascii and hex (16384 bytes):
    len 16384; hex 00.....000; asc ;
    InnoDB: End of page dump

    CONFIG_TEXT: [ERROR] InnoDB: Page [page id: space=0, page number=237] log sequence number 1737623713 is in the future! Current system log sequence number 1704662866.
    [ERROR] 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: Cannot create log files because data files are corrupt or the database was not shut down cleanly after creating the data files.

  • In case it is PleskSQL service, Plesk is inaccessible with:

    CONFIG_TEXT: ERROR 500
    Zend_Db_Adapter_Exception
    SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it.

  • The following messages can be found in Event Viewer > Windows Logs > Application:

    CONFIG_TEXT: InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 252683671 and the end 252687827.For more information, see Help and Support Center at http://www.mysql.com.
    InnoDB: Plugin initialization aborted with error Generic errorFor more information, see Help and Support Center at http://www.mysql.com.
    Plugin 'InnoDB' init function returned error.For more information, see Help and Support Center at http://www.mysql.com.
    Plugin 'InnoDB' registration as a STORAGE ENGINE failed.For more information, see Help and Support Center at http://www.mysql.com.

    CONFIG_TEXT: Faulting application name: mysqld.exe, version: 5.5.52.0, time stamp: 0x57c0337c
    Faulting module name: mysqld.exe, version: 5.5.52.0, time stamp: 0x57c0337c
    <..>
    Faulting application path: C:\Program Files (x86)\Parallels\Plesk\MySQL\bin\mysqld.exe
    Faulting module path: C:\Program Files (x86)\Parallels\Plesk\MySQL\bin\mysqld.exe
    Report Id: 3ec05b88-c439-11e8-8102-005056b17492
    Faulting package full name:
    Faulting package-relative application ID:

  • Plesk upgrade fails with the next error:

    PLESK_ERROR: ERROR 2013 (HY000) at line 275: Lost connection to MySQL server during query

  • The following information is found in the %plesk_dir%MySQL\Data\*.err file:

    CONFIG_TEXT: InnoDB: Completed initialization of buffer pool
    InnoDB: Error: checksum mismatch in data file .\ibdata1
    InnoDB: Could not open or create data files.
    InnoDB: If you tried to add new data files, and it failed here,
    InnoDB: you should now edit innodb_data_file_path in my.cnf back
    InnoDB: to what it was, and remove the new ibdata files InnoDB created
    InnoDB: in this failed attempt. InnoDB only wrote those files full of
    InnoDB: zeros, but did not yet use them in any way. But be careful: do not
    InnoDB: remove old data files which contain your precious data!
    [ERROR] Plugin 'InnoDB' init function returned error.
    [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    [ERROR] Unknown/unsupported storage engine: INNODB
    [ERROR] Aborting

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

Plesk has two MySQL servers on a Windows platform:

Note: To start/stop/restart PleskSQLserver/MySQL, it is recommended to use Plesk Services Monitor.

InnoDB recovery for PleskSQLserver service

  1. Connect to the server via RDP.

  2. Create a backup of all current Plesk-related MySQL data and databases at %plesk_dir%MySQL\Data\.

  3. Open the %plesk_dir%MySQL\my.ini file.

  4. Add the following lines into the [PleskSQLServer] section:

    CONFIG_TEXT: innodb_force_recovery = 1
    skip-grant-tables

  5. Try to start PleskSQLserver.

  6. In case it fails once again, set innodb_force_recovery to a greater value and try starting it again.

    Warning: Only set innodb_force_recovery to 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.

  7. Once PleskSQLserver is started in force recovery mode, create dumps of all databases using the following command executed via the PowerShell:

    PS cd $env:plesk_dir\mysql\bin\
    mkdir .\data_restore
    .\mysql.exe -uadmin -P8306 -sNe "SHOW DATABASES" | findstr /V performance_schema | findstr /V information_schema > c:\db_list.txt
    foreach ($var in get-content c:\db_list.txt) {.\mysqldump.exe -uadmin -P8306 $var > .\data_restore\$var.sql}

  8. Stop PleskSQLserver service.

  9. Remove all MySQL data except mysql folder and error logs from %plesk_dir%MySQL\Data\ directory.

  10. Remove the line innodb_force_recovery from my.ini file

  11. Start PleskSQLserver.

  12. Restore the databases from the dumps made during the step 7:

    PS foreach ($var in get-content c:\db_list.txt) {.\mysql.exe -uadmin -P8306 -e"create database $var"}
    foreach ($var in get-content c:\db_list.txt) {get-content .\data_restore\$var.sql | .\mysql.exe -uadmin -P8306 $var}

  13. Remove the skip-grant-tables line from my.ini file and restart PleskSQLserver .

InnoDB recovery for Clients MySQL service

  1. Connect to the server via RDP.

  2. Create a backup of all current MySQL data and databases at %plesk_dir%Databases\MySQL\data

  3. Open the %plesk_dir%Databases\MySQL\my.ini file.

  4. Add the following line into the [MySQLD] section:

    CONFIG_TEXT: innodb_force_recovery = 1
    skip-grant-tables

  5. Try to start MySQL.

  6. In case it fails once again, set innodb_force_recovery to a greater value and try starting it again.

    Warning: Only set innodb_force_recovery to 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.

  7. Once MySQL is started in force recovery mode, create dumps of all databases using the following command executed via the PowerShell:

    PS cd $env:plesk_dir\mysql\bin\
    mkdir .\data_restore
    .\mysql.exe -uadmin -P3306 -e"SHOW DATABASES" > c:\db_list.txt
    foreach ($var in get-content c:\db_list.txt) {.\mysqldump.exe -uadmin -P3306 $var > .\data_restore\$var.sql}

  8. Stop MySQL service.

  9. Remove all MySQL data except mysql folder and error logs from %plesk_dir%Databases\MySQL\data directory.

  10. Remove the line innodb_force_recovery from my.ini file and start MySQL.

  11. Restore the databases from the dumps made during the step 7:

    PS foreach ($var in get-content c:\db_list.txt) {.\mysql.exe -uadmin -P3306 -e"create database $var" }
    foreach ($var in get-content c:\db_list.txt) {get-content .\data_restore\$var.sql | .\mysql.exe -uadmin -P3306 $var}

  12. Remove the skip-grant-tables line from my.ini file and restart MySQL.

 

How to recreate Plesk´s admin MySQL database on a Windows server?

 

Additional information

How to fix InnoDB corruption cases for the MySQL database?

Comments

0 comments

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles