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 http://bugs.mysql.com/bug.php?id=28331 to see if your problem is described there? — jphofmann, Nov 03 '09 at 14:21
+1 for your comment for Svetlozar Angelov - it solved my problem as well! — Nir Alfasi, Sep 04 '12 at 22:44

score 13 · Accepted Answer · answered Nov 03 '09 at 14:22

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 03 '09 at 14:22

Svetlozar Angelov

21,214
6
62
67

I have checked the 'user' table in the 'mysql' database and there is no reference to myuser_shop. – Lizard Nov 03 '09 at 14:31
5

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 – Lizard Nov 03 '09 at 14:35

score 2 · Answer 2 · answered Nov 17 '09 at 09:56

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 '09 at 09:56

Alexei Tenitski

9,030
6
41
50

score 0 · Answer 3 · answered May 22 '13 at 17:48

0

drop user 'username@localhost '

answered May 22 '13 at 17:48

Humberto

1

You should explain your answer more – Ascherer May 22 '13 at 18:07

score 0 · Answer 4 · answered Jan 10 '16 at 11:30

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 '16 at 11:30

insCode

1,227
14
10

score -1 · Answer 5 · answered Apr 06 '12 at 07:15

-1

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

answered Apr 06 '12 at 07:15

marek rodkiewicz

41
5

2

Manually messing about in the `mysql.*` is highly not-recommended. – Sammitch Oct 26 '12 at 16:15

mysql CREATE USER

5 Answers5

Linked