Issue Zend_Db_Adapter_Exception

[jmzbe]

moin,
I have the problem that mysql doesn't work anymore.
"Zend_Db_Adapter_Exception
SQLSTATE[HY000] [2002] No such file or directory" comes on the page after an update from Plesk via the console.

This message appears in the console:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I looked in my.cnf and the 127.0.0.1 is also available.

the stand under systemctlstatus mysql.service


mysql.service - MySQL Community Server
Geladen: geladen (/lib/systemd/system/system/mysql.service; aktiviert; Lieferantenvoreinstellung: aktiviert)
Aktiv: Aktivierung (Start-Post) (Ergebnis: Exit-Code) seit Mo 2019-07-15 07:56:12 CEST; vor 4s
Prozess: 29909 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
Prozess: 29900 ExecStartPre=/usr/share/mysql/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Haupt PID: 29909 (code=exited, status=1/FAILURE); : 29910 (mysql-systemd-s)
Aufgaben: 2
Erinnerung: 448.0K
CPU: 338ms
CGroup: /system.slice/mysql.service
└─control
├─29910 /bin/bash /usr/share/mysql/mysql/mysql-systemd-start post
└─29958─29958 Schlaf 1

Jul 15 07:56:12 serv02.ngg.sh systemd[1]: MySQL Community Server starten.....
Jul 15 07:56:14 serv02.ngg.sh systemd[1]: mysql.service: Hauptprozess beendet, code=exited, status=1/FAILURE

an that under journalctl -xe:

Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has failed.
--
-- The result is failed.
Jul 15 07:57:43 serv02.ngg.sh systemd[1]: mysql.service: Unit entered failed state.
Jul 15 07:57:43 serv02.ngg.sh systemd[1]: mysql.service: Failed with result 'exit-code'.
Jul 15 07:57:43 serv02.ngg.sh systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jul 15 07:57:43 serv02.ngg.sh systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has finished shutting down.
Jul 15 07:57:43 serv02.ngg.sh systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has begun starting up.
Jul 15 07:57:43 serv02.ngg.sh audit[30379]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/30379/status" pid=30379 comm="mysqld" requested_mask="r" denied_mask
Jul 15 07:57:43 serv02.ngg.sh audit[30379]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=30379 comm="mysqld" requested_mask="r" deni
Jul 15 07:57:43 serv02.ngg.sh audit[30379]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/30379/status" pid=30379 comm="mysqld" requested_mask="r" denied_mask
Jul 15 07:57:43 serv02.ngg.sh kernel: audit: type=1400 audit(1563170263.895:3919): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/30379/status" pid=30379 comm="mys
Jul 15 07:57:43 serv02.ngg.sh kernel: audit: type=1400 audit(1563170263.895:3920): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=30379 co
Jul 15 07:57:43 serv02.ngg.sh kernel: audit: type=1400 audit(1563170263.895:3921): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/30379/status" pid=30379 comm="mys
Jul 15 07:57:44 serv02.ngg.sh smbd[30398]: pam_unix(samba:session): session closed for user nobody
Jul 15 07:57:46 serv02.ngg.sh systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

even plesk repair all -y does not work

I really don't know what to do right now.

Thanks and greetings

 

[Monty]

Your AppArmor is not properly configured: Unable to start MySQL on Ubuntu: AVC apparmor="DENIED" operation="open"

 

[jmzbe]

Hello,
thank you, i did it the way i did in the manual and the mistake doesn't occur anymore according to my knowledge.
is at least no longer to be seen in the journalctl -xe....

but unfortunately it still doesn't work, which data is needed now to be able to reproduce it?


ul 15 11:31:19 serv02.ngg.sh kernel: docker0: port 1(vethee272d6) entered forwarding state
Jul 15 11:31:19 serv02.ngg.sh systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has failed.
--
-- The result is failed.
Jul 15 11:31:19 serv02.ngg.sh systemd[1]: mysql.service: Unit entered failed state.
Jul 15 11:31:19 serv02.ngg.sh systemd[1]: mysql.service: Failed with result 'exit-code'.
Jul 15 11:31:19 serv02.ngg.sh containerd[2549]: time="2019-07-15T11:31:19.704131439+02:00" level=info msg="shim containerd-shim started" address="/containerd-shim/moby/d723d14b17d
Jul 15 11:31:19 serv02.ngg.sh systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jul 15 11:31:19 serv02.ngg.sh systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--
-- Unit mysql.service has finished shutting down.
Jul 15 11:31:19 serv02.ngg.sh systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: systemd-devel Info Page
--

this is what i found
and the plesk repair tool isn´t work

thanks

 

[jmzbe]

I wanted to do that now, but I stopped, maybe you can still find something here.
I was told that my.cnf is linked incorrectly or incorrectly.
after which i this:
sudo apt-get --reinstall install mysql-server-5.7


update-alternatives: Warnung: Neuinstallation der Alternative /etc/mysql/mysql.cnf ist erzwungen, weil Linkgruppe my.cnf defekt ist
update-alternatives: Warnung: /etc/mysql/my.cnf wird nicht durch einen Link ersetzt
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
invoke-rc.d: initscript mysql, action "start" failed.
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: activating (auto-restart) (Result: exit-code) since Mo 2019-07-15 14:23:16 CEST; 5ms ago
Process: 22671 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Process: 22670 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
Process: 22660 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 22670 (code=exited, status=1/FAILURE)

Jul 15 14:23:16 serv02.ngg.sh systemd[1]: Failed to start MySQL Community Server.
Jul 15 14:23:16 serv02.ngg.sh systemd[1]: mysql.service: Unit entered failed state.
Jul 15 14:23:16 serv02.ngg.sh systemd[1]: mysql.service: Failed with result 'exit-code'.
dpkg: Fehler beim Bearbeiten des Paketes mysql-server-5.7 (--configure):
Unterprozess installiertes post-installation-Skript gab den Fehlerwert 1 zurück
E: Sub-process /usr/bin/dpkg returned an error code (1)
W: Operation wurde unterbrochen, bevor sie beendet werden konnte.

 

[Monty]

Check your mysql log file (probably /var/log/mysql/error.log or /var/log/mysqld.log for any additional hints why your mysql service did not start.

And if nothing helps try "plesk repair all"

 

[jmzbe]

Hi,

hier der error.log

2019-07-17T04:28:02.116375Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2019-07-17T04:28:02.116421Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2019-07-17T04:28:02.291629Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-17T04:28:02.291652Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-07-17T04:28:02.293466Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-0ubuntu0.16.04.1) starting as process 30009 ...
2019-07-17T04:28:02.298200Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-07-17T04:28:02.298229Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-07-17T04:28:02.298237Z 0 [Note] InnoDB: Uses event mutexes
2019-07-17T04:28:02.298243Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-07-17T04:28:02.298250Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-07-17T04:28:02.298269Z 0 [Note] InnoDB: Using Linux native AIO
2019-07-17T04:28:02.298600Z 0 [Note] InnoDB: Number of pools: 1
2019-07-17T04:28:02.298736Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2019-07-17T04:28:02.300729Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-07-17T04:28:02.311512Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-07-17T04:28:02.313829Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-07-17T04:28:02.325997Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-07-17T04:28:02.398665Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-07-17T04:28:02.398738Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-07-17T04:28:02.714714Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-07-17T04:28:02.715856Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-07-17T04:28:02.715881Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-07-17T04:28:02.716604Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 15662718221
2019-07-17T04:28:02.716751Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-07-17T04:28:02.716957Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2019-07-17T04:28:02.717093Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2019-07-17T04:28:02.717137Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190717 6:28:02
2019-07-17T04:28:02.721387Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-07-17T04:28:02.721403Z 0 [Note] Server hostname (bind-address): '::ffff: 127.0.0.1'; port: 3306
2019-07-17T04:28:02.722830Z 0 [ERROR] Can't create IP socket: No such file or directory
2019-07-17T04:28:02.722847Z 0 [ERROR] Can't start server: cannot resolve hostname!
2019-07-17T04:28:02.722855Z 0 [ERROR] Aborting


Thx

 

[IgorG]

Can't open the mysql.plugin table. Please run mysql_upgrade to create it

Try to run

# mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

 

[jmzbe]

# mysql_upgrade -uroot -p `cat /etc/psa/.psa.shadow`
Enter password:
mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) while connecting to the MySQL server
Upgrade process encountered error and will not continue.

I tried it and unfortunately it didn't work.

 

[Monty]

You have some further errors that need to be investigated:

2019-07-17T04:28:02.722830Z 0 [ERROR] Can't create IP socket: No such file or directory
2019-07-17T04:28:02.722847Z 0 [ERROR] Can't start server: cannot resolve hostname!

Apparently, your my.cnf is not correct.

Check your "socket=" setting in my.cnf and make sure that path exists
Also, you seem to have an unresolvable hostname and/or a non-numeric "bind-address" setting in your my.cnf. So please check and correct the bind-address setting in your my.cnf.

If unsure, post your my.cnf and all files from /etc/my.cnf.d/*.conf here

 

[jmzbe]

here the /etc/mysql/my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0


and the files in

# ls -l /etc/mysql/
insgesamt 28
drwxr-xr-x 2 root root 4096 Apr 30 06:27 conf.d
-rw------- 1 root root 317 Jul 17 06:56 debian.cnf
-rwxr-xr-x 1 root root 120 Okt 18 2017 debian-start
-rw-r--r-- 1 root root 845 Jul 17 08:40 my.cnf
-rw-r--r-- 1 root root 839 Jan 21 2017 my.cnf.fallback
-rw-r--r-- 1 root root 732 Jul 17 08:38 mysql.cnf
drwxr-xr-x 2 root root 4096 Jul 15 14:22 mysql.conf.d

but /etc/my.cnf.d/ the folder unfortunately does not exist

only this:

z# ls -l /etc/mysql/mysql.conf.d/
insgesamt 8
-rw-r--r-- 1 root root 3028 Feb 3 2017 mysqld.cnf
-rw-r--r-- 1 root root 21 Feb 3 2017 mysqld_safe_syslog.cnf

 

[Monty]

So, check all your *.cnf files for the "socket=" setting and make sure that directory does exist and is writeable for the mysql user.
Also, check that your hostname is set up correctly (FQDN), that it can be resolved and that the data in /etc/hosts is correct.
Once you're able to start mysql, run the mysql_upgrade script as suggested by @IgorG

 

[jmzbe]

this is hostname:

# hostname
serxxx.nxg.sh
# hostname -f
serxxx.ngg.sh

but in the *cnf files ist 127.0.0.1 insert


here the error.log

19-07-17T09:48:59.933262Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-07-17T09:48:59.933268Z 0 [Note] InnoDB: Using Linux native AIO
2019-07-17T09:48:59.933578Z 0 [Note] InnoDB: Number of pools: 1
2019-07-17T09:48:59.933707Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2019-07-17T09:48:59.935581Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-07-17T09:48:59.945784Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-07-17T09:48:59.948058Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setprio
rity().
2019-07-17T09:48:59.960073Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-07-17T09:49:00.044263Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-07-17T09:49:00.044340Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-07-17T09:49:00.225323Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-07-17T09:49:00.226245Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-07-17T09:49:00.226257Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-07-17T09:49:00.226817Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 15662730032
2019-07-17T09:49:00.226981Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-07-17T09:49:00.227212Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2019-07-17T09:49:00.227345Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2019-07-17T09:49:00.227365Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190717 11:49:00
2019-07-17T09:49:00.231068Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and privat
e key
2019-07-17T09:49:00.231082Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2019-07-17T09:49:00.231105Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2019-07-17T09:49:00.231168Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2019-07-17T09:49:00.231366Z 0 [Warning] Failed to open optimizer cost constant tables

2019-07-17T09:49:00.231514Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2019-07-17T09:49:00.231529Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2019-07-17T09:49:00.231579Z 0 [ERROR] Aborting


the error table 'mysql.user' doesn't exist.....How can repair this?

thx

 

[Monty]

Looks like your MySQL is seriously broken. You appear to have upgraded your MySQL server version without running the necessary upgrade scripts.

Try this:
MySQL fails to start after an upgrade: mysql.user table is damaged

 

[jmzbe]

yes very good, partial success.... Now I come back to the admin backend of Plesk, but I have the problem that I can't execute mysql_upgrade.

I get the following error message:

# mysql_upgrade
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.26, use --force if you still need to run mysql_upgrade

# mysql_upgrade --force
Checking server version.
Error occurred: Query against mysql.user table failed when checking the mysql.session.

i even had to create a my.cnf under /etc and inserted skip-grant-tables there and then he did it. now i have a /etc/my.cnf file that only has two lines and i'm considering if it just copies /etc/mysql/my.cnf to /etc/. What do you think?

 

[Monty]

Well, as I said, you have some serious database issues/corruption.

Your best bet is probably to restore the mysql.* tables from a backup, provided you still have one with working tables.
Have a look here:
Plesk upgrade fails because of missing tables in mysql database: the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure

Also check this (and pay close attention to the "Important" section):
MySQL :: MySQL 5.7 Reference Manual :: 4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables

 

[jmzbe]

first of all I'm glad that it works so far again, I made a time plesk repair all -y and he apparently could repair something, pages can be reloaded, but I think that it is still not from the table.

i have plesk docker containers running and also the database containers of MariaDB, where the DBs of the pages are stored.

I still have a Plesk backup from Friday, is it advisable to restore it somehow? what exactly would I have to restore?

thank you very much, you have supported me very much

 

[Monty]

Restoring the whole server might be overkill.
Your main problem are the mysql.* tables (database "mysql") which are corrupt.
Check your mysql log file for any indications about when the problems started. Then check the KB article from my last comment and check if you have valid backups in /var/lib/psa/dumps/ from the time before the problems started.
But before you restore any database please perform a dump of it (as mentioned in the KB), in case you need to rollback.

 

[jmzbe]

Okey, I just realized that this looks like proper work. Are there people at Plesk who can do this?
I'm just thinking about setting up a new system, as I was going to go from 500 GB to 5 TB anyway. I'm the only one who thinks about how I'll have to upload all that again, download the docker containers again and start them again, save all the data in the containers and so on....

This is another Ubuntu 16.04 LTS and I would like to upgrade to 18