Laravel Sail : Cannot connect to the database

Hello,

Believe me I've tried and searched a lot of things before posting this thread.

I have a Laravel project (using sail), with inside a docker-compose.yml file containing a mysql service, among others:

    mysql:
        image: 'mysql/mysql-server:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ROOT_HOST: '%'
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 1
        volumes:
            - 'sail-mysql:/var/lib/mysql'
            - './vendor/laravel/sail/database/mysql/create-testing-database.sh:/docker-entrypoint-initdb.d/10-create-testing-database.sh'
        networks:
            - sail
        healthcheck:
            test:
                - CMD
                - mysqladmin
                - ping
                - '-p${DB_PASSWORD}'
            retries: 3
            timeout: 5s

A .env file, containg among others:

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
FORWARD_DB_PORT=3306
DB_DATABASE=databaseName
DB_USERNAME=username
DB_PASSWORD=password

When I run sail up, I get the last messages from the myql container:

       | 2023-12-04T09:59:27.319455Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
     | 2023-12-04T09:59:27.319546Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

Which indicates me that all should be good.

Now, when I'm trying to connect to the database, using TablePlus (but I also tried with the mysql client command), using this:

Host : 0.0.0.0
user : username
password : password
database : databaseName

I get this error message : Access denied for user 'username'@'172.18.0.1' (using password: YES)

Important information : I'm on Linux (Ubuntu), and my colleagues on MacOS, did exactly the same steps and didn't have the problem I'm having.

Any idea or way how to troubleshoot this?

LaryAI

2 years ago

Level 58

Based on the information provided, it seems like there might be an issue with the user permissions in MySQL or the environment variables not being set correctly. Here are a few steps you can take to troubleshoot the issue:

Check Environment Variables: Ensure that the .env file has the correct values for DB_USERNAME and DB_PASSWORD and that they match the credentials you are using to connect to the database.
Permissions: The error message suggests that there might be a permissions issue. You can try to connect to the MySQL server using the root user and then check if the user username has the correct permissions. You can do this by running the following commands inside the MySQL container:

sail artisan tinker

Once inside the tinker shell, you can use the DB facade to run raw SQL queries:

DB::statement("SHOW GRANTS FOR 'username'@'%';");

This will show you the permissions for the username user. Make sure that this user has the necessary permissions to access the databaseName database.

MySQL User Host: The error message shows that the user is being accessed from 172.18.0.1. Ensure that the user username is allowed to connect from any host (%) or specifically from the host 172.18.0.1.
Flush Privileges: If you made any changes to the user permissions, you might need to flush the privileges for the changes to take effect. You can do this by running the following SQL command:

FLUSH PRIVILEGES;

Check MySQL Logs: Check the logs of the MySQL container to see if there are any additional error messages that might give you a clue as to what is going wrong. You can view the logs by running:

docker-compose logs mysql

Recreate the MySQL Container: If all else fails, you can try to recreate the MySQL container. First, take down the containers:

sail down

Then, remove the volumes (note that this will delete your database data, so make sure to back up any important data first):

docker volume rm sail-mysql

After that, bring the containers back up:

sail up

This will recreate the MySQL container with fresh volumes, and the initialization scripts will run again to create the user and database.

If you've gone through these steps and are still facing issues, please provide any additional error messages or logs that might help in diagnosing the problem further.

Alex-a

2 years ago

Level 1

I'm sorry, I just found the solution.

I've ran docker down -v (the -v is the important part), and now it's working!

codeOnJim

1 year ago

Level 3

@Alex-a Literally -v flag is for version when I am running with docker command. -v works with sail though.

Please or to participate in this conversation.