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

mysql, mysqladmin

Solution

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> 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.

Problem

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

Original source