How can I drop a MySQL user whose username contains unprintable characters?

Question

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?

Spun up a test mysql instance; I just added and removed a user with that exact name (with and without a newline at the end) without issue. doesn't help your issue i know, but i'm wondering if something else is afoot. — peelman, Jan 09 '15 at 16:44
could it be a CR (`\r`) or a CRLF (`\r\n`) and not just a new line, and od or mysql is silently stripping the CR when you spit out the raw? — peelman, Jan 09 '15 at 16:49
I would doubt that MySQL or OD is stripping anything (this is on OS X), but I guess it's possible. I just noticed that can't actually create a new user similar to this from the MySQL command line either. It appears that `CREATE USER` is stripping out the newline when I create the user in the first place. For example, this works: `create user 'foo\n\n'@'%';` followed by `drop user 'foo'@'%';` — Scott Dudley, Jan 09 '15 at 16:52
The converse also executes successfully: `create user 'foo'@'%';` and then `drop user 'foo\n\n'@'%';`. This sleuthing suggests that `DROP USER` is stripping characters too, and I see my chances of success plummeting without editing the table directly. :-( — Scott Dudley, Jan 09 '15 at 16:53
Stuffing the `CREATE USER` command into a text file (confirming the single newline) and then sourcing it with `\.` didn't help either, so it doesn't appear to be the EditLine library interfering. Also, selecting length(user) says that the string has length=15, which implies the newline only. — Scott Dudley, Jan 09 '15 at 16:57
based on [this](http://stackoverflow.com/questions/10560268/difference-between-drop-user-and-deleting-a-row-from-the-mysql-user-table) I think you can safely delete the user record manually, as long as you ditch the privileges manually as well... for the record, i've done this without issue in the past too. — peelman, Jan 09 '15 at 17:01
While your question is certain on topic and welcome here, You would probably receive better/more expert answers on [our sister site for database administration](http://www.dba.stackexchange.com). If you'd like your question migrated, please flag it for moderator attention with a request to migrate it. In the event that you do so, it might be helpful to edit the information you've added in the comments into your question. — HopelessN00b, Jan 10 '15 at 07:58
@ScottDudley did you come up with a solution that suits you? — peelman, Jan 13 '15 at 15:28
@peelman While deleting from `mysql.user` will presumably work, my pedantic side would still like to know: (a) from the original question, is it actually safe to do this? (I haven't seen anything that says it's unsafe, but...?) and (b) How to remove the orphaned privileges safely from other tables? I've found scattered SO references to various *_priv tables in the `mysql` database, but at least in my instance, the relevant data appears to be stored in `information_schema` and not `mysql`. I can surely also whack these with my `DELETE` sledgehammer, but should I be messing with these at all? — Scott Dudley, Jan 13 '15 at 16:05

score 2 · Answer 1 · answered May 01 '15 at 23:47

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.

score 1 · Answer 2 · answered Jan 19 '16 at 14:41

1

How about this?

DELETE FROM mysql.user where user like "wiki4te%" and host like "rch%";

answered Jan 19 '16 at 14:41

Kourosh Samia

301
1
5
12

score 0 · Answer 3 · edited Jun 11 '20 at 10:02

0

try this:

use mysql;

delete from mysql where user = "wiki%";

flush privileges;

EDIT: this assumes there are no other mysql users whose usernames begin with "wiki"

edited Jun 11 '20 at 10:02

Community

1

answered Jan 09 '15 at 17:02

nandoP

2,001
14
15

How can I drop a MySQL user whose username contains unprintable characters?

3 Answers3