MySQL Bugs: #61525: DROP USER root@'<hostname>' does not remove corrsponding row in mysql.user

Bug #61525	DROP USER root@'<hostname>' does not remove corrsponding row in mysql.user
Submitted:	15 Jun 2011 14:52	Modified:	23 Aug 2011 13:54
Reporter:	LEVESQUE Olivier	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.53	OS:	Linux (RHEL 5.4 x86_64)
Assigned to:		CPU Architecture:	Any

Description:
After having created a Mysql server, we would like to remove a certain number of undesirable accounts. It works fine for everyone of them except for root@'<hostname>'.

mysql:root:(none)> select Host, User, Password from mysql.user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *8DF1B571A877418ADE6A25032FF8BBC884B1CFE5 |
| QMMIDI31  | root    |                                           |
| %         | myadmin | *8DF1B571A877418ADE6A25032FF8BBC884B1CFE5 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql:root:(none)> DROP USER root@'QMMIDI31';
Query OK, 0 rows affected (0.00 sec)

==> No error here, but ...

mysql:root:(none)> select Host, User, Password from mysql.user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *8DF1B571A877418ADE6A25032FF8BBC884B1CFE5 |
| QMMIDI31  | root    |                                           |
| %         | myadmin | *8DF1B571A877418ADE6A25032FF8BBC884B1CFE5 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)

==> The account root@'QMMIDI31' is still present.

Removing the acount via "DELETE FROM mysql.user WHERE Host='QMMIDI31' and User='root';" followed by a "FLUSH PRIVILEGES" can be a workaround, but this does not sounds clean.

If the DROP command does not actually removes the corresponding line in mysql.user, it should not exit succesfully.

 

How to repeat:
Not repeatable.

Please, check the manual, http://dev.mysql.com/doc/refman/5.1/en/drop-user.html:

"DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design."

I think it explains why DROP USER worked the way you described - you tried to drop user that you were logged in as. This is not a bug.

Thank you for your answer.

But I don't think it is the source of the problem, since I am connected as 'root'@'localhost' when running DROP USER.

mysql:root:(none)> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  |  202 | Waiting on empty queue | NULL             |
|  9 | root            | localhost | NULL | Query   |    0 | NULL                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

When I disconnect and reconnect from mysql, the row in mysql.user is still present but subsequent "DROP" fails.

[QMMIDI31:mysql:myserver01]$mysql -uroot -pxxxx
...
mysql:root:(none)> select host,user from mysql.user;
+-----------+---------+
| host      | user    |
+-----------+---------+
| %         | myadmin |
| QMMIDI31  | root    |
| localhost | root    |
+-----------+---------+
3 rows in set (0.00 sec)

mysql:root:(none)> DROP USER root@'QMMIDI31';
ERROR 1396 (HY000): Operation DROP USER failed for 'root'@'qmmidi31'
mysql:root:(none)> select host,user from mysql.user;
+-----------+---------+
| host      | user    |
+-----------+---------+
| %         | myadmin |
| QMMIDI31  | root    |
| localhost | root    |
+-----------+---------+
3 rows in set (0.00 sec)

mysql:root:(none)> Bye

Hi,

I eventually managed to reproduce the issue.
The problem appears when your server hostname contains at least an uppercase character.

Reproduced on Linux RHEL 5.5 64bit + MySQL 5.5.9
Reproduced on Linux RHEL 5.4 64bit + MySQL 5.1.53  
Reproduced on Linux Fedora 12 32bit + MySQL 5.5.15 (see the trace here, with hostname=FEDORA02):

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15 MySQL Community Server (GPL)
...
mysql> select host,user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| FEDORA02  |      |
| FEDORA02  | root |
| localhost |      |
| localhost | root |
+-----------+------+
6 rows in set (0.00 sec)

mysql> drop user root@'FEDORA02';
Query OK, 0 rows affected (0.00 sec)

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

But the problem does not appear with the standard release shipped with Fedora 12 (5.1.47). In this version, the user is properly removed. Something might have changed between 5.1.47 and 5.1.53.