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
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;
That's it !!!
OK, I finally found a solution. To give proper credit, I had to blend answers provided by two people: RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X.
For Wamp Users:
Click on the Wamp system tray icon to bring up the tray menu. Click
MySQL > Service > Stop Service.
Edit your MySQL's my.ini file...that's your configuration file for your SQL database, and it's generally located in the root folder for your MySQL database (try looking in wamp > bin > mysql > mysql5.5 [or whatever version]). Use a text editor, and scroll down in the my.ini file to [wampmysqld]
. Immediately after this entry, insert a new line containing the following command:
skip-grant-tables
This tells the MySQL database that you want to override any restrictions such as root user or password parameters. This is a major security hole, so once we are done with our repairs, we'll need to undo this! Save the my.ini file.
Resume MySQL services in Wamp (click MySQL > Service > Start/Resume Service).
Now open the MySQL console: click MySQL > MySQL Console
A "DOS-like" terminal window will open up (if it asks you for a password, simply press enter). You should see a MySQL prompt. Copy the following block of code and paste it at the prompt.
INSERT INTO mysql.user
SET user = 'root',
host = 'localhost',
password = '',
Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y';
Press enter. You should get some sort of message saying that one line (of database code) was affected, with some warnings (probably because you are messing with the root user...I think you can safely ignore these). Close the MySQL terminal.
Remember that security hole we opened in step two? Time to fix that. Reopen your my.ini SQL configuration file, and remove the skip-grant-tables
line you added. Save the my.ini file and close.
RESTART your MySQL Service (same as before, but click on MySQL > Service > Restart Services instead).
THAT should do the trick!
A note on the root user in MySQL for newbies: for some strange reason, even if you create another user as a superuser, MySQL seems to need this user to operate...however, if you don't set up a password for root, it notes this as a security hole. Whatever you do, don't delete it: you're better off just setting some wildly obscure password for it and leaving it alone if you want to use another superuser account. It would seem that there should be more protections to help prevent unwary users from doing this (a warning, or something)....that's my twelve cents on the issue.
Best Answer
I think the answer of the question can help hackers or something.
You can reinstall your MySQL. Before the reinstall, take a backup if you can or copy your data files.
There is an answer to how can do you recover your data files. If you want to guarantee your data healthy, you can install new MySQL and try that.