Received "ERROR 2002 (HY000)" via email

ivano.buffa · June 28, 2022, 6:44am

Hi Team,

I apologize in advance as this is definitely not supported. Anyhow I hope you can get back to me on this.

I have recently installed hestiacp 1.6.0 on Ubuntu Server 20.04 LTS. However, I decided to move the /var/lib/mysql MariaDB datadir into another drive (another directory called /db located in partition /dev/sdb1).

Steps followed:
• Issued commands:

sudo systemctl stop mariadb
sudo cp -R -p /var/lib/mysql/* /db
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

• Ensured to have (among the others) these lines present in this /etc/mysql/my.cnf:

 [client]
 port=3306
 socket=/db/mysqld.sock

 [mysqld]
 socket=/db/mysqld.sock
 datadir=/db

• Issued the following command:

 sudo systemctl start mariadb

After that it seems that everything works fine: I can see the location of the new datadir and I can successfully create and drop a data base however, every 5 minutes I receive an email from my Hestia Control panel with the subject MySQL connection error on <> and the following content:

 Can't connect to MySQL localhost
 ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2)

After that I tried the following, however the email is still sent every 5 minutes (paranoid):
• Issued the following commands from root:

systemctl stop mariadb
usermod -d /db/ mysql
systemctl start mariadb	 
chmod 777 /run
chmod 777 /db

• Made a backup of these files and replaced /var/run/mysqld/mysqld.sock and /run/mysqld/mysqld.sock with /db/mysqld.sock:

/etc/mysql/my.cnf
/etc/mysql/mariadb.cnf 
/etc/logcheck/ignore.d.workstation/mariadb-server-10_6
/etc/logcheck/ignore.d.paranoid/mariadb-server-10_6
/etc/logcheck/ignore.d.server/mariadb-server-10_6

As of now the issue still stands and this is very annoying.

Any hint would be highly appreciated.

Thanks in advance.

Regards.

Raphael · June 28, 2022, 6:51am

Check the service status of mariadb - all up and running?

ivano.buffa · June 28, 2022, 6:58am

Hi,

Thanks for getting back to me on this (so quickly).

Yes it’s fine. However, at the end I can see the error stated in the subject of this post (it occurs every time I restart the service (1.5 hours ago I did it) or when I reboot the server):

ubuntu@hcp:/db$ systemctl status mariadb
● mariadb.service - MariaDB 10.6.8 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Tue 2022-06-28 06:23:52 UTC; 32min ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 698 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 719 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 755 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=cd /usr/bin/..; /usr/bin/galera_recovery; [ $? -eq 0 ] && sys>
Process: 1194 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 1205 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 1047
Status: “Taking your SQL requests now…”
Tasks: 8 (limit: 1066)
Memory: 68.4M
CGroup: /system.slice/mariadb.service
└─1047 /usr/sbin/mariadbd

Jun 28 06:23:46 hcp.ubuntuserver.local systemd[1]: Starting MariaDB 10.6.8 database server…
Jun 28 06:23:50 hcp.ubuntuserver.local mariadbd[1047]: 2022-06-28 6:23:50 0 [Note] /usr/sbin/mariadbd (server 10.6.8-MariaDB-1:10.6.8+maria~focal) starting >
Jun 28 06:23:52 hcp.ubuntuserver.local systemd[1]: Started MariaDB 10.6.8 database server.
Jun 28 06:23:52 hcp.ubuntuserver.local /etc/mysql/debian-start[1209]: Upgrading MySQL tables if necessary.
Jun 28 06:23:53 hcp.ubuntuserver.local /etc/mysql/debian-start[1286]: Checking for insecure root accounts.
Jun 28 06:23:53 hcp.ubuntuserver.local debian-start[1290]: ERROR 2002 (HY000): Can’t connect to local server through socket ‘/run/mysqld/mysqld.sock’ (2)

BTW… I’m amazed of the great work you did guys with HestiaCP.

Thanks.

Regards.

ivano.buffa · June 28, 2022, 7:00am

Perhaps this is better:

ivano.buffa · June 28, 2022, 7:03am

One more hint. Normal functionalities appear to be there:

eris · June 28, 2022, 8:50am

Mails are generated by:

github.com

hestiacp/hestiacp/blob/main/bin/v-update-sys-rrd-mysql

#!/bin/bash
# info: update MySQL rrd
# options: PERIOD
#
# example: v-update-sys-rrd-mysql
#
# This function is for updating mysql rrd database and graphic.

#----------------------------------------------------------#
#                Variables & Functions                     #
#----------------------------------------------------------#

# Argument definition
period=${1-daily}

# Includes
# shellcheck source=/etc/hestiacp/hestia.conf
source /etc/hestiacp/hestia.conf
# shellcheck source=/usr/local/hestia/func/main.sh
source $HESTIA/func/main.sh

This file has been truncated. show original

ivano.buffa · June 28, 2022, 9:50am

Thansk @eris - I checked the config files below but I couldn’t find any reference to my error message received via email:

/etc/hestiacp/hestia.conf
/usr/local/hestia/func/main.sh
/usr/local/hestia/func/db.sh
$HESTIA/conf/hestia.conf

Perhaps I am missing something?

eris · June 28, 2022, 10:30am

github.com

hestiacp/hestiacp/blob/0b60c2c003aa86cd5c92d65c329c5a9996b68b3d/bin/v-update-sys-rrd-mysql#L77

      
        
                    RRA:AVERAGE:0.5:288:797 \
                    RRA:MAX:0.5:1:600 \
                    RRA:MAX:0.5:6:700 \
                    RRA:MAX:0.5:24:775 \
                    RRA:MAX:0.5:288:797
            fi
            
            
if [ "$period" = 'daily' ]; then
                mysql_connect "$host"
                query='SHOW GLOBAL STATUS'
                status=$(mysql_query "$query" 2>/dev/null)
                if [ $? -ne 0 ]; then
                    active=0
                    slow=0
                else
                    active=$(echo "$status"|grep 'Queries'|cut -f 2)
                    slow=$(echo "$status"|grep 'Slow_queries'|cut -f 2)
                fi
            
            
    # Updating rrd
                rrdtool update $RRD/db/mysql_$host.rrd N:$active:$slow

Does a query in db.sh

Did something change with the root password

ivano.buffa · June 28, 2022, 4:51pm

After digging and digging I even managed to bring by default the mysqld.pid file (which by default resides in /run/mysqld) into the new MariaDB datadir.
I also managed to fix (temporarily) this issue by following the steps below but unfortunately as the symlink /run/mysqld → /db does NOT survive to the reboot, this cannot be considered a solution AT ALL:

 cd /run
 mv mysqld/ mysqld.backup
 ln -s /db mysqld
 systemctl start mariadb

Eventually I realized that the proper solution consists of all the steps I published in the first post of this thread but without modifying the path for the socket within the /etc/mysql/my.cnf file:

So instead of this:

 [client]
 port=3306
 socket=/db/mysqld.sock

 [mysqld]
 socket=/db/mysqld.sock
 datadir=/db

I should have done this in file:

 [client]
 port=3306
 socket=/var/run/mysqld/mysqld.sock

 [mysqld]
 socket=/var/run/mysqld/mysqld.sock
 datadir=/db

Before I close, however, I would be glad if someone could tell me how to make the symlink below survive to a reboot in ubuntu with MariaDB:

ln -s /db /var/run/mysqld

Thanks!

system · July 28, 2022, 4:52pm

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.