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.