MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Hema Sridharan Date:August 5 2008 3:46pm
Subject:bzr commit into mysql-6.0-backup branch (hema:2675) Bug#34758
View as plain text  
#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.
+

Thread
bzr commit into mysql-6.0-backup branch (hema:2675) Bug#34758Hema Sridharan5 Aug
  • Re: bzr commit into mysql-6.0-backup branch (hema:2675) Bug#34758Jørgen Løland6 Aug