Error 1142: SELECT and LOCK TABLE commands denied

Question

I reinstalled the server running MySQL. I had created a backup of the database by using MySQL Workbench. Now I'm trying to import the dump in to the database trough the same program but I get the following error:

ERROR 1142 (42000) at line 656: SELECT,LOCK TABL command denied to user 'root'@'MIKKOS' for table 'events_waits_summary_by_thread_by_event_name'

I tought that root had full privileges. I ran the following but the same error appears with the previous query after executing this:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

It also appears when running locally (root@localhost).

What to do?

Answer 1

You need to repair internal table structure after reinstallation a newer MySQL server. To do so try:

REPAIR TABLE `events_waits_summary_by_thread_by_event_name`;

It would be better to drop database and restore it from full mysql database dump if you have one.

Answer 2

Check which databases MySQL Workbench included in the dumpfile. I believe it defaults to dumping everything, including DBs like user and performance_schema. Those are the problem, your grant tables are probably fine.

Remove the unneeded databases, especially performance_schema and MySQL will likely import the dumpfile without errors.