Issue DB query failed: "SET sql_mode = ''" Error: SQLSTATE[HY000] [2002] No such file or directory File Mysql.php Line 68

[kvant.kako]

Hi,

Got an error:

500 Plesk\Exception\Database​

DB query failed: "SET sql_mode = ''" Error: SQLSTATE[HY000] [2002] No such file or directory

Type	Plesk\Exception\Database
Message	DB query failed: "SET sql_mode = ''" Error: SQLSTATE[HY000] [2002] No such file or directory
File	Mysql.php
Line	68

Accessed server using ssh and ran command: grep mariadb /var/log/syslog

Here is the result:

Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] /usr/sbin/mariadbd (mysqld 10.6.3-MariaDB-1:10.6.3+maria~bionic) starting as process 54
59 ...
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Number of pools: 1
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Using Linux native AIO
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Completed initialization of buffer pool
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: 128 rollback segments are active.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please
 wait ...
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: 10.6.3 started; log sequence number 56246262715; transaction id 484563175
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] Plugin 'FEEDBACK' is disabled.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibil
ity with old my.cnf files.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Warning] 'innodb-large-prefix' was removed. It does nothing now and exists only for compatibi
lity with old my.cnf files.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds
 work.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [Note] Server socket created on IP: '127.0.0.1'.
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [ERROR] Can't start server: Bind on TCP/IP port. Got error: 22: Invalid argument
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
Sep 14 13:28:24 ip-172-26-12-43 mariadbd[5459]: 2021-09-14 13:28:24 0 [ERROR] Aborting
Sep 14 13:28:24 ip-172-26-12-43 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Sep 14 13:28:24 ip-172-26-12-43 systemd[1]: mariadb.service: Failed with result 'exit-code'.

There are several articles with nearly similar problem which are caused by running out of disk space, so I ran df as well:

Filesystem     1K-blocks     Used Available Use% Mounted onli
udev             1999436        0   1999436   0% /dev
tmpfs             402816      852    401964   1% /runil
/dev/xvda1      81253764 23611916  57625464  30% /
tmpfs            2014076        0   2014076   0% /dev/shms
tmpfs               5120        0      5120   0% /run/lock
tmpfs            2014076        0   2014076   0% /sys/fs/cgroup
/dev/loop0         25600    25600         0 100% /snap/amazon-ssm-agent/4046
/dev/loop1         56832    56832         0 100% /snap/core18/2074
/dev/loop2        101760   101760         0 100% /snap/core/11606
/dev/loop3         56832    56832         0 100% /snap/core18/2128
/dev/loop4        101888   101888         0 100% /snap/core/11420
/dev/loop5         34176    34176         0 100% /snap/amazon-ssm-agent/355220
tmpfs             402812        0    402812   0% /run/user/1000

Seems disk space is OK.

As the log stated about port usage I also checked it and ran: lsof -i :3306
and there was no output.

Any ideas?

 

[WebHostingAce]

Please post your my.cnf

 

[WebHostingAce]

Also try,

#service mysql stop
#service mysql start

 

[IgorG]

caused by running out of disk space, so I ran df as well:

I would check free inodes too, with

# df -i

 

[kvant.kako]

Please post your my.cnf

File location is: /etc/mysql/my.cnf

Content:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# 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.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
bind-address = ::ffff:127.0.0.1
local-infile=0

[client]
default-character-set = utf8mb4

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = true

innodb_read_only_compressed=OFF

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

[mysql]
default-character-set = utf8mb4

There is also another one on /etc/alternatives/my.cnf. Shall I post this one as well?

 

[kvant.kako]

Also try,

#service mysql stop
#service mysql start

root@ip-172-26-12-43:/home/ubuntu# service mysql stop
Failed to get properties: Unit name [email protected] is neither a valid invocation ID nor unit name.
Failed to get properties: Unit name [email protected] is neither a valid invocation ID nor unit name.

root@ip-172-26-12-43:/home/ubuntu# service mysql start
Failed to get properties: Unit name [email protected] is neither a valid invocation ID nor unit name.
Failed to get properties: Unit name [email protected] is neither a valid invocation ID nor unit name.
Job for mariadb.service failed because the control process exited with error code.

 

[kvant.kako]

I would check free inodes too, with

# df -i

root@ip-172-26-12-43:/home/ubuntu# df -i
Filesystem       Inodes  IUsed   IFree IUse% Mounted on
udev             499859    308  499551    1% /dev
tmpfs            503519    572  502947    1% /run
/dev/xvda1     10240000 756581 9483419    8% /
tmpfs            503519      1  503518    1% /dev/shm
tmpfs            503519      6  503513    1% /run/lock
tmpfs            503519     18  503501    1% /sys/fs/cgroup
/dev/loop0           16     16       0  100% /snap/amazon-ssm-agent/4046
/dev/loop1        10803  10803       0  100% /snap/core18/2074
/dev/loop2        12850  12850       0  100% /snap/core/11606
/dev/loop3        10803  10803       0  100% /snap/core18/2128
/dev/loop4        12850  12850       0  100% /snap/core/11420
/dev/loop5           16     16       0  100% /snap/amazon-ssm-agent/3552
tmpfs            503519     11  503508    1% /run/user/1000

 

[IgorG]

mysqld 10.6.3-MariaDB-1:10.6.3+maria~bionic

This version of MariaDB is not supported. According to Software Requirements:

• MariaDB 5.5-10.5

 

[kvant.kako]

This version of MariaDB is not supported. According to Software Requirements:

But it was working earlier. We installed PLESK about 6 months ago and it was operational. I think at some point it did update automatically.

 

[kvant.kako]

But it was working earlier. We installed PLESK about 6 months ago and it was operational. I think at some point it did update automatically.

Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Received SIGINT.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped target Timers.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped Daily Cleanup of Temporary Directories.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Closed Load/Save RF Kill Switch Status /dev/rfkill Watch.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopping ACPI event daemon...
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopping Authorization Manager...
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped Discard unused blocks once a week.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped Daily apt upgrade and clean activities.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped Daily apt download activities.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopping Availability of block devices...
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped target Cloud-init target.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped Execute cloud user/final scripts.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped Apply the settings specified in cloud-config.
Sep 14 06:56:05 ip-172-26-12-43 blkdeactivate[1813]: Deactivating block devices:
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped target Cloud-config availability.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopped target Graphical Interface.
Sep 14 06:56:05 ip-172-26-12-43 systemd[1]: Stopping Accounts Service...

Checked the syslogs. Sept 13 was working all normally. Sept 14mit has restarted and after restart the problem has started. It means respectively, that there is no issue with incompatibility of DB version. Any other suggestions?

 

[Bitpalast]

You said you checked port 3306 with no avail, what is the output of:

lsof -i -P -n | grep LISTEN | grep 3306

?

 

[kvant.kako]

You said you checked port 3306 with no avail, what is the output of:

lsof -i -P -n | grep LISTEN | grep 3306

?

ubuntu@ip-172-26-12-43:~$ lsof -i -P -n | grep LISTEN | grep 3306
ubuntu@ip-172-26-12-43:~$

 

[Bitpalast]

Which version of Ubuntu are you using?

 

[Tskito]

Hello, problem solved (for us) by
Stopping service while working on it
Update Linux
Update Plesk
Restart Server