Bug #36265 Views not backed up if they are dependent on tables from different Database
Submitted: 22 Apr 2008 21:10 Modified: 10 Jul 2008 19:53
Reporter: Hema Sridharan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:mysql-6.0-backup OS:Linux
Assigned to: Jørgen Løland CPU Architecture:Any
Triage: D2 (Serious)

[22 Apr 2008 21:10] Hema Sridharan
Description:
1) Create two databases (db1, db2) and table(t1 in db1).
2) Create views in db2
   a) v3 based on table db1.t1
3) Take backup of both databases, drop databases and perform a RESTORE. After restore, views aren't seen. Detailed steps are given below.

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

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

mysql> use db1;
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> use db2;
Database changed
mysql> create view v2 as select  * from db1.t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> show tables from db2;
+---------------+
| Tables_in_db2 |
+---------------+
| v2            |
+---------------+
1 row in set (0.01 sec)

mysql> backup database db1,db2 to '/tmp/db1db2';
+-----------+
| backup_id |
+-----------+
| 1         |
+-----------+
1 row in set (0.09 sec)

mysql> restore from '/tmp/db1db2';
+-----------+
| backup_id |
+-----------+
| 2         |
+-----------+
1 row in set (0.08 sec)

mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> show tables from db2;
Empty set (0.00 sec)

From the above example , we can notice that after RESTORE views are missing.
[22 Apr 2008 22:17] Miguel Solorzano
Thank you for the bug report.
[23 Apr 2008 5:32] Hema Sridharan
The Test and Result file for meta data consistency of views is located in the following link.
http://lists.mysql.com/commits/45695

Note:This test failed in the current Build mysql-6.0-backup (changeset@1.2611).This is a regression noticed.

Adding Jeb's email:
Hi,

If your test are now failing and you are sure the result file is correct, then I would open a bug, attach the .test and .result file to the report.

Then, when the developer works on the bug, he should push the test and result file into the clone as part of the patch.
[9 Jun 2008 13:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47602

2632 Jorgen Loland	2008-06-09
      bug#36265 - "Views not backed up if they are dependent on tables from different Database"
      
      Test case for backup of two databases with a view selecting from other db. 
      Test author: Hema Sridharan
[9 Jun 2008 14:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47604

2632 Jorgen Loland	2008-06-09
      bug#36265 - "Views not backed up if they are dependent on tables from different Database"
      
      Test case for backup of two databases with a view selecting from other db. 
      Test author: Hema Sridharan
[10 Jul 2008 19:53] Paul Dubois
Noted in 6.0.6 changelog.

BACKUP DATABASE failed to back up views that depend on tables in a 
different database.
[14 Sep 2008 0:42] Bugs System
Pushed into 6.0.7-alpha  (revid:jorgen.loland@sun.com-20080609135406-vxdovhphg9qqnz6d) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)