[FIXED] Can't connect to database: Access denied for user

Issue

I am setting up a CakePHP 3.7 application and using docker compose. I have a mysql service as well that I'm trying to connect to, but I am getting this error: Access denied for user 'ws_user'@'172.20.0.3' (using password: YES)

I am granting permissions to the user like so: GRANT ALL PRIVILEGES ON mydb.* TO 'ws_user'@'%' IDENTIFIED BY '<superSecretPasswordHere>'.

If I use the root credentials, cakephp is able to make the connection just fine.

I also expose the mysql service on port 3030 to my local machine and I am able to connect with the ws_user credentials just fine.

I also setup mysql running on my local machine with the same credentials and cake is able to connect to host 172.17.0.1 just fine as well.

I'm perplexed as what could be the problem. It sure seems like it's a permissions problem (because of the error message), but I'm able to connect via the exposed port via the command line. My next thought was that it might be because of special characters in the password, but again, if I connect to mysql running on my host machine, it works fine with the same password.

Here is my docker-compose file:

version: '2'
# define all services
services:
  # our service is called CakePHP ;-)
  cakephp:
    # we want to use the image which is build from our Dockerfile
    build:
      context: .
      dockerfile: Dockerfile
    # apache is running on port 80 but we want to expose this to port 4000 on our local machine
    ports:
      - "80:80"
    # we are depending on the mysql backend
    depends_on:
      - mysql
    # we mount the working dir into the container, handy for development 
    volumes:
      - .:/var/www/html/
    environment:
      - SECURITY_SALT
      - MYSQL_HOST
      - MYSQL_USERNAME
      - MYSQL_PASSWORD
  mysql:
      # we use the mysql base image, version 5.6.36
      #image: mysql:5.6.39
      build: 
        context: .
        dockerfile: Dockerfile.mysql
      ports:
        - "3030:3006"
      # we mount a datavolume to make sure we don't lose data
      volumes:
        - mysql_data:/var/lib/mysql
      # setting some envvars to create the DB
      environment:
        - MYSQL_ROOT_PASSWORD
        - MYSQL_DATABASE
volumes:
    mysql_data:

Solution

From "cakephp" you connect to "mysql:3306". This should be in your connection string.

From your host you can connect to "127.0.0.1:3030" to verify that your database accepts remote login.

Then you should check the credentials that they are the same. I suggest you put them in a .env file and then test the connection by "copy-paste" of the values.

you can check the values that are actually passed to the containers by running:

docker-compose config

This shows you the exact version of the docker-compose file that will be sent to the docker engine.

Hope this works, otherwise drop me a comment.

Answered By - Mihai