Resolved PHP mysqli_connect() not working for just one site after upgrade from Ubuntu 20 to Ubuntu 22.

[philglau]

Server operating system version

Ubuntu 22.04.2 LTS

Plesk version and microupdate number

Plesk 18.0.50

Did an upgrade of our server from Ubuntu 20.x to 22.x

Afterwards, all site work fine when connecting to the mariadb database except one.

In fact two seperate sites use the same database. One can connects to the the database using the same credentials and works as it did before. However the other site gives

Warning: mysqli_connect(): (HY000/2002): Connection refused in /path/to/my/connection_script.php

I've double checked that the user/pass are still being correctly loaded in the problem site but no joy.

Is there some possible 'jail' that has been activated that is cause MariaDB to reject that mysqli_connect() from one domain but not from another domain?? (Both domains are on the same server.) Prior to upgrade we did extensive testing of the migration from Ubuntu 20 to Ubuntu 22 on our DEV server which duplicates the Plesk setup of our live server and everything worked fine there. (and has for the many weeks since we moved DEV to 22)

We have several sites that use the same connection_script.php like:

/mnt/vhosts1/path/to/connection_script.php​

/mnt/vhosts2/path/to/connection_script.php​

Where 'site1' happily connects to the database as before, but 'site2' throws the error. the 'connection_script.php' code and credentials are identical on each site

On the problem site I can also shell into that particular domain and connect successfully to the database in question using the same credentials.

All I can think is that somebody hit our PROD server scripts during the transition and possibly triped some 'failed login bans' but I don't see anything in IP Address Banning for the fail2ban jails (we had previously white listed all of our IP addresses associated with the server, so it shouldn't be catching them anyways??)

The sites in question are on PHP 7.4. We were in the process of upgrading toe PHP 8.2 so I tried an emergency move to PHP 8.2, but got the same connection error on the site in question.

Any help would be greatly appreciated.

 

[philglau]

Update:

Seems to be that mysql_connect() can't connect to '127.0.0.1' anymore but can connect to 'localhost'.

Something must have changed during the upgrade from Ubuntu 20 to Ubuntu 22.

During the upgrade I was asked:

Configuration file '/etc/mysql/mariadb.cnf'
 ==> Modified (by you or by a script) since installation.
 ==> Package distributor has shipped an updated version.
   What would you like to do about it ?  Your options are:
    Y or I  : install the package maintainer's version
    N or O  : keep your currently-installed version
      D     : show the differences between the versions
      Z     : start a shell to examine the situation
 The default action is to keep your current version

And I selected 'N' at the time.

 

[Dave W]

what port is your mysql server listening on in /etc/my.cnf?

 

[manni]

Are you using the exact same PHP handler on both VHosts?

 

[Peter Debik]

Look for "skip-name-resolve" in my.cnf and remove it, then restart the database.

 

[philglau]

Thank you everyone for your help so far. I appreciate it.

Resolved sites on server connecting by changing any references to '127.0.0.1' to 'localhost' for scripts running directly hosted by the Plesk server.

I think I may have resolved the secondary issue referenced below:

From Tools & Settings > Database Servers > Settings > click Local MySQL Settings I had to 'uncheck' then 're-check' the Allow Local MySQL server to accept external connections as shown here. It was already previously 'checked', but cycling it allowed the server to receive external connections referenced below.

Something must have gotten slightly messed up during the upgrade that caused it to lose this setting (but leave the box 'checked) to allow external connections.

I'm leaving the information below for the next person who might have this problem

Secondary issue is now that our remote sites can't connect.

Server is confirmed running on port 3306 and Plesk Firewall is open to the specific outside connections referenced below. I've also confirmed that the user/host/db settings are correct in the mysql table. My remote IP address is whitelisted and not in any of the fail2ban jails.

in the directory /etc/mysql were the following files that were not modified during the update (which occurred last night Feb 2023)

drwxr-xr-x 2 root root 4.0K Feb 19 17:25 conf.d/
-rw------- 1 root root  261 Mar 25  2021 debian.cnf
-rwxr-xr-x 1 root root 1.7K Nov 12 23:48 debian-start*
-rw-r--r-- 1 root root  911 Mar 27  2021 mariadb.cnf
-rw-r--r-- 1 root root 1.1K Nov 12 23:48 mariadb.cnf.dpkg-dist
drwxr-xr-x 2 root root 4.0K Feb 19 17:33 mariadb.conf.d/
-rw-r--r-- 1 root root  990 Mar 27  2021 my.cnf
-rw-r--r-- 1 root root  839 Aug  3  2016 my.cnf.fallback

I tried adding skip-name-resolve to both 'my.cnf' as well as 'mariadb.cnf' and I still can't connect from remote locations.

From the command line where I'm trying to remote connect I get:

(base) philglau Desktop $ mysql -p -u my_user_name -h our.host.com
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'our.host.com' (61)

This previously worked fine. (I get same connection errors from from our remote scripts trying to connect to Plesk server as well.)

If I telnet to port 3306 I get a 'connection refused':

(base) philglau Desktop $ telnet our.host.com 3306
Trying 108.xxx.x.x...
telnet: connect to address 108.xxx.x.x: Connection refused
telnet: Unable to connect to remote host

If I try the http or https ports it connects
(base) philglau Desktop $ telnet our.host.com 80
Trying 108.xxx.x.x...
Connected to our.host.com.
Escape character is '^]'.

At this point I feel like there is something blocking the general connection to mariadb on port 3306 from external locations. With Telnet, I should be getting something like

(base) philglau Desktop $ telnet 1.2.4.3 3306
Trying 1.2.4.3...
Connected to 1.2.4.3.
Escape character is '^]'.
Y
5.5.5-10.4.13-MariaDB??
                       9V'\zqIY??-djiP4S%[{xk/mysql_native_password
^CConnection closed by foreign host.

rather than

telnet: connect to address 108.xxx.x.x: Connection refused

Additional Details
In the 'mysql' table, our users are granted permission by IP address normally. From the user table of mysql database: (assume that our.host.com matches 1.2.3.4 for this example)

| user_1          | 1.2.3.4
| user_2          | 1.2.3.4
| user_3          | 1.2.3.4
| user_1          | 5.6.7.8
| user_2          | 5.6.7.8
| user_1          | localhost
| user_2          | localhost
| user_1          | 127.0.0.1

Contents of 'my.cnf':

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
bind-address = ::
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local-infile=0
skip-name-resolve

when 'skip-name-resolve' is added, then neither 'localhost' nor '127.0.0.1' users are able to connect from local scripts on the server in question.

I get the following for 'localhost' when skip-name-resolve is active

Warning: mysqli_connect(): (HY000/1130): Host '::1' is not allowed to connect to this MariaDB server in /mnt/vhosts/mysite.com/path/mysql_connect_script.php on line xxx

and for '127.0.0.1' I get the same error as previously started the thread:

Warning: mysqli_connect(): (HY000/2002): Connection refused in /path/to/my/connection_script.php

 

[Peter Debik]

bind-address in /etc/my.cnf needs to be set to :: and nothing else.

 

[philglau]

bind-address in /etc/my.cnf needs to be set to :: and nothing else.

It currently is: (??)

[mysqld]
bind-address = ::

 

[Peter Debik]

If it's not an issue with binding to a specific address and if you are sure that on the server the port 3306 is open, maybe a firewall in front of the server is blocking port 3306. Else there is no reason why a connect to the database server should not be possible. If you can connect, but not login, then something is wrong with the access credentials. There is not much more to test. Just these things.

 

[Dave W]

I dont think its a firewall issue. I'm not 100% sure but I think MariaDB 10.6 handles localhost connections via a socket rather than a port, I skimmed over some information pertaining to this recently but dont have the time atm to dig for it. Ive had a similar issue connecting via localhost and 127.0.0.1 recently on a 10.6 MariaDB server.

 

[Dave W]

I can confirm that a brand new Plesk install on Almalinux 8 with Mariadb 10.6 results in this:

[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1
^C


[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h localhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1379
Server version: 10.6.12-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.

MariaDB [(none)]> \q
Bye

[root@srv ~]# cat /etc/my.cnf
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0

and

[root@srv ~]# cat /etc/my.cnf
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = ::
local-infile=0

results in

[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1
^C


[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h localhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.6.12-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.

MariaDB [(none)]> \q
Bye

Default users;

MariaDB [(none)]> SELECT user,host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| admin       | localhost |
| apsc        | localhost |
| mariadb.sys | localhost |
| mysql       | localhost |
| nrpe        | localhost |
| phpmyadmin  | localhost |
| roundcube   | localhost |
+-------------+-----------+
7 rows in set (0.001 sec)

In Plesk Allow remote connections from any host is enabled.

[root@srv ~]# telnet 127.0.0.1 3306
Trying 127.0.0.1...


(hangs)

Progress:

[root@srv ~]# systemctl stop psa-firewall.service
[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1
ERROR 2002 (HY000): Can't connect to server on '127.0.0.1' (115)
[root@srv ~]#

Changed my.cnf back to its default

[root@srv ~]# cat /etc/my.cnf
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0

and now SUCESS!

[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.6.12-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.

MariaDB [(none)]> \q
Bye
[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h localhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 28
Server version: 10.6.12-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.

MariaDB [(none)]> \q
Bye

Start the firewall again

What the actual....?

[root@srv ~]# systemctl start psa-firewall.service
[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.6.12-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.

MariaDB [(none)]> \q
Bye
[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h localhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.6.12-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.

MariaDB [(none)]> \q
Bye

 

[Dave W]

So, MariaDB 10.6 takes localhost connections via a SOCKET...where as it takes connections to 127.0.0.1 over a TCP connection;

[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h localhost -e "status"
--------------
mysql  Ver 15.1 Distrib 10.6.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          167
Current database:
Current user:           admin@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.12-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 9 min 39 sec

Threads: 3  Questions: 3266  Slow queries: 0  Opens: 60  Open tables: 53  Queries per second avg: 5.640
--------------

[root@srv ~]# mysql -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1 -e "status"
--------------
mysql  Ver 15.1 Distrib 10.6.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          175
Current database:
Current user:           admin@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.12-MariaDB MariaDB Server
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 9 min 59 sec

Threads: 3  Questions: 3382  Slow queries: 0  Opens: 60  Open tables: 53  Queries per second avg: 5.646
--------------

Not sure why it just started to work after restarting the firewall though.

 

[Peter Debik]

I think there are two issues in this thread. On is name resolution, the other is external access. It's been discussed before here:

Question - skip-name-resolve, bind-address and backup

Hi, I have upgradet to Maria DB 10.6.8 and since then I am having problems by using skip-name-resolve = 1 in my my.cnf. Warning: Database "xxx" Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: WARNING: Forcing protocol to TCP due to option specification. Please...

talk.plesk.com

Name resolution: My previous advice here was to remove skip-name-resolve completely, but in other threads here I had also seen that some users seem to have access credentials for localhost, some for 127.0.0.1, and I cannot be sure why there is such a mix of settings. I'd simply try either
skip-name-resolve = 0
or
skip-name-resolve = 1
in your configuration. Restart database server after each change, then test if it works.

 

[Dave W]

The issue is though that the my.cnf is from the plesk repo, so Plesk should investigate this further.

 

[Peter Debik]

No problem, simply open a ticket with Plesk support and it will be investigated on your server.

 

[learning_curve]

So, MariaDB 10.6 takes localhost connections via a SOCKET...where as it takes connections to 127.0.0.1 over a TCP connection;

~~

Not sure why it just started to work after restarting the firewall though.

Yes, we're on different Server OS (forum sig) so obviously, there will be differences. Having said that, below are our versions of your last two CLI commands @Dave W and the end results are unsurpisingly, reasonably similar. Was going to post re: the Socket question, but saw that you'd already answered that.

***:~# mariadb -u admin -p`cat /etc/psa/.psa.shadow` -h localhost -e "status"
--------------
mariadb  Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:        9760
Current database:  
Current user:        admin@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.6.12-MariaDB-1:10.6.12+maria~ubu2204-log mariadb.org binary distribution
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            8 hours 8 min 14 sec

Threads: 10  Questions: 182488  Slow queries: 0  Opens: 1100  Open tables: 1093  Queries per second avg: 6.229

***:~# mariadb -u admin -p`cat /etc/psa/.psa.shadow` -h 127.0.0.1 -e "status"
--------------
mariadb  Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:        9805
Current database:  
Current user:        admin@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.6.12-MariaDB-1:10.6.12+maria~ubu2204-log mariadb.org binary distribution
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
TCP port:        3306
Uptime:            8 hours 10 min 37 sec

Threads: 10  Questions: 183198  Slow queries: 0  Opens: 1100  Open tables: 1093  Queries per second avg: 6.223

Never had any real BIG problems with Maria DB and Plesk so far (and that's going all the way back to MariaDB 10.1.*) Just the occasional oddity, nearly all of which were resolved via subtle MariaDB setup changes. E.G. Sorting all of the logging / logs procedures was quite a long one. This great level of service does include when we upgraded from MariaDB 10.5.* to 10.6.* as part of a Dist Upgrade (documented by Plesk) from Ubuntu 20.04 > 22.04 ( FYI @philglau )

~~ Name resolution: My previous advice here was to remove skip-name-resolve completely, but in other threads here I had also seen that some users seem to have access credentials for localhost, some for 127.0.0.1, and I cannot be sure why there is such a mix of settings ~~

FWIW @Peter Debik Within our/etc/mysql/mariadb.cnf (in our case, /etc/mysql/my.cnf is symlinked to this, after a previous MariaDB setup change), the skip-name-resolve entry is commented out. Has been for as long as can be remembered (unless it was temporarily invoked, as part of a test and/or a fix process). To date, never had any of the issues described above (so far...) with this setup.

# Skip reverse DNS lookup of clients
# * skip-name-resolve
#

It would be useful for all of us @Dave W if the results of the Plesk investigation into your ticket & its content, can be posted on this thread afterward?

 

[Peter Debik]

Within our/etc/mysql/mariadb.cnf (in our case, /etc/mysql/my.cnf is symlinked to this, after a previous MariaDB setup change), the skip-name-resolve entry is commented out. Has been for as long as can be remembered (unless it was temporarily invoked, as part of a test and/or a fix process). To date, never had any of the issues described above (so far...) with this setup.

This should be right. That's why I advised to remove the entry altogether. But it seems that for the installation described above, it then is unclear what the setting is. So probably set to "= 0". But there are also reports where users said they had to use "= 1". I think that depended on their "user" table entries in the databse.

 

[mow]

I think that depended on their "user" table entries in the databse.

And maybe on the entry for localhost in /etc/hosts.

 

[Dave W]

simply open a ticket with Plesk support and it will be investigated on your server.

No point at the moment as it seems to be behaving correctly after restarting psa-firewall.
When I run up another server I'll test again, but there definitely seems to be something going on with Plesk + psa-firewall + MariaDB 10.6 and connection via socket or IP.