2677 Hema Sridharan 2008-08-07
Test modified
modified:
mysql-test/r/backup_views.result
mysql-test/t/backup_views.test
2676 Hema Sridharan 2008-08-06
backup_views.test modified to avoid the overlapping of the backup_view_on_view.test
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-08-05 08:04:30 +0000
+++ b/mysql-test/r/backup_views.result 2008-08-07 15:43:25 +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,67 +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********
-CREATE VIEW v6 AS SELECT education,gender FROM bup_db2.v2, t5 WHERE cand_age=age;
-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
@@ -91,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
@@ -118,72 +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
-SELECT * FROM v6;
+5 43 PHD
+SELECT * FROM bup_db1.v6 ORDER BY education, gender;
education gender
-BS F
BE F
-school M
-MS F
-PHD M
+BS F
Doctor F
Lawyer M
-undergrad M
+MS F
+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
-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
@@ -209,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;;
@@ -224,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
#
@@ -235,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 database with view dependency to other, non-existing db
RESTORE FROM 'bup_objectview1.bak';
ERROR 42S02: Table 'bup_db2.t2' doesn't exist
@@ -275,6 +313,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`
@@ -291,8 +331,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
@@ -302,24 +341,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
@@ -329,89 +368,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
-SELECT * FROM v6;
+5 43 PHD
+SELECT * FROM bup_db1.v6 ORDER BY education, gender;
education gender
-BS F
BE F
-school M
-MS F
-PHD M
+BS F
Doctor F
Lawyer M
-undergrad M
+MS F
+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
@@ -421,17 +459,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
@@ -439,37 +467,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
@@ -477,18 +508,22 @@ id name city
4 aa4 RR4
5 aa5 RR5
6 aa6 RR6
+7 aa7 RR7
8 aa8 RR8
-*** ENTER Backup of database with missing view dependency should fail but not crash server
-
+*** ENTER Backup of database with missing view dependency
+*** should fail but not crash server
+*** Test for bug#34902 ***
initializing test
+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';
backup_id
#
-USE bup_db1;
-SELECT * FROM t1;
+SELECT * FROM bup_db1.t1;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -498,7 +533,7 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-SELECT * FROM v1;
+SELECT * FROM bup_db1.v1;
id name city
1 aa1 RR1
2 aa2 RR2
@@ -508,11 +543,14 @@ id name city
6 aa6 RR6
7 aa7 RR7
8 aa8 RR8
-DROP TABLE t1;
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
+DROP TABLE bup_db1.t1;
Testing backup with missing view dependency in same db
-SELECT * FROM v1;
+SELECT * FROM bup_db1.v1;
ERROR HY000: View 'bup_db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
BACKUP DATABASE bup_db1 TO 'bup_shouldfail1.bak';
ERROR HY000: Failed to add view `bup_db1`.`v1` to the catalog
@@ -520,10 +558,10 @@ ERROR HY000: Failed to add view `bup_db1
Testing backup with missing view dependency in other db
USE bup_db2;
-SELECT * from v3;
+SELECT * from bup_db2.v3;
ERROR HY000: View 'bup_db2.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
BACKUP DATABASE bup_db2 TO 'bup_shouldfail2.bak';
-ERROR HY000: Failed to add view `bup_db2`.`v3` to the catalog
+ERROR HY000: Failed to add view `bup_db2`.`student_details` to the catalog
*** EXIT Backup of database with missing view dependency
=== 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-08-05 08:04:30 +0000
+++ b/mysql-test/t/backup_views.test 2008-08-07 15:43:25 +0000
@@ -25,138 +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;
-
---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
-
-INSERT INTO t5 VALUES
-('F',23),('F',24),('M',19),('F',28),('M',43),('F',30),('M',31),('M',27);
+(234, 'zuloa',1),(321,'yyy',2),(765,'iug',3),
+(124,'LKJ',4),(235,'uth',6);
-SELECT * FROM t5;
+SELECT * FROM bup_db1.t3 ORDER BY scode;
---echo *****Create views from the table t1 of bup_db1*******
+--echo *****Create view from the table bup_db1.t1*******
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW bup_db1.v1 AS SELECT * FROM bup_db1.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;
-
-USE bup_db1;
-
---echo *******Creating View from database bup_db2**********
-
-CREATE VIEW v5 AS SELECT * FROM bup_db2.t2;
-
---echo ******Creating View v6********
-
-CREATE VIEW v6 AS SELECT education,gender FROM bup_db2.v2, t5 WHERE cand_age=age;
+RENAME TABLE bup_db2.v4 to bup_db2.student_details;
-#Excercise the objects of bup_db1
-
-USE bup_db1;
+--echo *******Create view from database bup_db2**********
-SELECT * FROM t1;
+CREATE VIEW bup_db1.v5 AS SELECT * FROM bup_db2.t2;
-SELECT * FROM t3;
+--echo Creating Table t5
-SELECT * FROM t5;
+CREATE TABLE bup_db1.t5(
+Gender CHAR(5),
+cand_age INT,
+FOREIGN KEY(cand_age) REFERENCES bup_db2.t2(age)
+)ENGINE=INNODB;
-SELECT * FROM v1;
+--echo Loading data into table t5
-SELECT * FROM vcomb;
+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;
-SELECT * FROM v5;
+#Excercise the objects of bup_db1
-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 education, 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
@@ -168,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';
@@ -179,17 +190,23 @@ 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
+# 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.
-#Individual databases cannot be restored because of VIEW DEPENDENCY
--echo restore database with view dependency to other, non-existing db
--error ER_NO_SUCH_TABLE
-RESTORE FROM 'bup_objectview1.bak';
+RESTORE FROM 'bup_objectview1.bak';
# An incomplete bup_db1 was created by the failing restore operation.
# Remove it before trying restore of bup_db2.
@@ -217,87 +234,57 @@ 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 education, 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;
-###############
---echo
---echo *** ENTER Backup of database with missing view dependency should fail but not crash server
---echo
+--echo
+--echo *** ENTER Backup of database with missing view dependency
+--echo *** should fail but not crash server
+--echo *** Test for bug#34902 ***
--echo initializing test
# start with the backed up database
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
DROP DATABASE bup_db1;
DROP DATABASE bup_db2;
@@ -305,19 +292,21 @@ replace_column 1 #;
RESTORE FROM 'bup_objectview.bak';
# check that table t1 and v1 are initially correct
-USE bup_db1;
-SELECT * FROM t1;
-SELECT * FROM v1;
+SELECT * FROM bup_db1.t1;
+SELECT * FROM bup_db1.v1;
-DROP TABLE t1;
+DROP TABLE bup_db1.t3;
+DROP TABLE bup_db1.t5;
+DROP TABLE bup_db2.t2;
+DROP TABLE bup_db1.t1;
--echo
--echo Testing backup with missing view dependency in same db
---echo
+--echo
# v1 selects from t1, and select now reports error
--error ER_VIEW_INVALID
-SELECT * FROM v1;
+SELECT * FROM bup_db1.v1;
# try to backup - v1 selects from t1 and backup should now fail
--error ER_BACKUP_CATALOG_ADD_VIEW
@@ -325,21 +314,18 @@ BACKUP DATABASE bup_db1 TO 'bup_shouldfa
--echo
--echo Testing backup with missing view dependency in other db
---echo
+--echo
USE bup_db2;
--error ER_VIEW_INVALID
-SELECT * from v3;
-
+SELECT * from bup_db2.v3;
# try to backup - v3 selects from bup_db1.t1 and backup should now fail
--error ER_BACKUP_CATALOG_ADD_VIEW
BACKUP DATABASE bup_db2 TO 'bup_shouldfail2.bak';
---echo
+--echo
--echo *** EXIT Backup of database with missing view dependency
---echo
-
-###############
+--echo
# Test cleanup section
@@ -347,21 +333,16 @@ BACKUP DATABASE bup_db2 TO 'bup_shouldfa
--echo *** DROP bup_db1, bup_db2 DATABASE ****
--echo
-DROP DATABASE bup_db1;
+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
-
--error 0,1
--remove_file $MYSQLTEST_VARDIR/master-data/bup_shouldfail1.bak
--error 0,1
--remove_file $MYSQLTEST_VARDIR/master-data/bup_shouldfail2.bak
-#BUG#35249 Mysql server crash for delete operation followed by backup for Default Drivers.
| Thread |
|---|
| • bzr push into mysql-6.0-backup branch (hema:2676 to 2677) | Hema Sridharan | 7 Aug |
