MySQL – Fix Error 1045(28000): Anonymous User Problem

MySQL

I have removed anonymous users from mysql server but when one user trying to login from a specific host, current_user() still showing anonymous user. 

mysql> select user,host from mysql.user\G  
*************************** 1. row ***************************  
user: a  
host: %  
*************************** 2. row ***************************  
user: root  
host: 127.0.0.1  
*************************** 3. row ***************************  
user: root  
host: ::1  
*************************** 4. row ***************************  
user: a  
host: hosta  
*************************** 5. row ***************************  
user: root  
host: hosta  
*************************** 7. row ***************************  
user: a  
host: localhost  
*************************** 8. row ***************************  
user: root  
host: localhost  
8 rows in set (0.00 sec)  


mysql> show grants for 'a'@'hosta';  
+-------------------------------------------------------------------------------  
| Grants for a@hosta                                                                     
+-------------------------------------------------------------------------------  
| GRANT ALL PRIVILEGES ON *.* TO 'a'@'hosta' IDENTIFIED BY PASSWORD 'xxxxxxxx'  
+-------------------------------------------------------------------------------  
1 row in set (0.00 sec)

When I am giving correct password i am unable to login 

mysql@hosta ==> mysql -ua -p -S /tmp/mysql.sock -h hosta  
Enter password:  
ERROR 1045 (28000): Access denied for user 'a'@'hosta' (using password: YES)

but when I am giving blank password, I am able to log in:

mysql@hosta ==> mysql -ua -p -S /tmp/mysql.sock -h hosta  
Enter password:  

mysql> select user(),current_user();  
+---------------------------------------+-----------------------+  
| user()                                | current_user()        |  
+---------------------------------------+-----------------------+  
| a@hosta                               | @hosta                |  
+---------------------------------------+-----------------------+  
1 row in set (0.00 sec)

Any idea what I am missing here ?

Best Answer

Here is what you are missing

  • Connecting with user a via a socket is the same as connecting with user a via localhost.
  • Connecting with a socket will not use TCP/IP

If you are going to explicitly using a hostname, you must use TCP/IP with port 3306 and no socket

mysql -ua -p -h hosta --protocol=tcp -P3306

I have discussed using --protocol=tcp in my earlier posts

You need to delete blank users, blank hosts, and blank passwords from mysql.user

DELETE FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE host = '';
DELETE FROM mysql.user WHERE password = '';
FLUSH PRIVILEGES;
Related Question