why can't create a new user in mysql?

Question

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
| debian-sys-maint | localhost |
| developer        | localhost |
| jack             | localhost |
| root             | localhost |
| root             | rebuild   |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wpdatabase         |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE USER wpuser@localhost;
ERROR 1396 (HY000): Operation CREATE USER failed for 'wpuser'@'localhost'

Why can't create a new user in mysql? The user wpuser is not in the table user. I neved used databases, so can anyone help me on how to create a new user ?

No,it is not the problem of password(123456 is the key of mysql database).

mysql> CREATE USER 'wpuser'@'localhost' IDENTIFIED BY '123456';
ERROR 1396 (HY000): Operation CREATE USER failed for 'wpuser'@'localhost'

It is so strange ,please go on .

Why CREATE USER wpuser@localhost; can't ; CREATE USER wpusers@localhost; can?

Answer 1

I had the same problem I believe. I accidentally created 'myuser', deleted it using the command below, and then I cannot create the user, although its not showing up on mysql.user table

I tried these commands for deleting but to no avail.

delete user from mysql.user where user='myuser'
delete user from mysql.user where user='myuser' and host='localhost'
delete user from mysql.user where user='myuser' and host='%'

It worked for me when I use this command to remove the user.

DROP USER 'myuser'@'localhost';

In between trying out these commands I FLUSH PRIVILEGES as though I'm on diarrhoea. So in case it still does not work, do what Begueradj suggested.

Answer 2

The user you want to creat must have a MySQL password:

CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'password';

Then give him permissions:

GRANT ALL PRIVILEGES ON * . * TO 'wpuser'@'localhost';

Do not forget to reload all the privileges:

FLUSH PRIVILEGES;

Hope this helps you.

Answer 3

For me, I got this error in mysql workbench on trying to re-add a user that I had in the DB, but then I tried this query & found that the user was actually added:

select user, host from mysql.user

So I suggest if someone gets that error in mysql workbench to try this query as a first measure before the more complex solutions as the user might be already created.