MySQL Access Denied From Within Docker Containers

Submitted by david.reagan on Sun, 03/07/2021 - 11:40

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:

  1. # < project root > fruit/docker-compose.yml
  2. version: "3.7"
  3. networks:
  4.   testnet51:
  5.     external: True
  6. services:
  7.   db:
  8.     image: mysql
  9.     container_name: fruit_db
  10.     networks:
  11.       testnet51:
  12.     environment:
  13.       MYSQL_ROOT_PASSWORD: PASSWORD
  14.       MYSQL_DATABASE: FRUIT
  15.       MYSQL_USER: TOUCAN
  16.       MYSQL_PASSWORD: YOUCAN
  17.   app:
  18.     image: adminer
  19.     container_name: fruit_app
  20.     networks:
  21.       testnet51:
  22.     ports:
  23.      - 127.0.0.123:1234:8080
  24.     depends_on:
  25.      - db

  1. # < project root > metal/docker-compose.yml
  2. version: "3.7"
  3. networks:
  4.   testnet51:
  5.     external: True
  6. services:
  7.   db:
  8.     image: mysql
  9.     container_name: metal_db
  10.     networks:
  11.       testnet51:
  12.     environment:
  13.       MYSQL_ROOT_PASSWORD: PASSWORD
  14.       MYSQL_DATABASE: METAL
  15.       MYSQL_USER: STARK
  16.       MYSQL_PASSWORD: IAMIRON
  17.   app:
  18.     image: adminer
  19.     container_name: metal_app
  20.     networks:
  21.       testnet51:
  22.     ports:
  23.      - 127.0.0.123:1235:8080
  24.     depends_on:
  25.      - 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.

  1. docker network rm testnet51
  2. docker rm -f fruit_app
  3. docker rm -f fruit_db
  4. docker rm -f metal_app
  5. docker rm -f metal_db