MySQL Bugs: #44283: Backup: error if view depends on dropped object

Bug #44283	Backup: error if view depends on dropped object
Submitted:	15 Apr 2009 9:36
Reporter:	Jørgen Løland	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.7,6.0.11	OS:	Any
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
BACKUP DATABASE fails with an error if a database contains a view with a missing dependency. BUG#34902 made backup error instead of crash the server in this scenario, but BACKUP should rather be able to back up the database.

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

mysql> use db1;
Database changed
mysql> create table t1 (i int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> backup database db1 to '1.bup';
ERROR 1698 (HY000): Failed to add view `db1`.`v1` to the catalog

mysql> select * from v1;
ERROR 1356 (HY000): View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Suggested fix:
Make BACKUP DATABASE able to successfully back up the database in the presence of views with missing dependencies.

Probably duplicate of or related to BUG#44281

Bug #45251 was marked as duplicate of this one.

I've observed two more cases when backup fails due to incorrectness of db objects:

- for view

mysql> create database test_view;
Query OK, 1 row affected (0.00 sec)

mysql> use test_view;
Database changed
mysql> create table t1 (a int not null);
Query OK, 0 rows affected (0.00 sec)

mysql>  CREATE DEFINER=test VIEW v1 AS SELECT a FROM t1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('test'@'%') does not exist |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> backup database test_view to '/tmp/test_view.bk';
ERROR 1698 (HY000): Failed to add view `test_view`.`v1` to the catalog

- for procedure
mysql> create database test_proc;
Query OK, 1 row affected (0.00 sec)

mysql> use test_proc;
Database changed
mysql> CREATE PROCEDURE p1(a0 ENUM( 'w 64751 w', 'w 5085 w', 'w 18766 w','w 5085 w' ))  SET @foo=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1291 | Column '' has duplicated value 'w 5085 w' in ENUM |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> backup database test_proc to '/tmp/test_proc.bk';
ERROR 1717 (HY000): Failed to obtain meta-data for stored routine `test_proc`.`p1`

Most likely we would have FORCE option for 'backup database' command to be able to skip invalid objects during backup.

Such behavior (that backup will fail when invalid object will be found during backup) is not documented now (at least I didn't find such note in documentation) so it has sense to add note to highlight such issue.

Problem also exists with MySQL 5.0.83.

mysqldump should ignore this failure and continue with the dump (make this non fatal but a warning).