Description:
1) I create database and few tables.
2) I backup the database first without creating view in it.
3) Then I create view and backup the database again in different location.
4)If I check the size of both backup images it remains the same which means view is not backed up.
5)Also to verify it I drop the database and did a restore.
Restore is successful but I could not see view in the database.
How to repeat:
1)mysql> create database vmet;
Query OK, 1 row affected (0.00 sec)
2)mysql> use vmet;
Database changed
3)mysql> create table vv(s1 int, s2 char(60),s3 tinyint)engine=falcon;
Query OK, 0 rows affected (0.06 sec)
4)mysql> insert into vv values(1,'aaa',12),(2,'bbb',13),(3,'ccc',14),(4,'ddd',15);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
5)mysql> select * from vv;
+------+------+------+
| s1 | s2 | s3 |
+------+------+------+
| 1 | aaa | 12 |
| 2 | bbb | 13 |
| 3 | ccc | 14 |
| 4 | ddd | 15 |
+------+------+------+
4 rows in set (0.00 sec)
6)mysql> backup database vmet to '/data2/hema/backup_dmp/vmetnv';
***taking backup before creating view***
+-----------+
| backup_id |
+-----------+
| 15 |
+-----------+
1 row in set (0.06 sec)
7)mysql> create view mview as select s1,s2 from vv;
Query OK, 0 rows affected (0.00 sec)
8)mysql> select * from mview;
+------+------+
| s1 | s2 |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+------+------+
4 rows in set (0.00 sec)
9)mysql> show tables from vmet;
+----------------+
| Tables_in_vmet |
+----------------+
| mview |
| vv |
+----------------+
2 rows in set (0.00 sec)
10)mysql> show full tables;
+----------------+------------+
| Tables_in_vmet | Table_type |
+----------------+------------+
| mview | VIEW |
| vv | BASE TABLE |
+----------------+------------+
2 rows in set (0.00 sec)
11)mysql> backup database vmet to '/data2/hema/backup_dmp/vmethv';
---**Taking backup after creating view**----
+-----------+
| backup_id |
+-----------+
| 16 |
+-----------+
1 row in set (0.04 sec)
12)mysql> drop database vmet;
Query OK, 2 rows affected (0.01 sec)
13)mysql> restore from '/data2/hema/backup_dmp/vmethv';
+-----------+
| backup_id |
+-----------+
| 17 |
+-----------+
1 row in set (0.10 sec)
14)mysql> show tables from vmet;
View is missing here.
+----------------+
| Tables_in_vmet |
+----------------+
| vv |
+----------------+
1 row in set (0.00 sec)
15) -rw-rw---- 1 ndbdev ndbdev 1284 Feb 27 17:50 eventerr
-rw-rw---- 1 ndbdev ndbdev 397 Feb 28 03:03 vmetnv-----------** backup taken before creating view**----
-rw-rw---- 1 ndbdev ndbdev 397 Feb 28 03:06 vmethv ----------**backup taken after creating view**----
The above shows the size of backed up images.We notice that size of both vmetnv and vmethv are the same and view is not backed up.
Suggested fix:
View should be backed up and restored properly as per backup article
http://forge.mysql.com/wiki/OnlineBackup
