Thesql user creation with % to host , can only log on from remote host

MySQL

Using MySQL v5.1.52

After creating a mysql user with the following commands:

create user 'user1'@'%' identified by 'pass1';
grant select on db1.* to 'user1';
flush privileges;

Then doing a 'mysql -uuser1 -p' in the command line then entering the password I still get this error:

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

However when connecting from a remote system (with the -h) to the above server using the above credentials it works.

Why can I not log on locally with this user?

Here is the user/host outpout:

mysql> select User,Host from mysql.user;
+----------------+-----------------+
| User           | Host            |
+----------------+-----------------+
| user1          | %               |

Best Answer

When connecting to your localhost it's going you use sockets instead of ports to connect. Those grants need a specific 'localhost' grant. To use your % grant locally connect specifying you want to connect via TCP

mysql -u user -p --proto=TCP

Further reading

Related Question