Description:
1)I create two databases db1 and db2
2)I create tables and views in db1 and db2.
3)I create view (say db1.v6) which is dependent on other view(db2.v2) and table db1.t5.
4)I Backup database , Drop database and then perform Restore and my restore fails with error,
Table db2.v2' doesn't exist
How to repeat:
mysql> CREATE DATABASE bup_db1;
Query OK, 1 row affected (0.00 sec)
mysql> USE bup_db1;
Database changed
mysql> CREATE TABLE t5(Gender char(5), cand_age int, foreign key(cand_age) references
-> bup_db2.t2(age));
);
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE DATABASE bup_db2;
Query OK, 1 row affected (0.00 sec)
mysql> USE bup_db2;
Database changed
mysql> CREATE TABLE t2(idno int, age int primary key, education char(20) );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE VIEW v2 AS SELECT age, education FROM t2;
Query OK, 0 rows affected (0.00 sec)
mysql> use bup_db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE VIEW v6 AS SELECT education,gender FROM bup_db2.v2, t5 WHERE cand_age=age;
Query OK, 0 rows affected (0.00 sec)
mysql> show full tables from bup_db1;
+-------------------+------------+
| Tables_in_bup_db1 | Table_type |
+-------------------+------------+
| t1 | BASE TABLE |
| t5 | BASE TABLE |
| v6 | VIEW |
+-------------------+------------+
3 rows in set (0.00 sec)
mysql> show full tables from bup_db2;
+-------------------+------------+
| Tables_in_bup_db2 | Table_type |
+-------------------+------------+
| t2 | BASE TABLE |
| v2 | VIEW |
+-------------------+------------+
2 rows in set (0.00 sec)
mysql> backup database bup_db1, bup_db2 to '/space/hema/backup_dmp/both';
+-----------+
| backup_id |
+-----------+
| 1 |
+-----------+
1 row in set (0.14 sec)
mysql> restore from '/space/hema/backup_dmp/both';
ERROR 1146 (42S02): Table 'bup_db2.v2' doesn't exist
Description: 1)I create two databases db1 and db2 2)I create tables and views in db1 and db2. 3)I create view (say db1.v6) which is dependent on other view(db2.v2) and table db1.t5. 4)I Backup database , Drop database and then perform Restore and my restore fails with error, Table db2.v2' doesn't exist How to repeat: mysql> CREATE DATABASE bup_db1; Query OK, 1 row affected (0.00 sec) mysql> USE bup_db1; Database changed mysql> CREATE TABLE t5(Gender char(5), cand_age int, foreign key(cand_age) references -> bup_db2.t2(age)); ); Query OK, 0 rows affected (0.04 sec) mysql> CREATE DATABASE bup_db2; Query OK, 1 row affected (0.00 sec) mysql> USE bup_db2; Database changed mysql> CREATE TABLE t2(idno int, age int primary key, education char(20) ); Query OK, 0 rows affected (0.03 sec) mysql> CREATE VIEW v2 AS SELECT age, education FROM t2; Query OK, 0 rows affected (0.00 sec) mysql> use bup_db1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE VIEW v6 AS SELECT education,gender FROM bup_db2.v2, t5 WHERE cand_age=age; Query OK, 0 rows affected (0.00 sec) mysql> show full tables from bup_db1; +-------------------+------------+ | Tables_in_bup_db1 | Table_type | +-------------------+------------+ | t1 | BASE TABLE | | t5 | BASE TABLE | | v6 | VIEW | +-------------------+------------+ 3 rows in set (0.00 sec) mysql> show full tables from bup_db2; +-------------------+------------+ | Tables_in_bup_db2 | Table_type | +-------------------+------------+ | t2 | BASE TABLE | | v2 | VIEW | +-------------------+------------+ 2 rows in set (0.00 sec) mysql> backup database bup_db1, bup_db2 to '/space/hema/backup_dmp/both'; +-----------+ | backup_id | +-----------+ | 1 | +-----------+ 1 row in set (0.14 sec) mysql> restore from '/space/hema/backup_dmp/both'; ERROR 1146 (42S02): Table 'bup_db2.v2' doesn't exist