Issue Server Error 500 Plesk\Exception\Database DB query failed: SQLSTATE[HY000] [2002] No such file or directory

[Endre]

Hello Pleskians,

I get this error when I try to log in to Plesk control panel:

Type	Plesk\Exception\Database
Message	DB query failed: SQLSTATE[HY000] [2002] No such file or directory
File	Mysql.php
Line	79

I have seen many similar error reports here and tried the recomended solutions like this one below:
https://support.plesk.com/hc/en-us/...eption-SQLSTATE-HY000-2002-Connection-refused

Still no good.

Using ssh I got these:
systemctl status mariadb.service

● mariadb.service - MariaDB 10.2.41 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
/usr/lib/systemd/system/mariadb.service.d
└─respawn.conf
Active: activating (auto-restart) (Result: exit-code) since Tue 2021-11-09 08:37:34 CET; 3s ago
Docs: man:mysqld(8)
systemd
Process: 7952 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS --basedir=/usr $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 7923 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 7921 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 7952 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
Tasks: 0
Memory: 0B
CGroup: /system.slice/mariadb.service

Nov 09 08:37:34 bvfpl01.bvfheating.net systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Nov 09 08:37:34 bvfpl01.bvfheating.net systemd[1]: Failed to start MariaDB 10.2.41 database server.
Nov 09 08:37:34 bvfpl01.bvfheating.net systemd[1]: Unit mariadb.service entered failed state.
Nov 09 08:37:34 bvfpl01.bvfheating.net systemd[1]: mariadb.service failed.

journalctl -xe

Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139620843448064 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [Note] InnoDB: Highest supported file format is Barracuda.
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [Note] InnoDB: Starting crash recovery from checkpoint LSN=17672690040
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [ERROR] InnoDB: ############### CORRUPT LOG RECORD FOUND ##################
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [Note] InnoDB: Log record type 87, page 17672690176:140723838914912. Log parsing proceeded successfully up to 17672690040. Previous log record type 128, is mult
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [Note] InnoDB: Hex dump starting 0 bytes before and ending 100 bytes after the corrupted record:
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: len 100; hex 5700331c008093000000dd9062d900000f78196a04000000010370756d0b6c617374436865636b656401050a31363336333337353035a90083b0000600038004ffffffff800680077fff02035f00000b5725f90000ddfb7b00ae000
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [Warning] InnoDB: The log file may have been corrupt and it is possible that the log scan did not proceed far enough in recovery! Please run CHECK TABLE on your
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 17672690040 between the checkpoint 17672690040 and the end 17672690270.
Nov 09 08:39:03 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:03 139621408299200 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Nov 09 08:39:04 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:04 139621408299200 [Note] InnoDB: Starting shutdown...
Nov 09 08:39:04 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:04 139621408299200 [ERROR] Plugin 'InnoDB' init function returned error.
Nov 09 08:39:04 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:04 139621408299200 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Nov 09 08:39:04 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:04 139621408299200 [Note] Plugin 'FEEDBACK' is disabled.
Nov 09 08:39:04 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:04 139621408299200 [ERROR] Unknown/unsupported storage engine: InnoDB
Nov 09 08:39:04 bvfpl01.bvfheating.net mysqld[10576]: 2021-11-09 8:39:04 139621408299200 [ERROR] Aborting
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: Failed to start MariaDB 10.2.41 database server.
-- Subject: Unit mariadb.service has failed
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mariadb.service has failed.
--
-- The result is failed.
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: Unit mariadb.service entered failed state.
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: mariadb.service failed.
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: plesk-web-socket.service holdoff time over, scheduling restart.
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: Stopped Plesk Web Socket Service.
-- Subject: Unit plesk-web-socket.service has finished shutting down
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit plesk-web-socket.service has finished shutting down.
Nov 09 08:39:04 bvfpl01.bvfheating.net systemd[1]: Starting LSB: start and stop MariaDB...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has begun starting up.
Nov 09 08:39:05 bvfpl01.bvfheating.net mysql[10593]: Starting MariaDB.211109 08:39:05 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Nov 09 08:39:05 bvfpl01.bvfheating.net mysql[10593]: 211109 08:39:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Nov 09 08:39:06 bvfpl01.bvfheating.net mysql[10593]: /etc/rc.d/init.d/mysql: line 263: kill: (10618) - No such process
Nov 09 08:39:06 bvfpl01.bvfheating.net mysql[10593]: ERROR!
Nov 09 08:39:06 bvfpl01.bvfheating.net systemd[1]: mysql.service: control process exited, code=exited status=1
Nov 09 08:39:06 bvfpl01.bvfheating.net systemd[1]: Failed to start LSB: start and stop MariaDB.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has failed.
--
-- The result is failed.
Nov 09 08:39:06 bvfpl01.bvfheating.net systemd[1]: Unit mysql.service entered failed state.
Nov 09 08:39:06 bvfpl01.bvfheating.net systemd[1]: mysql.service failed.
Nov 09 08:39:06 bvfpl01.bvfheating.net grafana-server[2227]: t=2021-11-09T08:39:06+0100 lvl=eror msg="Alert Rule Result Error" logger=alerting.evalContext ruleId=3 name="Mail server memory usage" error="tsdb.HandleRequest() error Could not find execu

My /etc/my.conf file looks like this:

bind-address = ::ffff:127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in Understanding and administering systemd :: Fedora Docs

innodb_force_recovery=1

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

I am running vps with the below versions:

Product version: Plesk Obsidian 18.0.39.1
OS version: CentOS 7.9.2009 x86_64
Build date: 2021/10/14 23:00
Revision: abd80734589dbcc9d72ab53622ef5737a658fb43

Everything is up-to-date.
I don't know what else can I do.
Please help me as all our websites and applications are down because of this error.
Thank you!
Best regards,
Endre

 

[Endre]

Additional info.
I have free disk space: df -h

Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 3.9G 12M 3.9G 1% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/vg-lv_root 77G 29G 45G 40% /
/dev/sda1 477M 206M 242M 46% /boot
tmpfs 798M 0 798M 0% /run/user/0

plesk repair all

DB query failed: SQLSTATE[HY000] [2002] No such file or directory

I have set innodb_force_recovery=6 and now I get these:

Server Error

500 Plesk\Exception\Database​

DB query failed: SQLSTATE[HY000]: General error: 1036 Table 'SessionContexts' is read only, query was: DELETE FROM `SessionContexts` WHERE (`sessionId` IN (SELECT `sessions`.`sess_id` FROM `sessions` WHERE (`modified` + `lifetime` < 1636453645)))

Type	Plesk\Exception\Database
Message	DB query failed: SQLSTATE[HY000]: General error: 1036 Table 'SessionContexts' is read only, query was: DELETE FROM `SessionContexts` WHERE (`sessionId` IN (SELECT `sessions`.`sess_id` FROM `sessions` WHERE (`modified` + `lifetime` < 1636453645)))
File	Mysql.php
Line	79

service mariadb restart

Redirecting to /bin/systemctl restart mariadb.service
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

systemctl status mariadb.service -l

● mariadb.service - MariaDB 10.2.41 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
/usr/lib/systemd/system/mariadb.service.d
└─respawn.conf
Active: activating (start) since Tue 2021-11-09 11:10:39 CET; 1s ago
Docs: man:mysqld(8)
systemd
Process: 29052 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 29775 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 29773 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 29804 (mysqld)
Tasks: 11
Memory: 43.0M
CGroup: /system.slice/mariadb.service
└─29804 /usr/sbin/mysqld --basedir=/usr

Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: Number of pools: 1
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: Using SSE2 crc32 instructions
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: Disabling background log and ibuf IO write threads.
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: Completed initialization of buffer pool
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: Highest supported file format is Barracuda.
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: innodb_force_recovery=6 skips redo log apply
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: 5.7.36 started; log sequence number 0
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [Note] InnoDB: !!! innodb_force_recovery is set to 6 !!!
Nov 09 11:10:39 bvfpl01.bvfheating.net mysqld[29804]: 2021-11-09 11:10:39 140619093067968 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds

lsof '/var/lib/mysql/aria_log_control'

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 29192 mysql 6uW REG 253,0 52 522539 /var/lib/mysql/aria_log_control
mysqld 31520 mysql 6u REG 253,0 52 522539 /var/lib/mysql/aria_log_control

Any suggestions, please?

 

[Bitpalast]

To recover from this situation you probably need to skip the redo log of your database server when starting it up, because the redo log is damaged. This means that some transactions that were done before it crashed will be lost.

Before you try anything like that, I recommend to have a full copy of your MySQL data directory, e.g.
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

After you have a full copy, you can play around with the situation, because by copying the backup back to the original location you can go back to the current state.

See the second post from InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT for how to proceed. No guarantees.

I'd also consider a hard disk check, because there must be a reason why the redo log has been damaged. Maybe your disk has a problem?

 

[Endre]

Before you try anything like that, I recommend to have a full copy of your MySQL data directory, e.g.
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

After you have a full copy, you can play around with the situation, because by copying the backup back to the original location you can go back to the current state.

Thank you, Peter. Now I have a copy of the database.
I have tried to upgrade mariadb 10.2 to 10.5 but it failed:
yum upgrade -y

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.man.poznan.pl
* epel: mirror.cherryservers.com
* extras: ftp.man.poznan.pl
* updates: ftp.vectranet.pl
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:10.2.41-1.el7.centos will be updated
---> Package MariaDB-server.x86_64 0:10.5.13-1.el7.centos will be an update
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================
Updating:
MariaDB-server x86_64 10.5.13-1.el7.centos mariadb 26 M

Transaction Summary
========================================================================================================================================================
Upgrade 1 Package

Total download size: 26 M
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
MariaDB-server-10.5.13-1.el7.centos.x86_64.rpm | 26 MB 00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction

******************************************************************
A MySQL or MariaDB server package (MariaDB-server-10.2.41-1.el7.centos.x86_64) is installed.

Upgrading directly from MySQL 10.2 to MariaDB 10.5 may not
be safe in all cases. A manual dump and restore using mysqldump is
recommended. It is important to review the MariaDB manual's Upgrading
section for version-specific incompatibilities.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf
files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages. Usually this command will
list the packages you should remove:
rpm -qa | grep -i '^mysql-'

You may choose to use 'rpm --nodeps -ev <package-name>' to remove
the package which contains the mysqlclient shared library. The
library will be reinstalled by the MariaDB-shared package.
- Install the new MariaDB packages supplied by MariaDB Foundation
- Ensure that the MariaDB server is started
- Run the 'mysql_upgrade' program

This is a brief description of the upgrade process. Important details
can be found in the MariaDB manual, in the Upgrading section.
******************************************************************
error: %pre(MariaDB-server-10.5.13-1.el7.centos.x86_64) scriptlet failed, exit status 1
Error in PREIN scriptlet in rpm package MariaDB-server-10.5.13-1.el7.centos.x86_64
MariaDB-server-10.2.41-1.el7.centos.x86_64 was supposed to be removed but is not!
Verifying : MariaDB-server-10.2.41-1.el7.centos.x86_64 1/2
Verifying : MariaDB-server-10.5.13-1.el7.centos.x86_64 2/2

Failed:
MariaDB-server.x86_64 0:10.2.41-1.el7.centos MariaDB-server.x86_64 0:10.5.13-1.el7.centos

Complete!

I can not use mysqldump as my problem is that mysql server can not start.
I am tried and desperate now.

 

[Bitpalast]

 How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.x on Linux?

Applicable to: Plesk for Linux Question How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.X on Linux? Answer This article provides instructions for upgrading MySQL 5.5 to 5.6/5.7 and Mari...

support.plesk.com

 

[Endre]

 How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.x on Linux?

Applicable to: Plesk for Linux Question How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.X on Linux? Answer This article provides instructions for upgrading MySQL 5.5 to 5.6/5.7 and Mari...

support.plesk.com

Yes, I have seen that one and started as it said in the "Automatic method" but it failed just like in the above report.
Since then I have removed MariaDB-server-10.2.41-1.el7.centos.x86_64 using this method: Removing a package without its dependencies in CentOS or RHEL and installed MariaDB 10.5 using yum now with success.
But the error is the same as the database is still corrupted ( I assume ).

 

[Bitpalast]

As said before, it is very likely that you need to follow the advice in InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT and remove the log file. Else it probably won't work again.

 

[Endre]

As said before, it is very likely that you need to follow the advice in InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT and remove the log file. Else it probably won't work again.

I did that. I set innodb_log_checksums = ON in my.cnf and I have deleted those log files. Restarted the VPS and still the same.
Doesn't matter now. I am reinstalling the VPS from scratch.
Thank you for your help.
Best regards,
Endre

 

[Bitpalast]

If there is no log file it is impossible that you got the same error on startup of the database server. It cannot be the missing log entries error, because there is no redo log. In that case, another error must have shown up.

 

[mow]

Since then I have removed MariaDB-server-10.2.41-1.el7.centos.x86_64 using this method: Removing a package without its dependencies in CentOS or RHEL and installed MariaDB 10.5 using yum now with success.

Well, the preinst scriptlet explicitly told you

A manual upgrade is required.

(as in, dump the databases, uninstall old version, install new version, ingest the dumps)
But you went and tried to use the old database with the new version.
A database that was already broken at that point, if I understand you correctly. That doesn't really help things along.

 

[Endre]

dump the databases

that was not possible, since the database server could not be started
it could have been way much easier to fix it if it was not broken

 

[Bitpalast]

that was not possible, since the database server could not be started

You can always copy all databases and tables while MariaDB is offline:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
This is a good way of having a kind of "full backup" before anything is done to the database server or client.

 

[Endre]

You can always copy all databases and tables while MariaDB is offline

I do have a "raw" backup copy of the database files but using them on another server is quite difficult for me as I am not an SQL expert.
Myisam table files are easy to copy and use on another server but innodb is not my friend in this case.

 

[mow]

that was not possible, since the database server could not be started
it could have been way much easier to fix it if it was not broken

yeah, but when the instructions say you need to do that because it won't work otherwise, and you don't do that and it doesn't work, is it a surprise?

 

[Endre]

is it a surprise?

It is not a surprise, it is a reason to ask for help in the community forum.

 

[MihaiNecreala]

The last lines of the /var/log/mysql/error.log will indicate the problem there. Check for any incorrect syntax on the /etc/mysql/my.cnf or on any other mariadb related conf files.