Error 1396 hy000 operation create user failed for phpmyadmin localhost

How to fix MySQL ERROR 1396 — CREATE or DROP USER failed

Posted on Oct 07, 2021

Learn how to fix MySQL ERROR code 1396 related to creating and removing users account

The MySQL ERROR 1396 occurs when MySQL failed in executing any statement related to user management, like CREATE USER or DROP USER statements.

This error frequently appears when you run statements to create or remove users from your MySQL database server.

MySQL has a bug that triggers this error when you remove a user without using the DROP USER statement.

This bug prevents you from re-creating a user previously deleted using the DELETE statement.

For example, suppose you create and then delete the developer account as shown below:

Then the next time you create the user developer in your database server, you will trigger the error as follows:

To fix this, you need to run a DROP USER statement for the same user account.

MySQL will respond with the same error, but after that you can create the user again.

Take a look at the following example:

Even though the DROP USER statement above throws an error, the same user can be created using the CREATE USER statement after that.

The error hasn’t been fixed up to MySQL version 8.0.26 as of today.

Other ways the error can be triggered

The error can also occur when you run the CREATE USER statement for an already existing user:

The same error could happen when you run the DROP USER or ALTER USER statement for a non-existing user account:

To list all existing users in your database server, you need to query the user table in your mysql database.

SELECT the user and host column from the table as follows:

Please note that you may have different values between % and localhost in the host column.

Here’s an example from my database:

The % value in the host column is a wild card that allows the user account to connect from any host location.

The localhost value means that you need to connect from the localhost only.

MySQL treats two identical user account with different hosts value as different users.

When you don’t specify the host value in the CREATE USER statement, it will default to the % wild card.

The statements above will create two developer accounts with different hosts:

When you trigger the ERROR 1396 that’s not caused by the bug above, be sure to check out the users you have in your database first.

Level up your programming skills

I’m sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I’ll send new stuff straight into your inbox!

About

Nathan Sebhastian is a software engineer with a passion for writing tech tutorials.
Learn JavaScript and other web development technology concepts through easy-to-understand explanations written in plain English.

Источник

MySQL error code 1396 – Let’s solve it!

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:

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:

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.

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

Technology blog by Rathish kumar

[Solved] MySQL User Operation — ERROR 1396 (HY000): Operation CREATE / DROP USER failed for ‘user’@’host’

REAL AND GENUINE ONLINE LOVE SPELL CASTER TO BRING EX LOVER URGENTLY CALL OR WHATSAPP +2348118829899.

I want to use this opportunity I have to say a big thanks to Dr Great for getting back my ex husband. I’m Linda Gibson from USA, after 7 years of marriage my husband left me for another woman I did whatever I could to get him back but all I did was in vain I was sad, but I didn’t lose hope of getting him back because I had faith in God. So I search online on how to get my husband back, and I came across a post of Dr Great of how he helped a lady to get back her ex, I contacted him and told him the pain I was going through, he told me what to do and I did it, then he casted a love spell for me 48 hours later my husband called me and told me he is sorry for what he did and that he misses me very much, later that day he came back home and beg for my forgiveness, since then our love grew stronger. For the marvelous things Dr Great has done for me it would be unfair for me not to let the whole world know that such a powerful spell caster do live. If you want to get back your ex fast email Dr Great at infinitylovespell@gmail.com or infinitylovespell@yahoo.com or add him up on WhatsApp +2348118829899 Blog: lindagibson90.blogspot.com view his blog http://infinitylovespell1.blogspot.com

REAL AND GENUINE ONLINE LOVE SPELL CASTER TO BRING EX LOVER URGENTLY CALL OR WHATSAPP +2348118829899.

Источник

MySQL: ERROR 1396 (HY000): Operation DROP USER failed for ‘username’

При удалении пользователя MySQL сообщает об ошибке:

mysql> drop user usertest;
ERROR 1396 (HY000): Operation DROP USER failed for ‘usertest’@’%’

Хотя пользователь вроде есть:

Проверяем права пользователя:

mysql> show grants for ‘usertest’@’%’;
ERROR 1141 (42000): There is no such grant defined for user ‘usertest’ on host ‘%’

При выборке надо добавлять поле Host:

mysql> select User, Host from mysql.user where user like ‘usertest’;
+———+————+
| User | Host |
+———+————+
| usertest| localhost |
+———+————+
1 row in set (0.00 sec)

И теперь удаляем:

mysql> drop user ‘usertest’@’localhost’;
Query OK, 0 rows affected (0.00 sec)

Если имя было указано верно, но данный способ не помог, то проверьте активность сессий пользователя:

Остановите процесс, если такой есть:

mysql> kill номер процесса;
Query OK, 0 rows affected (0.01 sec)

После того, как остановили процесс – отзовите права пользователя на базу:

mysql> revoke all privileges on usertestdb.* from ‘usertest’@’localhost’;
Query OK, 0 rows affected (0.00 sec)

И после этого попробуйте удалить пользователя:

mysql> drop user ‘usertest’@’localhost’;
Query OK, 0 rows affected (0.00 sec)

Источник

ОШИБКА 1396 (HY000): сбой операции CREATE USER для ‘jack’ @ ‘localhost’

Кажется, я не могу воссоздать простого пользователя, которого я удалил, даже как root в MySQL.

Мой случай: пользовательский «jack» существовал раньше, но я удалил его из mysql.user, чтобы его воссоздать. Я не вижу пережитков этого в этой таблице. Если я выполню эту команду для какого-то другого случайного имени пользователя, скажем, «jimmy», он отлично работает (как это первоначально делалось для «jack» ).

Что я сделал, чтобы повредить «гнездо пользователя» и как я могу отменить это повреждение, чтобы воссоздать «джек» в качестве допустимого пользователя для этой установки MySQL?

См. пример ниже. (Конечно, изначально было много времени между созданием «джек» и его удалением.)

19 ответов

Попробуйте выполнить FLUSH PRIVILEGES . Это сообщение об ошибке MySQL в этом коде ошибки сообщает о некотором успехе в случае, аналогичном вашему, после промывки привилегий.

Да, эта ошибка существует. Однако я нашел небольшое обходное решение.

• Предположим, что пользователь существует, поэтому отпустите
• После удаления пользователя необходимо сбросить привилегии mysql
• Теперь создайте пользователя.

Это должно решить. Предполагая, что мы хотим создать user admin @localhost, это были бы команды:

Этот баг сидит на bugs.mysql.com с 2007 года, и этот поток в основном просто распутывает все эти неправильные ответы даже до года назад.

Согласно документации MySQL, команды типа CREATE USER , GRANT , REVOKE и DROP USER не требуют последующей команды FLUSH PRIVILEGES . Понятно, почему, если читать документы. Это потому, что изменение таблиц MySQL напрямую не перезагружает информацию в память; однако множество решений этой ошибки утверждают, что FLUSH PRIVILEGES является ответом.

Это тоже не может быть ошибкой. Это заговор документации — документы меняются в одном критическом месте от версии к версии.

13.7.1.2. Синтаксис DROP USER

Пользователь DROP USER [, пользователь].

Если вы укажете только имя имени пользователя в имени учетной записи, используется часть имени узла «%».

DROP USER , как представлено в MySQL 5.0.0, удаляет только учетные записи, которые не имеют никаких прав. В MySQL 5.0.2 он был изменен для удаления привилегий учетной записи. Это означает, что процедура удаления учетной записи зависит от вашей версии MySQL.

Начиная с MySQL 5.0.2 вы можете удалить учетную запись и ее привилегии следующим образом:

Оператор удаляет строки привилегий для учетной записи из всех таблиц предоставления.

Единственный раз, когда я получаю эту ошибку, когда я делаю DROP USER user ; как и в документе, но MySQL не рассматривает «%» как шаблон, чтобы удалить всех пользователей на всех хостах. В конце концов, это не так дико. Или, может быть, иногда он работает, когда он удаляет пользователя localhost, а затем пытается удалить его на%.

Мне ясно, что когда он пытается удалить пользователя в%, он выдает сообщение об ошибке и завершает работу. Последующий CREATE USER на localhost не сработает, потому что пользователь localhost не был удален. Кажется, нет необходимости тратить время на рытье в таблицах грантов, ищущих призраков, как предлагал один плакат.

Я вижу 7 голосов за:

DROP USER ‘jack @localhost’;//полностью удалить учетную запись

Что интерпретируется как DROP USER ‘[email protected]’@’%’; # wrong

На самом деле существует реальная ошибка, которая генерирует одно и то же сообщение об ошибке, но она связана с первым созданным пользователем (после установки нового сервера mysql). Исправлена ​​ли эта ошибка, я не знаю; но я не помню, что это произошло в последнее время, и я в настоящее время готов к версии 5.5.27.

Источник

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!

To fix the error, let us see how to create a user correctly. Let us create a user −

mysql> create user 'Emma'@'localhost' IDENTIFIED BY 'emma_654';
Query OK, 0 rows affected (0.00 sec)

Let us display all users along with host −

mysql> select user,host from MySQL.user;

This will produce the following output. The new user created above is visible in the below list of all users along with host −

+------------------+-----------+
| user             |      host |
+------------------+-----------+
| Bob              |         % |
| Charlie          |         % |
| Robert           |         % |
| User2            |         % |
| mysql.infoschema |         % |
| mysql.session    |         % |
| mysql.sys        |         % |
| root             |         % |
| @UserName@       | localhost |
| Adam             | localhost |
| Adam Smith       | localhost |
| Chris            | localhost |
| David            | localhost |
| Emma             | localhost |
| Jace             | localhost |
| James            | localhost |
| John             | localhost |
| John Doe         | localhost |
| Michael          | localhost |
| Mike             | localhost |
| Robert           | localhost |
| User1            | localhost |
| am               | localhost |
| hbstudent        | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
+------------------+-----------+
26 rows in set (0.00 sec)