ERROR 1044 42000 Access denied for root With All Privileges

Question

I have strange error  I am logged in local Mysql as root via command line  After creating database   create database some db    Then giving privileges to some user   grant all privileges on some db   to some user  localhost  identified by  password     This is giving error   ERROR 1044  42000   Access denied for user  root   localhost  to database  some db    The permissions for the root show grants   shows   GRANT ALL PRIVILEGES ON     TO  root   localhost  IDENTIFIED BY PASSWORD   8919C53DC7A4DFBF3F8584382E96463583EB7FDA     I am also making sure i am logged in as root   select current user      And this confirms that i am logged in as  root   localhost   I have created database and assigned user multiple times and never had an issue  Why i am unable assign user to the database created by root while logged in as root   p s  from other posts  i am thinking the issue may be caused due to some strange users  select host  user from mysql user    Some users that i have not added but does show up    MY COMPuTER name local             MY COMPuTER name local       root   I tried to delete these users  drop user  root   MY COMPuTER name local   drop user     MY COMPuTER name local     However  while it states query run successful  the users are not dropped even after flush privileges  Why i am unable delete users  Any help is much appricated

User · Answer

Try to comment string  sql-mode      in file my cnf and than restart mysql

User · Answer

First  Identify the user you are logged in as    select user     select current user      The result for the first command is what you attempted to login as  the second is what you actually connected as  Confirm that you are logged in as root localhost in mysql   Grant priv to root localhost  Here is how you can check       mysql gt  SELECT host user password Grant priv Super priv FROM mysql user   ----------- ------------------ ------------------------------------------- ------------ ------------    host        user               password                                    Grant priv   Super priv    ----------- ------------------ ------------------------------------------- ------------ ------------    localhost   root                                                           N            Y              localhost   debian-sys-maint                                               Y            Y              localhost   staging                                                        N            N             ----------- ------------------ ------------------------------------------- ------------ ------------    You can see that the Grant priv is set to N for root localhost  This needs to be Y  Below is how to fixed this   UPDATE mysql user SET Grant priv  Y   Super priv  Y  WHERE User  root   FLUSH PRIVILEGES  GRANT ALL ON     TO  root   localhost     I logged back in  it was fine

User · Answer

If you get an error 1044  42000  when you try to run SQL commands in MySQL  which installed along XAMPP server  cmd prompt  then here s the solution    Close your MySQL command prompt  Open your cmd prompt  from Start menu -  run -  cmd  which will show  C  Users User   Go to MySQL exe by Typing the following commands    C  Users User gt cd           C   gt cd xampp          C  xampp gt cd mysql          C  xxampp mysql gt cd bin          C  xampp mysql bin gt mysql -u root   Now try creating a new database by typing   mysql gt  create database employee    if it shows   Query OK  1 row affected  0 00 sec  mysql gt    Then congrats   You are good to go

User · Answer

The reason i could not delete some of the users via  drop  statement was that there is a bug in Mysql http   bugs mysql com bug php id 62255 with hostname containing upper case letters  The solution was running following query   DELETE FROM mysql user where host  Some Host With UpperCase Letters     I am still trying to figure the other issue where the root user with all permissions are unable to grant privileges to new user for particular database

[mysql] ERROR 1044 (42000): Access denied for 'root' With All Privileges

Examples related to mysql

Examples related to mysqladmin