1

I was setting up mysql on my prod server, this is actually the first time i set up a prod installation of mysql.

I've logged in as root, created my DBs, and then i've created 2 users as follow:

User1/permissions on database1

CREATE USER 'jhon'@'localhost' IDENTIFIED BY 'passwd';
GRANT CREATE ON database1 TO 'jhon'@'localhost';
GRANT INSERT ON database1.* TO 'jhon'@'localhost';
GRANT SELECT ON database1.* TO 'jhon'@'localhost';
GRANT UPDATE ON database1.* TO 'jhon'@'localhost';

User2/permissions on database2

CREATE USER 'jhonjhon'@'localhost' IDENTIFIED BY 'passwdpasswd';
GRANT CREATE ON database2 TO 'jhonjhon'@'localhost';
GRANT INSERT ON database2.* TO 'jhonjhon'@'localhost';
GRANT SELECT ON database2.* TO 'jhonjhon'@'localhost';
GRANT UPDATE ON database2.* TO 'jhonjhon'@'localhost';

and then

FLUSH PRIVILEGES;
exit;

Now i'm trying to log in as 'jhon' or 'jhonjhon', with the command:

mysql -u jhon -p

but both attempts give me 

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

I've also tried to select the database directly with -D option, accordingly with the permissions set above..but nothing, Mysql still denies access.

Where am i wrong?

thanks

EDIT

Users List:

| root | 127.0.0.1 |
| root | ::1 |
| debian-sys-maint | localhost |
| root | localhost |
| jhon | localhost |
| jhonjhon | localhost |

System Info: 

Ubuntu 14.04 LTS - MYSQL Ver 14.14 Distrib 5.5.43
Martijn Pieters
  • 889,049
  • 245
  • 3,507
  • 2,997
Jhon Zunda
  • 63
  • 11
  • Did you try `mysql -u jhon -p -h localhost`? – Leonardo Herrera Jun 03 '15 at 13:23
  • i dont see the need to do it, since the error says: "Access denied for user 'jhon'@'localhost " ..it seems that it already tries to log in as jhon@localhost... however, i've just tried, but nothing..still the same error. thank you for your interest – Jhon Zunda Jun 03 '15 at 13:26
  • Similar discussion: https://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw – teoreda Jun 03 '15 at 13:26
  • @teoreda already seen that discussion, but it doesnt match my case. i do not have anon users. thank you anyway – Jhon Zunda Jun 03 '15 at 13:27
  • Check [this SO discussion](http://stackoverflow.com/questions/7956031/mysql-access-denied-1045-error?rq=1) - they recommend running `GRANT ... IDENTIFIED BY`. – Leonardo Herrera Jun 03 '15 at 13:32
  • done.. still nothing :( – Jhon Zunda Jun 03 '15 at 13:37

2 Answers2

0

for mysql localhost and 127.0.0.1 are separate. so try this also:

GRANT ALL ON database1.* to 'jhon'@'127.0.0.1' IDENTIFIED BY 'passwd';
num8er
  • 15,883
  • 2
  • 33
  • 48
  • i've always created users with "localhost", and they always worked fine. The news this time (for me) are the specific permissions. Hence, i guess this issue is caused by privilages. Anyway, i will try in a minute.thanks EDIT: tried, still the same error, even with a new user :( – Jhon Zunda Jun 03 '15 at 13:29
  • Beyond `localhost` vs `127.0.0.1`, did you try the `GRANT ALL` suggested by @num8er? That might verify that it is a privilege issue as you suspect. – pjd Jun 03 '15 at 14:35
0

If the user password contains certain special characters (! is definitely one), you will get a generic authentication failure when trying to connect to the database as that user.

Unfortunately, this restriction is not documented anywhere. Try a simpler password until you can get it working, and then carefully add special characters back in.

Andrew Koster
  • 987
  • 1
  • 14
  • 23