Can't DELETE MySQL users

[bluethundr]

I am running MySQL 5.1.49 on FreeBSD 8.1 and I am for some reason unable to delete users from the users table.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM user WHERE user='bluethundr@virt1';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host FROM user;
+------------+--------------------------+
| user       | host                     |
+------------+--------------------------+
| root       | 127.0.0.1                |
| bluethundr | 192.168.1.23             |
| root       | ::1                      |
|            | lbsd8-2.example.com      |
| bluethundr | lbsd8-2.example.com      |
| root       | lbsd8-2.example.com      |
|            | localhost                |
| nobody     | localhost                |
| root       | localhost                |
| bluethundr | virt1                    |
+------------+--------------------------+
10 rows in set (0.00 sec)

mysql> DELETE FROM user WHERE user='bluethundr@lbsd8-2.example.com';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host FROM user;
+------------+--------------------------+
| user       | host                     |
+------------+--------------------------+
| root       | 127.0.0.1                |
| bluethundr | 192.168.1.23             |
| root       | ::1                      |
|            | lbsd8-2.example.com      |
| bluethundr | lbsd8-2.example.com      |
| root       | lbsd8-2.example.com      |
|            | localhost                |
| nobody     | localhost                |
| root       | localhost                |
| bluethundr | virt1                    |
+------------+--------------------------+
10 rows in set (0.00 sec)

hmmm...anyone got a clue?

 

[gilinko]

There is no user called bluethundr@lbsd8-2.example.com. There are however a user called bluethundr that is allowed to connect from lbsd8-2.example.com. As you can't join that easy in a delete query, your SQL should look like this:

[CMD=">"]DELETE FROM user WHERE user="bluethundr" AND host="lbsd8-2.example.com";[/CMD]

Thus matching rows where both the username and host are present.

 

[gordon@]

Just use DROP USER:
http://dev.mysql.com/doc/refman/5.0/en/drop-user.html