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
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:
CREATE USER `developer` IDENTIFIED BY "developer";
DELETE FROM mysql.user WHERE user = 'developer';
Then the next time you create the user developer in your database server, you will trigger the error as follows:
mysql> DELETE FROM mysql.user WHERE user = 'developer';
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'
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:
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'
mysql> DROP USER `developer`;
ERROR 1396 (HY000): Operation DROP USER failed for 'developer'@'%'
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
Query OK, 0 rows affected (0.01 sec)
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:
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'
The same error could happen when you run the DROP USER or ALTER USER statement for a non-existing user account:
mysql> DROP USER `notuser`;
ERROR 1396 (HY000): Operation DROP USER failed for 'notuser'@'%'
mysql> ALTER USER dev@localhost IDENTIFIED BY 'newPassword';
ERROR 1396 (HY000): Operation ALTER USER failed for 'dev'@'localhost'
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:
SELECT user, host FROM mysql.user;
Please note that you may have different values between % and localhost in the host column.
Here’s an example from my database:
+------------------+-----------+
| user | host |
+------------------+-----------+
| developer | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| nathan | localhost |
| root | localhost |
+------------------+-----------+
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.
-- Create developer@% account
CREATE USER `developer` IDENTIFIED BY "developer";
-- Create developer@localhost account
CREATE USER `developer`@localhost IDENTIFIED BY "developer";
The statements above will create two developer accounts with different hosts:
+------------------+-----------+
| user | host |
+------------------+-----------+
| developer | % |
| developer | localhost |
+------------------+-----------+
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.
I just tried to add a new user to MySQL using
CREATE USER 'name'@'%' IDENTIFIED BY '...'
However, it fails with the following error:
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
Adding the user just for localhost works fine:
CREATE USER 'name'@'localhost' IDENTIFIED BY '...'
I have no clue what could be the problem. I’d be grateful for any ideas.
(I’m using mysql Ver 14.14 Distrib 5.1.66.)
asked Feb 18, 2013 at 13:17
1
According to the docs if you ommit the @’hostname’ (that is CREATE USER ‘name’) MySQL will interpret it as it had a @’%’. The error message you provided suggests that there is already a user ‘name’@’%’ in the system:
mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
mysql> CREATE USER 'name' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
If you delete the user and still get the message, try running FLUSH PRIVILEGES.
Also see this SO question for additional info.
answered Feb 18, 2013 at 13:27
redguyredguy
9867 silver badges14 bronze badges
I was facing this same error while using mysql:5.7 docker image. Main mistake was trying to create root user which exists by default.
More information: https://github.com/docker-library/mysql/issues/129
As given in the above link, solution was to NOT set MYSQL_USER and MYSQL_PASSWORD in the environment variables while starting the docker image.
answered May 27, 2018 at 5:31
1
Решение ошибки с кодом 1396 Error ‘Operation CREATE USER failed for… при настроенной репликации MySQL.
- Ошибка возникает при ситуации, когда в схеме MasterSlave на мастере был создан пользователь и ему выданы некие права. Например, вот так:
CREATE USER 'zabbix'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'%';- После этого на слейве репликация останавливается с ошибкой:
Last_SQL_Errno: 1396
Last_SQL_Error: Error 'Operation CREATE USER failed for 'zabbix'@'%'' on query. Default database: ''. Query: 'CREATE USER 'zabbix'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B3198D30E7427FD4920A86A602DE74D9FABE22B9''А причиной ошибки является то, что на slave-сервере, вероятнее всего, уже был такой пользователь, а потому репликация и прерывается. Такая особенность MySQL:
- https://bugs.mysql.com/bug.php?id=28331
- https://dba.stackexchange.com/questions/34940/adding-a-user-to-mysql-with-name-fails-with-error-1396
Решение проблемы логичное и простое: необходимо удалить пользователя на слейве, и самое главное – не забыть выполнить очистку прав, после чего запускать слейв:
delete from mysql.user where user='zabbix';
flush privileges;
start slave;Либо же привести пользователя на слейве и мастере к единому виду, т.е. записи в таблицах должны совпадать, после чего также выполнить flush privileges; и запустить репликацию.