Reputation: 91

SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `posts`) with sail docker container

I am pretty new to Laravel but have worked with several frameworks in the past. I am using a Linux development host and have created a docker container as per documentation using:

curl -s https://laravel.build/example-app | bash

After that I have ramped it up using:

cd example-app
./vendor/bin/sail up

I have used migrations to create a post table, which worked fine, and used tinker to add some sample entries. Also, that worked without any issues. But when I am trying to read all table entries in a Controller using the model, I get a MySQL connection refused error.

Illuminate\Database\QueryException
SQLSTATE[HY000] [2002] Connection refused (SQL: select * from `posts`)

After quite a bit of research, I have not been able to solve it. I have tried various solution proposed in different other threads, like:

php artisan config:cache
php artisan config:clear
php artisan cache:clear

no success. I also changed IP address 127.0.0.1 to localhost in .env or towards the docker service mysql as described. All of that with NO success so far.

Summary of what is working:

php artisan migrate works fine
php artisan tinker works fine
phpMyAdmin or other db tools can connect without issues to the DB
ramping up a development server with php artisan serve does work and my controller returns table entries as JSON (as expected)

What does NOT work:

PostControler and Post::all() results in connection refused when running in docker environment

Has anyone had the same issue as this. It seems everything is setup correctly as everything works on the console and also when firing up a development server. However when running the normal app at http://locahost/posts (for the post controller index) it returns a connection refused.

Environment information

Laravel version: 8.40.0
Laravel locale:en
Laravel config cached: false
PHP version: 8.0.3

config entries

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=dbname
DB_USERNAME=user
DB_PASSWORD=password

docker-compose.yml

# For more information: https://laravel.com/docs/sail
version: '3'
services:
    laravel.test:
        build:
            context: ./vendor/laravel/sail/runtimes/8.0
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.0/app
        ports:
            - '${APP_PORT:-80}:80'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
        volumes:
            - '.:/var/www/html'
        networks:
            - sail
        depends_on:
            - mysql
            - redis
            - selenium
    mysql:
        image: 'mysql:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
        volumes:
            - 'sailmysql:/var/lib/mysql'
        networks:
            - sail
        healthcheck:
          test: ["CMD", "mysqladmin", "ping"]
    redis:
        image: 'redis:alpine'
        ports:
            - '${FORWARD_REDIS_PORT:-6379}:6379'
        volumes:
            - 'sailredis:/data'
        networks:
            - sail
        healthcheck:
          test: ["CMD", "redis-cli", "ping"]
    meilisearch:
        image: 'getmeili/meilisearch:latest'
        ports:
            - '${FORWARD_MEILISEARCH_PORT:-7700}:7700'
        volumes:
            - 'sailmeilisearch:/data.ms'
        networks:
            - sail
    mailhog:
        image: 'mailhog/mailhog:latest'
        ports:
            - '${FORWARD_MAILHOG_PORT:-1025}:1025'
            - '${FORWARD_MAILHOG_DASHBOARD_PORT:-8025}:8025'
        networks:
            - sail
    selenium:
       image: 'selenium/standalone-chrome'
       volumes:
            - '/dev/shm:/dev/shm'
       networks:
           - sail
    phpMyAdmin:
        image: 'phpmyadmin:latest'
        ports:
            - 8080:80
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
        links:
            - 'mysql:db'
        depends_on:
            - mysql
        networks:
            - sail
networks:
    sail:
        driver: bridge
volumes:
    sailmysql:
        driver: local
    sailredis:
        driver: local
    sailmeilisearch:
        driver: local

Post Controller

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Post;

class PostsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return Post::all();
        //return view('posts.index');

    }
...

Upvotes: 2

Answers (6)

Thiago Valentim

Reputation: 520

I had a similar issue, but it was access denied.

First thing: Using docker, the best practice is to add the container's hostname in the .env. For example, consider this simple Laravel + MySQL:

version: '3.8'
services:  
  laravel:
    build:
      context: .
      dockerfile: dockerfile
    container_name: laravel
    restart: unless-stopped
    working_dir: /var/www
    ports:
      - "8000:8000"
    env_file:
      - .env
    volumes:
      - ./:/var/www
    networks:
      - laravel_network

  mysql:
    image: mysql:8
    container_name: mysql
    restart: unless-stopped
    env_file:
     - .env
    environment:
     MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
      MYSQL_DATABASE: ${DB_DATABASE}
      MYSQL_USER: ${DB_USERNAME}
      MYSQL_PASSWORD: ${DB_PASSWORD}
    volumes:
      - mysql-data:/var/lib/mysql
    ports:
      - "3306:3306"
    networks:
      - laravel_network

networks:
  laravel_network:
    driver: bridge

volumes:
  mysql-data:

the hostname is mysql

Second thing: In the .env, I had to put the password between Quotation Marks, for example: Instead of DB_PASSWORD=T2a$8Zk#7Qb2Xv5 I had to put it like this: DB_PASSWORD="T2a$8Zk#7Qb2Xv5"

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=database-name
DB_USERNAME=username
DB_PASSWORD="T2a$8Zk#7Qb2Xv5"

Upvotes: 0

Hyemin Kim

Reputation: 21

I had the exact same issue and solved with the following steps.

Change DB_HOST to mysql
php artisan config:clear

It worked for me.

Upvotes: 2

Saeed

Reputation: 4125

As the OP mentioned he uses IP address of container, it's not a good practice to do that since the IP address may change.

Change your .env file to:

DB_HOST=mysql

And it will connect this exact container.

Upvotes: 3

Hedayatullah Sarwary

Reputation: 2834

Use localhost instead of 127.0.0.1 (in your .env file) and configure your .env database part:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laboratory
DB_USERNAME=root
DB_PASSWORD=

In your config folder database.php configure the MySQL part:

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

After that run these commands:

//---Remove the configuration cache file
php artisan config:cache;
php artisan config:clear;

If your answer is not solved go to the link below:

Source: PDOException SQLSTATE[HY000] [2002] No such file or directory

Upvotes: 1

Jürgen Neyses

Reputation: 91

I have found a solution to the issue.

Changing to localhost did end trying to use a socket, which apparently did not work. I even added the docker mysql socket to the php.ini as default_socket, but it did not resolve anything.

Solution:

I have changed localhost/127.0.0.1 to the real IP address of my machine and now everything works as expected.

Upvotes: 7

JEJ

Reputation: 904

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