mysql CREATE USER

Question

I am logged into mysql...

mysql -u root -pmypass

And I have run the following command to remove the database user which worked succesfully

mysql> FLUSH PRIVILEGES; DROP USER 'myuser_shop';

I can then add user successfully using the following command, again this works successfully

mysql> FLUSH PRIVILEGES; CREATE USER 'myuser_shop' IDENTIFIED BY 'mypass';

The above command create a user with the host as a wildcard (%) The issue I am having is if I was to create a user with the host being say localhost or an external IP i get the following error:

mysql> FLUSH PRIVILEGES; DROP USER 'myuser_shop';
mysql> FLUSH PRIVILEGES; CREATE USER 'myuser_shop'@'localhost' IDENTIFIED BY 'mypass';

Query OK, 0 rows affected (0.00 sec)

ERROR 1396 (HY000): Operation CREATE USER failed for 'myuser_shop'@'localhost'

Any suggestions?

Thanks in advance

What you are trying looks ok to me. Have you checked out bugs.mysql.com/bug.php?id=28331 to see if your problem is described there? — jphofmann
– jphofmann, Commented Nov 3, 2009 at 14:21
+1 for your comment for Svetlozar Angelov - it solved my problem as well! — Nir Alfasi
– Nir Alfasi, Commented Sep 4, 2012 at 22:44

Svetlozar Angelov · Accepted Answer · 2009-11-03 14:22:21Z

13

This is a bug reported here - http://bugs.mysql.com/bug.php?id=28331

See if your user exists after you drop it.

answered Nov 3, 2009 at 14:22

Svetlozar Angelov

21.8k7 gold badges64 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lizard Over a year ago

I have checked the 'user' table in the 'mysql' database and there is no reference to myuser_shop.

Lizard Over a year ago

Actually although it didn't appear in the user table, it must have existed in the db file, i specifically removed the FLUSH PRIVILEGES; DROP USER 'myuser_shop'@'localhost'; and the CREATE USER then worked! thanks

Alexei Tenitski · Accepted Answer · 2009-11-17 09:56:17Z

2

Check following 2 tables if user is still there after DROP USER command: mysql.user and mysql.db.

Delete user from both, then run FLUSH PRIVILEGES and then you can recreate the user.

answered Nov 17, 2009 at 9:56

Alexei Tenitski

9,5206 gold badges45 silver badges51 bronze badges

Comments

Humberto · Accepted Answer · 2013-05-22 17:48:00Z

0

drop user 'username@localhost '

answered May 22, 2013 at 17:48

Humberto

1

1 Comment

Ascherer Over a year ago

You should explain your answer more

insCode · Accepted Answer · 2016-01-10 11:30:06Z

0

i will suggest you to use phpmyadmin its very esey do do with that.

steps:

open phpmyadmin
go to admin section
hit on add user account
put user name and password
set privileges

that's all see in action on youtube [ click here ]

answered Jan 10, 2016 at 11:30

insCode

1,28314 silver badges10 bronze badges

Comments

marek rodkiewicz · Accepted Answer · 2012-04-06 07:15:00Z

-1

You have to manualy remove records with from mysql.tables_priv also.

answered Apr 6, 2012 at 7:15

marek rodkiewicz

415 bronze badges

1 Comment

Sammitch Over a year ago

Manually messing about in the mysql.* is highly not-recommended.

Collectives™ on Stack Overflow

mysql CREATE USER

5 Answers 5

2 Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related