If you get an error messages like “mysqldump: Couldn’t execute ‘SHOW FIELDS FROM `store_information`’: View ‘database.viewname’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)” then a number of things might be wrong:
- The view is “corrupted” (this could happen if the table the view is of has been updated in a way so that the view is no longer correct – for instance if the table structure is updated)
- The user you are using are lacking permissions (to the view/table (that the view is referencing) )
What I usually do when this happens to solve it is to:
- Verify if the user running the mysqldump has the permissions (or test as root if you can – as root has more permissions).
If this is the case: update the permissions on the user running the dump – or change to another user
- Try to find out if the table the view is referencing has been updated (think back what have I done/check with the rest of the team)
Or if you do not wish to/can not do this, check a previous backup up the base table (from when mysqldump worked with the view) and compare the structure with the current structure in the database.
If this is the case: I just drop the view and recreate the view so that it gets corrected in the underlying db structure and re-run the dump.
More info for people using this notebook as a article; here is some manual pages that will help you along if you don’t know how to alter permissions/create views.
More information about user permissions
More information about views
good article, i will add my feeds.
This is a silly article and is wasting bandwidth, disk space and peoples time reading it. The error says “or definer/invoker of view lack rights to use them” and you just say “Perhaps the user you are using are lacking permissions”. Thanks for translating English into English. Why not tell us the permissions that are missing or something more enlightening, genius. I hope you get no ad clicks on this page, you don’t deserve any.
@Doggerty: To bad you don’t like the article.
I have rephrased part of the article so the word perhaps is not used any more.
I am not able to tell you what the problem is since that is depending on your database.
In most cases when the dump has failed on servers where I run backup scripts – then the error has been with the underlying table (it has been updated; the rows that the view is referencing has been deleted/renamed).
I think I can clarify Doggerty’s criticism. This article does not provide any additional information about the mysqldump view error. If indeed “it depends” on someone’s particular database, then how? Even if your experience is limited, how can your readers know whether their particular experience is materially same or different? How did you come to discover the problems with your underlying tables? This blog entry pretends to document something about the essential nature of the reported error. I too am frustrated to have read this and gained nothing.
@Jeremy: The reason that it depends on the underlying database is since more than one thing can cause this error.
I have added more info on what I do to figure out what is wrong in the underlying structure.
My experience on this is limited yes – since it for me normally the error is that the view gets corrupted. The permission error I have never run in to so far.
Jontas, thanks man. While your blog may not have satisfied some, it has satisfied others. So that’s how it goes. However, if a view has become corrupted due to a change in an referenced table, can I shomehow recover the description of the view if the view was not documented?
@Tre Sure the view is stored as meta data so you can get the view by querying the information table like this
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ‘myCorruptView’
Jontas, good tip about retrieving the view definition.
N.B. Once you have this SQL, you can run it as an isolated query to find out what has changed in the underlying tables! Then you can adjust and recreate the view.