Applicable to:
- Plesk for Windows
Symptoms
-
Plesk SQL server cannot be started with the following error:
Incorrect function (Error code 1) at Start service PleskSQLServer at (service::startStopService line 997)
Error 1067: The process terminated unexpectedly, Exception code: 0xc0000005
-
MySQL.MariaDB on Windows is crashing with the following error:
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: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
[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.
[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:
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:
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.
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:
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: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/MariaDB servers on a Windows platform:
-
PleskSQLServer is for Plesk-related databases on port 8306: [Windows] Local MySQL/MariaDB server for Plesk-related databases (psa, apsc, horde)
-
The other is for client's MySQL/MariaDB databases on port 3306: [Windows] Local MySQL/MariaDB server for customers' databases
Note: To start/stop/restart PleskSQLServer/MySQL/MariaDB, it is recommended to use Plesk Services Monitor.
Recover InnoDB for the affected database server:
-
Connect to the server via RDP.
-
Create a backup of all current Plesk-related MySQL/MariaDB data and databases at
%plesk_dir%MySQL\Data\
. -
Open the
%plesk_dir%MySQL\my.ini
file. -
Add the following lines into the
[PleskSQLServer]
section:innodb_force_recovery = 1
skip-grant-tables -
Try to start PleskSQLServer.
-
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. -
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} -
Stop PleskSQLServer service.
-
Remove all MySQL/MariaDB data except
mysql
folder and error logs from%plesk_dir%MySQL\Data\
directory. -
Remove the line
innodb_force_recovery
frommy.ini
file -
Start PleskSQLServer
-
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} -
Remove the
skip-grant-tables
line frommy.ini
file and restart PleskSQLServer .
-
Connect to the server via RDP.
-
Create a backup of all current MySQL/MariaDB data and databases at
%plesk_dir%Databases\MySQL\data
-
Open the
%plesk_dir%Databases\MySQL\my.ini
file. -
Add the following line into the
[MySQLD]
section:innodb_force_recovery = 1
skip-grant-tables -
Try to start MySQL/MariaDB.
-
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. -
Once MySQL/MariaDB 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} -
Stop MySQL/MariaDB service.
-
Remove all MySQL/MariaDB data except
mysql
folder and error logs from%plesk_dir%Databases\MySQL\data
directory. -
Remove the line
innodb_force_recovery
frommy.ini
file and start MySQL/MariaDB. -
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} -
Remove the
skip-grant-tables
line frommy.ini
file and restart MySQL/MariaDB.
If MySQL/MariaDB fails to start even with the innodb_force_recovery
set to the highest possible value of 6
, the only remaining method is to re-initialize MySQL/MariaDB and restore databases from a backup:
-
Connect to the server via RDP.
-
Make sure that there are Plesk daily dumps available on the server in the
%plesk_dir%Mysql\Backup
. -
Stop the Plesk SQL server via the Plesk Services Monitor.
-
Create a backup of all current MySQL/MariaDB data and databases in
%plesk_dir%MySQL\Data
folder. - After ensuring the backup was done on step 4, erase all contents of the Data folder.
-
Start the Command Prompt and initialize MySQL/MariaDB by running the following command:
-
For MariaDB >= 10.5:
"%plesk_dir%MySQL\bin\mysql_install_db.exe" --datadir="%plesk_dir%MySQL\Data"
-
For older MariaDB/MySQL versions:
"%plesk_dir%MySQL\bin\mysql.exe" --initialize --datadir="%plesk_dir%MySQL\Data" --console
-
-
Apply correct permissions to MySQL/MariaDB directory:
plesk sbin ApplySecurity.exe --apply-to-directory --directory="%plesk_dir%MySQL"
-
Start Plesk SQL Server via the Plesk Services Monitor.
-
Create a
root
MySQL/MariaDB user and password:plesk sbin mysqlmng_adm.exe --inject-root
plesk sbin mysqlmng_adm.exe --add-super-user --login=admin "--password=password" --allowed-host=localhost
Note: Replace password with the password of choice
plesk sbin psadb.exe --update-admin-password --password="password"
-
Log in to the MySQL database server with the password set in prior step:
"%plesk_dir%MySQL\bin\mariadb.exe" -uadmin -p
-
Create the psa database and log off from the database server:
CREATE DATABASE psa;
Query OK, 1 row affected (0.002 sec)
\q -
Restore the Plesk
psa
database from a daily dump. -
Create the
apsc
database:plesk sbin mysqlmng_adm.exe --create-database --database=apsc
plesk sbin mysqlmng_adm.exe --add-user --database=apsc --new-user-login=apsc "--new-user-password=password" --allowed-host=localhost
plesk php "%plesk_dir%admin\plib\scripts\register_apsc_database.php" --register -host 127.0.0.1 -port 8306 -database apsc -login apsc -password "password"
-
Restore the
apsc
database from a daily dump as on step 9.
-
Connect to the server via RDP.
-
Create a backup of all current MySQL/MariaDB data and databases in
%plesk_dir%Databases\MySQL\Data
. -
Remove all the data from the
%plesk_dir%Databases\MySQL\Data
folder. -
Open Command Prompt as Administrator and execute the command below to initialize MariaDB.
Note: In the example below the command is provided for MariaDB 10.6 and Plesk directory is a default one (
%plesk_dir%
=C:\Program Files (x86)\Plesk
). Please consider adjusting paths if required"C:\Program Files\MariaDB 10.6\bin\mysql_install_db.exe" --datadir="%plesk_dir%Databases\MySQL\Data"
-
Start MariaDB via the Plesk Services Monitor.
-
Open the
%plesk_dir%Databases\MySQL\my.ini
file for editing, add theskip-grant-tables
option to it under the[MySQLD]
section:[MySQLD]
...
skip-grant-tablesSave the changes.
-
Restart MariaDB via the Plesk Services Monitor to apply changes.
-
Log into the customers' MySQL/MariaDB without the password via the Command Prompt:
"%plesk_dir%\MySQL\bin\mysql.exe" -P3306
-
Execute the commands below one by one:
9.1. Reload the grant-tables policies by running the next command:
FLUSH PRIVILEGES;
9.2. Recreate the user by specifying a new password using the next two commands:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON . TO 'admin'@'localhost' identified by 'password' WITH GRANT OPTION;
Note: Make sure to replace password with a new password of choice.
9.3. Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:
FLUSH PRIVILEGES;
-
Remove the
skip-grant-tables
from the MySQL/MariaDB configuration file%plesk_dir%Databases\MySQL\my.ini
and restart the service again as in Step 7. -
Navigate to Tools & Settings > Database Servers and click localhost.
-
In the opened page click the Settings button and specify new the password, that was set in Step 9.2 in the Administrator's password and Confirm password fields.
-
In the Command Prompt execute the command below to synchronize databases and database users with Plesk:
plesk repair mysql -y
Comments
0 comments
Please sign in to leave a comment.