# Can't DELETE MySQL users



## bluethundr (Aug 28, 2010)

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 (Aug 28, 2010)

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@ (Aug 29, 2010)

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


----------

