Failure to add a user for all origins

Question

When I try to add a new user with

grant all privileges on retrosias.* to 'rs3'@'%' identified by 'a';

I can't even connect locally :

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

If I add the same user using

grant all privileges on retrosias.* to 'rs3'@'localhost' identified by 'a';

then I can connect locally but my real need is to connect from any computer.

My server version is

Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)

And I verified that users added before can still connect from anywhere (so not a firewall problem).

Where's my error ? Or what could be a workaround ?

Answer 1

mysql> select User,Host,Password from user;
+------+------------+-------------------------------------------+
| User | Host       | Password                                  |
+------+------------+-------------------------------------------+
| root | localhost  | *62395BB52702DE50773EBF629DD4AE90F07FFD94 |
| root | sgeorge-mn |                                           |
| root | 127.0.0.1  | *62395BB52702DE50773EBF629DD4AE90F07FFD94 |
| root | ::1        |                                           |
|      | localhost  |                                           |
|      | sgeorge-mn |                                           |
| suku | localhost  | *EAF5C8242B88A14545BB61062D64CA5207DD1A37 |
| rs3  | %          | *0FDB28C86F3804FCA60FA633DB4264B0EB169D9B |
| rs3  | localhost  | *667F407DE7C6AD07358FA38DAED7828A72014B4E |
+------+------------+-------------------------------------------+
9 rows in set (0.00 sec)

It is necessary to have both (% and localhost) accounts for rs3 to be able to connect from anywhere as rs3. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when rs3 connects from the localhost. As a result, rs3 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 'rs3'@'%' 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 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).

For more reading : http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

Answer 2

Rebooting the server did accomplish what neither flush privileges nor service stop mysql / service start mysql didn't.

All the (many) users I added before reboot are now accessible.

And the new users I add now with the same commands are accessible too.

I'm reluctant to accept my answer. I don't know if this is a known/frequent problem with mysql. The fact that I had to restart not only the service but also the whole computer is pretty disturbing.