Ошибка 1396 mysql

A simple work around on this issue. As «delete» command only removes the user record in «user» table of «mysql» database, we could add it back and then drop the user completely. Then you could create user with same name.

Step 1. find the record format of user table in mysql database

use mysql;
select * from user;

Step 2. According to the columns showed in step1, create a dummy record with the user name. Insert it into the table, for example, be reminded to replace the «username» with your username.

Insert into user value ('%','username','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','','','','','0','0','0',
'0','mysql_native_password',
'*52C5E3AC6BC5E2E0BFF86978BF62A1481AC79D58','N',
'2016-12-10 23:59:12',null,'N');

Note: sometimes you may encounter issues in inserting, just change the data to make it work.

Step 3. Drop the user.

drop user username;

Now you are able to create user with same name.

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

Does indeed indicate that the user already exists or did exist.

FLUSH PRIVILEGES doesn’t delete users.

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT, CREATE USER, 
CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released 
by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN 
statements, so for a server that executes many instances of the statements 
that cause caching, there will be an increase in memory use. 
This cached memory can be freed with FLUSH PRIVILEGES.

You are looking for DROP USER.

DROP USER user [, user] ...

http://dev.mysql.com/doc/refman/5.1/en/drop-user.html

Order of buisness would be:

DROP USER 'saravanakumar'@HOSTNAME;
CREATE USER 'saravanakumar'@HOSTNAME [IDENTIFIED BY 'password'];

You will probably need to flush privileges if you use delete from (do not).
Remember: this does not necessarily revoke all the privileges this user may have (like table privileges), you will have to do this yourself — if you don’t you may not be able to recreate the user.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'saravanakumar'@HOSTNAME;
DELETE FROM mysql.user WHERE user='saravanakumar';
FLUSH PRIVILEGES;
CREATE USER 'saravanakumar'@HOSTNAME [IDENTIFIED BY 'password'];

«user» requires you to specify an account name

Syntax for account names is 'user_name'@'host_name'

and

An account name consisting only of a user name is equivalent 
to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.

Additional reading: http://dev.mysql.com/doc/refman/5.1/en/account-names.html

Please read those bug reports for further clarification

http://bugs.mysql.com/bug.php?id=28331

http://bugs.mysql.com/bug.php?id=62255

Do you have trouble adding MySQL database users?

Often MySQL error code 1396 pops up when we try to create a user that already exists.

At Bobcares, we often receive requests to solve such Database errors as part of our Server Management Services.

Today we will discuss MySQL error code 1396 in detail and see how our Support Engineers fix this error for our customers.

What is the MySQL error code 1396?

All databases should have valid users that have privileges to do database queries as well as edits.

Unfortunately, errors are common while querying in the MySQL databases. MySQL error code 1396 is often related to website restore, migration, etc. In such scenarios, this error occurs when we try to create an already existing user in MySQL.

Similarly, we often see this error even if we delete the already existing user too.

A typical error message when adding MySQL user appears as:

How we fix MySQL error 1396?

We now know the scenario that causes MySQL error 1396. Let’s see how our Support Engineers fix this error.

Recently, one of our customers got MySQL error 1396 while trying to create a new user. He checked and found that the user already existed in the database. So, he deleted the user and tried creating the user once again.

But unfortunately, he received the same error.

Here, our Support Engineers started troubleshooting by checking all occurrences of the database user in MySQL table.

We used the command:

use mysql;
select * from user;

There was a user with the same name and this was creating problems while new user creation. Here, the customer was trying to delete the user using the command DROP user username. In MySQL, if you specify only the user name part of the account name, it uses a hostname part of ‘%’. This stopped removing the correct user.

Therefore, we had to remove the exact MySQL user using:

mysql>delete from user where user='username'and host='localhost';

Further, we used the FLUSH PRIVILEGEs command to remove all the caches. FLUSH PRIVILEGE operation will make the server reload the grant tables.

So, we executed the following query and created the newuser ‘user’ successfully.

mysql>flush privileges;
mysql>CREATE USER 'user'@'localhost' IDENTIFIED BY 'xxx123';

This resolved the error effectively.

[Need help to solve MySQL error code 1396?- We’ll help you.]

Conclusion

In short, the MySQL error 1396 occurs when we try to create a user that already exists in the database. Today’s write-up also discussed the various causes of this error and saw how our Support Engineers fixed it for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;