76

I am trying to use a PHP connection to connect MySQL Database which is on phpmyadmin. Nothing fancy about the connection just trying to see whether the connection is successful or not. I am using MAMP to host the database, the connection I am trying to use is this: 

<?php
$servername = "127.0.0.1";
$username = "root";
$password = "root";

try {
    $conn = new PDO("mysql:host=$servername;dbname=AppDatabase", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}
?>

I have been using postman to test to see if the connection is working, but I keep receiving this error message:

Connection failed: SQLSTATE[HY000] [2002] Connection refused

Before I was receiving an error message of:

Connection failed: SQLSTATE[HY000] [2002] No such file or directory

This was because I had set the servername to localhost, through changing this to the IP address it has given me connection refused and I have no idea what is wrong.

Any help regarding this would be appreciated.

Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
Jonck
  • 1,587
  • 1
  • 10
  • 15
  • It sounds like your MySQL service is not listening on 127.0.0.1 TCP. If it is actually running at all, it may be that it is on a local socket but not TCP. Does `mysql:localhost;dbname=AppDatabase` make a difference? – Michael Berkowski Apr 01 '15 at 18:14
  • ... or was the "no such file or directory" happening with `localhost`? If that was the case, either the socket path was incorrect in php.ini, or MySQL is not running at all. – Michael Berkowski Apr 01 '15 at 18:15
  • In case someone else has this problem for the same reason I did, for me the problem was that I made a mistake in my code which caused me to be logged in to the database with the wrong host name. – Donald Duck Jan 13 '19 at 16:14
  • 1
    You might get the same error for out of disk space for MySQL. If your on Ubuntu, check the disk spaces ```$ df -h``` – Sadee Dec 12 '19 at 10:57

10 Answers10

68

I found the reason why the connection was not working, it was because the connection was trying to connect to port 8888, when it needed to connect to port 8889.

$conn = new PDO("mysql:host=$servername;port=8889;dbname=AppDatabase", $username, $password);

This fixed the problem, although changing the server name to localhost still gives the error.

Connection failed: SQLSTATE[HY000] [2002] No such file or directory

But it connects successfully when the IP address is entered for the server name.

Jonck
  • 1,587
  • 1
  • 10
  • 15
  • 1
    Try to do a `ping localhost` to see wether it ping to the loopback address. Could be your hosts files is corrupted.Using the IP over the hostname will provide a small performance boost anyway – DarkBee Apr 01 '15 at 21:17
  • 4
    How did you figure out the right port? Isn't by default the local host port 8080? Does that change when you use the tcp protocol? – Pasha Skender Sep 04 '16 at 19:38
  • I believe this has something to do with ipv6. The host localhost for me resolves to `::1`, so I'm suspecting the underlying mysql client doesn't have ipv6 support (even for local addresses). Changing it to 127.0.0.1 resolved it for me, like the last line of the answer suggests. – Tit Petric Aug 20 '17 at 17:04
  • I got the same error message when using Laradock.io (Laravel 8), however then I needed to update it to use my real IP-adress to solve it. I guess due to the code access was done inside the docker container. – olle.holm Mar 14 '21 at 21:38
10

I spent quite a few hours in a docker environment where all my containers are docker containers and I was using Phinx for migrations. Just to share different responses with different configurations.

Working solutions

"host" => "db", // {docker container's name} Worked
"host" => "172.22.112.1", // {some docker IP through ipconfig - may change on every instance - usually something like 172.x.x.x} Worked

Non-working solutions

"host" => "127.0.0.1", // SQLSTATE[HY000] [2002] Connection refused
"host" => "docker.host.internal", //  SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name does not resolve
"host" => "localhost", //  SQLSTATE[HY000] [2002] No such file or directory

I was running Phinx in following way.

docker compose --env-file .env run --rm phinx status -e development
GoharSahi
  • 488
  • 1
  • 8
  • 22
7

In my case MySQL sever was not running. I restarted the MySQL server and issue was resolved.

//on ubuntu server
sudo /etc/init.d/mysql start

To avoid MySQL stop problem, you can use the "initctl" utility in Ubuntu 14.04 LTS Linux to make sure the service restarts in case of a failure or reboot. Please consider talking a snapshot of root volume (with mysql stopped) before performing this operations for data retention purpose[8]. You can use the following commands to manage the mysql service with "initctl" utility with stop and start operations.

$ sudo initctl stop mysql
$ sudo initctl start mysql

To verify the working, you can check the status of the service and get the process id (pid), simulate a failure by killing the "mysql" process and verify its status as running with new process id after sometime (typically within 1 minute) using the following commands.

$ sudo initctl status mysql         # get pid
$ sudo kill -9 <pid>                # kill mysql process
$ sudo initctl status mysql         # verify status as running after sometime

Note : In latest Ubuntu version now initctl is replaced by systemctl

vinod
  • 2,850
  • 1
  • 18
  • 23
  • So what's your answer as to why the connection failed error is coming? Seems like this is addressing how to start and stop mysql service, might have missed it. – oemb1905 Jul 24 '22 at 18:12
  • Please check my first line where I clearly stated that , In my case server automatically stopped and I was not aware about that. So to solve the issue where server stopped I just used above statements to prevent unexpected server halting issue. Hope that make sense. – vinod Jul 26 '22 at 10:06
  • Sure, that's easy to see, but/and since it's not addressing the error codes or the inability to connect to the database, it seems like an alternate issue, etc. I was coming here last night to debug some problems on a mariadb instance and spent like 5 minutes reading your post to see if I was missing something, but/and it was merely informing people how to start/stop a service, not why you might get those errors when the service is confirmed running, as is the case with the OP. Usually, people providing answers to unrelated questions, note that at the top of the post to help readers. – oemb1905 Jul 26 '22 at 18:44
3

Using MAMP I changed the host=localhost to host=127.0.0.1. But a new issue came "connection refused"

Solved this by putting 'port' => '8889', in 'Datasources' => [

Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
Quentin Vaisala
  • 31
  • 1
2

1. server cert verify flag

I was required to use SSL to connect, and needed to set PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to false in the new PDO options array, besides the entry PDO::MYSQL_ATTR_SSL_CA for the CA file.

Without it, the mysql log on the server helpfully mentions

2021-07-27 17:02:51 597605 [Warning] Aborted connection 597605 to db: 'unconnected' user: 'unauthenticated' host: '192.168.10.123' (This connection closed normally without authentication)

where I was definitely passing the right db and username and such in the DSN. An empty options array will show the db and user in the error log, at least. I am sure there is a valid, technical reason for these things.
I am adding this information so I can more easily find it, the next time I end up on this page..

2. host in connection string

In the context of SSL, I've also seen the error when using the IP address instead of the hostname to connect, if the hostname was used as CN (Common Name) in the certificate.

MSpreij
  • 1,142
  • 13
  • 20
1

Using MAMP ON Mac, I solve my problem by renaming

/Applications/MAMP/tmp/mysql/mysql.sock.lock

to

/Applications/MAMP/tmp/mysql/mysql.sock
Dey
  • 842
  • 9
  • 21
0

For me was php version from mac instead of MAMP, PATH variable on .bash_profile was wrong. I just prepend the MAMP PHP bin folder to the $PATH env variable. For me was:

/Applications/mampstack-7.1.21-0/php/bin

  1. In terminal run vim ~/.bash_profile to open ~/.bash_profile

  2. Type i to be able to edit the file, add the bin directory as PATH variable on the top to the file:

    export PATH="/Applications/mampstack-7.1.21-0/php/bin/:$PATH"

  3. Hit ESC, Type :wq, and hit Enter

  4. In Terminal run source ~/.bash_profile
  5. In Terminal type which php, output should be the path to MAMP PHP install.
Mahdiyeh
  • 865
  • 11
  • 12
0

I had the same issue on a docker container from php:8.0-fpm-alpine image. I just added the following line in the Dockerfile and it fixed the issue:

RUN apk add mysql-client
Omid Ebrahimi
  • 1,150
  • 2
  • 20
  • 38
-1

I had a similar problem once, turned out the User in the database was created with something like:

CREATE USER 'webpage'@'localhost' IDENTIFIED BY 'password';

worked fine when the connection details php script had localhost, but not when the IP address was there. A quick swap (ip address when creating user and localhost in connection details) revealed those two things have to match.

csfrancis555
  • 9
  • 2
  • If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/32474556) – ORHAN ERDAY Aug 19 '22 at 07:04
-4

For everyone if you still strugle with Refusing connection, here is my advice. Download XAMPP or other similar sw and just start MySQL. You dont have to run apache or other things just the MySQL.

PatrikPika
  • 3
  • 1
  • How does installing another software resolve the given problem? What if the same problem occurs and XAMPP is already installed? – Nico Haase May 05 '21 at 13:23