MySQL Bugs: #35964: mysqldump fails to dump a malformed view: SHOW CREATE VIEW fails.

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:	None
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

Description:
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
"digifonica_v2".)

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;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD ... WITH GRANT OPTION

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.

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.

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

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.