Bug #44281 Backup: Backup fails with error if view has been altered

• Submitted: 15 Apr 2009 9:27
• Modified: 29 Apr 2009 12:47
• Reporter: Jørgen Løland
• Status: Verified
• Category: MySQL Server: Backup
• Severity: S3 (Non-critical)
• Version: 6.0.7
• OS: Any
• Assigned to: Rafal Somla
• CPU Architecture: Any

[15 Apr 2009 9:27] Jørgen Løland

Description:
BACKUP DATABASE fails with an error message if a view has been altered. BUG#34867 made the command fail with an error instead of crashing, but BACKUP should be able to complete successfully in this case.

mysql> create database db2;
Query OK, 1 row affected (0.01 sec)

mysql> use db2;
Database changed
mysql> create view view1 as select 1;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> alter view v1 as select 6;
Query OK, 0 rows affected (0.01 sec)

mysql> backup database db2 to '2.bup';
ERROR 1716 (HY000): Failed to obtain meta-data for view `db2`.`v2`

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

How to repeat:
create database db2;
use db2;
create view view1 as select 1;
create view v1 as select 5;
create view v2 as select * from v1;
alter view v1 as select 6;
backup database db2 to '2.bup';

Suggested fix:
BACKUP should be able to backup this database

[15 Apr 2009 9:39] Jørgen Løland

Probably duplicate of or related to BUG#44283.

[4 May 2009 5:15] Rafal Somla

Here is another example where view is based on a table, not another view.
---------------------------------------------
use test;
create table t1 (a int, b int);
create view v1 as select a from t1;
alter table t1 drop column a;

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

backup database test to 'test.bkp';
# ^^ ERROR 1716 (HY000): Failed to obtain meta-data for view `test`.`v1`
---------------------------------------------

[22 Jun 2009 10:32] Jørgen Løland

The reason why this altering is a problem is this:

> use db;
> create view v1 as select 5;
> create view v2 as select * from v1;
> show create view v2;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`5` AS `5` from `v1`

Since this view is defined as select v1.5 (not v1.*), the view gets invalid when the definition of v1 changes to "... select 6" (because the column name is now "6".

[22 Jun 2009 10:36] Jørgen Løland

This bug can be partially fixed by changing this code in si_objects.cc:get_view_create_stmt:

  if (run_service_interface_sql(thd, &ed_connection, s_stream.lex_string()) ||
      ed_connection.get_warn_count())
  {
-    /*
-      There should be no warnings. A warning means that serialization has
-      failed.
-    */
-    return TRUE;
+    /* Warnings are OK, but push them to BACKUP's error stack */
+    thd->warning_info->append_warnings(thd, ed_connection.get_warn_list());
  }

This enables BACKUP of the view. However, with this change, RESTORE will fail for the backup image instead.