Sql 1396 error

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»;

A few weeks back, we faced an issue in a replication environment for a Managed Services client:

LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”

After some initial investigation, we noticed that the user in the replica didn’t exist! Was MySQL going crazy? But then the customer mentioned they had the following error in the primary before being able to execute the query successfully:

But we knew that the first command couldn’t be replicated (since failed commands are never logged to the binlog), so the question was, what happened, and why was MySQL having trouble creating a nonexistent user?

If you do quick research, you will find a few posts offering some options: 

• Execute FLUSH PRIVILEGES.
• DROP USER and then FLUSH PRIVILEGES.
• Many mentions that this is a reported bug.

And fair enough, if you try the options mentioned above, you’ll fix the issue eventually. Still, I wanted to take a deeper look at why this happened and what should be the correct approach to fix it (and understand WHY something will fix it); so I decided to do some tests and create a post for whoever faces an issue like this in the future, in the hope that you’ll too understand it.

Let’s start with a reminder: You shouldn’t manipulate the mysql grant tables manually, and if you do it, you should be sure that you know what you’re doing. So to start, here’s a summary of how MySQL’s privileges work.

A summary of MySQL privileges

At startup, MySQL reads the grant tables and loads them into memory, so whenever it needs to check if a user is allowed to read this or to connect there, MySQL can look for the privileges faster (reading from memory).

MySQL keeps track of the privileges using the grant tables, so when we issue a CREATE USER, internally, it translates to

• Insert into mysql.user
• Insert into mysql.db

If we GRANT or REVOKE, it will translate into a

• Insert into mysql.table_priv
• Update in mysql.db
• Delete in mysql.table_priv.

And so on. Please note this isn’t an exact step-by-step on what happens; it’s just an example of a few internal things MySQL needs to execute.

Finally, after each Account Management Statement, MySQL reads the new privileges into memory, and the changes are applied.

With this, MySQL makes it easy for us to manipulate privileges; should you need to delete a user, instead of manually deleting every row in each grant table, you can execute a DROP USER, and that’s it.

Even though it’s simpler that way, sometimes users decide to go the “hard way” and manipulate the privileges manually: instead of DROP USER, execute a DELETE from grant tables. But, as mentioned before, MySQL won’t know about these changes because the grant tables were read already, and the privileges are in memory.

Let’s go through the issue mentioned above

First step: The user was created in the primary server:

Second step: After some tests, they wanted to drop the user and create it again, but they went with manually deleting the user:

So far, as commented above, MySQL isn’t aware that the user test_user was deleted (MySQL hasn’t re-read the grant tables into memory because they did not execute a FLUSH PRIVILEGES); that’s why when they tried to create the user again, it failed.

And since they couldn’t find the user, they did what we would all do: Retry again and hope that the command would magically work this time.

And it worked, the user got created, and everything was ok in the world again. Why did it work? Because with the first CREATE USER (even though the command failed), MySQL did a reload of the grant privileges.

Here’s what happened with the first CREATE:

1. MySQL checks its in-memory table and finds that the user already exists.
2. Rejects the new user because it can’t duplicate the user.
3. MySQL reloads the in-memory tables (This happens regardless of whether it was successful).

So, even though the command failed, MySQL reloads the in-memory tables and the user test_user doesn’t exist (since the user was deleted manually before); that’s why the second CREATE was successful.

The second CREATE USER executes successfully, it goes to the binlog and to the replica.

Now, from the replica’s point of view so far it has replicated the DELETE statements and ONE CREATE USER, and back to where it all began, the error in the replica:

LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”

With the things explained above, I think it’s easier to understand what happened here: This CREATE statement is the “first” since the first one in the primary failed, so that one didn’t reach the replica, so to “fix” this we only started the replication, and that did the trick, this time the CREATE worked because it already reloaded the in-memory tables.

Before finishing, I wanted to test a few more things to understand why sometimes a simple FLUSH PRIVILEGES works and why sometimes it’s necessary to DROP USER entirely; short answer: It depends on how “well” you manually manipulated the grant tables.

When FLUSH PRIVILEGES does the trick

Since the create user only inserted in mysql.user, it was the only table where the user exists, so the flush privileges command was successful.

When FLUSH PRIVILEGES isn’t enough

There are many grant tables, so when manually deleting a user, it’s easy to miss one of them and lead to errors that FLUSH PRIVILEGES won’t solve.

The GRANT command inserts a record in mysql.db table as well

Then if I manually delete, but only from mysql.user table, the record in mysql.db will be there until it’s cleaned.

So even if I try with the FLUSH command, I won’t be able to create the user (due to the user existing in mysql.db)

The proper way to do this is by executing the DROP command and letting MySQL clean whatever tables are required (You can see below that the mysql.db table was cleaned as well).

And now the CREATE works:

What about “IF NOT EXISTS” clause?

Using the same scenario with replication; first, create the user in the primary.

After this, manually delete the user from the mysql.user table.

We know that a CREATE USER command would fail and reload the in-memory tables; let’s see what happens if we add the IF NOT EXISTS clause:

No errors, but one warning:

And the user doesn’t exist, as expected (It was manually deleted, but without FLUSH PRIVILEGES so MySQL is not aware).

But, this time the command was logged in the binlog:

And was replicated without issues as well, below are the relay log events:

Last thing to keep in mind, this time the Account Management Statement didn’t reload the grant tables after execution (We can know this because the second execution didn’t create the user, as happens when the CREATE USER  is executed without the clause, see the warnings below):

So, the IF NOT EXISTS clause can save you from the error (and no issues in a replication environment as well), but as always, be sure to check the warnings to understand what’s happening, otherwise, you’ll still don’t have the user required.

Final thoughts

You should definitely use the Account Management Statements when you want to modify privileges/users in MySQL, so you don’t have to worry about all the details.

I wanted to share this particular scenario with you in the hopes that this will make clearer how MySQL manages internal privileges and that the in-memory tables are a critical thing to have in mind should you modify anything manually.

And finally, remember that the docs are your friend:

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. Thus, if you change the grant tables directly but forget to reload them, the changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!