Creating new MySQL user... password and privileges not sticking

Question

Connecting to MySQL through the command-line client as "root", I am trying to create a MySQL user with the following commands:

mysql> create user 'myuser'@'%' identified by 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on MY_DATABASE.* to 'myuser'@'%' identified by 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

The user is created, but its password and privileges are not "sticking". When I try to login using the password, I get an error:

~$ mysql -u myuser -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

However, I CAN login by simply pressing [enter] without a password:

~$ mysql -u myuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95
Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Still, the user cannot see any tables... so not only did the password fail to persist, but the privileges that I set were lost too.

Does anyone know what I'm missing here? Thanks!

UPDATE: Posting contents from the mysql.user table, as requested in the comments:

mysql> select User, Host, Password from mysql.user;
+------------------+---------------------+-------------------------------------------+
| User             | Host                | Password                                  |
+------------------+---------------------+-------------------------------------------+
| root             | localhost           | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | 127.0.0.1           | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | ::1                 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
|                  | localhost           |                                           |
| debian-sys-maint | localhost           | *817684763DD0B095B4703EC55053DAB57A2D9F4F |
| phpmyadmin       | localhost           | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| myuser           | %                   | *C5250F20FB991AA969917726DE6547D425DB3234 |
+------------------+---------------------+-------------------------------------------+

Answer 1

You have an anonymose user for localhost and that anonymous user don't have any password.

It is necessary to have both (% and localhost) accounts for your myuser to be able to connect from anywhere as myuser. Without the localhost account, the anonymous user account for localhost that is created by mysql_install_db would take precedence when myuser connects from the localhost. As a result, myuser would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the myuser'@'%' account and thus comes earlier in the user table sort order.

About sort order:

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so for example 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means "any host" and is least specific. The empty string '' also means "any host" but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific).

So mysql is considering your myuser as an anonymous user and since the anonymous user for localhost don't have any password, it is able to login without password.

To resolve your issue, you just need to create a localhost user for your myuser with password