MySQL Drop User Fails for Remote Host

mysql-5.5remoteusers

We're clearing out some old MySQL users that connected from remote hosts during a database transfer.

Why is this DROP USER query failing for the user asdfasdf_imac4? I'm logged in as root.

mysql> select user,host from mysql.user;
+------------------+----------------------+
| user             | host                 |
+------------------+----------------------+
| root             | 127.0.0.1            |
| root             | ::1                  |
| debian-sys-maint | localhost            |
| forum            | localhost            |
| root             | localhost            |
| asdfasdf_imac4   | ‘303.303.303.90’     |
| asdfasdf_imac4   | ‘303.303.303.92’     |
+------------------+----------------------+
7 rows in set (0.00 sec)

mysql> drop user 'asdfasdf_imac4'@'303.303.303.90';
ERROR 1396 (HY000): Operation DROP USER failed for 'asdfasdf_imac4'@'303.303.303.90'

Best Answer

I have never seen an IP address like that before. Since 303 is an impossible number for an IP address, you should just save yourself the headache and delete both from mysql.user

DELETE FROM mysql.user WHERE user='asdfasdf_imac4';
FLUSH PRIVILEGES;

Give it a Try !!!

Related Solutions

Mysql – Adding a user to MySQL with ‘name’@’%’ fails with ERROR 1396

According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
mysql> CREATE USER 'name' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

If you delete the user and still get the message, try running FLUSH PRIVILEGES.

Also see this SO question for additional info.

MySQL User with Host set to a Range of IP Addresses

you can use the grant assign to a netmask like:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'81.10.20.1/255.255.255.240';

It will give you access from 81.10.20.1 -> 81.10.20.14, just change the subnet part to fit the range you need.

You can see more info at mysql doc account names

Best Answer

Related Solutions

Mysql – Adding a user to MySQL with ‘name’@’%’ fails with ERROR 1396

MySQL User with Host set to a Range of IP Addresses

Related Question