why does PHP DB 127.0.0.1 works , but localhost doesn't work

qtwrk · December 2022

Hi,

I have few sites running on a ubuntu 20 server on few different version of PHP

what bugs me is that , if I configure DB to 127.0.0.1 it works , however it breaks if I set to localhost

I have been googling around and confirm by phpinfo(); that mysqld.socket is set in correct position confirmed by netstat commands

skip name resolve = OFF
skip networking = OFF

what else am I missing ?

yoursunny · December 2022

localhost may resolve to ::1.
Is your MySQL listening on IPv6?

qtwrk · December 2022

@yoursunny said:
localhost may resolve to ::1.
Is your MySQL listening on IPv6?

actually , yes , thanks for the hint , but I make it bind-address = 0.0.0.0 or =127.0.0.1 doesn't work either , double checked with netstat

matheny · December 2022

@yoursunny said:
localhost may resolve to ::1.
Is your MySQL listening on IPv6?

yeah I think it's likely this too

qtwrk · December 2022

root@server /etc/mysql # netstat -lnp | grep mariadb
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      127999/mariadbd
unix  2      [ ACC ]     STREAM     LISTENING     1330618  127999/mariadbd      /run/mysqld/mysqld.sock
root@server /etc/mysql # vi my.cnf
root@server /etc/mysql # systemctl restart mariadb
root@server /etc/mysql # netstat -lnp | grep mariadb
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      129125/mariadbd
tcp6       0      0 ::1:3306                :::*                    LISTEN      129125/mariadbd
unix  2      [ ACC ]     STREAM     LISTENING     1341089  129125/mariadbd      /run/mysqld/mysqld.sock
root@server /etc/mysql # vi my.cnf
root@server /etc/mysql # systemctl restart mariadb
root@server /etc/mysql # netstat -lnp | grep mariadb
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      129651/mariadbd
tcp6       0      0 :::3306                 :::*                    LISTEN      129651/mariadbd
unix  2      [ ACC ]     STREAM     LISTENING     1354812  129651/mariadbd      /run/mysqld/mysqld.sock

none of these works with localhost

JabJab · December 2022

You sure you are using TCP/IP when specifying localhost? I've already seen software that when you provide localhost it was trying to use socket rather than TCP/IP. Do you see it actually spitting errors about localhost:3306, not some /var/lib/mysql/blablabla

also check what does localhost actually resolves to https://www.php.net/manual/en/function.dns-get-record.php#refsect1-function.dns-get-record-examples

qtwrk · December 2022

@JabJab said:
You sure you are using TCP/IP when specifying localhost? I've already seen software that when you provide localhost it was trying to use socket rather than TCP/IP. Do you see it actually spitting errors about localhost:3306, not some /var/lib/mysql/blablabla

also check what does localhost actually resolves to https://www.php.net/manual/en/function.dns-get-record.php#refsect1-function.dns-get-record-examples

I think so

on my wp site , if I do

define( 'DB_HOST', 'localhost:/run/mysqld/mysqld.sock' );

it seems working , also with 127.0.0.1, just not localhost

JabJab · December 2022

@qtwrk said: define( 'DB_HOST', 'localhost:/run/mysqld/mysqld.sock' );

What the hell is this

qtwrk · December 2022

@JabJab said:

@qtwrk said: define( 'DB_HOST', 'localhost:/run/mysqld/mysqld.sock' );

What the hell is this

I think it's tell wordpress to connect via that specific socket path

qtwrk · December 2022

@JabJab said:
also check what does localhost actually resolves to https://www.php.net/manual/en/function.dns-get-record.php#refsect1-function.dns-get-record-examples

now it's interesting

Array ( [0] => Array ( [host] => localhost.mydomain.com [class] => IN [ttl] => 3789 [type] => HINFO [cpu] => RFC8482 [os] => ) )

somehow PHP takes localhost to localhost.mydomain.com !?

JabJab · December 2022

Most likely you don't have localhost specified in /etc/hosts and it just search in domain provided.

qtwrk · December 2022

@JabJab said:
Most likely you don't have localhost specified in /etc/hosts and it just search in domain provided.

yeah , tried that , did not work , compared to other cpanel server, its PHP also gives similar result as

Array ( [0] => Array ( [host] => localhost.myotherdomain.com[class] => IN [ttl] => 3789 [type] => HINFO [cpu] => RFC8482 [os] => ) )

and I did try /etc/hosts with entry 127.0.0.1 localhost.mydomain.com as well as ther is an existing entries

127.0.0.1   localhost
# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

and php gives Array ( [0] => Array ( [host] => localhost.mydomain.com [class] => IN [ttl] => 0 [type] => A [ip] => 127.0.0.1 ) )

but still no joy

xTom · December 2022

127.0.0.1 means TCP/IP connection to MySQL service.
localhost means you use UNIX socket with the connection.

That's why localhost:/run/mysqld/mysqld.sock is working.

I recommend to use localhost, which will be faster and more secure.

If you wish to use TCP/IP, change to 127.0.0.1 instead.

qtwrk · December 2022

@xTom said:
If you wish to use TCP/IP, change to 127.0.0.1 instead.

thanks for the tip, if you see the title, you will see I want to use localhost, but it doesn't work...

That's whole thread about...

xTom · December 2022

@qtwrk said:

@xTom said:
If you wish to use TCP/IP, change to 127.0.0.1 instead.

thanks for the tip, if you see the title, you will see I want to use localhost, but it doesn't work...

That's whole thread about...

Did you try

unix:/run/mysqld/mysqld.sock

or

unix:///run/mysqld/mysqld.sock

qtwrk · December 2022

@xTom said:

unix:/run/mysqld/mysqld.sock

or

unix:///run/mysqld/mysqld.sock

thanks for the hint , but both don't work...

webcraft · December 2022

Have you checked if your MySQL server is actually listening on the socket address?

[mysqld]
socket=/run/mysql/mysql.sock

qtwrk · December 2022

@webcraft said:
Have you checked if your MySQL server is actually listening on the socket address?
socket=/run/mysql/mysql.sock

yes, it is on socket

root@server /etc/mysql # netstat -lnp | grep mariadb
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      129651/mariadbd
tcp6       0      0 :::3306                 :::*                    LISTEN      129651/mariadbd
unix  2      [ ACC ]     STREAM     LISTENING     1354812  129651/mariadbd      /run/mysqld/mysqld.sock

capric98 · December 2022

maybe check your mysql user permission see if it was allowed to login in from localhost?

qtwrk · December 2022

@capric98 said:
maybe check your mysql user permission see if it was allowed to login in from localhost?

for sure it does , even tried with @%

capric98 · December 2022

@qtwrk said:

@capric98 said:
maybe check your mysql user permission see if it was allowed to login in from localhost?

for sure it does , even tried with @%

Then socket file permission? Okay I have no idea now...

qtwrk · December 2022

@capric98 said:

@qtwrk said:

@capric98 said:
maybe check your mysql user permission see if it was allowed to login in from localhost?

for sure it does , even tried with @%

Then socket file permission? Okay I have no idea now...

yes , if I explicitly use localhost:/run/mysqld/mysqld.sock in wordpress conf , it works , but for other non-wp CMS , this socket definition doesn't work and localhost alone errors out

thanks for trying , appreciated it

amj · December 2022

Let's experiment!

prepare: run multiple MySQL/MariaDB instances
1: mysql -h localhost -P (port other than first instance)
2: mysql -h 127.0.0.1 -P (port other than first instance)

qtwrk · December 2022

@amj said:
Let's experiment!

prepare: run multiple MySQL/MariaDB instances
1: mysql -h localhost -P (port other than first instance)
2: mysql -h 127.0.0.1 -P (port other than first instance)

root@server ~ # mysql -u xxx -h 127.0.0.1 --port 3306 --password
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 213672
Server version: 10.6.11-MariaDB-1:10.6.11+maria~ubu2004-log mariadb.org binary distribution

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)]> Bye
root@server ~ # mysql -u xxx -h localhost --port 3306 --password
Enter password:
WARNING: Forcing protocol to  TCP  due to option specification. Please explicitly state intended protocol.
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 213713
Server version: 10.6.11-MariaDB-1:10.6.11+maria~ubu2004-log mariadb.org binary distribution

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)]> Bye

SSH command line seems work for both localhost and 127.0.0.1

qtwrk · December 2022

something more paculiar

it was working for localhost

then I set up a master-slave replication and edit the my.cnf file , restart , start the replication , then it stopped working for localhost but only 127.0.0.1

so today I grabbed another idling server, did same env , with exact same my.cnf , but it just still works on second server

1q1 · December 2022

what's the result of
ping localhost

?

1q1 · December 2022

@qtwrk said: actually , yes , thanks for the hint , but I make it bind-address = 0.0.0.0 or =127.0.0.1 doesn't work either , double checked with netstat

binding to 0.0.0.0 doesn't automatically mean it also binds to ipv6.

qtwrk · December 2022

@1q1 said:
what's the result of
ping localhost

?

on SSH 127.0.0.1

on PHP shell_exec

string(383) "PING localhost(localhost (::1)) 56 data bytes 64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.021 ms 64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.026 ms 64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.027 ms --- localhost ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2035ms rtt min/avg/max/mdev = 0.021/0.024/0.027/0.002 ms "

deqi · December 2022

i fell for this one a long time ago, mysqltuner.pl recommends to set

skip-name-resolve

ref: https://www.vionblog.com/skip-name-resolve-to-speed-up-mysql-and-avoid-problems/

so mysql users where host like 'localhost' cant resolve to 127.0.0.1

=> fix?
a) change mysql host to 127.0.0.1
b) update user config
c) remove skip-name-resolve

decide for youself

qtwrk · December 2022

@deqi said:
i fell for this one a long time ago, mysqltuner.pl recommends to set

skip-name-resolve

ref: https://www.vionblog.com/skip-name-resolve-to-speed-up-mysql-and-avoid-problems/

so mysql users where host like 'localhost' cant resolve to 127.0.0.1

=> fix?
a) change mysql host to 127.0.0.1
b) update user config
c) remove skip-name-resolve

decide for youself

I never set skip name resolve to ON though...

1q1 · December 2022

@qtwrk said: on SSH 127.0.0.1

on PHP shell_exec

sure enough, your MySQL is not binding to IPv6.

you need either to bind your mysql server to IPv6, or
edit /etc/hosts

remove localhost and add 127.0.0.1 localhost

Howdy, Stranger!

Categories

In this Discussion

why does PHP DB 127.0.0.1 works , but localhost doesn't work

Comments

Howdy, Stranger!

Quick Links

Categories

In this Discussion

why does PHP DB 127.0.0.1 works , but localhost doesn't work

Comments