PHPMyAdmin connection issues

[kevinbentlage]

Hi There,

I've just setup a fresh DirectAdmin server, based on Debian 11 (x64), and did some default config on on it.

I'am having issue with PhpMyAdmin and MySQL client, it will not login to any database.

I already did some research, and it has to do something with the "Host" PhpMyAdmin users to reach MySQL. By default it's configured to 'localhost' in the config.inc.php:

$cfg['Servers'][$i]['host'] = 'localhost';

When i change this to:

$cfg['Servers'][$i]['host'] = '127.0.0.1';

It works! But when i'll rebuild (or update) PHPMyAdmin, this value will be restored to the default (localhost).

I did all my tests with the default "da_admin" user.

My /etc/my.cnf config:

[client-server]

[mysqld]
datadir = /data/mysql
socket = /data/mysql/mysql.sock

[client]
socket = /data/mysql/mysql.sock

[server]
port = 3306
character-set-server = utf8
thread_concurrency = 8
innodb_thread_concurrency = 8
local-infile = 0
max_allowed_packet = 1G
skip_name_resolve = 1

wait_timeout = 28800
interactive_timeout = 28800

key_buffer_size = 128M
sort_buffer_size = 1M
#join_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 2M

query_cache_type = 0
query_cache_size = 1024M
query_cache_limit = 8M

thread_cache_size = 8

tmp_table_size = 1G
max_heap_table_size = 256M
table_open_cache = 4000

innodb_lock_wait_timeout = 100
innodb_buffer_pool_size = 256M
innodb_buffer_pool_instances = 4
innodb_log_file_size = 256M
innodb_file_per_table = 1
innodb_log_buffer_size = 4M
innodb_file_format = Barracuda

log_error = /var/log/mysql/error.log

(Yes, we're using /data instead of /var/lib/mysql)

Also on the CLI it does not work:

[root@xxx01 phpMyAdmin]$ mysql -h localhost -u da_admin -p
Enter password:
ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.sock' (2)

But works with 127.0.0.1

[root@xxx01 phpMyAdmin]$ mysql -h 127.0.0.1 -u da_admin -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 212
Server version: 10.6.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

My /etc/hosts does seem right:

[root@xxx01 phpMyAdmin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost

::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
1.2.3.4 xxx01.myhost.com xxx01

Telnet works on both hosts:

[root@xxx01 phpMyAdmin]$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
X
5.5.5-10.6.7-MariaDB2b"}sHN.;�{}#0g[sOromysql_native_password^CConnection closed by foreign host.

[root@xxx01 phpMyAdmin]$ telnet localhost 3306
Trying ::1...
Connected to localhost.
Escape character is '^]'.
X
5.5.5-10.6.7-MariaDB3I1T8V-y?�k!]2!L=-f?Ctmysql_native_password^CConnection closed by foreign host.

Netstat:

[root@xxx01 phpMyAdmin]$ netstat -tnlp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 693561/mariadbd
tcp6 0 0 :::3306 :::* LISTEN 693561/mariadbd

MySQL user:

MariaDB [mysql]> select Host, User from user WHERE user = 'da_admin';
+-----------+----------+
| Host | User |
+-----------+----------+
| localhost | da_admin |
+-----------+----------+
1 row in set (0.001 sec)

I've also tested to switch skip_name_resolve on or off, but this does not make a difference!

What do i miss here?

 

[Zhenyapan]

You can permit modifying of PhpMyadmin's config.inc.php by: chattr +i config.inc.php
Or check apparmor settings for mysql maybe some restrictions here, or specify mysql host/socket in php.ini
Or for "Can't connect to local server through socket '/tmp/mysql.sock'" you can create symlink to real socket place.

 

[kevinbentlage]

You can permit modifying of PhpMyadmin's config.inc.php by: chattr +i config.inc.php
Or check apparmor settings for mysql maybe some restrictions here, or specify mysql host/socket in php.ini
Or for "Can't connect to local server through socket '/tmp/mysql.sock'" you can create symlink to real socket place.

Yeah, but the default setup should work fine right? Normally you don't need to change these values because the default (as delivered by the DA setup script) should work just fine?

AppArmor seems not configured for MySQL at this moment:

[cb-kb@xxx01 apparmor.d]$ sudo aa-status
apparmor module is loaded.
4 profiles are loaded.
4 profiles are in enforce mode.
/usr/bin/man
man_filter
man_groff
named
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode.
/usr/sbin/named (1159) named
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

 

[wtptrs]

Your config entry

[client]
socket = /data/mysql/mysql.sock

seems to be overwritten somewhere. Check for instances of "socket = /tmp/mysql.sock" in /etc/mysql, /etc/mysql/conf.d etc.

 

[kevinbentlage]

Found the issue!

Because we're running mysql in /data/mysql (instead of the default /var/lib/mysql) we have to change the default socket locations in PHP.ini (which default points to /var/lib/mysql/mysql.sock).

This is because MySQL uses a socket when "localhost" is defined as a host. When u use "127.0.0.1" it uses a TCP/IP connection.

In file /usr/local/php81/lib/php.ini

pdo_mysql.default_socket = /data/mysql/mysql.sock
mysqli.default_socket = /data/mysql/mysql.sock

 

[Zhenyapan]

as i said in first reply: "or specify mysql host/socket in php.ini"
but as for me it's better to use default mysql path, I'm just mounting needed device here (separate nvme).