Resolved Mariadb not working - DB query failed: SQLSTATE[HY000] [2002] No such file or directory

[Crash79797]

Server operating system version

CentOS Linux 7.9.2

Plesk version and microupdate number

Plesk Obsidian v18.0.58 #2

Plesk Obsidian v18.0.58
CentOS Linux 7.9.2
3 domains hosted on server
MariaDB version 10.11.8

Hello,
For the last 48hrs my Mysql databases have been down (including the Plesk web interface)...... Been searching online but I've not been able to resolve the issue so need some help.

Plesk we interface page shows the (I'm guessing generic) error:

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

All Mysql based pages on the websites show a 500 server error.

When I try to restart (or start) either the Mariadb, Mysql or Mysqld servers I get the error:

'Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.'

Output of systemctl status mariadb.service is:

mariadb.service - MariaDB 10.11.8 database server
Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf, override.conf
Active: failed (Result: exit-code) since Sat 2024-05-18 22:16:05 UTC; 1s ago
Docs: man:mariadbd(8)
systemd
Process: 3070 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 2968 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: 2962 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 3070 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"

May 18 22:16:05 <hostname> mariadbd[3070]: 2024-05-18 22:16:05 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
May 18 22:16:05 <hostname> mariadbd[3070]: 2024-05-18 22:16:05 0 [Note] InnoDB: Starting shutdown...
May 18 22:16:05 <hostname> mariadbd[3070]: 2024-05-18 22:16:05 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
May 18 22:16:05 <hostname> mariadbd[3070]: 2024-05-18 22:16:05 0 [Note] Plugin 'FEEDBACK' is disabled.
May 18 22:16:05 <hostname> mariadbd[3070]: 2024-05-18 22:16:05 0 [ERROR] Unknown/unsupported storage engine: InnoDB
May 18 22:16:05 <hostname> mariadbd[3070]: 2024-05-18 22:16:05 0 [ERROR] Aborting
May 18 22:16:05 <hostname> systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
May 18 22:16:05 <hostname> systemd[1]: Failed to start MariaDB 10.11.8 database server.
May 18 22:16:05 <hostname> systemd[1]: Unit mariadb.service entered failed state.
May 18 22:16:05 <hostname> systemd[1]: mariadb.service failed.

Output of journalctl -xe is:

-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit plesk-web-socket.service has finished starting up.
--
-- The start-up result is done.
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: [2024-05-18 22:13:34.296] 32539:6649280e47f40 ERR [panel] DB query failed: SQLSTATE[HY000] [2002] No such file or directory:
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 0: /usr/local/psa/admin/plib/Db/Adapter/Pdo/Mysql.php:79
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: Db_Adapter_Pdo_Mysql->query(string 'SET sql_mode = ''')
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 1: /usr/local/psa/admin/plib/CommonPanel/Application/Abstract.php:113
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: CommonPanel_Application_Abstract::initDbAdapter()
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 2: /usr/local/psa/admin/plib/Session/Helper.php:176
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: Plesk\Session\Helper::initStorage()
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 3: /usr/local/psa/admin/plib/CommonPanel/Application/Abstract.php:48
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: CommonPanel_Application_Abstract->run()
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 4: /usr/local/psa/admin/plib/CommonPanel/Application/Abstract.php:30
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: CommonPanel_Application_Abstract::init()
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 5: /usr/local/psa/admin/plib/pm/Bootstrap.php:18
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: pm_Bootstrap::init()
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 6: /usr/local/psa/admin/plib/sdk.php:11
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: require_once(string '/usr/local/psa/admin/plib/sdk.php')
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: 7: /usr/local/psa/admin/plib/WebSocket/bin/ws-server.php:3
May 18 22:13:34 <hostname> sw-engine-pleskrun[32539]: ERROR: Plesk\Exception\Database: DB query failed: SQLSTATE[HY000] [2002] No such file or directory (Mysql.php:79)
May 18 22:13:34 <hostname> systemd[1]: plesk-web-socket.service: main process exited, code=exited, status=1/FAILURE
May 18 22:13:34 <hostname> systemd[1]: Unit plesk-web-socket.service entered failed state.
May 18 22:13:34 <hostname> systemd[1]: plesk-web-socket.service failed.
May 18 22:13:36 <hostname> grafana[1494]: logger=ngalert.scheduler rule_uid=Q3Zz2YC7k org_id=1 version=1 fingerprint=46974b97e95c130c attempt=1 now=2024-05-18T22:13:30Z rule_uid=Q3Zz2YC7k org_id=1 t=2024-05-18T22:13:36.

There is no mysql.sock file in /var/lib/mysql/ or anywhere on the server (but I haven't been able to start Mysql to generate it?)

The path to mysql.sock is stated in /etc/my.cnf and /etc/psa/psa.conf but not in /usr/local/psa/admin/conf/php.ini

Nothing is being written to the mysql error logs

Can't get a mysqldump as it gives the error:

mysqldump: Got error: 2002: "Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2)" when trying to connect

I hadn't changed or modified the server at all prior to this happening but I think there was an update (which may possibly mean this is caused by a corrupt file or something).

Any help or pointers would be greatly apprieacted (my Plesk is through a 3rd party vendor so I don't have access to Plesk support).

Many thanks

 

[Maarten]

You can still get access to Plesk Support. It's free for the first month:

https://support.plesk.com/hc/en-us/articles/12388090147095-How-to-get-support-directly-from-Plesk

 

[Crash79797]

@Maarten. Good to know, will try here first as Mysql and Mariadb are not directly supported by Plesk though (as there maintained by other people/companies)

 

[Maarten]

Does this article help?

https://support.plesk.com/hc/en-us/articles/12377560870935-Unable-to-start-MySQL-MariaDB-after-upgrading-it-on-a-Plesk-for-Linux-server-Unknown-unsupported-storage-engine-InnoDB

 

[Kaspar@Plesk]

In addition to the excellent suggestion by @Maarten. I recommend looking for errors in the MariaDB log /var/log/mariadb/mariadb.log, which might contain more details to find the cause of the issue.

@Maarten. Good to know, will try here first as Mysql and Mariadb are not directly supported by Plesk though (as there maintained by other people/companies)

Even tough MariaDB and MySQL are software products maintained by other organizations/companies, the MariaDB server component is a (vital) part of Plesk. Plesk can't function without it (as you have experienced). Which why an issue like this is actually within the scope of Plesk support (if your license is eligible for support).

 

[Crash79797]

@Maarten. Tried that article already (and just tried again to make sure). Doesn't work for me:

# mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` -h127.0.0.1 --protocol=tcp shutdown

mysqladmin: connect to server at '127.0.0.1' failed
error: 'Can't connect to server on '127.0.0.1' (115)'
Check that mariadbd is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

Which then leads to:

# systemctl restart mariadb

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

 

[Crash79797]

@Kaspar@Plesk /var/log/mariadb/mariadb.log file is there but it is empty (blank).

Thanks for the support info (as you said Mysql is a critical part of Plesk). Will contact support now.

 

[Crash79797]

Well, a big thank you to @Maarten. and @Kaspar@Plesk I contacted Plesk support and it's been fixed, the sites are now back up and running

For anyone who has a similar problem this is what Plesk Support did:

1. The server is not able to start with the following error:
   ```
   May 19 13:01:35 <hostname> mariadbd[19849]: 2024-05-19 13:01:35 0 [ERROR] InnoDB: File ./ib_logfile0 was not found
   ```
   
2. The "ib_logfile0" file is indeed missing
3. There is "/var/lib/mysql_original/" folder modified 2 days ago.
   
   
4. There is also a modification dated 2 days ago:
   
   ```
   [root@new ~]# diff /etc/my.cnf_original /etc/my.cnf
   19c19
   < innodb_buffer_pool_size=12G
   ---
   > innodb_buffer_pool_size=100M
   38d37
   <
   ```

I rolled back /var/lib/mysql_original folder and after that, I was able to start the MariaDB server.

Oddly, I hadn't modified anything prior to the crash (and definatly didn't modify innodb_buffer_pool_size or delete ib_logfile0)?