mildred commented 9 years ago

I have issues connecting to the mysql database spawned by this docker container. I ger repeated access denied errors. I tried multiple ways:

use nsenter to enter the mysql container and then the mysql client to connect as root or as user (as configured with the environment variables): nsenter --target $(docker inspect --format {{.State.Pid}} etc_mysql_1) --mount --uts --ipc --net --pid /bin/bash
use a linked container as the documentation shows: run -it --link some-mysql:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'
in my application container, run the mysql client and connect from there

I tried with and without a password. With the root user and with $MYSQL_USER. With the -h localhost or -h mysql or without. Each time, I get an error similar to this:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This is the environment of the mysqld process:

root@07632642725f:/# tr '\0' '\n' </proc/1/environ 
HOSTNAME=07632642725f
MYSQL_VERSION=5.6.23
MYSQL_DATABASE=webmail
MYSQL_PASSWORD=webmailpasswd
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
PWD=/
SHLVL=0
HOME=/root
MYSQL_MAJOR=5.6
MYSQL_USER=webmail
MYSQL_ROOT_PASSWORD=rootpasswd

Do you have an idea why it fails?

Thank you

ltangvald commented 9 years ago

The error usually means the password supplied is incorrect. Did you start the container in any particular way, or just as specified in the documentation?

mildred commented 9 years ago

The container is started with docker-compose. The yaml script is :

mysql:
  image: mysql
  environment:
    - MYSQL_ROOT_PASSWORD=rootpasswd
    - MYSQL_DATABASE=webmail
    - MYSQL_USER=webmail
    - MYSQL_PASSWORD=webmailpasswd

yosifkit commented 9 years ago

Have you changed the passwords since you first tried running the containers? docker-compose does extra work to preserve volumes between runs (thus preserving the database); you may want to try docker-compose rm -v to delete everything and try starting it up again.

sformisano commented 9 years ago

@yosifkit +1 I had the exact same issue and figured it out thanks to your answer. Is the -v flag even documented in the compose cli docs? I saw no mention of it on the rm command.

yosifkit commented 9 years ago

Glad I could help. It doesn't seem to have anything (https://docs.docker.com/compose/cli/#rm). I think I verified it with docker-compose rm --help or something. It is a mirror of the regular docker rm. You should totally file an issue to improve the documentation :wink:.

etoews commented 9 years ago

I have to +1 here. I had my MySQL files in a data volume container that had stuck around and this discussion helped me realize it.

faceleg commented 9 years ago

This got me as well, opened an issue. Thanks @yosifkit!

paulredmond commented 9 years ago

you may want to try docker-compose rm -v to delete everything and try starting it up again.

@yosifkit thank you so much for your advice, you saved me :heart:

marcellodesales commented 9 years ago

@yosifkit Thanks again... However, another detail here is that if you use a data container with docker-compose, you need to delete the ENTIRE directory before running again.

qgMysqlData:
  image: busybox
  volumes:
    - ./mysql-data:/var/lib/mysql

qgMySQLServer:
  image: mysql
  environment:
    MYSQL_ROOT_PASSWORD: *******
    MYSQL_USER: *****
    MYSQL_PASSWORD: ******
    MYSQL_DATABASE: ****
  ports:
    - "3306:3306"
  volumes_from:
    - "qgMysqlData"

qgPhpMyAdmin:
  image: nazarpc/phpmyadmin
  ports:
    - "8090:80"
  links:
    - qgMySQLServer:mysql

The command docker-compose rm -v reflects in the containers, but NOT in the volume mapped. So, the following command will remove the containers and since it doesn't delete the volumes, the command files to create a new user.

$ docker-compose rm -v
Going to remove backendphp_qgPhpMyAdmin_1, backendphp_qgMySQLServer_1, backendphp_qgMysqlData_1
Are you sure? [yN] y
Removing backendphp_qgMysqlData_1...
Removing backendphp_qgMySQLServer_1...
Removing backendphp_qgPhpMyAdmin_1...

At this point, the only way to make it right is to delete the directory that you mapped your mysql data.

$ sudo rm -rf mysql-data

Only after that, the command created a new database with the users correctly set!

Good luck!

yosifkit commented 9 years ago

You are correct; docker-compose rm -v does delete docker managed volumes, but not bind mounted "volumes". They have similar properties and act mostly the same, but the bind mounts are directories controlled by the user so docker is not just going to delete them for you. Thanks for the input, hopefully it will help someone in the future.

strarsis commented 8 years ago

I had the same error but from a different cause: The environment variable MYSQL_PWD was set to the same value as MYSQL_PASSWORD, so mysql client commands run against this container would use the correct password. This causes the mysql container init script to log in too early with that password which user hadn't been set up yet. The same issue also occurs when MYSQL_PWD is set to the MYSQL_ROOT_PASSWORD.

geerlingguy commented 7 years ago

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently).

In docker-compose.yml:

...
volumes:
  data_volume:

If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.

blachawk commented 7 years ago

I have to give a + 1 on this discussion because I had different passwords going on for 2 containers trying to connect to mysql with WORDPRESS_DB_PASSWORD, when i should of been using the same password on both containers.

Thanks

benhtn commented 7 years ago

yes @marcellodesales you're a star! thank you!!

glaux commented 6 years ago

Another possible resolution to the 'access denied' issue is provided in this issue: #241. In short, add MYSQL_ROOT_HOST=% as an environment variable.

stingus commented 6 years ago

I was facing this issue, not related to Docker, but found it while using docker-compose with multiple mysql instances on localhost:

You can specify a port number for connections to a local server, too. However, as indicated previously, connections to localhost on Unix will use a socket file by default. You will need to force a TCP/IP connection as already described or any option that specifies a port number will be ignored.

I am using OSX, so all my connections using -h localhost went straight to the MySQL instance on the host itself (via socket, --port is ignored). Using 127.0.0.1 instead of localhost forces a TCP connection on the right port, thus on the right container.

erikverheij commented 6 years ago

We ran into this issue because we used the env var MYSQL_PWD. It was intended to be used by another container but the mysql container also uses it when the init queries are executed.

Rukeith commented 6 years ago

I just still got this error even I use docker-compose rm -v

yosifkit commented 6 years ago

@Rukeith, rm -v only removes anonymous volumes; if you have a named volume for /var/lib/mysql, you would also need to delete that. (Assuming that it contains a misconfigured database and you are trying to start over.)

Rukeith commented 6 years ago

@yosifkit I didn't mount any volume

amir5121 commented 5 years ago

I had similar problem apparently some characters aren't allowed in the password..

after changing the password environment variable and removing the volume the issue was resolved

taha-moghaddam commented 5 years ago

I had this problem, too. docker-compose down -v solved my problem. This command removed all corresponding volume, network and containers.

hayatoise commented 5 years ago

I had this problem, too. docker-compose down -v solved my problem. This command removed all corresponding volume, network and containers.

@taha-moghaddam I solved it with this command. Thank you 👍

pourya2374 commented 5 years ago

Have you changed the passwords since you first tried running the containers? docker-compose does extra work to preserve volumes between runs (thus preserving the database); you may want to try docker-compose rm -v to delete everything and try starting it up again.

I didn't see it coming!

rahulsingh336 commented 5 years ago

Hello, clearing the data volumes made by app to connect to mysql using docker-compose. I have one question, I think there is not password for default image for mysql 5.7.20, thats why without specifying password anywhere it is allowing to connect ? or, when we link container then some magic happens, could you please explain this behaviour?

mohamed-aiman commented 5 years ago

in my case it was a permission issue to the mounted volume. what worked for me was run docker-compose down -v which deleted all networks(if not external), volumes and containers. I previously tried docker-compose rm -v and it didn't work as the problem was with volumes and not with the containers, it removes only containers but not the volumes.

celovivas commented 5 years ago

Have you changed the passwords since you first tried running the containers? docker-compose does extra work to preserve volumes between runs (thus preserving the database); you may want to try docker-compose rm -v to delete everything and try starting it up again.

Acctually, the command that resolved for me was docker-compose down -v

celovivas commented 5 years ago

I had this problem, too. docker-compose down -v solved my problem. This command removed all corresponding volume, network and containers.

That was my problem and that command solved it. Thank you man!

vjohnk commented 5 years ago

this worked for me

i had to COMMENT everything from environment under wordpress till wordpress DB_NAME

version: '3.1'

services:

wordpress: image: wordpress restart: always ports:

8081:80

environment:

WORDPRESS_db_host: mysql-wordpress

WORDPRESS_db_user: wordpress

WORDPRESS_db_password: password

WORDPRESS_db_name: wordpress

links:
mysql-wordpress container_name: wordpress-yaml

mysql-wordpress: image: mysql:5.6 restart: always command: '--default-authentication-plugin=mysql_native_password' environment: MYSQL_DATABASE: wordpress MYSQL_USER: wordpress MYSQL_ROOT_PASSWORD: password

MYSQL_RANDOM_ROOT_PASSWORD: '1'

ports:
- 3307:3307 container_name: mysql

boyaziqi commented 5 years ago

I have a similar problem（changing MYSQL_USER)。I tried to run docker-compose rm -v and docker-compose down -v. but I just still got this error.

sfmok commented 5 years ago

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently).

In docker-compose.yml:
...
volumes:
  data_volume:
If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.

Thank you

DJStress commented 5 years ago

Thanks @sfmok this worked for me. Also as thank you @amir5121 as you stated some characters are not accepted and I found this to be spot on with an error I was receiving, so I decided to go with a basic password. In addition to removing the data directory I used the "docker volume prune" command to remove all volumes associated on the host machine. Thank you again guys!

SaveYourTime commented 5 years ago

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently).

In docker-compose.yml:
...
volumes:
  data_volume:
If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.

You save my life!!! 🥺

ladaposamuel commented 5 years ago

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently). In docker-compose.yml:
...
volumes:
  data_volume:
If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.
Thank you

You saved my life

wischi-chr commented 5 years ago

I ran into the same problem a few minutes ago. Keep in mind that there must not be a space after the "-p" argument.

cristianmonsalve commented 5 years ago

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently).

In docker-compose.yml:
...
volumes:
  data_volume:
If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.

Man, u're the best, u saved my life !!

livthomas commented 4 years ago

I my case, I had to execute all these steps to solve this problem:

Stop and remove containers and volumes:

docker-compose down -v
Remove my db volume folder:

sudo rm -rf db
Remove all affected Docker images:

docker rmi IMAGE_ID

jp-git1986 commented 4 years ago

faced same issue.Seems the problem is with volumes with originally created when there is no mysql_root_password was defined in docker-compose.yaml file.After removed using docker volume rm(since named volumes not removed while running docker-compose rm -v) and reran docker-compose up after updated yaml file with mysql_root_password,it was working properly

sontd-0882 commented 4 years ago

docker-compose down -v will delete your mounted data, my database has gone away 😢

ericpitcher commented 4 years ago

I got the access denied error because I had a dollar sign in my password, which the docker compose file parses as variable substitution (I've only seen the ${} syntax) on this page you will read "Both $VARIABLE and ${VARIABLE} syntax are supported." https://docs.docker.com/compose/compose-file/#variable-substitution

Therefore if I had for example this as my database password in the docker compose file: hello$world, the world variable would get substituted, of course I don't have a 'world' variable, the password that would be set in that case would be 'hello', so without knowing the docker compose syntax you would be trying to log in with 'hello$world', and you'd be denied access.

leonardosdias commented 4 years ago

Eu tive esse problema também. docker-compose down -vresolveu meu problema. Este comando removeu todo o volume, rede e contêineres correspondentes.

This command solved my problem, thank you very much. Then I ran docker-compose up -d to create the environment.

Rafarel commented 4 years ago

Have you changed the passwords since you first tried running the containers? docker-compose does extra work to preserve volumes between runs (thus preserving the database); you may want to try docker-compose rm -v to delete everything and try starting it up again.

It makes so much sense now !!! You saved my life, I lost a full day on this yesterday ! Hero of my day :) Thanks a million times

elchroy commented 4 years ago

Note also that if you have a non-named volume (e.g. something like below in your docker-compose.yml), then you have to manually delete it (The Compose file doesn't track it for deletion, and preserves it silently).

In docker-compose.yml:
...
volumes:
  data_volume:
If you run docker volume ls, you'll see a volume like projectfolder_data_volume. To delete it, run docker volume rm projectfolder_data_volume.

Thank you! This saved me.

GABAnich commented 4 years ago

docker system prune

helped me

Yelfive commented 4 years ago

I was facing this issue, not related to Docker, but found it while using docker-compose with multiple mysql instances on localhost:

You can specify a port number for connections to a local server, too. However, as indicated previously, connections to localhost on Unix will use a socket file by default. You will need to force a TCP/IP connection as already described or any option that specifies a port number will be ignored.

I am using OSX, so all my connections using -h localhost went straight to the MySQL instance on the host itself (via socket, --port is ignored). Using 127.0.0.1 instead of localhost forces a TCP connection on the right port, thus on the right container.

And I ran brew stop mysql, it worked.

gurpreetsingh087 commented 4 years ago

docker-compose rm -v did not work for me. I manually removed volume directory then restarted using docker-compose up.

This only worked for me.

ryanjanborja commented 4 years ago

OS: MacOS Catalina 10.15.3 Docker: 2.2.0.3

db:
  container_name: app_db
  image: mysql:5.7
  volumes:
    - app_db:/var/lib/mysql
  ports:
    - 3306:3306
  environment:
    - MYSQL_ROOT_PASSWORD="passwd"

volumes:
  app_db:

I wen't inside the mysql container to login

docker exec -ti app_db bash
mysql -u root -p
Enter password: passwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I did docker-compose down -v and manually removed the container and volume but no luck

yosifkit commented 4 years ago

@iryanjan18, you need to remove the quotes in your yaml, switch to a map (instead of a list), or use them in your password to mysql.

# either of these should work
  environment:
    - MYSQL_ROOT_PASSWORD=passwd

  environment:
    MYSQL_ROOT_PASSWORD: "passwd"

askme23 commented 4 years ago

you may want to try docker-compose rm -v to delete everything and try starting it up again. @yosifkit it's work for me, thank you so much )

shaktals commented 4 years ago

I had same issue with a running mysql instance in a docker container. What happened it that I updated some system packages, that restarted the local mysql instance. Thus the docker mysql was dropped. But when trying docker-compose up it would not throw any errors. After `docker-compose rm -v', it indicated a connection error, which lead to the root cause.

docker-library / mysql

Cannot connect to mysql database: Access denied #51

this worked for me

environment:

WORDPRESS_db_host: mysql-wordpress

WORDPRESS_db_user: wordpress

WORDPRESS_db_password: password

WORDPRESS_db_name: wordpress

MYSQL_RANDOM_ROOT_PASSWORD: '1'