List:General Discussion« Previous MessageNext Message »
From:Tim Dunphy Date:January 15 2012 12:23am
Subject:Re: delete all hosts using a wildcard
View as plain text  
Hello again list,

 Thanks for pointing out where I was making my mistake. I just needed to select the right
field. And this is just a test environment so getting rid of those users won't have any
meaningful impact. Also previewing what you will be deleting by using a select is great
advice I intend to use. 

Best
tim

----- Original Message -----
From: "Paul DuBois" <paul.dubois@stripped>
To: "Tim Dunphy" <bluethundr@stripped>
Cc: mysql@stripped
Sent: Saturday, January 14, 2012 6:46:38 PM
Subject: Re: delete all hosts using a wildcard


On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote:

> hello list,
> 
> I have a number of hosts that I would like to delete using a wildcard (%) symbol. 
> 
>  Here is the query I am using:
> 
>  mysql> delete from mysql.user where user='%.summitnjhome.com';

Couple of things:

* You want to compare your pattern to the host column, not user.
* To match the pattern, use LIKE, not =.

So: WHERE host LIKE '%.summitnjhome.com'

But to see what rows your DELETE will affect, try this first:

SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com';

Something else to consider: What if these accounts have privileges
defined in the other grant tables, such as database-level privileges
in the db table?

http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like
http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html

> Query OK, 0 rows affected (0.00 sec)
> 
> And I am attempting to delete all the hosts at the domain 'summitnjhome.com'...
> 
> But as you can see I am unsuccessful:
> 
> mysql> select user,host from mysql.user;
> +----------+-----------------------------+
> | user     | host                        |
> +----------+-----------------------------+
> | root     | 127.0.0.1                   |
> | repl     | virtcent10.summitnjhome.com |
> | admin    | virtcent11.summitnjhome.com |
> | repl     | virtcent19.summitnjhome.com |
> | repl     | virtcent23.summitnjhome.com |
> | repl     | virtcent30.summitnjhome.com |
> +----------+-----------------------------+
> 
> 
> I know I can delete them individually and this is what I am going to do. But I would
> like to use this as a learning opportunity to help me understand how the wildcard works. 
> 
> Thanks in advance..
> 
> Best regards,
> Tim

-- 
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com

Thread
delete all hosts using a wildcardTim Dunphy14 Jan
  • Re: delete all hosts using a wildcardPaul DuBois14 Jan
    • Re: delete all hosts using a wildcardTim Dunphy15 Jan
      • Re: delete all hosts using a wildcardClaudio Nanni16 Jan
Re: delete all hosts using a wildcardJan Steinman16 Jan
  • Re: delete all hosts using a wildcardGovinda16 Jan