Whenever I try to Grant Privileges as root@localhost, it says permission denied (using password=Yes).

My root user is the one that I am logged in as. I verify this by select user();

It responds with 'root'@'localhost'.

I have also checked out this stack overflow post which seems to be the same issue:


and this one


I am having no luck. If I Show GRANTS;, It lists all GRANT privileges: SELECT, Insert, Update, DELETE, CREATE, DROP, etc.

  1. How do I make sure that ROOT has GRANT option itself?

  2. Why would my root user not have all privileges? I am using mysql version: 5.5.38-0+wheezy1 on Debian.

  • A) What command are you running? B) Are you having trouble connecting to the MySQL server or running the command?
    – tadman
    Apr 9, 2015 at 16:21
  • I am running: GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION; I am not having trouble connecting to mysql -u root -pMyPassword Apr 9, 2015 at 16:53
  • Please run the following query : select count(1) UserTableColumnCount from information_schema.columns where table_schema='mysql' and table_name='user';. What number does it report ??? Apr 9, 2015 at 16:54
  • The output of that command is 39. Apr 9, 2015 at 16:55
  • 1
    Ah ha. I know the problem. Please stand by ... Apr 9, 2015 at 16:55

2 Answers 2


I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

# Backup the mysql.user table
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
# Add Missing Columns
ALTER TABLE mysql.user 
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
# Give root user all privileges
UPDATE mysql.user SET
WHERE user='root';

That's it !!!

  • The --upgrade-system-tables option was used, databases won't be touched. FATAL ERROR: Upgrade failed Apr 9, 2015 at 17:02
  • Maybe I should downgrade to 5.1. This is a test server. Apr 9, 2015 at 17:03
  • You could add the missing columns manually to mysql.user Apr 9, 2015 at 17:05
  • Go to my post dba.stackexchange.com/questions/16397/… and see all the columns each version of MySQL has for mysql.user Apr 9, 2015 at 17:07
  • I just added a manual fix to the mysql.user table. Apr 9, 2015 at 17:27
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
  • Is this not risky? What if it allows me to revoke all fine but still won't allow me to put them back? Is that possible? Apr 9, 2015 at 16:56
  • No. Is not. This will grant ALL.
    – Florin
    Apr 9, 2015 at 17:10
  • The privileges will not be activated until you FLUSH PRIVILEGES;.
    – Anders
    Feb 11, 2016 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.