# Unable to do a mysqldump due to invalid view

**URL:** <https://forums.percona.com/t/unable-to-do-a-mysqldump-due-to-invalid-view/10296>\
**Category:** Other MySQL® Questions\
**Created:** [April 29, 2021, 7:26am UTC](https://forums.percona.com/t/unable-to-do-a-mysqldump-due-to-invalid-view/10296 "2021-04-29T07:26:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\
**Post date:** [April 29, 2021, 7:26am UTC](https://forums.percona.com/t/unable-to-do-a-mysqldump-due-to-invalid-view/10296/1 "2021-04-29T07:26:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Baptiste\_Mille-Mathi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/baptiste_mille-mathi/32/9237_2.png) [@Baptiste\_Mille-Mathi](https://forums.percona.com/u/Baptiste_Mille-Mathi)\
**Post date:** [April 29, 2021, 12:49pm UTC](https://forums.percona.com/t/unable-to-do-a-mysqldump-due-to-invalid-view/10296/2 "2021-04-29T12:49:57Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\
**Post date:** [May 10, 2021, 12:41pm UTC](https://forums.percona.com/t/unable-to-do-a-mysqldump-due-to-invalid-view/10296/3 "2021-05-10T12:41:29Z")

</div>

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](https://dev.mysql.com/doc/refman/5.7/en/drop-view.html) .

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

---

<div class="post-metadata">

**Author:** ![ribeiro.damian](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ribeiro.damian/32/6497_2.png) [@ribeiro.damian](https://forums.percona.com/u/ribeiro.damian)\
**Post date:** [May 5, 2022, 12:38pm UTC](https://forums.percona.com/t/unable-to-do-a-mysqldump-due-to-invalid-view/10296/4 "2022-05-05T12:38:41Z")

</div>

```auto
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

```