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 Email Updates:
Status: Verified Impact on me:
None 
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.