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.

×
2 answers

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

Submit an Answer