80

Using Mac OS X and Homestead 2.2.1 with Laravel 5.2.

In terminal (within homestead in my project folder) I can do php artisan to see all the available commands. When I try to run php artisan migrate I get a connection error:
SQLSTATE[HY000] [2002] Connection refused


I have setup a Laravel project with these .env settings

DB_HOST=127.0.0.1
DB_DATABASE=tcv
DB_USERNAME=homestead
DB_PASSWORD=secret

I have also tried localhost for DB_HOST and root for DB_USERNAME and DB_PASSWORD. And all possible variations of these put together!


In Sequel Pro (db management application) I CAN connect with these settings

Host       127.0.0.1
Username   homestead
Password   secret
Database   tcv
Port       33060

But this database is obviously empty, because I cant migrate to it from terminal ...

As far as I can make out it is a configuration issue, since I can connect to it with Sequel Pro. But I have honestly no freaking idea what is setup wrong.

Thanks for the help !!

EDIT
For some reason I get the same SQLSTATE[HY000] [2002] Connection refused error when moving my project to MAMP and running php artisan migrate.
Now I am completely lost ...

nclsvh
  • 2,628
  • 5
  • 30
  • 52
  • 11
    Have you tried changing the ip address from 127.0.0.1 to localhost? I had a similar issue ages ago and that worked for me ( cant test now as not on mac ) – Adam Feb 14 '16 at 16:49
  • @Adam Yes I have (like I said in the original question), doesn't change a thing. But thanks for the input... – nclsvh Feb 14 '16 at 17:24
  • 1
    My bad, didnt see you already tried – Adam Feb 14 '16 at 17:42
  • 1
    your app is looking for the database on the virtual machine. I assume the Sequel Pro is able to access because that's on the host machine. You have to use your actual computer IP to access the database from homestead – tnash Feb 14 '16 at 17:54
  • @chikurubhi And what would be a correct fix? Making the app look for the database locally? HOW would I do that? – nclsvh Feb 14 '16 at 17:56
  • change 127.0.0.1 in the .env to your host machine IP. You can find your machine IP http://whatismyip.org/. Otherwise you can ssh to homestead and install mysql to have that local to the virtual machine. – tnash Feb 14 '16 at 17:58
  • Doesn't seem to work... Did try both options – nclsvh Feb 14 '16 at 19:10
  • you have port 33060 or 3306? – tnash Feb 14 '16 at 19:21
  • @chikurubhi I finally found the solution.. Thanks for asking what port I use. This made me check the database.php file again and see my own mistake. Posted the solution below for future dumbasses like me! – nclsvh Feb 15 '16 at 19:04
  • I am using Mac and MAMP as localserver, in file `.env` i just change `DB_HOST=127.0.0.1` to `DB_HOST=localhost` and it works for me, thanks @Adam – Rolly Feb 26 '16 at 15:00

32 Answers32

132

I just ran into this and found that changing this in the .env file from 127.0.0.1 to localhost fixed it.

DB_HOST=localhost
Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
Richard Hood
  • 1,445
  • 2
  • 9
  • 4
  • Thanks Richard Hood. It fixed my issue. And what a stupid thing from Laravel to have this issue. – hhsadiq Apr 05 '18 at 12:53
  • WOW! It was really solved this issue for me! Thx dude! :) – szatti1489 May 21 '18 at 11:34
  • 2
    FYI MAMP users... it seems when running on a MAMP server that 127.0.0.1 works, but on a MAMP PRO server it needs to be localhost! – James Cushing Apr 09 '20 at 19:28
  • I got the same error message when using laradock.io (Laravel 8). Then I needed to set my real IP-adress of my computer. I guess it was due to the request came from inside the PHP-fpm container. – olle.holm Mar 14 '21 at 21:43
  • This fixed my issue on a live server, shared hosting. my website was running fine until after a few weeks it went down by itself, and by doing this, it fixed it but i do not know how this exactly works. – Billions.A.Joel May 03 '21 at 15:51
  • After making this change, you may get the error similar to `SQLSTATE[HY000] [2002] No such file or directory` and if you search on stackoverflow, people will suggest to use `127.0.0.1` instead. This is deadlock then. I faced this issue and tried to search from the MySQL side and found one answer which fixed it. Check this out here: https://serverfault.com/a/337928 Basically you are telling MySQL to use TCP protocol by setting `protocol=tcp` in `my.cnf` file. Then you can use `127.0.0.1` in your Laravel config and things will run normally. – Abhilash Mandaliya Jun 07 '21 at 08:23
  • This actually worked for me on my production server. quite odd – Vrrayz Nov 22 '21 at 16:34
53

Problem

In Laravel you have config/database.php where all the setup for the connection is located. You also have a .env file in the root directory in your project (which everyone uses for timesaving). This contains variables that you can use for the entire project.

On a standard L5 project the MySql section of config/database.php looks like this:

    'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
        'engine'    => null,
    ],

Notice there is no port set!

Although in my .env file I had set DB_PORT=33060. But that value (3306) was never read into the config/database.php.
So don't be a dumbass like myself and forget to check the database.php file.


FIX
Simply add 'port' => env('DB_PORT', 3306), to your config/database.php and set that value in .env like this DB_PORT=3306
azro
  • 53,056
  • 7
  • 34
  • 70
nclsvh
  • 2,628
  • 5
  • 30
  • 52
43

If you are using MAMP on a mac OS add the following line to your mysql database config file

'unix_socket' => env('DB_SOCKET', ''),

and on your .env file add

DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock
Albert A
  • 469
  • 4
  • 9
32

Use localhost instead of 127.0.0.1 (in your .env file), then run command:

php artisan config:cache
Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
Lode Alaert
  • 671
  • 6
  • 6
21

If you have your backend and database started on docker

Instead of putting localhost or 127.0.0.1 as DB_HOST put the name of the registered service that indicates your database service in the docker-compose file.

In my case for example I replaced 127.0.0.1 with db because in my docker-compose file I had defined the name of the service for the database as db

My docker-compose looks something like that

services:
    db: <------ This is the name of the DB_HOST
      container_name: admin_db
      image:mysql:5.7.22
    .
    .
    .
Charbel.AY
  • 783
  • 1
  • 11
  • 17
18

PROBLEM SOLVED

I had the same problem and fixed just by converting DB_HOST constant in the .env File FROM 127.0.0.1 into localhost

JUST DO THIS AS GIVEN IN THE BELOW LINE

DB_HOST = localhost.

No need to change anything into config/database.php

Don't forget to run

php artisan config:clear

Ashwani Garg
  • 1,479
  • 1
  • 16
  • 22
  • 1
    I made a PR to add some explanation for this in official Laravel documentation but they decide to ignore it. [link](https://github.com/laravel/docs/pull/6159) – Guo Yunhe Jul 03 '20 at 19:53
  • 1
    some times this will not be worked. so you have to remove some files from here. /bootstrap/cache/packages.php /bootstrap/cache/services.php /bootstrap/cache/config.php after that run the php artisan config:clear – Vidurajith Darshana Jun 28 '21 at 07:29
  • 1
    php artisan config:clear solved my problem on azure with nginx, laravel 8 and php 8. – Imran Qamer Oct 12 '21 at 06:36
  • 1
    just miraculous – CodeToLife May 17 '22 at 05:52
15

Another solution for anyone else who has a problem. I had all the settings correct but for some reason my changes weren't updated. Laravel actually caches the config file (which I find completely stupid).

Here was my solution after updating the configs:

php artisan config:clear

Andrei
  • 1,183
  • 2
  • 19
  • 40
  • 1
    Laravel does not cache the config by default. You might have done that to speed up the application during development? See: https://laravel.com/docs/5.5/configuration#configuration-caching – nclsvh Feb 12 '18 at 15:08
  • 1
    In development environment, `php artisan config:clear` will fix the issue. In production, you should also run `php artisan config:cache` as laravel suggests here: https://laravel.com/docs/8.x/configuration#configuration-caching. – Amr May 02 '21 at 21:50
10

I was having this problem. When connecting with Sequel Pro, I need to use 33060 as the port, but in the .env file it has to be 3306. I had 33060 in the .env file. Changed it to 3306 and it worked.

Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
Tom Kincaid
  • 4,887
  • 6
  • 47
  • 72
  • 1
    Thanks, this has worked for me. Strangely enough I have another project that I just ran before this one and that did work on 33060 while this one really needed 3306 – Sormano Oct 12 '17 at 12:55
9

This is a simple fix. Your mysql database has lost its connection to the server. If you are running a local server run this in your terminal:

mysqld

That will reconnect your database. Then (if you are using homebrew) run:

brew services start mysql

This will automatically connect your database on login.

SouthernBoy
  • 223
  • 2
  • 5
  • Just wanted to let SouthernBoy know that this saved me from a long day of debugging a mistake I made! – Arty Oct 16 '17 at 16:26
  • I had to `brew install mysql` Now it's working but I get a different error message instead. – Matt Feb 03 '18 at 16:50
7

In my case, I was having the same error using docker and the trick was in setting in the .env file this DB_HOST=db where db is the name of the container running the database server.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
  • 1
    This is the proper solution for those of u running their code on a Docker/Laradock mysql container. The value for DB_HOST is equal to the container hostname (ie. id) which Docker then translates to the proper network address. – 99linesofcode Dec 09 '19 at 00:10
5

It's possible that your mysql hasn't started or is not to the port 3306

Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
gxet4n
  • 349
  • 6
  • 11
5

I had the same problem, try this works

DB_HOST=localhost
3

In my case this error appeared out of blue. While staring at that mysterious error I've realized, that I was trying to run the command outside of vm...

Be Kind
  • 4,712
  • 1
  • 38
  • 45
3

After you put all configuration on .env file, if you already run php artisan serve, RESTART IT.

Udhav Sarvaiya
  • 9,380
  • 13
  • 53
  • 64
  • 1
    This was the simple fix I needed. I change the .env file for DB_HOST from 127.0.0.1 to localhost, cleared config & cache but it still wasn't working. Restarting via this command worked. Thanks! – codewithfeeling Jun 22 '19 at 16:38
2

For me enclosing the credentials in quotes did the trick

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE='zzz'
DB_USERNAME='yyy'
DB_PASSWORD='XXX'
Max
  • 2,561
  • 1
  • 24
  • 29
2

When Laravel connects to mysql on Homestad box and running commands on local environment:

DB_HOST=127.0.0.1
the app needs DB_PORT=3306
the migrations need DB_PORT=33060 otherwise gives Connection refused
phpunit needs DB_PORT=33060 otherwise gives Connection refused

DB_HOST=localhost
the app works with both DB_PORT=3306 or DB_PORT=33060
migrations cannot run
phpunit cannot run

Have in mind that phpunit takes its values from the .env.testing file, if exists, otherwise from the phpunit.xml file on the root folder of the app

Alex Christodoulou
  • 2,873
  • 2
  • 16
  • 18
1

Me, Im using vagrant, yet im executing php artisan outside the box so basically it doesn't have permission

Marlon Bernal
  • 577
  • 5
  • 5
1

I could be because you might have not restarted PHP artisan since long

So After making DB changes and config:clear Tinker works fine

But to make browser refect the new DB connection you need to re-run

php artisan serve

shivanisdev
  • 687
  • 6
  • 16
1
  1. terminate sqld from task manager
  2. stop MySQL from xampp control panel and restart it

if it still throw the same problem, from root folder run

php artisan cache:clear
php artisan config:cache
php artisan serve
  1. if you still have the problem, check whether you are using multiple copies of sql server, for example through Ubuntu app, if so stop the MySQL server in ubuntu

    sudo service MySQL stop

zabi
  • 49
  • 1
1
DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=8080
DB_DATABASE=flap_safety
DB_USERNAME=root
DB_PASSWORD=mysql

the above given is my .env

    'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', 'mysql'),
       // 'port' => env('DB_PORT', '8080'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', 'mysql'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],

the above given is my database.php file. i just commented out port from database.php and it worked for me.

0

If you are using Homestead then you should be running it with the default mysql port. So instead of using DB_PORT=33060, you should be using DB_PORT=3306 in your .env file. Also, remember to run your php artisan migrate commands in your homestead installation and everything should be ok.

Hope that helps.

Lamin Barrow
  • 839
  • 10
  • 16
0

The only thing that solved it for me was to put the connection details in config/database.php instead of the .env file. Hope this helps

Maxime Barber
  • 101
  • 1
  • 2
  • 10
0

I had a similar problem and no suggestions placed here helped me. This what has fixed my problem was to set the application name and database hostname with the same value. In my case, 127.0.0.1 works correctly.

APP_URL=127.0.0.1

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=zzz
DB_USERNAME=yyy
DB_PASSWORD=XXX
LukaszTaraszka
  • 801
  • 1
  • 10
  • 26
0

Okay update it seems the host is not the issue but actually the port.

So its actually the port 3306 for the browser testing and yet for terminal and Sequel Pro 33060. Could it have something to do with adding it in the Homestead.yaml and set it here?

On reading the setup in relation to Laravel it says:

MySQL: 33060 → Forwards To 3306

Paul
  • 129
  • 5
0

I encountered

SQLSTATE[HY000] [2002] Connection refused (SQL: select * from projects)

The cause was that I had not started the MySQL server.

It may help to check that the MySQL is running as the first step in troubleshooting the error.

Anastasios Selmani
  • 3,579
  • 3
  • 32
  • 48
Steven
  • 1,071
  • 15
  • 15
0

I found that my server just needed to be restarted and "bam" it was fixed.

Chad C.
  • 21
  • 4
0

If you are using Homestead like I do, do not forget to ssh into it before running artisan test for example.

daneczech
  • 655
  • 1
  • 9
  • 20
0

I don't think it even reads config/database.php. As soon as edited my entries into .env file it started working.

0

In case this helps someone in the future... I have to run artisan commands and so on using WSL but MySQL lives on my Windows environment. I struggle with the correct terminology but my brain says "I don't care if you think you're a virtual machine, you're quite literal and literally right in front of me so when I say connect to yourself I expect you to do so."

If I understand correctly, PHP is executing the commands in magical WSL-land so it needs the IP of the database in relation to WSL-land.

From a command prompt (Windows, not in WSL) I ran ipconfig to find my IPv4 and put that for my Laravel DB_HOST. Now, when I migrate in WSL it works.

I realize this was overly wordy but I personally get very confused with all of the virtual machine stuff, so I put the answer I wish I'd found weeks ago.

Wish I'd never gone back to Windows.

0

Not so much a fix to the solution as another way round it, but it's also possible (and much faster) to use SQLite as a database instead of MySQL, simply uncommenting the following lines in phpunit.xml:

<server name="DB_CONNECTION" value="sqlite"/>
<server name="DB_DATABASE" value=":memory:"/>
Hashim Aziz
  • 4,074
  • 5
  • 38
  • 68
-2

If you have any kind of proxy or vpn , turn them off and close them . it was my problem .

Geco
  • 177
  • 1
  • 17
-3

On ubuntu/linux you can try to restart mysql with this command: sudo service mysql restart

Girgetto
  • 1,010
  • 9
  • 19