MySQL Access Denied From Within Docker Containers

A few days ago I ran into some very odd behavior. Even though I was setting my usernames and passwords via Docker secrets (so the same file on the db container and the app container), MySQL was throwing Access Denied errors at me. Thankfully all the frustrating details of my troubleshooting have left me, but I thought I would share both the problem, and the solution.

The Setup

Be running multiple MySQL containers, and their associated application containers, on the same Docker network.

You can find a git repo with all the needed files here: drnet_tech_mysql_access_denied

Run:

docker network create testnet51

Create two Docker Compose based projects with `docker-compose.yml` files that look like these:

# < project root > fruit/docker-compose.yml
version: "3.7"
networks:
  testnet51:
    external: True
services:
  db:
    image: mysql
    container_name: fruit_db
    networks:
      testnet51:
    environment:
      MYSQL_ROOT_PASSWORD: PASSWORD
      MYSQL_DATABASE: FRUIT
      MYSQL_USER: TOUCAN
      MYSQL_PASSWORD: YOUCAN
  app:
    image: adminer
    container_name: fruit_app
    networks:
      testnet51:
    ports:
      - 127.0.0.123:1234:8080
    depends_on:
      - db

# < project root > metal/docker-compose.yml
version: "3.7"
networks:
  testnet51:
    external: True
services:
  db:
    image: mysql
    container_name: metal_db
    networks:
      testnet51:
    environment:
      MYSQL_ROOT_PASSWORD: PASSWORD
      MYSQL_DATABASE: METAL
      MYSQL_USER: STARK
      MYSQL_PASSWORD: IAMIRON
  app:
    image: adminer
    container_name: metal_app
    networks:
      testnet51:
    ports:
      - 127.0.0.123:1235:8080
    depends_on:
      - db

Run the two projects. Once everything has initialized, visit fruit at http://127.0.0.123:1234 and metal at http://127.0.0.123:1235. Try logging into fruit with the username and password set in the Docker Compose file. Same thing on metal. If it works, logout and try again. Eventually you will see an error like:

SQLSTATE[HY000] [1045] Access denied for user 'STARK'@'172.27.0.5' (using password: YES)

Fun, huh? For some reason you can log in sometimes, but it fails other times, and you're using the correct connection information. Laid out like this, I expect many have already figured out what is wrong. But when you're troubleshooting in production, it's not this obvious.

What is going on?

Docker Compose sets the hostnames of the container to the name of their service. So when you connect to the database in either project, you use `db` as the host. Normally that doesn't matter, but these projects are using the same network. And that means Docker is naming two different containers `db`. Thus, sometimes your app will know to connect to the right db container, and sometimes it won't.

To make things even more fun, try naming the databases the same, then installing the same kind of app in each container. It can get confusing really, really fast.

The fix

The fix is simple. Actually, there are two simple fixes. A) Don't put your Docker Compose projects on the same network. B) Make sure your service containers are uniquely named. For example, prefix both services in `fruit` with `fruit_`, and in `metal` with `metal_`.

Final thoughts

So, I'm fairly certain not many people use the setup I have. I'm using a combination of Ansible, HAProxy, environment variables, and Docker Compose, to orchestrate my apps. Keeping the apps all on the same network helps me keep track of what ip/port values I've assigned to what environment variable better. I'm not entirely sold on my system yet... Anyway, that explanation of how I actually ran into this issue over with, I hope someone finds this helpful. If you have any questions, feel free to email me. me@davidreagan.net

Clean Up

In case you forget, make sure to remove the test network. Oh, and the containers.

docker network rm testnet51
docker rm -f fruit_app
docker rm -f fruit_db
docker rm -f metal_app
docker rm -f metal_db