Question

Access denied for user 'test'@'localhost' (using password: NO)

My login attempts fail for a new user account that I have created, and I have spent several hours learning how authentication is handled by MySQL, with no progress.

Everything is on the same server:

  • MySQL 5.7
  • phpMyAdmin 4.9
  • Ubuntu 18.04

The end result doesn’t change, regardless of whether I:

  1. Run the query (as root)
  2. Open a browser on a different machine and run a simple PHP connection script

Here’s what I have tried from phpMyAdmin (logged in as root):

flush privileges;
create user 'testuser'@'localhost' identified by 'password!';
grant * on inventory to 'testuser'@'localhost';
flush privileges;

While logged into Ubuntu via ssh: mysql -u testuser -p mysql -u testuser -h 127.0.0.1 -p mysql -u testuser -h localhost -p mysql -u testuser -h <the-actual-hostname> -p

When attempting to log in, depending on whether I used 'testuser'@'%' or 'testuser'@'localhost' I’ll get one of two errors:

Access denied for user ‘testuser’@‘%’ (using password: YES) Access denied for user ‘testuser’@‘localhost’ (using password: YES)

To rule out this being a permissions issue, I assigned the user all global permissions, but the error message didn’t change, so I reverted the change.

Running ‘’‘select host,user,plugin,authentication_string,account_locked from mysql.user where user=testuser;’‘’ shows:

host user plugin authentication_string account_locked
localhost testuser mysql_native_password blahblahblah N
  • There are no users with blank usernames in the table mysql.user
  • The password doesn’t have special characters
  • I’ve tried localhost, 127.0.0.1 and the actual hostname itself

Just to show another way I’m trying to connect, I have a LAMP stack running and navigate to this php page in multiple web browsers:

<?php
$dbhost='localhost';
$dbuser='testuser';
$dbpass='password';
$dbname='inventory';

$conn = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if($conn->connect_error) {
	die("Error: Couldn't connect: ".$conn->connect_error);
}
?>

I’ve tried deleting the user and re-creating again after having confirmed that there’s no blank usernames or duplicates of this user.

/var/log/mysql/error.log only shows the exact same error message.


Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Accepted Answer

Hello,

I think that you have a typo in your grant privileges query. I’ve tried your queries and it worked as normal, I just had to adjust the grant privileges query a little bit.

Here’s what I did:

  • I created a database:
CREATE DATABASE inventory;
  • I created user and grant privileges for the new database:
GRANT ALL ON inventory.* TO 'testuser'@'localhost' IDENTIFIED BY 'password';
  • Then I tested your sample connection script and it worked as normal.

I could suggest just trying this from scratch, just drop the user and try to create a new one.

Hope that this helps! Regards, Bobby

lol wrong server, time for coffee, thanks Bobby

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel