kas kas - 6 months ago 161
SQL Question

Error (1142) SELECT command denied on session_variables table with mysqldump

When I try to run

mysqldump -u dump -p myschema > dumpfile.sql
I get this error message:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': SELECT command denied to user 'dump'@'localhost' for table 'session_variables' (1142)


I found something about adding
--set-gtid-purged=OFF
for a similar error. But I'm not running the MySQL 5.6 version of
mysqldump
on an older MySQL database. However, when I try it, I get a new, yet similar, error:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': SELECT command denied to user 'dump'@'localhost' for table 'session_variables' (1142)


I'm not sure why the user needs access to the session_variables table, which I presume is
information_schema.SESSION_VARIABLES
, since this SO post said that the following permissions were sufficient.​

Grants for dump@localhost

GRANT USAGE ON *.* TO 'dump'@'localhost'
GRANT SELECT, LOCK TABLES ON mysql.* TO 'dump'@'localhost'
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON myschema.* TO 'dump'@'localhost'


What's going on and how do I make mysqldump work?

Here's some of my system info

MySQL Version

Server version: 5.7.8-rc MySQL Community Server (GPL)

mysqldump version

Ver 10.13 Distrib 5.7.8-rc, for Linux (x86_64)

Answer

This is a result of mysql server upgrade. Run

# mysql_upgrade -u root -p --force
# systemctl restart mysqld