Access denied for user Error

15 Feb 2020 in Posts

Error - ER_ACCESS_DENIED_ERROR: Access denied for user ‘admin’@’localhost’ (using password: YES)

• The Problem
  • What Works
    • PHPMyAdmin
    • MySQL Workbench
    • Docker Exec
  • What Doesn’t Work
    • MySQL Command Line
    • Node.js Driver For MySQL
  • Attempts To Fix
    • docker-compose rm -v
    • Validating Docker Container
    • Changing Docker Port
    • Explicit Permissions
• The Solution

The Problem

For my Horsin’ Around application I have a docker-compose to spin up docker containers for MySQL and PHPMyAdmin. The yaml configuration file can be found here. These containers start up and work fine with a simple docker-compose up command.

What Works

PHPMyAdmin

I can connect to PHPMyAdmin using the root user (Username: root, Password: root). I can see that the horsin-around database was created successfully. The user admin is created and given full permissions to the horsin-around database from any host (signified by %).

MySQL Workbench

I can connect to the horsin-around database with MySQL Workbench with this configuration

• Host: localhost
• Port: 3306
• User: admin
• Password: admin

Docker Exec

I am also able to connect to the docker image and login to mysql with this command

docker exec -it mysql mysql --user=admin --password=admin

Then I was able to run SELECT USER(),CURRENT_USER(); and SHOW GRANTS; and get the expected output

mysql> SELECT USER(),CURRENT_USER();
+-----------------+----------------+
| USER()          | CURRENT_USER() |
+-----------------+----------------+
| admin@localhost | admin@%        |
+-----------------+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+----------------------------------------------------------+
| Grants for admin@%                                       |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%'                        |
| GRANT ALL PRIVILEGES ON `horsin-around`.* TO 'admin'@'%' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

What Doesn’t Work

There are a lot of things that do work which makes the things that don’t work that much more confusing.

MySQL Command Line

I am unable to connect to connect using command line, I have tried all sorts of variations

mysql --user=admin --password=admin
mysql --user=admin --password=admin --database=horsin-around
mysql --user=admin --password=admin --database=horsin-around --port=3306
mysql --host=127.0.0.1 --user=admin --password=admin --database=horsin-around --port=3306
mysql --host=localhost --user=admin --password=admin --database=horsin-around --port=3306

But to no avail, each results in ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

I also tried specifying the protocol (tcp and socket)

mysql --host=localhost --user=admin --password=admin --database=horsin-around --port=3306 --protocol=tcp
mysql --host=localhost --user=admin --password=admin --database=horsin-around --port=3306 --protocol=socket

The socket protocol gave me the same error as above, the tcp protocol gave me this error

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

I also tried changing localhost to 127.0.0.1, when using protocol of socket it resulted in this error

ERROR 2047 (HY000): Wrong or unknown protocol

Changing the protocol to tcp resulted in the Error 1045 above.

Node.js Driver For MySQL

The main problem is that when the application tries to connect to MySQL, it gets the following error

Error - ER_ACCESS_DENIED_ERROR: Access denied for user 'admin'@'localhost' (using password: YES)

I used a console.log to print the TypeORM ConnectionOptions and got the configuration I would expect

{
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  database: 'horsin-around',
  username: 'admin',
  password: 'admin',
  synchronize: true,
  logging: true,
  dropSchema: false,
  ...
}

These are the same configuration options that I used to connect with MySQL Workbench and using the MySQL command line client

mysql --host=localhost --user admin --password=admin --database=horsin-around --port=3306

Attempts To Fix

There was a lot of Googling, trying this and trying that, tweaking and testing. These are some of the thigns I tried (and remembered to document).

docker-compose rm -v

I have tried deleting the volume folder and removing the docker images as suggested in this mysql docker-library issue

docker-compose rm -v

Validating Docker Container

I used a docker ps to look at the processes to see the host IP and ports

CONTAINER ID        IMAGE                   COMMAND                  CREATED              STATUS              PORTS      
              NAMES
ded1b34bde46        phpmyadmin/phpmyadmin   "/docker-entrypoint.…"   About a minute ago   Up About a minute   0.0.0.0:808
0->80/tcp     phpmyadmin
046254e59a92        mysql:5.7.21            "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:330
6->3306/tcp   mysql

I tried chaning the host to 0.0.0.0, but that did not change anything.

I then checked to make sure that the docker port was not being forwarded to another port

$ docker port mysql 3306
0.0.0.0:3306

Changing Docker Port

To try and eliminate the possibility that a local instance onf MySQL was conflicting on port 3306, I updated my MYSQL_PORT environment variable to 3307 in my .env file.

# Remove local docker volume
$ rm -rf db_data/

# Remove docker images
$ docker-compose rm -v
Are you sure? [yN] y
Removing phpmyadmin ... done
Removing mysql      ... done

# Check docker container processes
$ docker ps
CONTAINER ID        IMAGE                   COMMAND                  CREATED             STATUS              PORTS                    NAMES
be4246d0d654        phpmyadmin/phpmyadmin   "/docker-entrypoint.…"   6 minutes ago       Up 6 minutes        0.0.0.0:8080->80/tcp     phpmyadmin
5a38be2c5365        mysql:5.7.21            "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        0.0.0.0:3307->3306/tcp   mysql

# Check port mapping
$ docker port mysql 3306
0.0.0.0:3307

# Attempt connection
$ mysql --host=localhost --user=admin --password=admin --database=horsin-around --port=3307
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

I was again able to successfully connect using MySQL Workbench using the new port.

Explicit Permissions

There were so many StockOverflow answers that resulted in wrong password or user didn’t have the correct privileges. Because of this, I went into PHPMyAdmin and explicitely created and entry for admin@localhost

But trying to connect led to the same old errors

# TypeORM Connection
Error - ER_ACCESS_DENIED_ERROR: Access denied for user 'admin'@'localhost' (using password: YES)

# Command line client
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

The Solution

I am extremely disapointed to not know what exactly fixed it, I tried again and it just worked. If I had to guess it was the Changing Docker Port, I think what might have happened is that the .env hadn’t been pulled in when it was run. I unfortunately do not know. But hopefully this post gives some ideas for things to try and checks to make.