Error 1142: SELECT and LOCK TABLE commands denied

mysql mysqldump mysql-workbench mysql-error-1142
MikkoP picture MikkoP · Jan 20, 2013 · Viewed 10.3k times · Source

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

joemaller picture joemaller · Aug 24, 2014

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.

Related questions

How to copy table between two models in Mysql workbench?

I am doing some databese thing, I need copy one table from one model to another, but i try many ways there no effect. Is there any way for doing this?

Read More
Using mysqldump to format one insert per line?

This has been asked a few times but I cannot find a resolution to my problem. Basically when using mysqldump, which is the built in tool for the MySQL Workbench administration tool, when I dump a database using extended inserts, …

Read More
How can I access the MySQL command line with XAMPP for Windows?

How can I access the MySQL command line with XAMPP for Windows?

Read More