#At file:///data0/team6.0aug4/mysql-6.0-backup/
2675 Hema Sridharan 2008-08-05
Modified backup_views.test and result file as bug#34758 is fixed.
removed:
mysql-test/r/backup_view_on_view.result
mysql-test/t/backup_view_on_view.test
modified:
mysql-test/r/backup_views.result
mysql-test/t/backup_views.test
=== removed file 'mysql-test/r/backup_view_on_view.result'
--- a/mysql-test/r/backup_view_on_view.result 2008-06-25 13:39:04 +0000
+++ b/mysql-test/r/backup_view_on_view.result 1970-01-01 00:00:00 +0000
@@ -1,38 +0,0 @@
-SET GLOBAL debug="d,backup:d,backup_data";
-DROP DATABASE IF EXISTS db1;
-CREATE DATABASE db1;
-CREATE TABLE db1.t1(a int) ENGINE=INNODB;
-CREATE VIEW db1.v1 AS SELECT * FROM db1.t1;
-CREATE VIEW db1.v2 AS SELECT * FROM db1.v1;
-INSERT INTO db1.t1 VALUES (1),(2),(3),(5),(7),(11);
-BACKUP DATABASE db1 TO 'test.bak';
-backup_id
-#
-RESTORE FROM 'test.bak';
-backup_id
-#
-SELECT * FROM db1.v2;
-a
-1
-2
-3
-5
-7
-11
-SELECT * FROM db1.v1;
-a
-1
-2
-3
-5
-7
-11
-SELECT * FROM db1.t1;
-a
-1
-2
-3
-5
-7
-11
-DROP DATABASE db1;
=== modified file 'mysql-test/r/backup_views.result'
--- a/mysql-test/r/backup_views.result 2008-06-12 09:55:35 +0000
+++ b/mysql-test/r/backup_views.result 2008-08-05 15:45:20 +0000
@@ -6,11 +6,16 @@ DROP DATABASE IF EXISTS bup_db2;
CREATE DATABASE bup_db1;
USE bup_db1;
Creating Table t1
-CREATE TABLE t1(id int not null primary key, name char(10),city varchar(10));
+CREATE TABLE bup_db1.t1(
+id INT NOT NULL PRIMARY KEY,
+name CHAR(10),
+city VARCHAR(10)
+)ENGINE=INNODB;
loading data
-INSERT INTO t1 VALUES
-(1,'aa1','RR1'),(2,'aa2','RR2'),(3,'aa3','RR3'),(4,'aa4','RR4'),(5,'aa5','RR5'),(6,'aa6','RR6'),(7,'aa7','RR7'),(8,'aa8','RR8');
-SELECT * FROM t1;
+INSERT INTO bup_db1.t1 VALUES
+(1,'aa1','RR1'),(2,'aa2','RR2'),(3,'aa3','RR3'),(4,'aa4','RR4'),
+(5,'aa5','RR5'),(6,'aa6','RR6'),(7,'aa7','RR7'),(8,'aa8','RR8');
+SELECT * FROM bup_db1.t1 ORDER BY id;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -21,66 +26,85 @@ id name city
7 aa7 RR7
8 aa8 RR8
Creating Table t3
-CREATE TABLE t3(ccode int, District char(20) not null primary key, scode int, foreign key (scode) references t1(id));
+CREATE TABLE bup_db1.t3(
+ccode INT,
+District CHAR(20) NOT NULL PRIMARY KEY,
+scode INT,
+FOREIGN KEY (scode) REFERENCES bup_db1.t1(id)
+)ENGINE=INNODB;
Loading Data
INSERT INTO t3 VALUES
-(234, 'zuloa',1),(321,'yyy',2),(765,'iug',3),(124,'LKJ',4),(235,'uth',6);
-SELECT * FROM t3;
+(234, 'zuloa',1),(321,'yyy',2),(765,'iug',3),
+(124,'LKJ',4),(235,'uth',6);
+SELECT * FROM bup_db1.t3 ORDER BY scode;
ccode District scode
234 zuloa 1
321 yyy 2
765 iug 3
124 LKJ 4
235 uth 6
+*****Create view from the table bup_db1.t1*******
+CREATE VIEW bup_db1.v1 AS SELECT * FROM bup_db1.t1;
+***Create views from 2 tables(t1 and t3) within same DB bup_db1****
+CREATE VIEW bup_db1.vcomb AS
+SELECT name, city, ccode FROM bup_db1.t1, bup_db1.t3 WHERE id=scode;
+CREATE DATABASE bup_db2;
+CREATE TABLE bup_db2.t2(
+idno INT,
+age INT PRIMARY KEY,
+education CHAR(20) ,
+FOREIGN KEY (idno) REFERENCES bup_db1.t1(id)
+)ENGINE=INNODB;
+INSERT INTO bup_db2.t2 VALUES
+(1,23,'BS'),(2,24,'BE'),(3,19,'School'),(4,28,'MS'),
+(5,43,'PHD'),(6,30,'Doctor'),(7,31,'Lawyer'),(8,27,'Undergrad');
+SELECT * FROM bup_db2.t2 ORDER BY age;
+idno age education
+3 19 School
+1 23 BS
+2 24 BE
+8 27 Undergrad
+4 28 MS
+6 30 Doctor
+7 31 Lawyer
+5 43 PHD
+****Create view in bup_db2****
+CREATE VIEW bup_db2.v2 AS SELECT age, education FROM bup_db2.t2;
+******Create views from combination of 2 databases*******
+CREATE VIEW bup_db2.v3 AS SELECT name, age, education
+FROM bup_db1.t1 , bup_db2.t2 WHERE id=idno;
+*********Create view from another view in bup_db2***********.
+CREATE VIEW bup_db2.vv (N, A, E) AS SELECT * FROM bup_db2.v3;
+*****Create view from other Database********
+CREATE VIEW bup_db2.v4 AS SELECT * FROM bup_db1.t3;
+Rename the view name
+RENAME TABLE bup_db2.v4 to bup_db2.student_details;
+*******Create view from database bup_db2**********
+CREATE VIEW bup_db1.v5 AS SELECT * FROM bup_db2.t2;
Creating Table t5
-CREATE TABLE t5(Gender char(5), cand_age int,foreign key(cand_age) references
-bup_db2.t2(age));
+CREATE TABLE bup_db1.t5(
+Gender CHAR(5),
+cand_age INT,
+FOREIGN KEY(cand_age) REFERENCES bup_db2.t2(age)
+)ENGINE=INNODB;
Loading data into table t5
-INSERT INTO t5 VALUES
-('F',23),('F',24),('M',19),('F',28),('M',43),('F',30),('M',31),('M',27);
-SELECT * FROM t5;
+INSERT INTO bup_db1.t5 VALUES
+('F',23),('F',24),('M',19),('F',28),
+('M',43),('F',30),('M',31),('M',27);
+SELECT * FROM bup_db1.t5 ORDER BY Gender;
Gender cand_age
F 23
F 24
-M 19
F 28
-M 43
F 30
+M 19
+M 43
M 31
M 27
-*****Create views from the table t1 of bup_db1*******
-CREATE VIEW v1 AS SELECT * FROM t1;
-*****Creating views from 2 tables(t1 and t3) within same database******
-CREATE VIEW vcomb AS SELECT name, city, ccode FROM t1, t3 WHERE id=scode;
-CREATE DATABASE bup_db2;
-USE bup_db2;
-CREATE TABLE t2(idno int, age int primary key, education char(20) ,foreign key (idno) references bup_db1.t1(id));
-INSERT INTO t2 VALUES(1,23,'BS'),(2,24,'BE'),(3,19,'school'),(4,28,'MS'),(5,43,'PHD'),(6,30,'Doctor'),(7,31,'Lawyer'),(8,27,'undergrad');
-SELECT * FROM t2;
-idno age education
-1 23 BS
-2 24 BE
-3 19 school
-4 28 MS
-5 43 PHD
-6 30 Doctor
-7 31 Lawyer
-8 27 undergrad
-****Creating View****
-CREATE VIEW v2 AS SELECT age, education FROM t2;
-******Creating Views from combination of 2 databases*******
-CREATE VIEW v3 AS SELECT name, age, education FROM bup_db1.t1 , bup_db2.t2 WHERE id=idno;
-*********Creating View from another view ***********.
-*****Creating View from other Database********
-CREATE VIEW v4 AS SELECT * FROM bup_db1.t3;
-Rename the view name
-RENAME TABLE v4 to student_details;
-USE bup_db1;
-*******Creating View from database bup_db2**********
-CREATE VIEW v5 AS SELECT * FROM bup_db2.t2;
-******Creating View v6********
-USE bup_db1;
-SELECT * FROM t1;
+******Create view v6********
+CREATE VIEW bup_db1.v6 AS SELECT education,gender
+FROM bup_db2.v2, bup_db1.t5 WHERE cand_age=age;
+SELECT * FROM bup_db1.t1 ORDER BY id;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -90,24 +114,24 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-SELECT * FROM t3;
+SELECT * FROM bup_db1.t3 ORDER BY scode;
ccode District scode
234 zuloa 1
321 yyy 2
765 iug 3
124 LKJ 4
235 uth 6
-SELECT * FROM t5;
+SELECT * FROM bup_db1.t5 ORDER BY Gender;
Gender cand_age
F 23
F 24
-M 19
F 28
-M 43
F 30
+M 19
+M 43
M 31
M 27
-SELECT * FROM v1;
+SELECT * FROM bup_db1.v1;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -117,62 +141,81 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-SELECT * FROM vcomb;
+SELECT * FROM bup_db1.vcomb ORDER BY name;
name city ccode
aa1 RR1 234
aa2 RR2 321
aa3 RR3 765
aa4 RR4 124
aa6 RR6 235
-SELECT * FROM v5;
+SELECT * FROM bup_db1.v5 ORDER BY age;
idno age education
+3 19 School
1 23 BS
2 24 BE
-3 19 school
+8 27 Undergrad
4 28 MS
-5 43 PHD
6 30 Doctor
7 31 Lawyer
-8 27 undergrad
+5 43 PHD
+SELECT * FROM bup_db1.v6 ORDER BY gender;
+education gender
+MS F
+Doctor F
+BS F
+BE F
+Undergrad M
+Lawyer M
+PHD M
+School M
excercise objects of bup_db2
-USE bup_db2;
-SELECT * FROM t2;
+SELECT * FROM bup_db2.t2 ORDER BY age;
idno age education
+3 19 School
1 23 BS
2 24 BE
-3 19 school
+8 27 Undergrad
4 28 MS
-5 43 PHD
6 30 Doctor
7 31 Lawyer
-8 27 undergrad
-SELECT * FROM v2;
+5 43 PHD
+SELECT * FROM bup_db2.v2 ORDER BY age;
age education
+19 School
23 BS
24 BE
-19 school
+27 Undergrad
28 MS
-43 PHD
30 Doctor
31 Lawyer
-27 undergrad
-SELECT * FROM v3;
+43 PHD
+SELECT * FROM bup_db2.v3 ORDER BY age;
name age education
+aa3 19 School
aa1 23 BS
aa2 24 BE
-aa3 19 school
+aa8 27 Undergrad
+aa4 28 MS
+aa6 30 Doctor
+aa7 31 Lawyer
+aa5 43 PHD
+SELECT * FROM bup_db2.vv;
+N A E
+aa1 23 BS
+aa2 24 BE
+aa3 19 School
aa4 28 MS
aa5 43 PHD
aa6 30 Doctor
aa7 31 Lawyer
-aa8 27 undergrad
-SELECT * FROM student_details;
+aa8 27 Undergrad
+SELECT * FROM bup_db2.student_details;
ccode District scode
-234 zuloa 1
-321 yyy 2
765 iug 3
124 LKJ 4
235 uth 6
+321 yyy 2
+234 zuloa 1
showing objects and create statements.
SHOW FULL TABLES FROM bup_db1;;
Tables_in_bup_db1 t1
@@ -185,6 +228,8 @@ Tables_in_bup_db1 v1
Table_type VIEW
Tables_in_bup_db1 v5
Table_type VIEW
+Tables_in_bup_db1 v6
+Table_type VIEW
Tables_in_bup_db1 vcomb
Table_type VIEW
SHOW FULL TABLES FROM bup_db2;;
@@ -196,14 +241,16 @@ Tables_in_bup_db2 v2
Table_type VIEW
Tables_in_bup_db2 v3
Table_type VIEW
+Tables_in_bup_db2 vv
+Table_type VIEW
SHOW CREATE VIEW bup_db1.v1;;
View v1
-Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`v1` AS select `bup_db1`.`t1`.`id` AS `id`,`bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city` from `bup_db1`.`t1`
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,`t1`.`name` AS `name`,`t1`.`city` AS `city` from `t1`
character_set_client latin1
collation_connection latin1_swedish_ci
SHOW CREATE VIEW bup_db1.vcomb;;
View vcomb
-Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`vcomb` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city`,`bup_db1`.`t3`.`ccode` AS `ccode` from (`bup_db1`.`t1` join `bup_db1`.`t3`) where (`bup_db1`.`t1`.`id` = `bup_db1`.`t3`.`scode`)
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vcomb` AS select `t1`.`name` AS `name`,`t1`.`city` AS `city`,`t3`.`ccode` AS `ccode` from (`t1` join `t3`) where (`t1`.`id` = `t3`.`scode`)
character_set_client latin1
collation_connection latin1_swedish_ci
SHOW CREATE VIEW bup_db2.v3;;
@@ -211,7 +258,7 @@ View v3
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db2`.`v3` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db2`.`t2`.`age` AS `age`,`bup_db2`.`t2`.`education` AS `education` from (`bup_db1`.`t1` join `bup_db2`.`t2`) where (`bup_db1`.`t1`.`id` = `bup_db2`.`t2`.`idno`)
character_set_client latin1
collation_connection latin1_swedish_ci
-backup data
+backup database
BACKUP DATABASE bup_db1, bup_db2 TO 'bup_objectview.bak';
backup_id
#
@@ -222,8 +269,12 @@ BACKUP DATABASE bup_db2 TO 'bup_objectvi
backup_id
#
dropping database.
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
DROP DATABASE bup_db2;
+Restore database.
RESTORE FROM 'bup_objectview.bak';
backup_id
#
@@ -242,6 +293,8 @@ Tables_in_bup_db1 v1
Table_type VIEW
Tables_in_bup_db1 v5
Table_type VIEW
+Tables_in_bup_db1 v6
+Table_type VIEW
Tables_in_bup_db1 vcomb
Table_type VIEW
SHOW FULL TABLES FROM bup_db2;;
@@ -253,6 +306,8 @@ Tables_in_bup_db2 v2
Table_type VIEW
Tables_in_bup_db2 v3
Table_type VIEW
+Tables_in_bup_db2 vv
+Table_type VIEW
SHOW CREATE VIEW bup_db1.v1;;
View v1
Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`v1` AS select `bup_db1`.`t1`.`id` AS `id`,`bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city` from `bup_db1`.`t1`
@@ -269,8 +324,7 @@ Create View CREATE ALGORITHM=UNDEFINED D
character_set_client latin1
collation_connection latin1_swedish_ci
****check for view contents after Restore*****
-USE bup_db1;
-SELECT * FROM t1;
+SELECT * FROM bup_db1.t1 ORDER BY id;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -280,24 +334,24 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-SELECT * FROM t3;
+SELECT * FROM bup_db1.t3 ORDER BY scode;
ccode District scode
234 zuloa 1
321 yyy 2
765 iug 3
124 LKJ 4
235 uth 6
-SELECT * FROM t5;
+SELECT * FROM bup_db1.t5 ORDER BY Gender;
Gender cand_age
F 23
F 24
-M 19
F 28
-M 43
F 30
+M 19
+M 43
M 31
M 27
-SELECT * FROM v1;
+SELECT * FROM bup_db1.v1;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -307,79 +361,88 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-SELECT * FROM vcomb;
+SELECT * FROM bup_db1.vcomb ORDER BY name;
name city ccode
aa1 RR1 234
aa2 RR2 321
aa3 RR3 765
aa4 RR4 124
aa6 RR6 235
-SELECT * FROM v5;
+SELECT * FROM bup_db1.v5 ORDER BY age;
idno age education
+3 19 School
1 23 BS
2 24 BE
-3 19 school
+8 27 Undergrad
4 28 MS
-5 43 PHD
6 30 Doctor
7 31 Lawyer
-8 27 undergrad
+5 43 PHD
+SELECT * FROM bup_db1.v6 ORDER BY gender;
+education gender
+Doctor F
+BS F
+BE F
+MS F
+Lawyer M
+PHD M
+School M
+Undergrad M
excercise objects of bup_db2
-use bup_db2;
-SELECT * FROM t2;
+SELECT * FROM bup_db2.t2 ORDER BY age;
idno age education
+3 19 School
1 23 BS
2 24 BE
-3 19 school
+8 27 Undergrad
4 28 MS
-5 43 PHD
6 30 Doctor
7 31 Lawyer
-8 27 undergrad
-SELECT * FROM v2;
+5 43 PHD
+SELECT * FROM bup_db2.v2 ORDER BY age;
age education
+19 School
23 BS
24 BE
-19 school
+27 Undergrad
28 MS
-43 PHD
30 Doctor
31 Lawyer
-27 undergrad
-SELECT * FROM v3;
+43 PHD
+SELECT * FROM bup_db2.v3 ORDER BY age;
name age education
+aa3 19 School
+aa1 23 BS
+aa2 24 BE
+aa8 27 Undergrad
+aa4 28 MS
+aa6 30 Doctor
+aa7 31 Lawyer
+aa5 43 PHD
+SELECT * FROM bup_db2.vv;
+N A E
aa1 23 BS
aa2 24 BE
-aa3 19 school
+aa3 19 School
aa4 28 MS
aa5 43 PHD
aa6 30 Doctor
aa7 31 Lawyer
-aa8 27 undergrad
-SELECT * FROM student_details;
+aa8 27 Undergrad
+SELECT * FROM bup_db2.student_details;
ccode District scode
-234 zuloa 1
-321 yyy 2
765 iug 3
124 LKJ 4
235 uth 6
-DROP DATABASE bup_db1;
-DROP DATABASE bup_db2;
-Restoring Database
-RESTORE FROM 'bup_objectview.bak';
-backup_id
-#
-USE bup_db1;
-ALTER TABLE t1 CHANGE id id tinyint not null;
-SHOW CREATE TABLE t1;;
-Table t1
-Create Table CREATE TABLE `t1` (
- `id` tinyint(4) NOT NULL,
- `name` char(10) DEFAULT NULL,
- `city` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-SELECT * FROM t1;
+321 yyy 2
+234 zuloa 1
+ALTER TABLE bup_db1.t1 CHANGE name name VARCHAR(10);
+DESCRIBE bup_db1.t1;
+Field Type Null Key Default Extra
+id int(11) NO PRI NULL
+name varchar(10) YES NULL
+city varchar(10) YES NULL
+SELECT * FROM bup_db1.t1 ORDER BY id;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -389,17 +452,7 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-DELETE FROM t1 WHERE id=7;
-SELECT * FROM t1;
-id name city
-1 aa1 RR1
-2 aa2 RR2
-3 aa3 RR3
-4 aa4 RR4
-5 aa5 RR5
-6 aa6 RR6
-8 aa8 RR8
-SELECT * FROM v1;
+SELECT * FROM bup_db1.v1;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -407,37 +460,40 @@ id name city
4 aa4 RR4
5 aa5 RR5
6 aa6 RR6
+7 aa7 RR7
8 aa8 RR8
-USE bup_db2;
-SELECT * FROM v3;
+SELECT * FROM bup_db2.v3 ORDER BY age;
name age education
+aa3 19 School
aa1 23 BS
aa2 24 BE
-aa3 19 school
+aa8 27 Undergrad
aa4 28 MS
-aa5 43 PHD
aa6 30 Doctor
-aa8 27 undergrad
+aa7 31 Lawyer
+aa5 43 PHD
BACKUP DATABASE bup_db1, bup_db2 TO 'bup_objectview3.bak';
backup_id
#
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
DROP DATABASE bup_db2;
RESTORE FROM 'bup_objectview3.bak';
backup_id
#
-USE bup_db2;
-SELECT * FROM v3;
+SELECT * FROM bup_db2.v3 ORDER BY age;
name age education
+aa3 19 School
aa1 23 BS
aa2 24 BE
-aa3 19 school
+aa8 27 Undergrad
aa4 28 MS
-aa5 43 PHD
aa6 30 Doctor
-aa8 27 undergrad
-USE bup_db1;
-SELECT * FROM t1;
+aa7 31 Lawyer
+aa5 43 PHD
+SELECT * FROM bup_db1.t1 ORDER BY id;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -445,9 +501,13 @@ id name city
4 aa4 RR4
5 aa5 RR5
6 aa6 RR6
+7 aa7 RR7
8 aa8 RR8
*** DROP bup_db1, bup_db2 DATABASE ****
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
DROP DATABASE bup_db2;
=== removed file 'mysql-test/t/backup_view_on_view.test'
--- a/mysql-test/t/backup_view_on_view.test 2008-06-25 13:39:04 +0000
+++ b/mysql-test/t/backup_view_on_view.test 1970-01-01 00:00:00 +0000
@@ -1,32 +0,0 @@
-# Test case for bug#34758
-
---source include/not_embedded.inc
---source include/have_debug.inc
---source include/have_innodb.inc
-
-# Setup the server to use the backup breakpoints
-SET GLOBAL debug="d,backup:d,backup_data";
-
---disable_warnings
-DROP DATABASE IF EXISTS db1;
---enable_warnings
-
-CREATE DATABASE db1;
-
-CREATE TABLE db1.t1(a int) ENGINE=INNODB;
-CREATE VIEW db1.v1 AS SELECT * FROM db1.t1;
-CREATE VIEW db1.v2 AS SELECT * FROM db1.v1;
-
-INSERT INTO db1.t1 VALUES (1),(2),(3),(5),(7),(11);
-
-replace_column 1 #;
-BACKUP DATABASE db1 TO 'test.bak';
-replace_column 1 #;
-RESTORE FROM 'test.bak';
-
-SELECT * FROM db1.v2;
-SELECT * FROM db1.v1;
-SELECT * FROM db1.t1;
-
-DROP DATABASE db1;
-
=== modified file 'mysql-test/t/backup_views.test'
--- a/mysql-test/t/backup_views.test 2008-06-12 09:55:35 +0000
+++ b/mysql-test/t/backup_views.test 2008-08-05 15:45:20 +0000
@@ -25,140 +25,148 @@ connect (breakpoints,localhost,root,,);
DROP DATABASE IF EXISTS bup_db1;
DROP DATABASE IF EXISTS bup_db2;
-
-#We are creating 2 databases bup_db1 and bup_db2 to accomplish wide testing of views in order to check their consistency # after BACKUP AND RESTORE.
+#
+# We are creating 2 databases bup_db1 and bup_db2 to accomplish wide testing
+# of views in order to check their consistency after BACKUP AND RESTORE.
# In bup_db1 DATABASE consists of tables :t1 t3 t5
-# and views v1(based on t1 alone), vcomb(based on t1 and t3), v5( based on bup_db2.t2), v6(based on bup_db2.v2,bup_db1.t5)
+# and views v1(based on t1 alone), vcomb(based on t1 and t3),
+# v5( based on bup_db2.t2), v6(based on bup_db2.v2,bup_db1.t5)
#
# In bup_db2,it consists table t2
-# views v2(based on t2), v3( based on combination of bup_db1 and bup_db2),v4( based on bup_db1.t3), vv( based on v3)
+# views v2(based on t2), v3( based on combination of bup_db1 and bup_db2),
+# v4( based on bup_db1.t3), vv( based on v3)
#
--enable_warnings
-
CREATE DATABASE bup_db1;
USE bup_db1;
#Create table and load with data.
--echo Creating Table t1
-CREATE TABLE t1(id int not null primary key, name char(10),city varchar(10));
+CREATE TABLE bup_db1.t1(
+id INT NOT NULL PRIMARY KEY,
+name CHAR(10),
+city VARCHAR(10)
+)ENGINE=INNODB;
--echo loading data
-INSERT INTO t1 VALUES
-(1,'aa1','RR1'),(2,'aa2','RR2'),(3,'aa3','RR3'),(4,'aa4','RR4'),(5,'aa5','RR5'),(6,'aa6','RR6'),(7,'aa7','RR7'),(8,'aa8','RR8');
+INSERT INTO bup_db1.t1 VALUES
+(1,'aa1','RR1'),(2,'aa2','RR2'),(3,'aa3','RR3'),(4,'aa4','RR4'),
+(5,'aa5','RR5'),(6,'aa6','RR6'),(7,'aa7','RR7'),(8,'aa8','RR8');
-SELECT * FROM t1;
+SELECT * FROM bup_db1.t1 ORDER BY id;
--echo Creating Table t3
-CREATE TABLE t3(ccode int, District char(20) not null primary key, scode int, foreign key (scode) references t1(id));
+CREATE TABLE bup_db1.t3(
+ccode INT,
+District CHAR(20) NOT NULL PRIMARY KEY,
+scode INT,
+FOREIGN KEY (scode) REFERENCES bup_db1.t1(id)
+)ENGINE=INNODB;
--echo Loading Data
INSERT INTO t3 VALUES
-(234, 'zuloa',1),(321,'yyy',2),(765,'iug',3),(124,'LKJ',4),(235,'uth',6);
-
-SELECT * FROM t3;
+(234, 'zuloa',1),(321,'yyy',2),(765,'iug',3),
+(124,'LKJ',4),(235,'uth',6);
---echo Creating Table t5
-
-CREATE TABLE t5(Gender char(5), cand_age int,foreign key(cand_age) references
-bup_db2.t2(age));
-
---echo Loading data into table t5
+SELECT * FROM bup_db1.t3 ORDER BY scode;
-INSERT INTO t5 VALUES
-('F',23),('F',24),('M',19),('F',28),('M',43),('F',30),('M',31),('M',27);
+--echo *****Create view from the table bup_db1.t1*******
-SELECT * FROM t5;
+CREATE VIEW bup_db1.v1 AS SELECT * FROM bup_db1.t1;
---echo *****Create views from the table t1 of bup_db1*******
-
-CREATE VIEW v1 AS SELECT * FROM t1;
-
---echo *****Creating views from 2 tables(t1 and t3) within same database******
-
-CREATE VIEW vcomb AS SELECT name, city, ccode FROM t1, t3 WHERE id=scode;
+--echo ***Create views from 2 tables(t1 and t3) within same DB bup_db1****
+CREATE VIEW bup_db1.vcomb AS
+SELECT name, city, ccode FROM bup_db1.t1, bup_db1.t3 WHERE id=scode;
CREATE DATABASE bup_db2;
-USE bup_db2;
-
-CREATE TABLE t2(idno int, age int primary key, education char(20) ,foreign key (idno) references bup_db1.t1(id));
-INSERT INTO t2 VALUES(1,23,'BS'),(2,24,'BE'),(3,19,'school'),(4,28,'MS'),(5,43,'PHD'),(6,30,'Doctor'),(7,31,'Lawyer'),(8,27,'undergrad');
+CREATE TABLE bup_db2.t2(
+idno INT,
+age INT PRIMARY KEY,
+education CHAR(20) ,
+FOREIGN KEY (idno) REFERENCES bup_db1.t1(id)
+)ENGINE=INNODB;
+INSERT INTO bup_db2.t2 VALUES
+(1,23,'BS'),(2,24,'BE'),(3,19,'School'),(4,28,'MS'),
+(5,43,'PHD'),(6,30,'Doctor'),(7,31,'Lawyer'),(8,27,'Undergrad');
-SELECT * FROM t2;
+SELECT * FROM bup_db2.t2 ORDER BY age;
---echo ****Creating View****
+--echo ****Create view in bup_db2****
-CREATE VIEW v2 AS SELECT age, education FROM t2;
+CREATE VIEW bup_db2.v2 AS SELECT age, education FROM bup_db2.t2;
---echo ******Creating Views from combination of 2 databases*******
+--echo ******Create views from combination of 2 databases*******
-CREATE VIEW v3 AS SELECT name, age, education FROM bup_db1.t1 , bup_db2.t2 WHERE id=idno;
+CREATE VIEW bup_db2.v3 AS SELECT name, age, education
+FROM bup_db1.t1 , bup_db2.t2 WHERE id=idno;
---echo *********Creating View from another view ***********.
+--echo *********Create view from another view in bup_db2***********.
-#Bug#35347 Mysql Server crash while doing restore with views for default driver
+# Bug#35347 Mysql Server crash while doing restore with views for default driver
# BUG#34758 Server crashes if database with views backed up using CS driver
-#Creatig view from another view is possible if bug#35347 and bug#34758 is fixed.
+#Creating view from another view is possible if bug#35347 and bug#34758is fixed.
-#CREATE VIEW vv (N, A, E) AS SELECT * FROM v3;
+CREATE VIEW bup_db2.vv (N, A, E) AS SELECT * FROM bup_db2.v3;
---echo *****Creating View from other Database********
+--echo *****Create view from other Database********
-CREATE VIEW v4 AS SELECT * FROM bup_db1.t3;
+CREATE VIEW bup_db2.v4 AS SELECT * FROM bup_db1.t3;
--echo Rename the view name
-RENAME TABLE v4 to student_details;
+RENAME TABLE bup_db2.v4 to bup_db2.student_details;
-USE bup_db1;
+--echo *******Create view from database bup_db2**********
---echo *******Creating View from database bup_db2**********
+CREATE VIEW bup_db1.v5 AS SELECT * FROM bup_db2.t2;
-CREATE VIEW v5 AS SELECT * FROM bup_db2.t2;
+--echo Creating Table t5
---echo ******Creating View v6********
+CREATE TABLE bup_db1.t5(
+Gender CHAR(5),
+cand_age INT,
+FOREIGN KEY(cand_age) REFERENCES bup_db2.t2(age)
+)ENGINE=INNODB;
-#Bug#36213 Restore fails for a database that has views created using another database .
+--echo Loading data into table t5
-#CREATE VIEW v6 AS SELECT education,gender FROM bup_db2.v2, t5 WHERE cand_age=age;
+INSERT INTO bup_db1.t5 VALUES
+('F',23),('F',24),('M',19),('F',28),
+('M',43),('F',30),('M',31),('M',27);
+
+SELECT * FROM bup_db1.t5 ORDER BY Gender;
+
+#Bug#36213 Restore fails for a database that has views created using
+#another database .
+
+--echo ******Create view v6********
+CREATE VIEW bup_db1.v6 AS SELECT education,gender
+FROM bup_db2.v2, bup_db1.t5 WHERE cand_age=age;
#Excercise the objects of bup_db1
-USE bup_db1;
-
-SELECT * FROM t1;
-
-SELECT * FROM t3;
-
-SELECT * FROM t5;
-
-SELECT * FROM v1;
-
-SELECT * FROM vcomb;
-
-SELECT * FROM v5;
-
-#SELECT * FROM v6;
+SELECT * FROM bup_db1.t1 ORDER BY id;
+SELECT * FROM bup_db1.t3 ORDER BY scode;
+SELECT * FROM bup_db1.t5 ORDER BY Gender;
+SELECT * FROM bup_db1.v1;
+SELECT * FROM bup_db1.vcomb ORDER BY name;
+SELECT * FROM bup_db1.v5 ORDER BY age;
+SELECT * FROM bup_db1.v6 ORDER BY gender;
--echo excercise objects of bup_db2
-USE bup_db2;
-
-SELECT * FROM t2;
-
-SELECT * FROM v2;
-
-SELECT * FROM v3;
-
-#SELECT * FROM vv;
-
-SELECT * FROM student_details; #view v4 is renamed as student_details
+SELECT * FROM bup_db2.t2 ORDER BY age;
+SELECT * FROM bup_db2.v2 ORDER BY age;
+SELECT * FROM bup_db2.v3 ORDER BY age;
+SELECT * FROM bup_db2.vv;
+SELECT * FROM bup_db2.student_details; #view v4 is renamed as student_details
#Show the data and Create statements
@@ -170,7 +178,8 @@ SELECT * FROM student_details; #view v4
--query_vertical SHOW CREATE VIEW bup_db2.v3;
#Backup and restore data.
---echo backup data
+--echo backup database
+
replace_column 1 #;
BACKUP DATABASE bup_db1, bup_db2 TO 'bup_objectview.bak';
@@ -181,13 +190,18 @@ replace_column 1 #;
BACKUP DATABASE bup_db2 TO 'bup_objectview2.bak';
--echo dropping database.
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
-
DROP DATABASE bup_db2;
-#RESTORE FROM bup_objectview.bak;
+--echo Restore database.
-#Individual databases cannot be restored because of VIEW DEPENDENCY
+# Individual databases cannot be restored because of VIEW DEPENDENCY
+# For restoring we need base tables in the database, otherwise the
+# Restore will fail. Once this bug is fixed, we can remove the '#'
+# for the restore below.
#--error 1146
#RESTORE FROM 'bup_objectview1.bak';
@@ -209,77 +223,45 @@ RESTORE FROM 'bup_objectview.bak';
--echo ****check for view contents after Restore*****
#Excercise the objects of bup_db1
-USE bup_db1;
-SELECT * FROM t1;
-
-SELECT * FROM t3;
-
-SELECT * FROM t5;
-
-SELECT * FROM v1;
-SELECT * FROM vcomb;
+SELECT * FROM bup_db1.t1 ORDER BY id;
+SELECT * FROM bup_db1.t3 ORDER BY scode;
+SELECT * FROM bup_db1.t5 ORDER BY Gender;
+SELECT * FROM bup_db1.v1;
+SELECT * FROM bup_db1.vcomb ORDER BY name;
+SELECT * FROM bup_db1.v5 ORDER BY age;
+SELECT * FROM bup_db1.v6 ORDER BY gender;
-SELECT * FROM v5;
-
-#SELECT * FROM v6;
--echo excercise objects of bup_db2
-use bup_db2;
-SELECT * FROM t2;
-
-SELECT * FROM v2;
-
-SELECT * FROM v3;
-
-#SELECT * FROM vv;
-
-SELECT * FROM student_details;
-
-DROP DATABASE bup_db1;
-DROP DATABASE bup_db2;
-
---echo Restoring Database
-
-replace_column 1 #;
-RESTORE FROM 'bup_objectview.bak';
-USE bup_db1;
+SELECT * FROM bup_db2.t2 ORDER BY age;
+SELECT * FROM bup_db2.v2 ORDER BY age;
+SELECT * FROM bup_db2.v3 ORDER BY age;
+SELECT * FROM bup_db2.vv;
+SELECT * FROM bup_db2.student_details;
#Alter table t1 and take BACKUP to see if view is not affected.
-ALTER TABLE t1 CHANGE id id tinyint not null;
---query_vertical SHOW CREATE TABLE t1;
-
-SELECT * FROM t1;
-
-DELETE FROM t1 WHERE id=7;
-SELECT * FROM t1;
-
-SELECT * FROM v1;
-
-USE bup_db2;
-SELECT * FROM v3;
-
-#BUG#35249 Mysql server crash for delete operation followed by backup for Default Drivers.
-
-#DELETE FROM t2 WHERE age=24;
-#SELECT * FROM t2;
-#SELECT * FROM v3;
+ALTER TABLE bup_db1.t1 CHANGE name name VARCHAR(10);
+DESCRIBE bup_db1.t1;
+SELECT * FROM bup_db1.t1 ORDER BY id;
+SELECT * FROM bup_db1.v1;
+SELECT * FROM bup_db2.v3 ORDER BY age;
replace_column 1 #;
BACKUP DATABASE bup_db1, bup_db2 TO 'bup_objectview3.bak';
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
DROP DATABASE bup_db2;
replace_column 1 #;
RESTORE FROM 'bup_objectview3.bak';
-USE bup_db2;
-SELECT * FROM v3;
-
-USE bup_db1;
-SELECT * FROM t1;
+SELECT * FROM bup_db2.v3 ORDER BY age;
+SELECT * FROM bup_db1.t1 ORDER BY id;
# Test cleanup section
@@ -287,15 +269,14 @@ SELECT * FROM t1;
--echo *** DROP bup_db1, bup_db2 DATABASE ****
--echo
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
-
DROP DATABASE bup_db2;
-
--remove_file $MYSQLTEST_VARDIR/master-data/bup_objectview.bak
-
--remove_file $MYSQLTEST_VARDIR/master-data/bup_objectview1.bak
-
--remove_file $MYSQLTEST_VARDIR/master-data/bup_objectview2.bak
--remove_file $MYSQLTEST_VARDIR/master-data/bup_objectview3.bak
-#BUG#35249 Mysql server crash for delete operation followed by backup for Default Drivers.
+
