Unable To Remove User

I just created a generic test user account on MySQL 5.1 and now for
some reason I am unable to remove him...

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select User, Host from user;
+--------+------------+
| User | Host |
+--------+------------+
| carlos | localhost |
| root | localhost |
+--------+------------+
2 rows in set (0.01 sec)

I obviously from above have a user name 'carlos'@'localhost' & when I
try to delete him, I get the following:

mysql> drop user 'carlos'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'carlos'@'localhost'

Can someone please help me understand why I am unable to remove this
user from MySQL?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unable To Remove User

At 06:35 PM 9/29/2009, Carlos Williams wrote:
>I just created a generic test user account on MySQL 5.1 and now for
>some reason I am unable to remove him...
>
>mysql> use mysql;
>Reading table information for completion of table and column names
>You can turn off this feature to get a quicker startup with -A
>
>Database changed
>mysql> select User, Host from user;
>+--------+------------+
>| User | Host |
>+--------+------------+
>| carlos | localhost |
>| root | localhost |
>+--------+------------+
>2 rows in set (0.01 sec)
>
>I obviously from above have a user name 'carlos'@'localhost' & when I
>try to delete him, I get the following:
>
>mysql> drop user 'carlos'@'localhost';
>ERROR 1396 (HY000): Operation DROP USER failed for 'carlos'@'localhost'
>
>Can someone please help me understand why I am unable to remove this
>user from MySQL?


Why don't you just say "Drop User carlos"?
Also are you logged in as root?

Mike


>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unable To Remove User

On Tue, Sep 29, 2009 at 11:36 PM, mos wrote:
> Why don't you just say "Drop User carlos"?
> Also are you logged in as root?

In my original message to the board I demonstrated the error I get on
my server when I attempt to run the 'drop user 'carlos'@'localhost';
command. Why can't I remove this user from MySQL? I am logged in as
root on both Linux and MySQL so I don't see why I can't remove
'carlos'@'localhost'. It's as if MySQL doesn't understand this user
exist. He might not exist because I can't login as him however I need
this entry removed from the "user" table.

[root@tiger ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.38 Source distribution

mysql> show grants for 'carlos'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'carlos'
on host 'localhost'
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select User, Host from user;
+--------+------------+
| User | Host |
+--------+------------+
| carlos | localhost
|
| root | localhost |
+--------+------------+
2 rows in set (0.00 sec)

mysql> drop user 'carlos'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'carlos'@'localhost'

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unable To Remove User

--001485e9ab4ff849e50474ca88ef
Content-Type: text/plain; charset=ISO-8859-1

Remove manually (delete) the user from all the privilege tables:

delete from columns_priv where user='carlos';
delete from db where user='carlos';
delete from procs_priv where user='carlos';
delete from tables_priv where user='carlos';
delete from user where user='carlos';

then a nice FLUSH PRIVILEGES;

this should fix, let me know!

Claudio

2009/9/30 Carlos Williams

> On Tue, Sep 29, 2009 at 11:36 PM, mos wrote:
> > Why don't you just say "Drop User carlos"?
> > Also are you logged in as root?
>
> In my original message to the board I demonstrated the error I get on
> my server when I attempt to run the 'drop user 'carlos'@'localhost';
> command. Why can't I remove this user from MySQL? I am logged in as
> root on both Linux and MySQL so I don't see why I can't remove
> 'carlos'@'localhost'. It's as if MySQL doesn't understand this user
> exist. He might not exist because I can't login as him however I need
> this entry removed from the "user" table.
>
> [root@tiger ~]# mysql -u root -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 9
> Server version: 5.1.38 Source distribution
>
> mysql> show grants for 'carlos'@'localhost';
> ERROR 1141 (42000): There is no such grant defined for user 'carlos'
> on host 'localhost'
> mysql> use mysql;
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Database changed
> mysql> select User, Host from user;
> +--------+------------+
> | User | Host |
> +--------+------------+
> | carlos | localhost
> |
> | root | localhost |
> +--------+------------+
> 2 rows in set (0.00 sec)
>
> mysql> drop user 'carlos'@'localhost';
> ERROR 1396 (HY000): Operation DROP USER failed for 'carlos'@'localhost'
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--001485e9ab4ff849e50474ca88ef--

Re: Unable To Remove User

Carlos Williams wrote:
> On Tue, Sep 29, 2009 at 11:36 PM, mos wrote:
>> Why don't you just say "Drop User carlos"?
>> Also are you logged in as root?
>
> In my original message to the board I demonstrated the error I get on
> my server when I attempt to run the 'drop user 'carlos'@'localhost';
> command. Why can't I remove this user from MySQL?

[ snip ]

> Database changed
> mysql> select User, Host from user;
> +--------+------------+
> | User | Host |
> +--------+------------+
> | carlos | localhost
> |
> | root | localhost |
> +--------+------------+
> 2 rows in set (0.00 sec)


Carlos, is this output exactly as it appeared when you ran the command? Specifically, that extra carriage return after localhost for the user carlos? I wonder if the host value is actually localhost followed by a carriage return, which is why it isn't found when you try to drop the user using the 'carlos'@'localhost' value. Try this select statement to confirm:

select user, host from user where host = 'localhost';

If you don't see carlos returned as a user, this is potentially the problem. If that's the case, use Claudio's suggestion to remove the user manually.

Ciao
Fuzzy
:-)

------------------------------------------------
Dazed and confused about technology for 20 years
http://fuzzydata.wordpress.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Unable To Remove User

What version of mysql a you using? Drop user works differently for versions=
before 5.0.2.

You could just remove the user from the mysql.user table with a delete stat=
ement.

Regards

John Daisley
Mobile +44(0)7812 451238
Email john@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

-----------------------
Sent from HP IPAQ mobile device.



-----Original Message-----
From: Carlos Williams
Sent: Wednesday, September 30, 2009 12:47 PM
To: mysql@lists.mysql.com
Subject: Re: Unable To Remove User

On Tue, Sep 29, 2009 at 11:36 PM, mos wrote:
> Why don't you just say "Drop User carlos"?
> Also are you logged in as root?

In my original message to the board I demonstrated the error I get on
my server when I attempt to run the 'drop user 'carlos'@'localhost';
command. Why can't I remove this user from MySQL? I am logged in as
root on both Linux and MySQL so I don't see why I can't remove
'carlos'@'localhost'. It's as if MySQL doesn't understand this user
exist. He might not exist because I can't login as him however I need
this entry removed from the "user" table.

[root@tiger ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.38 Source distribution

mysql> show grants for 'carlos'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'carlos'
on host 'localhost'
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
=0A=
=0A=
[The entire original message is not included]=


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Unable To Remove User

--_1bd72867-6f62-4389-a01b-e49d153f5bb6_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Grant

he is trying to login as that user
then drop the user he logged in as

Martin Gainty=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
=20
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..




> Date: Wed=2C 30 Sep 2009 22:39:50 +1000
> From: gxallen@gmail.com
> To: carloswill@gmail.com
> CC: mysql@lists.mysql.com
> Subject: Re: Unable To Remove User
>=20
> Carlos Williams wrote:
> > On Tue=2C Sep 29=2C 2009 at 11:36 PM=2C mos wrote:
> >> Why don't you just say "Drop User carlos"?
> >> Also are you logged in as root?
> >=20
> > In my original message to the board I demonstrated the error I get on
> > my server when I attempt to run the 'drop user 'carlos'@'localhost'=3B
> > command. Why can't I remove this user from MySQL?
>=20
> [ snip ]
>=20
> > Database changed
> > mysql> select User=2C Host from user=3B
> > +--------+------------+
> > | User | Host |
> > +--------+------------+
> > | carlos | localhost
> > |
> > | root | localhost |
> > +--------+------------+
> > 2 rows in set (0.00 sec)
>=20
>=20
> Carlos=2C is this output exactly as it appeared when you ran the command?=
Specifically=2C that extra carriage return after localhost for the user c=
arlos? I wonder if the host value is actually localhost followed by a carr=
iage return=2C which is why it isn't found when you try to drop the user us=
ing the 'carlos'@'localhost' value. Try this select statement to confirm:
>=20
> select user=2C host from user where host =3D 'localhost'=3B
>=20
> If you don't see carlos returned as a user=2C this is potentially the pro=
blem. If that's the case=2C use Claudio's suggestion to remove the user ma=
nually.
>=20
> Ciao
> Fuzzy
> :-)
>=20
> ------------------------------------------------
> Dazed and confused about technology for 20 years
> http://fuzzydata.wordpress.com/
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20
=0A=
____________________________________________________________ _____=0A=
Insert movie times and more without leaving Hotmail=AE.=0A=
http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=3DTXT_ TAGLM_WL_HM_Tut=
orial_QuickAdd_062009=

--_1bd72867-6f62-4389-a01b-e49d153f5bb6_--

Re: Unable To Remove User

On Wed, Sep 30, 2009 at 9:12 AM, Martin Gainty wrote:
> Grant
>
> he is trying to login as that user
> then drop the user he logged in as

No, no and no. I clearly showed my commands including my login to
MySQL as root. I also cleanly noted I can't even login as the user I
am trying to delete.

I will try Claudio's suggestion and post back my results. I find it
odd that the output of users has a carriage return in it which
malformed the table output. It's not normally like that so you're
possibly right and that could be the issue! Either way I hope
Claudio's instructions resolve it.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Unable To Remove User

--_f36cff7b-d862-43b9-b768-ee7b7e6bedf8_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit


hi Carloswil,



could you do below check:

1. show processlists; // is there any session logined by this user account?

2. select CURRENT_USER() ; // ensure you are logining with root@localhost, not the root@%



best regards

liuyann



> Date: Wed, 30 Sep 2009 09:37:28 -0400
> Subject: Re: Unable To Remove User
> From: carloswill@gmail.com
> To: mysql@lists.mysql.com
>
> On Wed, Sep 30, 2009 at 9:12 AM, Martin Gainty wrote:
> > Grant
> >
> > he is trying to login as that user
> > then drop the user he logged in as
>
> No, no and no. I clearly showed my commands including my login to
> MySQL as root. I also cleanly noted I can't even login as the user I
> am trying to delete.
>
> I will try Claudio's suggestion and post back my results. I find it
> odd that the output of users has a carriage return in it which
> malformed the table output. It's not normally like that so you're
> possibly right and that could be the issue! Either way I hope
> Claudio's instructions resolve it.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=liuyann@live.com
>

____________________________________________________________ _____
Show them the way! Add maps and directions to your party invites.
http://www.microsoft.com/windows/windowslive/products/events .aspx
--_f36cff7b-d862-43b9-b768-ee7b7e6bedf8_--

Re: Unable To Remove User

On Wed, Sep 30, 2009 at 8:18 AM, Claudio Nanni wrote:
> Remove manually (delete) the user from all the privilege tables:
>
> delete from columns_priv where user='carlos';
> delete from db where user='carlos';
> delete from procs_priv where user='carlos';
> delete from tables_priv where user='carlos';
> delete from user where user='carlos';
>
> then a nice FLUSH PRIVILEGES;
>
> this should fix, let me know!

Worked great!!!

mysql> select User, Host, Password from user;
+------+-----------+---------------------------------------- ---+
| User | Host | Password |
+------+-----------+---------------------------------------- ---+
| root | localhost | *FDCCC9BE5FC366E7D9714988DBC7F111A950C428 |
+------+-----------+---------------------------------------- ---+
1 row in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org