Davy Kavanagh Davy Kavanagh - 6 months ago 120
MySQL Question

connecting to a docker-compose mysql container denies access but docker running same image does not

I am having some issues connecting to the mysql docker container that I have launched with docker-compose. This is a long post (sorry!).

Here is my docker-compose.yml file:

db:
image: mysql:5.7
ports:
- "3306:3306" # I have tried both ports and expose "3306". Still doesn't work
environment:
- MYSQL_ROOT_PASSWORD="secret"
- MYSQL_USER="django"
- MYSQL_PASSWORD="secret"
- MYSQL_DATABASE="myAppDB"


Then:

$> docker-compose build
db uses an image, skipping #expected!
$> docker-compose up
<<LOTS OF OUTPUT>>


OK, so now I have an up and running docker container runner mysql:5.7. Great! Or is it?
When testing in my django app, I get Operational errors saying that the user isn't allowed to connect the database. Ok, so maybe it's my django then?

$> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c7216f99ca0f mysql:5.7 "docker-entrypoint.sh" 3 minutes ago Up 3 minutes 0.0.0.0:3306->3306/tcp sharpfin_db_1

$> docker-machine ip dev
192.168.99.100
$> mysql -h 192.168.99.100 -P 3306 -u django -p
Enter password:
ERROR 1045 (28000): Access denied for user 'django'@'192.168.99.1' (using password: YES)


ok so maybe It's something to do with connecting to the docker-compose container?
What if I try connecting from inside the docker container?

$> docker exec -it c7216f99ca0f /bin/bash
root@c7216f99ca0f:/#
root@c7216f99ca0f:/# mysql -u django -p
Enter password:
ERROR 1045 (28000): Access denied for user 'django'@'localhost' (using password: YES)


ok, so docker mysql won't let me connect, don't know why. Let's see what happens when I try do this without docker-compose:

$> docker run --name run-mysql -e MYSQL_ROOT_PASSWORD="secret" -e MYSQL_USER="django" -e MYSQL_PASSWORD="secret" -e MYSQL_DATABASE="myAppDB" -p "3306:3306" mysql:5.7
<<LOTS OF OUTPUT SAME AS BEFORE>>


Ok, so now we have a container running the same image as before with the same settings. (I think this assertion is probably not true - docker-compose is doing something different to docker run).

$> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
73071b929e82 mysql:5.7 "docker-entrypoint.sh" 3 minutes ago Up 3 minutes 0.0.0.0:3306->3306/tcp run-mysql


There's my container (called run-mysql). Let's connect!

$> mysql -h 192.168.99.100 -P 3306 -u django -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myAppDB |
+--------------------+
2 rows in set (0.01 sec)

mysql>


Alright. Can log in. That's weird... What about from inside the container?

$> docker exec -it 73071b929e82 /bin/bash
root@73071b929e82:/# mysql -u django -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myAppDB |
+--------------------+
2 rows in set (0.00 sec)

mysql>


Ok, I can log in from outside and inside the container when I launch with docker run, but not with docker-compose. What's going on? There must be something either docker-compose is doing behind the scenes that changes how the database is initialized.

All the above is the exact same if I try with the root user as well. So it's not a permissions issue with the django user.

Any ideas how to resolve this?

Answer

I am using the official mysql image with docker-compose and not having a problem. The only difference in my compose file is that I am using a dictionary instead of an array:

environment:
  MYSQL_ROOT_PASSWORD: secret
  MYSQL_USER: django
  MYSQL_PASSWORD: secret
  MYSQL_DATABASE: myAppDB

I have noticed that the compose file documentation is still stuck in V1 in some places, so you could try this, if you're using V2. Otherwise, for debugging you can use docker-compose exec to interact with the container created by compose directly.

docker-compose exec db /bin/bash will get you a shell on the container that is giving you trouble and you can check things like SHOW GRANTS FOR django@'%' or whether the ports are being forwarded correctly. I hope this helps.

Comments