Bug #35964 mysqldump fails to dump a malformed view: SHOW CREATE VIEW fails.
Submitted: 10 Apr 2008 16:21 Modified: 10 Apr 2008 18:15
Reporter: Lee Wood Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.0.33, 5.0, 5.1, 6.0 BK OS:FreeBSD
Assigned to: CPU Architecture:Any

[10 Apr 2008 16:21] Lee Wood
About a year ago we migrated our appliations to use a new version of our main database.

(i.e. instead of using a database named "digifonica", they should use the database called

Yesterday I decided it was time to finally delete the old database, however, just to be safe 
I renamed all the tables and views, prepending "KILL_" to each name, which would have
caused any application that still referenced a table or view in the old database to fail
in an obvious fashion.

(NB I would have simply renamed the entire database but we're still using MySQL version 5.0.33,
and I don't believe RENAME DATABASE was implimented before version 5.1.)

The operation went well, and all our applications passed the test, however, last night's
backup did not.

The backup command I used was:

mysqldump -u root           \
 --all-databases            \
 --flush-logs               \
 --master-data=2            \
 --routines                 \
 --single-transaction       \
 --triggers                 \
 > full.dump;

Please note that the MySQL user account referenced was root@localhost.

MySQLDump produced the following error message:

mysqldump: Couldn't execute 'SHOW FIELDS FROM `KILL_cdr_billing`': 
View 'digifonica.KILL_cdr_billing' references invalid table(s) or column(s) or function(s) 
or definer/invoker of view lack rights to use them (1356)

NB KILL_cdr_billing is a view. 

The documentation for RENAME TABLE says that it also works on views, but that :

  "Any privileges granted specifically for the renamed table or view are not migrated to the new name.
  They must be changed manually."

However, root@localhost appears to have had sufficient privileges to perform this (or any other) operation:

mysql> show grants for root@localhost;

How to repeat:
1) create a database with a few tables and views.
2) rename the table and views, using RENAME TABLE.
3) attempt to run mysqldump on the database.
[10 Apr 2008 16:29] Lee Wood
The views were broken when I renamed all the tables. So either RENAME TABLE should update any views that reference the renamed table, or mysqldump shouldn't break when it encounters a broken view.
[10 Apr 2008 18:15] Sveta Smirnova
Thank you for the reasonable feature request.

In my opinion problem is only with mysqldump here as according to http://dev.mysql.com/doc/refman/5.1/en/create-view.html: "The view definition is “frozen” at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view." So only some flag to mysqldump can be added to proceed such invalid views
[27 Apr 2016 12:25] Mauro Molinari
This is a real problem. When you use mysqldump to take a "picture" of a database (either for backup purposes or to initialize a replication) you may want to take the picture "as is", so with broken view if there are any.
It would be extremely useful if mysqldump had an option to let it dump and restore views (and/or functions, triggers, etc.) even if they are broken.

This is not to say "there's no problem", but "please clone my data, even if they have problems" because there are scenarios in which this is what you actually want. I can always fix errors at a later time, if I can go on, while I'm surely stuck if I can't.