MySQL Bugs: #36265: Views not backed up if they are dependent on tables from different Database

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

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.

Thank you for the bug report.

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.

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

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

Noted in 6.0.6 changelog.

BACKUP DATABASE failed to back up views that depend on tables in a 
different database.

Pushed into 6.0.7-alpha  (revid:jorgen.loland@sun.com-20080609135406-vxdovhphg9qqnz6d) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)