MySQL appears to DROP USER; but user still exists in mysql.users table

Question

I've just installed MySQL Community server (5.5.8) on Mac OS X 10.6.6.

I've been following the rules for a secure install (assign password to root, delete anonymous accounts, etc), however, there is one user account which I can't DROP:

mysql> select host, user from mysql.user;
+--------------------------------+------+
| host                           | user |
+--------------------------------+------+
| 127.0.0.1                      | root |
| ::1                            | root |
| My-Computer-Hostname.local     |      |
| My-Computer-Hostname.local     | root |
| localhost                      | root |
| localhost                      | web  |
+--------------------------------+------+
6 rows in set (0.00 sec)

mysql> drop user ''@'My-Computer-Hostname.local';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select host, user from mysql.user;
+--------------------------------+------+
| host                           | user |
+--------------------------------+------+
| 127.0.0.1                      | root |
| ::1                            | root |
| My-Computer-Hostname.local     |      |
| My-Computer-Hostname.local     | root |
| localhost                      | root |
| localhost                      | web  |
+--------------------------------+------+
6 rows in set (0.00 sec)

mysql> 

As you can see, MySQL reports no errors when executing the DROP USER command, but doesn't actually delete the user!

I've tried also deleting the user from within phpMyAdmin (3.3.9) and that produced the same results (i.e. reported success, no error messages, user not deleted).

I've researched this and some people suggest that GRANT may be blocking the DROP USER command, however, the user has no GRANT privileges:

mysql> SHOW GRANTS FOR ''@'My-Computer-Hostname.local';
+-----------------------------------------------------------+
| Grants for @my-computer-hostname.local                |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO ''@'my-computer-hostname.local' |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE GRANT OPTION ON *.* FROM ''@'My-Computer-Hostname.local';
ERROR 1141 (42000): There is no such grant defined for user '' on host 'my-computer-hostname.local'

I tried dropping the user again after that but it didn't drop/delete the user either.

I've checked my MySQl error logs and there's nothing unusual in there.

The MySQL manual suggests that it is possible to delete all anonymous accounts, so why can't I delete this one?

Solution 1

This is a known bug due to your uppercase characters: http://bugs.mysql.com/bug.php?id=62255

Use the suggestion from user douger as a workaround

Solution 2

Or, to delete just the anonymous one and not the root as well:

mysql> DELETE FROM mysql.user WHERE User='' AND Host='my-computer-hostname.local';

Worked for me on 5.1.57.

Solution 3

You can still delete the records from the user table:

mysql> DELETE FROM user WHERE host='my-computer-hostname.local';
Query OK, 2 rows affected (0.00 sec)

This method was used prior to MySQL 4.1...