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.
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
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]  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 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_`.
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. firstname.lastname@example.org
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