Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


why does PHP DB 127.0.0.1 works , but localhost doesn't work
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

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

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 ?

«1

Comments

  • yoursunnyyoursunny Member, IPv6 Advocate

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

    Thanked by 2matheny nick_
  • @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

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

    yeah I think it's likely this too

  • qtwrkqtwrk Member
    edited 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 :(

  • JabJabJabJab Member
    edited 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

  • @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

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

    What the hell is this :D

  • @JabJab said:

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

    What the hell is this :D

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

  • 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 !?

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

  • @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 :(

  • xTomxTom Member, Patron Provider
    edited 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.

  • qtwrkqtwrk Member
    edited 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...

  • xTomxTom Member, Patron Provider

    @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
    
  • @xTom said:

    unix:/run/mysqld/mysqld.sock
    

    or

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

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

  • webcraftwebcraft Member
    edited December 2022

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

    [mysqld]
    socket=/run/mysql/mysql.sock
    
  • @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
    
  • maybe check your mysql user permission see if it was allowed to login in from localhost?

  • @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 @%

  • @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... o:)

  • @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... o:)

    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

  • Let's experiment! :wink:

    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)

  • @amj said:
    Let's experiment! :wink:

    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

  • 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 :'(

  • 1q11q1 Member
    edited December 2022

    what's the result of
    ping localhost

    ?

  • @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.

  • @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 "

  • 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

  • @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...

  • @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

Sign In or Register to comment.