Mysql: Access denied for user 'testuser'@'172.17.0.1' (using password: YES)

[ChambreNoire]

Hello,

I have created a mysql docker container thus :

docker run
--detach
--name=mysql-test
--env="MYSQL_ROOT_PASSWORD=test"
--volume=/Docker/mysql-cleanweb/config/conf.d:/etc/mysql/conf.d
--volume=/Docker/mysql-cleanweb/storage/:/var/lib/mysql
--publish 6604:3306
mysql:latest

I have a java app which, amongst other things, can create both users and schemas via SQL. My app creates a 'testschema' and a 'testuser'@'localhost' and grants 'testuser' all privileges on 'testschema' :

CREATE DATABASE testschema;
GRANT ALL PRIVILEGES ON testschema.* TO 'testuser'@'localhost:6604' IDENTIFIED BY 'testuser'
FLUSH PRIVILEGES;

Unfortunately when my app tries to connect to this schema as 'testuser' it fails. Using testuser / testuser for jdbc://mysql://localhost:6604/testschema I get "Access denied for user 'testuser'@'172.17.0.1' (using password: YES)".

I've googled around about this and tried using 'testuser'@'localhost' and '172.17.0.1:6604' but still no solution in sight.

Your help would be most appreciatated!

Thanks

Anthony

[ltangvald]

Localhost is handled differently by MySQL, as it goes through the local unix socket instead of the network interface.
So if you were to create a 'test'@'localhost' user you wouldn't be able to connect to it through 'test'@'127.0.0.1' for instance. Handling this is a bit more complicated with Docker (this is why we change the root user to be accessible from outside "localhost" in the entrypoint script) because even when you try to connect to localhost:6604 on your host machine, the MySQL container sees that as coming from outside.

To test you can change your grant statement to 'testuser'@'%' and you should see the connection succeed, but then anything with access to your host's 6604 port can connect

[ltangvald]

'testuser'@'172.17.0.1' should also work (172.17.0.1 being the default ip for the host computer), but that ip might change.

[clintonb]

I see this issue when I create accounts too early. In my first iteration of provisioning I did the following:

• Start MySQL container
• Execute docker exec -i edx.devstack.mysql mysql -uroot -se "SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'roots')" &> /dev/null every second until success
• Wait 7 seconds
• Run SQL script to create users and flush privileges.

The problem here seems to lie with the fact that container restarts the server at least three times. I suspect my script is run sometime after the first restart, and privileges are not truly flushed. The users exist, but my containers cannot connect as those users.

If I wait 20 seconds, until after the third restart, to run my user creation script my containers have no issue connecting to the databases.

The fact that a new container restarts the server three times needs to be documented so that folks are aware of this, and can work around it appropriately.

[clintonb]

Related: https://github.com/docker-library/mysql/issues/245

[ltangvald]

@clintonb Can you do the user init by putting your user creation script in the container's /docker-entrypoint-initdb.d/ directory instead? It's meant to be used for this kind of initialization, and any .sql or .sh files you map to that location will be run as part of the first startup.

The server doesn't start three times in a connectable manner. It runs with --initialize once, but it won't accept any connections then. The main init is done by starting the server with --skip-networking, so it won't accept external connections, but at this point it's possible to connection through the exec command you use, which is the main problem.

At least for 5.7+ I want to try replacing this way of doing init with using the --init-file functionality, which should prevent people from connecting before the container is ready

[YouriT]

ran into the same issue. The problem was related to docker-compose where I needed to use a hash instead of a list for environment variable definition. In case you had the same and didn't show you were using compose.

[maclonghorn]

I think I've got a similar problem. I am running this image along with another docker image (eboraas/apache-php) for my PHP app. I am using Phinx for migrations. The MySQL image seems to work fine as I can connect to it as 'dbuser' (non-root) using MySQL Workbench. I can also connect with Docker exec as well.

Problem occurs when I try to run any Phinx command, such as status. I'm getting:

[InvalidArgumentException]
There was a problem connecting to the database: SQLSTATE[HY000][1045] Access denied for user 'dbuser'@'localhost' (using password: YES)

I have tried running the migration from the host as well as in the PHP container...both give me an error.

My grants on the DB look like:

GRANT USAGE ON *.* TO 'dbuser'@'%' IDENTIFIED BY PASSWORD '*8232A1298A49F710DBEE0B330C42EEC825D4190A'
GRANT ALL PRIVILEGES ON `dbuser`.* TO 'app_db'@'%'

My development section of the phinx.yml file looks like:

development:
    adapter: mysql
    host: localhost
    name: app_db
    user: dbuser
    pass: 'userpass'
    port: 3306
    charset: utf8

Finally, here is my docker-compose.yml:

version: '2'

services:
  web:
    image: eboraas/apache-php
    volumes:
      - ./app:/var/www/html
      - ./api:/var/www/api
    ports:
      - "80:80"
    links:
      - db
  db:
    image: mysql:5.5
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: Password1
      MYSQL_DATABASE: app_db
      MYSQL_USER: dbuser
      MYSQL_PASSWORD: userpass

Any ideas?

[ltangvald]

@maclonghorn
Could there be an issue with the hash_string you supply for the grant statement? Maybe try
identified by 'password'
and see if that has any effect?

[maclonghorn]

the password you see in the GRANTS was created when I created the container, as per the Environment Variables. does that look wrong/suspicious? I could try to update the pwd via CLI.
[edit]
Nope, no difference.

[yosifkit]

@maclonghorn, Where are you running the Phinx commands?

If you are running it on the host, are you also running a local mysql server that it might be connecting to via unix socket rather than the one in docker on port 3306? Maybe you need to switch to 127.0.0.1 rather thanlocalhost`?

[maclonghorn]

I had tried running both from host and from within container -- neither worked, but had different errors. I did have MySQL running on the host, so I shut that down. I'm now able to run phinx within the container. Thanks!

[llitfkitfk]

web & mysql services not in the save network can cause error:

Access denied for user 'summer'@'172.25.0.9' (using password: YES)

for example app service connect to db service:

version: '2'
services:
  app:
    image: tomcat:8.0-jre8
    depends_on: 
      - db
    ports: 
      - 8080:8080
    volumes:
      - ../:/usr/local/tomcat/webapps/ROOT
    networks:
      - web
  db:
    image: mysql
    ports:
      - "3306:3306"
networks:
  web:
    external:
      name: web-network

[lucasthibau]

@yosifkit solved the problem for me: https://github.com/docker-library/mysql/issues/51

[nivesh2]

Face the same issue, after running the mysql container.
Run mysql container command:

docker run --name mysqlDB -p 3306:3306 -v /tmp/docker/mySql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=hello11 -e MYSQL_DATABASE=auditDB -e MYSQL_USER=mark -e MYSQL_PASSWORD=hello22 -d mysql/mysql-server:latest 

Worked For me:
log into the bash of the container and connect to the mysql using root id and password, then GRANT outside access to the required user:

bash-4.2# mysql --user=root --password=hello11
mysql> SELECT User,authentication_string FROM mysql.user;
mysql> GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*7AF9230F3DD61D90636A0490F285516093837B81'

use the select query to get the hash value of the stored password in user table. Now u can access root user from host machine, one can also grant access to other users as per need. Thanks.

[tianon]

Closing given that the original issue appears to be solved.

For further help debugging issues with deploying this image, I'd recommend posting to the Docker Community Forums, the Docker Community Slack, or Stack Overflow. Thanks!

[OmidAhmadyani]

check you config/database.php maybe changed user name and password

[sanderdewijs]

I ran into this error after setting up a laravel app in a virtual machine. The app uses a local and an external database. After trying different solutions, I wrapped the remote password in my env file in double quotes. This solved the issue. Apparently the password could not be properly read without the wrapping quotes.

[jerrychong25]

Localhost is handled differently by MySQL, as it goes through the local unix socket instead of the network interface.
So if you were to create a 'test'@'localhost' user you wouldn't be able to connect to it through 'test'@'127.0.0.1' for instance. Handling this is a bit more complicated with Docker (this is why we change the root user to be accessible from outside "localhost" in the entrypoint script) because even when you try to connect to localhost:6604 on your host machine, the MySQL container sees that as coming from outside.

To test you can change your grant statement to 'testuser'@'%' and you should see the connection succeed, but then anything with access to your host's 6604 port can connect

Thanks for this useful info.

'username'@'%' solved my Access Denied issue in MySQL 5.7.

[gr33k]

Ok so say you have a user nextclouddb - this is my local MariaDB instance (actual server DB) - and I wanted my docker nextcloud to be able to access it - This worked

MariaDB [nextcloud]> grant all on nextcloud to 'nextclouddb'@'%';
Query OK, 0 rows affected (0.001 sec)

MariaDB [nextcloud]> grant all on . to 'nextclouddb'@'%';
Query OK, 0 rows affected (0.000 sec)

[CaliforniaMountainSnake]

@yosifkit solved the problem for me: #51

Thank you, this was really fault of the password change:
https://github.com/docker-library/mysql/issues/51#issuecomment-76989402