Mysql – How to drop a MySQL user whose username contains unprintable characters

database-administrationMySQL

I have a user in MySQL 5.6 that was somehow created with a non-printable character in the username. How can I drop this user?

mysql> select user,host from mysql.user where user like 'wiki%';
+-----------------+------+
| user            | host |
+-----------------+------+
| wiki4thech@rch
 | %    |
+-----------------+------+
1 row in set (0.00 sec)

Well, that doesn't look good. Let's stick in a prefix and a suffix so we can see if MySQL is adding any padding to the output:

# mysql -E --disable-column-names -u root -p mysql --execute "select concat('START',user,'END') from user where user like 'wiki%'"
Enter password: 
*************************** 1. row ***************************
STARTwiki4thech@rch
END

It looks like a newline, but just to be sure:

# mysql -E --disable-column-names -u root -p mysql --execute \
  "select concat('START',user,'END') from user where user like 'wiki%'" | od -t x1c
Enter password: 
0000000    2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a
           *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *
0000020    2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  20  31  2e  20  72
           *   *   *   *   *   *   *   *   *   *   *       1   .       r
0000040    6f  77  20  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a
           o   w       *   *   *   *   *   *   *   *   *   *   *   *   *
0000060    2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  2a  0a  53
           *   *   *   *   *   *   *   *   *   *   *   *   *   *  \n   S
0000100    54  41  52  54  77  69  6b  69  34  74  68  65  63  68  40  72
           T   A   R   T   w   i   k   i   4   t   h   e   c   h   @   r
0000120    63  68  0a  45  4e  44  0a                                    
           c   h  \n   E   N   D  \n                                    
0000127

OK, so it's definitely a newline. Trying the obvious:

mysql> drop user 'wiki4thech@rch\n'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'wiki4thech@rch'@'%'

Slightly less obvious:

mysql> drop user 'wiki4thech@rch
    '> '@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'wiki4thech@rch'@'%'

I see another question or two that suggests editing the user table directly in similar situations, but it's unclear if this is actually a good idea (ie. what side effects could it cause, or how risky is it to mess with the system tables?).

Alternatively, is there a safe way to do this with DROP USER?

Best Answer

There is no side effects if you edit the mysql.user table directly, except one: you have to issue a 

FLUSH PRIVILEGES;

command after you done (to re-read the privilege tables and put your changes online) So the most easiest way would be if you delete the entry itself from mysql user, like

DELETE FROM mysql.user WHERE user ='wiki%';

if there is no other user which name begins with wiki (as it was mentioned before) If was, try to find an another selectable valuse like the password, etc. If there are more users with the similar name, then don't worry, you can delete them all, and recreate the ones which will be used (because the changes will only take place after you flush that privileges - as I was said before.) If you want to do the user recreation fast, you should install the Percona toolkit first, dump all the users with 

  pt-show-grants > users.sql

and reload the needed users from that dump.

Hope it helps.

Related Topic