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
?