Unable to do a mysqldump due to invalid view

MySQL & MariaDB Other MySQL® Questions
1

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
2

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
3

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
4 
function getInvalidViews {
	mysql -u$DBUSER -p$DBPASS -N -s -e "SELECT CONCAT('--ignore-table=',table_schema,'.',table_name) FROM information_schema.tables WHERE table_type='VIEW' AND table_comment LIKE '%invalid%'" 2>/dev/null
}

DBUSER=root
DBPASS=root
mysqldump -u$DBUSER -p$DBPASS $(getInvalidViews) --all-databases > mysqldump.sql
1 Like