stratboy's avatar

Can't connect to db

Hi, I'm running sail locally with docker. Latest Laravel version. I'm experiencing problems with database connection, using both Sequel Ace (Sequel Pro new fork) and Table Plus. I'm on macos 13.5.1. Laravel works (the staging website), so Laravel itself seems it can connet to the database. But I cannot connect with a client. Also, if I try for example a migration (sail artisan migrate), I get this error:

 Illuminate\Database\QueryException 

  SQLSTATE[HY000] [1045] Access denied for user 'root'@'172.18.0.7' (using password: NO) (Connection: mysql, SQL: select * from information_schema.tables where table_schema = testsite5 and table_name = migrations and table_type = 'BASE TABLE')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:801
    797▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    798▕                 );
    799▕             }
    800▕ 
  ➜ 801▕             throw new QueryException(
    802▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    803▕             );
    804▕         }
    805▕     }

      +35 vendor frames 

  36  artisan:35
      Illuminate\Foundation\Console\Kernel::handle()

My .env file is like:

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=testsite5
DB_USERNAME=root
DB_PASSWORD=

So my client configuration is like

Host: 127.0.0.1
port: 3306
db name: testsite5
db user: root
pw: empty

What I get from table plus is:

Access denied for user 'root'@'192.168.65.1' (using password: NO)

Any idea?

0 likes
9 replies
LaryAI's avatar
LaryAI
AI
2 years ago
Level 58

The error message suggests that the database connection is being denied for the user 'root'@'172.18.0.7' (in the error message) or 'root'@'192.168.65.1' (in the Table Plus error message).

To resolve this issue, you can try the following steps:

  1. Make sure that the database container is running. You can check this by running the command sail ps in your terminal. If the database container is not running, you can start it by running sail up -d or sail up if you want to see the logs.

  2. Verify that the database credentials in your .env file are correct. In this case, the credentials seem to be correct, but you can double-check the following:

    • DB_CONNECTION should be set to mysql.
    • DB_HOST should be set to mysql.
    • DB_PORT should be set to 3306.
    • DB_DATABASE should be set to testsite5.
    • DB_USERNAME should be set to root.
    • DB_PASSWORD should be empty (as it is in the provided .env file).

  3. Check if there are any other services running on port 3306 that might conflict with the database container. You can use the command sudo lsof -i :3306 to check if any other process is using that port. If there is a conflicting process, you can either stop it or change the port in your .env file and the client configuration accordingly.

  4. If you have made any changes to the .env file, make sure to restart the containers by running sail down followed by sail up -d.

If the above steps do not resolve the issue, you can try the following:

  • Check if the database container is accessible from your local machine. You can try connecting to the database using the command line client. Run sail artisan tinker to open the Laravel Tinker shell, and then try running a simple query like DB::select('SELECT 1'). If you get a successful response, it means that the database container is accessible.

  • Verify if the database container is using the correct IP address. You can check the IP address of the container by running sail exec mysql ip addr show. Make sure that the IP address matches the one mentioned in the error message.

If you are still unable to connect to the database, you can try reaching out to the Laravel community or the Laracasts forum for further assistance.

stratboy's avatar

So, of all the Lary suggestions, I can say that:

sail artisan tinker
DB::select('SELECT 1')

Does output this:

Illuminate\Database\QueryException  SQLSTATE[HY000] [1045] Access denied for user 'root'@'172.18.0.7' (using password: NO) (Connection: mysql, SQL: SELECT 1).

Then

sail exec mysql ip addr show

Does output this:

OCI runtime exec failed: exec failed: unable to start container process: exec: "ip": executable file not found in $PATH: unknown

But in Docker it seems it's all running fine.

stratboy's avatar

I've found the problem: It seems that, is chages are made in .env config to mysql params, they are not immediately reflected by the system, or container, I don't know. Even after reloading the container. I mean sail down + sail up -d.

So, if I change db username and password in .env, how can make all the system conform to them?

Talinon's avatar

@stratboy Have you cached your config? Try running sail artisan config:clear

1 like
stratboy's avatar

@Talinon Anyway, it didn't work. The only way I had to connect to the db and have all running correctly was to go back to the initial credentials in .env, where the user is 'sail' and the password is 'password'.

Then I tried again changing the user to 'sail2', just to test your solution, and it didn't work. Even after restarting the container. No way. It seems that simply the db credentials are not updated on the real db. How can I check them in docker/sail? I mean, how can I see what's really running? With what user and password? And in the end, how can I really change them?

Thank you

2 likes
stratboy's avatar

@Talinon And by the way, the error I get from Sequel Ace is:

Unable to connect to host 127.0.0.1, or the request timed out. Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds). MySQL said: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Talinon's avatar

@stratboy I have limited experience with Sail, but from my understanding, the docker volumes will be installed on the initial run of sail up. When you change your .env variables, it sounds like you might have to rebuild for the change to take effect.

Try:

sail down
sail build --no-cache
sail up -d

If that doesn't work, maybe someone with more experience with Sail can chime in and help.

stratboy's avatar

@Talinon Thank you, I tried but it doesn't work. In fact, it always uses the same user/password: sail/password. In other words, it seems that somewhat it completely bypass .env config. I'll try to open a more specifc thread and see it I get other answers. Thank you anyway for your time.

Please or to participate in this conversation.