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"
To: "Tim Dunphy"
Cc: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
