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,authenticationstring,accountlocked from mysql.user where user=testuser;”’ shows:
host | user | plugin | authentication_string | account_locked |
---|---|---|---|---|
localhost | testuser | mysqlnativepassword | 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.
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.
×