Warning: mysqli_connect(): (HY000/2002): No such file or directory

Question

I'm trying to install vanilla forums on my Mac, and for this I just created a database and a user from the MySQL command line:

mysql> CREATE DATABASE vanilla;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'vanilla_user3'@'localhost' IDENTIFIED BY 'vanilla_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'vanilla_user3'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

So I try to connect using the following code:

$con=mysqli_connect("localhost","vanilla_user3","vanilla_password","vanilla");
if (mysqli_connect_errno($con)) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

but unfortunately, I get an error saying

Warning: mysqli_connect(): (HY000/2002): No such file or directory in /Users/kramer65/Sites/vanilla/info.php on line 3 Failed to connect to MySQL: No such file or directory

Any idea where I'm going wrong?

score 218 · Accepted Answer · edited May 10 '20 at 12:10

218

Alright, I just found the solution. The problem turned out to be that the host shouldn't have been localhost, but 127.0.0.1. I always thought localhost and 127.0.0.1 was the same, but it turned out to be different.

So maybe as a tip for future users, always use the ip when in doubt.

edited May 10 '20 at 12:10

Dharman

30,962
25
85
135

answered Nov 19 '13 at 14:37

kramer65

50,427
120
308
488

3

The answer by mohitmayank is correct. If `localhost` does not work, you need to tell PHP where to find your mysql socket file. – Nilpo Mar 18 '17 at 01:21
see my comment to #Adam's Nov 21 '16 answer – Jack Holt Apr 25 '17 at 20:39
1

Similar issue but simpler: MySQL not starting can cause the same error. Client's server hadn't been rebooted in months, after reboot mysql wasn't running and nobody thought to check. Too worried about all the codebase and "who caused this bug". lol (Just because Apache is up and you can ssh into the server, that's not a guarantee MySQL is up, too) – TheSatinKnight Jun 16 '18 at 02:29
1

@TheSatinKnight I observe this same error like once in a couple of months too, which seems to be related to what you say, as MySQL server being temporarily unavailable at that exact moment. – yenren Mar 11 '19 at 06:55
This answer worked for me partially. I also had to define [mysql_native_password](https://stackoverflow.com/a/50776838/11932012). – tash Mar 08 '22 at 03:02

score 22 · Answer 2 · answered Nov 10 '16 at 13:19

22

I had the same problem but the issue was something related to php.ini file.

I had to edit these two lines in /etc/php.ini (or wherever your php.ini is located):

mysql.default_socket = /tmp/mysql.sock
mysqli.default_socket = /tmp/mysql.sock

Restart apache server to make sure the changes are reflected.

sudo apachectl restart

answered Nov 10 '16 at 13:19

mohitmayank

631
2
7
20

2

This is correct answer for using socket instead TCP/IP. – Yuda Prawira Sep 05 '17 at 17:33
mysql.sock was located at /var/run/mysqld/mysqld.sock for me' – Agnel Vishal Nov 08 '18 at 14:16
Follow this up by using the instructions here for Mac users https://stackoverflow.com/questions/4219970/warning-mysql-connect-2002-no-such-file-or-directory-trying-to-connect-vi – Let A Pro Do IT Apr 12 '19 at 01:56

hpaknia · Answer 3 · 2022-12-28T13:58:20.007

If using docker use the mysql container name as host name of mysql. For example if this is the docker compose file:

mysqldb:
    image: mysql:5.7.22
    container_name: mysqlHost
    ports:
        - "33099:3306"

Host name will be mysqlHost and port will be 3306 and not 33099!

If you are not using docker compose, docker ps reveals the running container names.

On a different note, to connect mysql server on the host machine from docker, connect to host.docker.internal:

$conn = mysqli_connect("host.docker.internal", "mysqluser", "mysqlpass", "dbname");

score 7 · Answer 4 · answered Nov 21 '16 at 07:53

7

Let's say your MAMP MySQL Port is set to 8889 like it is by default. Sometimes localhost alone is not enough in which case you have to put the MySQL port in there too so you would do localhost:8889 or localhost:{whatever your MySQL port number is}. I'm still new to MySQL so I don't know the reason why, but to anyone out there who recently got the message: Warning: mysqli::mysqli(): (HY000/2002): No such file or directory in ... adding the MySQL port number onto localhost was the fix for me.

answered Nov 21 '16 at 07:53

Adam

2,070
1
14
18

3

In what I consider to be a bug, localhost does indeed need to have the port (even if it is the default of 3306) added to it in the $host parameter but no other hostname does. So you would use "localhost:3306" for the $host parameter. Other hostnames and IP addresses will make use of the $port parameter. But the $port parameter is ignored if $host is 'localhost'. I believe the problem traces to something in the PHPdoc for __construct(): "When possible, pipes will be used instead of the TCP/IP protocol". – Jack Holt Apr 25 '17 at 20:31

score 3 · Answer 5 · edited May 10 '20 at 05:39

3

find php.ini file in /opt/lampp/etc/ then edit:

mysql.default_socket = /tmp/mysql.sock
mysqli.default_socket = /tmp/mysql.sock

after this in your DB connect file change from localhost to 127.0.0.1. everything will be ok. however, if you attempted the config.inc.php file, make sure its all set to 127.0.0.1, not localhost.

edited May 10 '20 at 05:39

aminography

21,986
13
70
74

answered May 10 '20 at 01:44

Andrew Njuki

31
3

this is the right answer. when providing "localhost" as the server name mysql tries to connect via the socket file. There should be a missconfiguration between php.ini default_socket, and the my.cnf socket setting – Marek Roj Feb 06 '23 at 09:10

Fellipe Sanches · Answer 6 · 2023-03-09T16:07:20.797

As additional verifications, check if ipv6 is enable on your localhost environment, so disable it.

Some times localhost envirement aren't ready to work with ipv6, mainly when your local machine queries a remote ip on the internet.

On Windows machines, comment also the ::1 localhost entry from the hosts file.

If you can, a good way to check if that is the issue is:

Execute the built-in web server from php with:

php -S localhost:7000

Now, access your script by browser and check on console if the response comes from the loopback address ipv4 127.0.0.1 or from the loopback ipv6 ::1.

What we don't want:

[Thu Mar  9 10:58:12 2023] [::1]:51636 Accepted
[Thu Mar  9 10:58:37 2023] [::1]:51636 [200]: GET /my_script.php
[Thu Mar  9 10:58:37 2023] [::1]:51636 Closing

What we want:

[Thu Mar  9 11:12:10 2023] 127.0.0.1:58730 Accepted
[Thu Mar  9 11:12:10 2023] 127.0.0.1:58728 [200]: GET /my_script.php
[Thu Mar  9 11:12:10 2023] 127.0.0.1:58728 Closing

If you can't run the built-in server, check the logs!

Note that there is no problem about using ipv6 with php or MySQL, but your environment needs to be ready to it.

score -1 · Answer 7 · edited May 10 '20 at 12:10

-1

set port in host:

$con = mysqli_connect("localhost:**3306**","vanilla_user3","vanilla_password","vanilla");

edited May 10 '20 at 12:10

Dharman

30,962
25
85
135

answered Nov 28 '19 at 03:40

sergvb

37
3

Warning: mysqli_connect(): (HY000/2002): No such file or directory

7 Answers7

Linked

Related