ERROR 1396 HY000 Operation CREATE USER failed for jack localhost

Question

I seem to be unable to re-create a simple user I ve deleted  even as root in MySQL   My case  user  jack  existed before  but I deleted it from mysql user in order to recreate it  I see no vestiges of this in that table  If I execute this command for some other  random username  say  jimmy   it works fine  just as it originally did for  jack     What have I done to corrupt user  jack  and how can I undo that corruption in order to re-create  jack  as a valid user for this installation of MySQL   See example below   Of course  originally  there was much time between the creation of  jack  and his removal    mysql gt  CREATE USER  jack   localhost  IDENTIFIED BY  test123   Query OK  0 rows affected  0 00 sec   mysql gt  select user host from user   ------------------ -----------------    user               host               ------------------ -----------------    root               127 0 0 1           debian-sys-maint   localhost           jack               localhost           root               localhost           root               russ-elite-book    ------------------ -----------------  5 rows in set  0 00 sec   mysql gt  delete from user where user    jack   Query OK  1 row affected  0 00 sec   mysql gt  select user host from user   ------------------ -----------------    user               host               ------------------ -----------------    root               127 0 0 1           debian-sys-maint   localhost           root               localhost           root               russ-elite-book    ------------------ -----------------  4 rows in set  0 00 sec   mysql gt  CREATE USER  jack   localhost  IDENTIFIED BY  test123   ERROR 1396  HY000   Operation CREATE USER failed for  jack   localhost  mysql gt  CREATE USER  jimmy   localhost  IDENTIFIED BY  test123   Query OK  0 rows affected  0 00 sec   mysql gt  select user host from user   ------------------ -----------------    user               host               ------------------ -----------------    root               127 0 0 1           debian-sys-maint   localhost           jimmy              localhost           root               localhost           root               russ-elite-book    ------------------ -----------------  5 rows in set  0 00 sec

User · Answer

Funnily enough the MySQL workbench solved it for me  In the Administration tab -  Users and Privileges  the user was listed with an error  Using the delete option solved the problem

User · Answer

If you want to delete a user with sql  you need to delete the related data in these tables  columns priv  db  procs priv  tables priv   Then execute flush privileges

User · Answer

If you use a DELETE statement on the mysql user table in an attempt to remove a user  then attempt to re-establish the user with CREATE USER  you will get a 1396 error  Get rid of this error by running DROP USER  username   host    DELETE    FROM mysql user   WHERE user    jack      You will get 1396 errors if you attempt to re-create jack   CREATE USER  jack   localhost  IDENTIFIED BY PASSWORD   Fi47ytFF3CD5B14E7EjkjkkC1D3F8086A5C0-krn      Get out of this situation by running DROP USER   DROP USER  jack   localhost      I suppose FLUSH PRIVILEGES can t hurt  but definitely drop the user first

User · Answer

yes this bug is there  However  I found a small workaround    Assume the user is there  so drop the user After deleting the user  there is need to flush the mysql privileges Now create the user    That should solve it  Assuming we want to create the user admin   localhost  these would be the commands    drop user admin localhost  flush privileges  create user admin localhost identified by  admins password    Cheers

User · Answer

The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

User · Answer

Seems you  need to create user for your database and grant privileges for created user -- gt  create user for Data base CREATE  USER  lt  username  gt     IDENTIFIED BY  lt  password  gt    ex - CREATE  USER  root     IDENTIFIED BY  root   -- gt  Grant Privileges FLUSH PRIVILEGES  GRANT ALL PRIVILEGES ON  lt db name gt    TO   lt username gt         ex- GRANT ALL PRIVILEGES ON mydb   TO  root

User · Answer

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

User · Answer

I had the same error  But command  FLUSH PRIVILEGES   didn t help  I did like that   CREATE USER  jimmy   localhost  IDENTIFIED BY  test123   UPDATE mysql user SET USER  jack  WHERE USER  jimmy

User · Answer

This bug has been sitting on bugs mysql com since 2007 and this thread is mainly just a parroting of all those wrong answers even up to a year ago   According to the MySQL documentation  commands like CREATE USER  GRANT  REVOKE  and DROP USER do not require a subsequent FLUSH PRIVILEGES command  It s quite clear why  if one reads the docs  It s because altering the MySQL tables directly does not reload the info into memory  yet the plethora of solutions to this bug claim that FLUSH PRIVILEGES is the answer   This also may not even be a bug  It is a documentation conspiracy - docs vary in one critical place from version to version   13 7 1 2  DROP USER Syntax          DROP USER user    user                DROP USER  jeffrey   localhost     If you specify only the user name part of the account name  a host name part of     is used   DROP USER as present in MySQL 5 0 0 removes only accounts that have no privileges  In MySQL 5 0 2  it was modified to remove account privileges as well  This means that the procedure for removing an account depends on your version of MySQL    As of MySQL 5 0 2  you can remove an account and its privileges as follows      DROP USER user    The statement removes privilege rows for the account from all grant tables   The only time I get this error is when I do DROP USER user  like the doc suggests  but MySQL does not treat the     as a wildcard in a way that would drop all users at all hosts  It s not so wild after all  Or  it may be that it sometimes works when it deletes the localhost user and then tries to delete the one at     It s clear to me that when it tries to delete the user at    it issues an error message and quits  Subsequent CREATE USER at localhost will fail because the localhost user was never deleted  There seems to be no need to waste time digging in the grant tables looking for ghosts as one poster suggested   I see 7 votes for      DROP USER  jack localhost      completely delete the account   Which is interpreted as DROP USER  jack localhost         wrong  There actually seems to be a real bug that generates the same error message  but it has to do with the first created user  after a new mysql server install  being dropped  Whether that bug has been fixed  I don t know  but I don t recall that happening lately and I m up to ver 5 5 27 at this time

User · Answer

mysql gt  DELETE FROM mysql db WHERE user    jack   Restart the server     mysql server restart  Then do your CREATE USER command

User · Answer

two method  one   setp 1  drop user  jack   localhost   setp 2  create user  jack  localhost identified by  ddd    two  setp 1  delete from user where user  jack and host  localhost   setp 2  flush privileges  setp 3  create user  jack   localhost  identified by  ddd

User · Answer

You shouldn t be manually deleting users that way  MySQL has REVOKE syntax for removing privileges and DROP USER for deleting them   REVOKE priv1 priv2 priv3 etc    FROM  jack localhost      remove certain privileges DROP USER  jack localhost      completely delete the account   Best to use the tools provided rather than mucking around in the background

User · Answer

Just delete the user related data from mysql db maybe from other tables too   then recreate both

User · Answer

I had the same problem as OP  and the accepted answer did not work for me  In the comments of the accepted answer   Rathish posted a solution which worked for me  I wanted to call attention to it  Here s the link  https   www rathishkumar in 2018 10 Error-1396-HY000-Operation-CREATE-DROP-USER-failed-for-user-host html Rathish s solution is to revoke access for all users  REVOKE ALL ON     FROM  user   host   DROP USER  user   host   FLUSH PRIVILEGES   And he also helpfully points out that you can query the following tables by selecting  quot user quot  and  quot host quot  to determine whether you have a vestigial user left-over from a previous operation  mysql user  User accounts  global privileges  and other non-privilege columns mysql db  Database-level privileges mysql tables priv  Table-level privileges mysql columns priv  Column-level privileges mysql procs priv  Stored procedure and function privileges mysql proxies priv  Proxy-user privilege  Thank you

User · Answer

This post MySQL ERROR 1045  28000   Access denied for user   39 bill  39    39 localhost  39   using password  YES  is useful  Sometimes  there is an anonymous user     localhost  or     127 0 0 1   So  to solve the problem     first drop the user whose  create user  failed   Create new user   Grant required privileges to the new user  Flush privileges

User · Answer

I recently got this error    What worked for me is checking in the mysql workbench  Users and Privileges  and realizing user still existed    After deleting it from there  I was able to recreate the user

User · Answer

I faced this issue today  and I resolved it by doing the following steps   1  manually inserting that troubling user  providing value of mandatory fields into mysql user   mysql gt  insert into user Host  User  Password  ssl type      values   localhost    jack    jack    ANY      2    mysql gt  select   from user where User    jack      1 row in set  0 00 sec    3  A    mysql gt  drop user jack  Query OK  0 rows affected  0 00 sec   B  mysql gt  flush privileges  Query OK  0 rows affected  0 00 sec   C  mysql gt  create user  jack  identified by  jack   Query OK  0 rows affected  0 00 sec   D  mysql gt  select Host  User  Password  ssl type  from user where User    jack    ----------- ----------- ------------------------------------------- ----------    Host        User        Password                                    ssl type    ----------- ----------- ------------------------------------------- ----------    localhost   jack        jack                                        ANY                      jack         45BB7035F11303D8F09B2877A00D2510DCE4D758               ----------- ----------- ------------------------------------------- ----------  2 rows in set  0 00 sec    4  A    mysql gt  delete from user   where User    nyse user  and         Host    localhost  and         Password   nyse   Query OK  1 row affected  0 00 sec    B    mysql gt  select Host  User  Password  ssl type  from user where User    jack    ------ ----------- ------------------------------------------- ----------    Host   User        Password                                    ssl type    ------ ----------- ------------------------------------------- ----------           jack         45BB7035F11303D8F09B2877A00D2510DCE4D758               ------ ----------- ------------------------------------------- ----------  1 row in set  0 00 sec    Hope this helps

User · Answer

In MySQL 5 6 using Drop user userid  does not work  Use  Drop user  userid   localhost   and or Drop user  userid         In this way I was able to drop the user and recreate it

User · Answer

I know this is old  but since it is the first result in Google I figured I should add my solution   In my case dropping the user worked fine  but recreating the user gave me a  ERROR 2013  HY000   Lost connection to MySQL server during query  and  ERROR 2006  HY000   MySQL server has gone away    I tried the flush privileges -  drop user solution  but still had the same error   In my case the error was due to a mysql upgrade from 5 1 -  5 6   Viewing the error logs  I noticed that it said to run mysql upgrade   Did that and my create user statement worked fine

User · Answer

Try doing a FLUSH PRIVILEGES    This MySQL bug post on that error code appears to report some success in a case similar to yours after flushing privs  nbsp

User · Answer

Drop the user  flush the privileges  then  create the user  It does work

User · Answer

try delete from mysql db where user    jack  and then create a user

User · Answer

I had also faced the same issue  after few searches  I found a solution that worked for me I hope it will help you  As you have already created users  now try to do a FLUSH PRIVILEGES on your Mysql console  This issue is already in MySql bug post You can also check this one Now after flushing  you can create a new user  follow below Steps  Step-1  Open terminal Ctrl Alt T Step-2  mysql -u root -p    it will ask for your MySQL password   Now you can able to see Mysql console  Step-3  CREATE USER  username   host  IDENTIFIED by  PASSWORD    Instead of username you can put username you want  If you are running Mysql on your local machine  then type  quot localhost quot  instead of the host  otherwise give your server name you want to access  Ex  CREATE USER smruti localhost IDENTIFIED by  hello   Now new user is created  If you want to give all access then type GRANT ALL PRIVILEGES ON       TO  newuser   localhost   Now you can quit the MySQL by typing  q Now once again login through mysql -u newusername -p   then press Enter  You can see everything  Hope this helps

User · Answer

Check if is       user         or       user   localhost

[mysql] ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'

Examples related to mysql

Examples related to mysql-management