MySQL Bugs: #22054: MySQL Dump fails on invalid view

Bug #22054	MySQL Dump fails on invalid view
Submitted:	6 Sep 2006 15:39	Modified:	24 Jul 2008 17:24
Reporter:	Eric Brunson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.24	OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Tags:	Backup, mysqldump, VIEW

Description:
Changing a table that underlies a view can create a situation in which mysqldump fails to export the database.  

While I realize that this is a situation caused by a user error, I don't feel the failure mode is acceptable.  This can cause automated backups to fail until the error is noticed, if those backups are needed before the error is fixed it could  result in extensive data loss.

How to repeat:
mysql> create database dumpbug;
Query OK, 1 row affected (0.00 sec)

mysql> use dump bug;
ERROR 1049 (42000): Unknown database 'dump'
mysql> use dumpbug;
Database changed
mysql> create table t1 ( c1 int );
Query OK, 0 rows affected (0.05 sec)

mysql> create view v1 as select c1 from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 change c1 c2 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> exit
Bye
wbebrunsonlx(~)$ mysqldump -p dumpbug > /dev/null
Enter password:
mysqldump: mysqldump: Couldn't execute 'SHOW FIELDS FROM `v1`': View 'dumpbug.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
[status 2]

Suggested fix:
I would be in favor of simply exporting the invalid view and allowing the error to be noticed at import time, at least the data would be backed up.  Other solutions are welcome as long as the dump succeeds.

I'm not sure why mysqldump is processing the view as a table and attempting "SHOW FIELDS FROM" rather than "SHOW CREATE VIEW", as bug #13818 would have addressed the problem.

This may be considered a duplicate of bug #17371, but the fix applied in that did not address this problem.  I'm unsure if the protocol is to try to reopen the original bug since it is against an older release.  Also, I believe the patch fixed the condition when the underlying table was dropped, not when a field definition went away.  If it's appropriate to close this bug and reopen the original, feel free.

Is it inappropriate to update the view definition when a change is made to an underlying table?

Thank you for the bug report.

Workaround is to drop and recreate views when 
altering underlying table(s).

Trudy, you are missing the point of the bug report.  

The bug is not that the views are wrong, the bug is that a mysqldump will fail after the view is invalid.  Please feel free to re-read the original bug report.

This works exactly as intended. If there is an invalid view, mysqldump will exit with an error. If you aren't checking for error exit codes in your backup scripts, you have larger problems.

If you specify --force, the view definition will be included in the dump file (as a comment) and the error will not cause the dump to be stopped, but mysqldump will still exit with an error code when the rest of the dump has been completed.