Unable to do a mysqldump due to invalid view

While trying to backup all data from a mysql server with mysqldump I got this error

root@server$ mysqldump -uroot -p --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases
mysqldump: Couldn't execute 'SHOW FIELDS FROM `_bare_grantee_grants`': View 'common_schema._bare_grantee_grants' 
   references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

I assume this is not a permission issue as I’m a root.

What could be the source of the problem and the solution ?

MySQL version is 5.7.33 upgraded a couple of week ago from 5.6

thanks

1 Like

I did a SHOW CREATE VIEW common_schema._bare_grantee_grants; and executed the query, it returned me the same error but pointing to view sql_grants itself broken ERROR 1356 (HY000): View 'common_schema.sql_grants' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them which returns complicated query.
My point is there a way to reset all the views provided by mysql by pristine ones ?

1 Like

Hi bmmalc,

You can try dropping the view with “DROP VIEW <view_name>” one at a time for the ones that fail as in MySQL :: MySQL 5.7 Reference Manual :: 13.1.32 DROP VIEW Statement .

Because of the error you are getting, I would strongly suggest you check error.log to see if there are any other issues in there. The error you are showing is not common, and might be due to other issues/bugs/corruption/etc…

Also, did you run mysql_upgrade after the install?

Let us know how it goes

1 Like