4

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?

10
  • 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
    Commented Jan 9, 2015 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
    Commented Jan 9, 2015 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'@'%'; Commented Jan 9, 2015 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. :-( Commented Jan 9, 2015 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. Commented Jan 9, 2015 at 16:57

3 Answers 3

3

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.

2

How about this?

DELETE FROM mysql.user where user like "wiki4te%" and host like "rch%";
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"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .