#At file:///export/home2/tmp/wl4225/mysql-6.0-backup/ based on revid:rafal.somla@stripped
2772 Hema Sridharan 2009-02-17
WL#4225(Test objects dependency and consistency).
New test created for this WL.
added:
mysql-test/suite/backup/include/objects_dependency.inc
mysql-test/suite/backup/include/objects_dependency_use.inc
mysql-test/suite/backup/r/backup_objects_dependency.result
mysql-test/suite/backup/t/backup_objects_dependency.test
per-file messages:
mysql-test/suite/backup/include/objects_dependency.inc
This include file will be called by main test whenever objects consistency needs to be verified.
mysql-test/suite/backup/include/objects_dependency_use.inc
This include file is created to test use dependency objects.
Main test will refer to file for testing use dependencies.
mysql-test/suite/backup/r/backup_objects_dependency.result
Result file for backup_objects_dependency.test
mysql-test/suite/backup/t/backup_objects_dependency.test
Test file for backup_objects_dependency
=== added file 'mysql-test/suite/backup/include/objects_dependency.inc'
--- a/mysql-test/suite/backup/include/objects_dependency.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/include/objects_dependency.inc 2009-02-17 20:51:15 +0000
@@ -0,0 +1,16 @@
+#
+# This include file is created in order to verify the consistency of objects.
+# The main test will refer to this file whenever consistency of objects
+# needs to be checked.
+#
+# Note: This is created in order to not complicate the
+# backup_objects_dependency.test by repeating the same steps again and again.
+
+SHOW DATABASES;
+SHOW FULL TABLES FROM ob1;
+SHOW FULL TABLES FROM ob2;
+SHOW FULL TABLES FROM ob3;
+
+CALL test.objects_ob1;
+CALL test.objects_ob2;
+CALL test.objects_ob3;
=== added file 'mysql-test/suite/backup/include/objects_dependency_use.inc'
--- a/mysql-test/suite/backup/include/objects_dependency_use.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/include/objects_dependency_use.inc 2009-02-17 20:51:15 +0000
@@ -0,0 +1,81 @@
+#
+# This include file is created to use the missing use dependencies. The main
+# test will refer to this file for testing use dependencies scenarios.
+#
+
+eval DROP $drop_object;
+--replace_column 1 #
+eval BACKUP DATABASE $db TO 'ob_missing.bak';
+--replace_column 1 #
+eval RESTORE FROM 'ob_missing.bak' OVERWRITE;
+
+#Exercise objects to verify if missing dependencies
+
+# This condition will be used when a procedure or function is dropped.
+if(`SELECT '$object'='procedure'`)
+{
+--error ER_SP_DOES_NOT_EXIST
+eval CALL $object_proc;
+}
+
+# Table is dropped and stored function depends on this table
+if(`SELECT '$object'='function'`)
+{
+--error ER_NO_SUCH_TABLE
+eval SELECT $object_fun;
+}
+
+# This condition is used when table is dropped in which procedure depends.
+if(`SELECT '$object'='table'`)
+{
+--error ER_NO_SUCH_TABLE
+eval CALL $object_proc;
+}
+
+# This condition is used when procedure drops table
+if(`SELECT '$object'='table1'`)
+{
+eval CALL $object_proc;
+SHOW WARNINGS;
+}
+
+# Table is dropped when trigger depends on it
+if(`SELECT '$object'='trigger1'`)
+{
+--error ER_NO_SUCH_TABLE
+eval INSERT INTO $object_trg VALUES(200);
+}
+
+# This condition is used when procedure is dropped in which trigger depends
+if(`SELECT '$object'='trigger2'`)
+{
+--error ER_SP_DOES_NOT_EXIST
+eval INSERT INTO $object_trg VALUES(200);
+}
+
+# Trigger is dropped when another trigger depends on it.
+if(`SELECT '$object'='trigger3'`)
+{
+--error ER_TRG_DOES_NOT_EXIST
+SHOW CREATE TRIGGER ob2.trg12;
+--echo Notice that ob2.trg12 will not exist.
+}
+
+# This condition is used when table is dropped when event depends on it.
+if(`SELECT '$object'='event'`)
+{
+DROP EVENT ob3.e4;
+CREATE EVENT ob3.e4 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE
+DO DELETE FROM ob1.t1 WHERE id=12;
+--echo Note there will be no effect in event even if table does not exist
+}
+
+--echo Perform restore again to get all objects back.
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+
+#verify the status of all objects:
+--source suite/backup/include/objects_dependency.inc
+
+#clean-up section
+--remove_file $bdir/ob_missing.bak
=== added file 'mysql-test/suite/backup/r/backup_objects_dependency.result'
--- a/mysql-test/suite/backup/r/backup_objects_dependency.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_objects_dependency.result 2009-02-17 20:51:15 +0000
@@ -0,0 +1,2028 @@
+**START TEST**
+**** TEST1 ****
+CREATE DATABASE IF NOT EXISTS ob1;
+CREATE DATABASE IF NOT EXISTS ob2;
+CREATE DATABASE IF NOT EXISTS ob3;
+CREATE USER 'tom'@'%';
+GRANT ALL ON *.* TO tom@'%';
+** create tables in 'ob1' database **
+
+CREATE TABLE ob1.t1(
+id INT,
+name CHAR(20),
+pay FLOAT(7,3)
+);
+INSERT INTO ob1.t1 VALUES
+(10, 'ab1', 2200.23),(12, 'ab2',2190.34),(14,'ab3',3123.45),
+(15,'ab4',1236.89),(18,'ab5',3890.78),(20,'ab6',1300);
+SELECT * FROM ob1.t1;
+id name pay
+10 ab1 2200.230
+12 ab2 2190.340
+14 ab3 3123.450
+15 ab4 1236.890
+18 ab5 3890.780
+20 ab6 1300.000
+CREATE TABLE ob1.t2(
+scode TINYINT,
+empcode CHAR(10),
+join_date DATE
+);
+INSERT INTO ob1.t2 VALUES
+(10, 'E120','1997-10-09'),(12,'E145','1999-01-20'),(14,'C134','2001-09-08'),
+(15, 'C156','2005-09-11'),(18,'E250','2007-08-06');
+SELECT * FROM ob1.t2;
+scode empcode join_date
+10 E120 1997-10-09
+12 E145 1999-01-20
+14 C134 2001-09-08
+15 C156 2005-09-11
+18 E250 2007-08-06
+CREATE TABLE ob1.t3(details VARCHAR(40));
+CREATE TABLE ob1.t4(payment_type VARCHAR(30), salary MEDIUMINT);
+INSERT INTO ob1.t4 VALUES('cash',4000),('cheque',5000);
+SELECT * FROM ob1.t4;
+payment_type salary
+cash 4000
+cheque 5000
+** Create tables in 'ob2' database **
+
+CREATE TABLE ob2.t11(
+ecode CHAR(20),
+company VARCHAR(20),
+identity CHAR(20),
+designation ENUM('Engineer','support','sales','lead')
+);
+INSERT INTO ob2.t11 VALUES
+('E250','Mysql','ab2','Engineer'),('E120','SUN','ab3','Sales'),
+('E145','Nokia','ab4','Support'),('C134','Veritas','ab5','sales');
+SELECT * FROM ob2.t11;
+ecode company identity designation
+E250 Mysql ab2 Engineer
+E120 SUN ab3 sales
+E145 Nokia ab4 support
+C134 Veritas ab5 sales
+CREATE TABLE ob2.t12(
+empcode CHAR(10),
+bonus DECIMAL(5,2),
+rating CHAR(20)
+);
+INSERT INTO ob2.t12 VALUES
+('E250','10.12','good'),('E120','7.23','average'),('E145','12','outstanding');
+SELECT * FROM ob2.t12;
+empcode bonus rating
+E250 10.12 good
+E120 7.23 average
+E145 12.00 outstanding
+CREATE TABLE ob2.t13 AS SELECT 1 A UNION SELECT 2 UNION SELECT 3;
+CREATE TABLE ob2.t14 AS SELECT * FROM ob2.t13;
+SELECT * FROM ob2.t13 ORDER BY A;
+A
+1
+2
+3
+SELECT * FROM ob2.t14;
+A
+1
+2
+3
+** Create tables in 'ob3' database
+
+CREATE TABLE ob3.t21(
+ccode TINYINT,
+gcode INT,
+company_name CHAR(20),
+company_stocks SMALLINT
+);
+INSERT INTO ob3.t21 VALUES
+(10, 102,'Nokia',2000),(12, 122,'Veritas',1500),(15,152,'Mysql',3000);
+CREATE TABLE ob3.t23(id INT);
+
+------------------------------------------------------------
+CREATE VIEWS, TRIGGERS, EVENTS, PROCEDURES AND FUNCTIONS
+------------------------------------------------------------
+
+** creating views in 'ob1' database **
+
+### Create views from 2 tables(same DB) ###
+CREATE DEFINER=tom@'%' VIEW ob1.v1 AS SELECT id, name, empcode, join_date
+FROM ob1.t1, ob1.t2 WHERE id=scode;
+SELECT * FROM ob1.v1;
+id name empcode join_date
+10 ab1 E120 1997-10-09
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2007-08-06
+
+### Create views from 2 tables (same/different DB) ###
+CREATE VIEW ob1.v2 AS SELECT ecode, name, company, designation
+FROM ob1.t1, ob2.t11 WHERE name=identity;
+SELECT * FROM ob1.v2;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+
+### Create views from 2 tables (diff DB) ###
+CREATE VIEW ob1.v3 AS SELECT identity, bonus, rating
+FROM ob2.t11, ob2.t12 WHERE ecode=empcode;
+SELECT * FROM ob1.v3;
+identity bonus rating
+ab2 10.12 good
+ab3 7.23 average
+ab4 12.00 outstanding
+
+### Create view based on view(same DB) ###
+CREATE VIEW ob1.vv1 AS SELECT * FROM ob1.v2;
+SELECT * FROM ob1.vv1;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+
+### Create view from view, table (same DB) ###
+CREATE VIEW ob1.vv2 AS SELECT name, empcode, join_date
+FROM ob1.v2, ob1.t2 WHERE empcode=ecode;
+SELECT * FROM ob1.vv2;
+name empcode join_date
+ab2 E250 2007-08-06
+ab3 E120 1997-10-09
+ab4 E145 1999-01-20
+ab5 C134 2001-09-08
+
+### Create view from 2 tables (diff DB) ###
+CREATE VIEW ob1.v4 AS SELECT ecode,ccode,gcode
+FROM ob2.t11, ob3.t21 WHERE company=company_name;
+
+### Create view from table (same DB) ###
+CREATE VIEW ob1.v5 AS SELECT SUM(pay), AVG(pay),MAX(pay),MIN(pay)
+FROM ob1.t1;
+SELECT * FROM ob1.v5;
+SUM(pay) AVG(pay) MAX(pay) MIN(pay)
+13941.690 2323.6150106 3890.780 1236.890
+
+### Create view on tables (diff DB)###
+CREATE VIEW ob1.v6 AS SELECT CONCAT(name," ",empcode) AS emp_details
+FROM ob1.t1, ob1.t2 WHERE id=scode;
+SELECT * FROM ob1.v6;
+emp_details
+ab1 E120
+ab2 E145
+ab3 C134
+ab4 C156
+ab5 E250
+
+** creating views in 'ob2' database **
+
+### Create view from views (diff DB) ###
+CREATE VIEW ob2.vv11 AS SELECT designation, bonus
+FROM ob1.v2, ob1.v3 WHERE name=identity;
+SELECT * FROM ob2.vv11;
+designation bonus
+Engineer 10.12
+sales 7.23
+support 12.00
+
+### Create view from view (same DB) ###
+CREATE VIEW ob2.vv12 AS SELECT * FROM ob2.vv11;
+SELECT * FROM ob2.vv12;
+designation bonus
+Engineer 10.12
+sales 7.23
+support 12.00
+### Create view from tables (diff DB) ###
+CREATE VIEW ob2.v14 AS SELECT id, designation FROM ob1.t1
+JOIN ob2.t11 ON name=identity WHERE id>15;
+SELECT * FROM ob2.v14;
+id designation
+18 sales
+### Create view from table (diff DB) ###
+CREATE VIEW ob2.v15 AS SELECT empcode FROM ob2.t12 WHERE EXISTS
+(SELECT empcode FROM ob1.t2);
+SELECT * FROM ob2.v15;
+empcode
+E250
+E120
+E145
+
+### Create a simple procedure ###
+CREATE DEFINER=tom@'%' PROCEDURE ob2.p13(x INT, OUT y INT)
+BEGIN
+DECLARE z INT;
+SET z=x+1, y=z;
+END||
+
+### Trigger calls procedure ###
+CREATE TRIGGER ob2.trg11 AFTER INSERT ON ob2.t13 FOR EACH ROW
+BEGIN
+INSERT INTO ob1.t3 VALUES('TRIGGER FIRED FROM trg11');
+INSERT INTO ob1.t4 VALUES('dd',2000);
+CALL ob2.p13(10, @ob2.p13);
+END;||
+
+### Trigger will in turn trigger trg12 ###
+CREATE TRIGGER ob1.trg1 BEFORE INSERT ON ob1.t1 FOR EACH ROW
+BEGIN
+DELETE FROM ob2.t13 WHERE a < 2;
+END;||
+
+### Trigger on function and trigger ###
+CREATE TRIGGER ob2.trg12 AFTER DELETE ON ob2.t13 FOR EACH ROW
+BEGIN
+INSERT INTO ob1.t3 VALUES('TRIGGER FIRED FROM trg12');
+INSERT INTO ob3.t23 SELECT ob2.f11();
+END;||
+
+### Trigger trg2 will inturn trigger trg1 ###
+CREATE TRIGGER ob1.trg2 AFTER UPDATE ON ob1.t2 FOR EACH ROW
+BEGIN
+INSERT INTO ob1.t1 VALUES(22,'ab7','4123.23');
+END;
+||
+
+### Trigger trgu depends on user ###
+CREATE DEFINER=tom@'%' TRIGGER ob3.trgu AFTER INSERT ON ob3.t23 FOR EACH ROW
+BEGIN
+UPDATE ob1.t4 SET salary=1000 WHERE payment_type='cash';
+END;
+||
+
+### Procedure will trigger trg2 ###
+CREATE PROCEDURE ob2.p12()
+BEGIN
+UPDATE ob1.t2 SET join_date='2008-08-08' WHERE empcode='E250';
+SELECT * FROM ob1.v5;
+END;
+||
+### Procedure depends on function ###
+CREATE PROCEDURE ob1.p1()
+BEGIN
+INSERT INTO ob2.t11 VALUES('E450', 'SUN','ab6','support');
+CREATE VIEW ob1.vp1 AS SELECT 100 AS NUMBER;
+SELECT ob1.f1();
+END;||
+### Function on table ###
+CREATE FUNCTION ob1.f1() RETURNS INT
+RETURN (SELECT SUM(salary) FROM ob1.t4)||
+### Create function with definer ###
+CREATE DEFINER=tom@'%' FUNCTION ob2.f11() RETURNS INTEGER
+BEGIN
+DECLARE retn INTEGER;
+SELECT NUMBER FROM ob1.vp1 INTO retn;
+RETURN retn;
+END;||
+
+## Procedure calling procedure p1 and p12, trigger and function(f11) ##
+CREATE PROCEDURE ob2.p11(srno INT)
+IF srno < 0 THEN
+DELETE FROM ob1.t1 WHERE name='ab6';
+CALL ob1.p1();
+ELSEIF srno=0 THEN
+INSERT INTO ob2.t13 VALUES(1),(20),(30);
+ELSE
+SELECT ob2.f11();
+CALL ob2.p12();
+END IF||
+
+### Create procedure that alters tables ###
+CREATE PROCEDURE ob1.p2()
+BEGIN
+ALTER TABLE ob2.t13 CONVERT TO CHARACTER SET latin7;
+ALTER TABLE ob3.tp CHANGE id a VARCHAR(4);
+END;||
+### Procedure that creates table and calls ob1.p2 ###
+CREATE PROCEDURE ob3.p21(a CHAR(20))
+BEGIN
+CREATE TABLE IF NOT EXISTS ob3.tp(id INT);
+CALL ob1.p2();
+END;||
+
+### Create procedure that drops table ###
+CREATE PROCEDURE ob3.p22()
+BEGIN
+DROP TABLE IF EXISTS ob3.tp;
+DROP VIEW ob1.vp1;
+END;||
+SET GLOBAL EVENT_SCHEDULER=ON;
+CREATE DEFINER=tom@'%' EVENT ob3.e1 ON SCHEDULE AT NOW()
+ON COMPLETION PRESERVE
+DO INSERT INTO ob1.t4 VALUES('cash',0);
+
+### Create an event that will fire trigger trg12 ###
+CREATE EVENT ob2.e3 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
+DO DELETE FROM ob2.t13 WHERE A=3;
+
+### Create an event using definer clause ###
+CREATE DEFINER=tom@'%' EVENT ob3.e4 ON SCHEDULE EVERY 1 SECOND ON COMPLETION
+PRESERVE DO UPDATE ob1.t1 SET id=100 WHERE name='ab1';
+### Create procedure to check objects in all databases ###
+CREATE PROCEDURE test.objects_ob1()
+BEGIN
+(SELECT routine_name , routine_type FROM information_schema.routines
+WHERE routine_schema = 'ob1' )
+UNION (SELECT event_name, 'EVENT' FROM information_schema.events
+WHERE event_schema = 'ob1' )
+UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
+WHERE trigger_schema = 'ob1') ORDER BY routine_name, routine_type;
+END;||
+CREATE PROCEDURE test.objects_ob2()
+BEGIN
+(SELECT routine_name , routine_type FROM information_schema.routines
+WHERE routine_schema = 'ob2')
+UNION (SELECT event_name, 'EVENT' FROM information_schema.events
+WHERE event_schema = 'ob2')
+UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
+WHERE trigger_schema = 'ob2') ORDER BY routine_name, routine_type;
+END;||
+CREATE PROCEDURE test.objects_ob3()
+BEGIN
+(SELECT routine_name , routine_type FROM information_schema.routines
+WHERE routine_schema = 'ob3')
+UNION (SELECT event_name, 'EVENT' FROM information_schema.events
+WHERE event_schema = 'ob3')
+UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
+WHERE trigger_schema = 'ob3') ORDER BY routine_name, routine_type;
+END;||
+
+Excercise procedure p11
+p11(srno<0) will call procedure p1 -->create vp1 and calls f1()
+Function f1 will show sum(salary) from t4
+CALL ob2.p11(-2);
+ob1.f1()
+9000
+SELECT * FROM ob1.vp1;
+NUMBER
+100
+SELECT * FROM ob1.t1;
+id name pay
+100 ab1 2200.230
+12 ab2 2190.340
+14 ab3 3123.450
+15 ab4 1236.890
+18 ab5 3890.780
+SELECT * FROM ob2.t11;
+ecode company identity designation
+E250 Mysql ab2 Engineer
+E120 SUN ab3 sales
+E145 Nokia ab4 support
+C134 Veritas ab5 sales
+E450 SUN ab6 support
+
+p11(srno=0) will trigger trg11(by inserting values in t13) which
+inturn will call another procedure p13
+CALL ob2.p11(0);
+
+t13 table will be added with 3 new values of 1, 20 and 30.
+
+SELECT * FROM ob2.t13 ORDER BY A;
+A
+1
+1
+2
+20
+30
+SELECT * FROM ob2.t14;
+A
+1
+2
+3
+Trigger trg11 will insert values in t3 and t4
+t3 and t4 will have 3 new values
+
+SELECT * FROM ob1.t3;
+details
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+SELECT * FROM ob1.t4;
+payment_type salary
+cash 4000
+cheque 5000
+cash 0
+dd 2000
+dd 2000
+dd 2000
+Function f1 will show sum(salary) from ob1.t4
+SELECT ob1.f1();
+ob1.f1()
+15000
+select ob1@p13 should show 11
+SELECT @ob2.p13;
+@stripped
+11
+
+p11(srno > 0) will call function f11 and p12
+Procedure p12 will call trigger trg2 (by updating table t2)
+Trigger trg2 will also trigger trg1 by inserting values in t1
+Trigger trg1 in turn will trigger trg12 by deleting values from t13
+
+CALL ob2.p11(10);
+ob2.f11()
+100
+SUM(pay) AVG(pay) MAX(pay) MIN(pay)
+16764.920 2794.1533407 4123.230 1236.890
+p12 will update table t2. The join_date column is updated for id=18
+we will check all the dependencies of table t2.
+
+SELECT * FROM ob1.t2;
+scode empcode join_date
+10 E120 1997-10-09
+12 E145 1999-01-20
+14 C134 2001-09-08
+15 C156 2005-09-11
+18 E250 2008-08-08
+SELECT * FROM ob1.v1;
+id name empcode join_date
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2008-08-08
+SELECT * FROM ob1.vv2;
+name empcode join_date
+ab2 E250 2008-08-08
+ab3 E120 1997-10-09
+ab4 E145 1999-01-20
+ab5 C134 2001-09-08
+SELECT * FROM ob1.v6;
+emp_details
+ab2 E145
+ab3 C134
+ab4 C156
+ab5 E250
+SELECT * FROM ob2.v15;
+empcode
+E250
+E120
+E145
+
+Update of table t2 will trigger trg2
+Table t1 will include additional row with id=22
+SELECT * FROM ob1.t1;
+id name pay
+100 ab1 2200.230
+12 ab2 2190.340
+14 ab3 3123.450
+15 ab4 1236.890
+18 ab5 3890.780
+22 ab7 4123.230
+SELECT * FROM ob1.v1;
+id name empcode join_date
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2008-08-08
+SELECT * FROM ob1.v2;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+SELECT * FROM ob1.vv2;
+name empcode join_date
+ab2 E250 2008-08-08
+ab3 E120 1997-10-09
+ab4 E145 1999-01-20
+ab5 C134 2001-09-08
+SELECT * FROM ob1.vv1;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+SELECT * FROM ob2.v14;
+id designation
+18 sales
+Inserting values in t1 will trigger trg1
+Values < 2 will be deleted from table t13.
+
+SELECT * FROM ob2.t13 ORDER BY A;
+A
+2
+20
+30
+SELECT * FROM ob2.t14;
+A
+1
+2
+3
+
+Deletion of data t13 will trigger trg12
+SELECT * FROM ob1.t3;
+details
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg12
+SELECT * FROM ob1.v6;
+emp_details
+ab2 E145
+ab3 C134
+ab4 C156
+ab5 E250
+SELECT * FROM ob3.t23;
+id
+100
+100
+SELECT * FROM ob3.t21;
+ccode gcode company_name company_stocks
+10 102 Nokia 2000
+12 122 Veritas 1500
+15 152 Mysql 3000
+Verify that Insertion in t23 triggers trgu
+SELECT * FROM ob1.t4 WHERE payment_type='cash';
+payment_type salary
+cash 1000
+cash 1000
+p21 will create table tp and calls procedure p2 which inturn
+alters tables tp and t13
+CALL ob3.p21('TEST');
+SHOW CREATE TABLE ob3.tp;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` varchar(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE ob2.t13;
+Table Create Table
+t13 CREATE TABLE `t13` (
+ `A` bigint(20) NOT NULL DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin7
+SHOW CREATE TABLE ob3.tp;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` varchar(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+checking tables and other objects in databases.
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+BACKUP DATABASE ob1, ob2, ob3 TO 'ob.bak';
+backup_id
+#
+BACKUP DATABASE ob1 TO 'ob1.bak';
+backup_id
+#
+BACKUP DATABASE ob2 TO 'ob2.bak';
+backup_id
+#
+BACKUP DATABASE ob3 TO 'ob3.bak';
+backup_id
+#
+dropping database.
+DROP DATABASE ob2;
+restore ob2 database and check all objects are included
+RESTORE FROM 'ob2.bak';
+backup_id
+#
+checking tables and other objects in databases are intact
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+Drop all databases
+DROP DATABASE ob2;
+DROP DATABASE ob1;
+DROP DATABASE ob3;
+Restore individual databases will fail because of view dependencies
+**** 1. RESTORE only ob1 database.****
+RESTORE FROM 'ob1.bak';
+ERROR HY000: Could not restore view `ob1`.`v2`. Please check the view definition for possible missing dependencies.
+**** 2. Restore only ob2 database ****
+RESTORE FROM 'ob2.bak';
+ERROR HY000: Could not restore view `ob2`.`vv11`. Please check the view definition for possible missing dependencies.
+Restore all databases together
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+Verify consistency of all objects and data after restore.
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+Excercise objects
+p22 will drop view vp1 and table tp
+CALL ob3.p22();
+Excercise procedure p11 that calls p1 and inturn selects from f1
+CALL ob2.p11(-4);
+ob1.f1()
+13000
+SELECT * FROM ob1.vp1;
+NUMBER
+100
+SELECT * FROM ob2.t11;
+ecode company identity designation
+E250 Mysql ab2 Engineer
+E120 SUN ab3 sales
+E145 Nokia ab4 support
+C134 Veritas ab5 sales
+E450 SUN ab6 support
+E450 SUN ab6 support
+CALL ob2.p11(0);
+
+t13 table will be added with 3 new values of 1, 20 and 30.
+
+SELECT * FROM ob2.t13 ORDER BY A;
+A
+1
+2
+20
+20
+30
+30
+SELECT * FROM ob2.t14;
+A
+1
+2
+3
+trg11 will be triggered by insertion of values in t13
+SELECT * FROM ob1.t3;
+details
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+SELECT * FROM ob1.t4;
+payment_type salary
+cash 1000
+cheque 5000
+cash 1000
+dd 2000
+dd 2000
+dd 2000
+dd 2000
+dd 2000
+dd 2000
+SELECT ob1.f1();
+ob1.f1()
+19000
+select ob1@p13 should show 11
+SELECT @ob2.p13;
+@stripped
+11
+
+p11(srno > 0) will call function f11 and p12
+Procedure p12 will call trigger trg2
+Trigger trg2 will also trigger trg1 which in turn will trigger trg12
+
+CALL ob2.p11(10);
+ob2.f11()
+100
+SUM(pay) AVG(pay) MAX(pay) MIN(pay)
+20888.150 2984.0214321 4123.230 1236.890
+p12 will update table t2. The join_date column is updated for id=18
+we will check all the dependencies of table t2.
+
+SELECT * FROM ob1.t2;
+scode empcode join_date
+10 E120 1997-10-09
+12 E145 1999-01-20
+14 C134 2001-09-08
+15 C156 2005-09-11
+18 E250 2008-08-08
+SELECT * FROM ob1.v1;
+id name empcode join_date
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2008-08-08
+SELECT * FROM ob1.vv2;
+name empcode join_date
+ab2 E250 2008-08-08
+ab3 E120 1997-10-09
+ab4 E145 1999-01-20
+ab5 C134 2001-09-08
+SELECT * FROM ob1.v6;
+emp_details
+ab2 E145
+ab3 C134
+ab4 C156
+ab5 E250
+
+When trigger trg2 is fired, t1 will include additional row with id=22
+SELECT * FROM ob1.t1;
+id name pay
+100 ab1 2200.230
+12 ab2 2190.340
+14 ab3 3123.450
+15 ab4 1236.890
+18 ab5 3890.780
+22 ab7 4123.230
+22 ab7 4123.230
+SELECT * FROM ob1.v1;
+id name empcode join_date
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2008-08-08
+SELECT * FROM ob1.v2;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+SELECT * FROM ob1.vv2;
+name empcode join_date
+ab2 E250 2008-08-08
+ab3 E120 1997-10-09
+ab4 E145 1999-01-20
+ab5 C134 2001-09-08
+SELECT * FROM ob1.vv1;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+SELECT * FROM ob2.v14;
+id designation
+18 sales
+Inserting values in t1 will trigger trg1
+Values < 2 will be deleted from table t13.
+
+SELECT * FROM ob2.t13 ORDER BY A;
+A
+2
+20
+20
+30
+30
+
+Deletion of data t13 will trigger trg12
+SELECT * FROM ob1.t3;
+details
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg12
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg11
+TRIGGER FIRED FROM trg12
+SELECT * FROM ob3.t23;
+id
+100
+100
+100
+SELECT * FROM ob1.v6;
+emp_details
+ab2 E145
+ab3 C134
+ab4 C156
+ab5 E250
+SELECT * FROM ob3.t21;
+ccode gcode company_name company_stocks
+10 102 Nokia 2000
+12 122 Veritas 1500
+15 152 Mysql 3000
+SELECT * FROM ob1.v4;
+ecode ccode gcode
+E250 15 152
+E145 10 102
+C134 12 122
+p21 will create table tp and calls procedure p2 which inturn
+alters tables.
+CALL ob3.p21('TEST');
+SHOW CREATE TABLE ob3.tp;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` varchar(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE ob2.t13;
+Table Create Table
+t13 CREATE TABLE `t13` (
+ `A` bigint(20) NOT NULL DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin7
+SHOW CREATE TABLE ob3.tp;
+Table Create Table
+tp CREATE TABLE `tp` (
+ `a` varchar(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE DEPENDENT TESTS
+======================
+
+# Drop the base table(ob1.t1) in which a view(ob1.v1) is dependent on.
+DROP TABLE ob1.t1;
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+ERROR HY000: Failed to add view `ob1`.`v1` to the catalog
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SELECT * FROM ob1.t1;
+id name pay
+100 ab1 2200.230
+12 ab2 2190.340
+14 ab3 3123.450
+15 ab4 1236.890
+18 ab5 3890.780
+22 ab7 4123.230
+SELECT * FROM ob1.v1;
+id name empcode join_date
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2008-08-08
+DROP TABLE ob2.t11;
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+ERROR HY000: Failed to add view `ob1`.`v2` to the catalog
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SELECT * FROM ob1.v3;
+identity bonus rating
+ab2 10.12 good
+ab3 7.23 average
+ab4 12.00 outstanding
+SELECT * FROM ob2.t11;
+ecode company identity designation
+E250 Mysql ab2 Engineer
+E120 SUN ab3 sales
+E145 Nokia ab4 support
+C134 Veritas ab5 sales
+E450 SUN ab6 support
+
+# Drop view(ob1.v2), table(ob1.t2) in which a view(vv2) depends.
+DROP TABLE ob1.t2;
+DROP VIEW ob1.v2;
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+ERROR HY000: Failed to add view `ob1`.`v1` to the catalog
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SELECT * FROM ob1.v2;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+SELECT * FROM ob1.t2;
+scode empcode join_date
+10 E120 1997-10-09
+12 E145 1999-01-20
+14 C134 2001-09-08
+15 C156 2005-09-11
+18 E250 2008-08-08
+SELECT * FROM ob1.vv2;
+name empcode join_date
+ab2 E250 2008-08-08
+ab3 E120 1997-10-09
+ab4 E145 1999-01-20
+ab5 C134 2001-09-08
+
+# Drop view(ob1.v2) in which a view(vv11) depends.
+DROP VIEW ob1.v2;
+BACKUP DATABASE ob2 TO 'ob2_missing.bak';
+ERROR HY000: Failed to add view `ob2`.`vv11` to the catalog
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SELECT * FROM ob1.v2;
+ecode name company designation
+E250 ab2 Mysql Engineer
+E120 ab3 SUN sales
+E145 ab4 Nokia support
+C134 ab5 Veritas sales
+SELECT * FROM ob2.vv11;
+designation bonus
+Engineer 10.12
+sales 7.23
+support 12.00
+
+# Drop user(tom) in which view(ob1.v1) depends.
+DROP USER tom@'%';
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+ERROR HY000: Failed to add view `ob1`.`v1` to the catalog
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+Warnings:
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SELECT * FROM ob1.v1;
+ERROR HY000: The user specified as a definer ('tom'@'%') does not exist
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+SELECT * FROM ob1.v1;
+id name empcode join_date
+12 ab2 E145 1999-01-20
+14 ab3 C134 2001-09-08
+15 ab4 C156 2005-09-11
+18 ab5 E250 2008-08-08
+
+# Drop user in which procedure(ob2.p13) depends.
+DROP USER tom@'%';
+BACKUP DATABASE ob2 TO 'ob2_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob2_missing.bak' OVERWRITE;
+backup_id
+#
+Warnings:
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+SHOW WARNINGS;
+Level Code Message
+Note # The user specified as a definer ('tom'@'%') does not exist
+Note # The user specified as a definer ('tom'@'%') does not exist
+SHOW CREATE PROCEDURE ob2.p13;
+Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
+p13 CREATE DEFINER=`tom`@`%` PROCEDURE `p13`(x INT, OUT y INT)
+BEGIN
+DECLARE z INT;
+SET z=x+1, y=z;
+END latin1 latin1_swedish_ci latin1_swedish_ci
+CALL ob2.p13(20, @ob2.p13);
+ERROR HY000: The user specified as a definer ('tom'@'%') does not exist
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+
+# Drop user in which function(ob2.f11) depends.
+DROP USER tom@'%';
+BACKUP DATABASE ob2 TO 'ob2_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob2_missing.bak' OVERWRITE;
+backup_id
+#
+Warnings:
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+SHOW WARNINGS;
+Level Code Message
+Note # The user specified as a definer ('tom'@'%') does not exist
+Note # The user specified as a definer ('tom'@'%') does not exist
+SHOW CREATE FUNCTION ob2.f11;
+Function sql_mode Create Function character_set_client collation_connection Database Collation
+f11 CREATE DEFINER=`tom`@`%` FUNCTION `f11`() RETURNS int(11)
+BEGIN
+DECLARE retn INTEGER;
+SELECT NUMBER FROM ob1.vp1 INTO retn;
+RETURN retn;
+END latin1 latin1_swedish_ci latin1_swedish_ci
+SELECT ob2.f11();
+ERROR HY000: The user specified as a definer ('tom'@'%') does not exist
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+
+# Drop user in which trigger(trgu) depends on.
+DROP USER tom@'%';
+BACKUP DATABASE ob3 TO 'ob3_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob3_missing.bak' OVERWRITE;
+backup_id
+#
+Warnings:
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+SHOW CREATE TRIGGER ob3.trgu;
+Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation
+trgu CREATE DEFINER=`tom`@`%` TRIGGER ob3.trgu AFTER INSERT ON ob3.t23 FOR EACH ROW
+BEGIN
+UPDATE ob1.t4 SET salary=1000 WHERE payment_type='cash';
+END latin1 latin1_swedish_ci latin1_swedish_ci
+Fire trigger trgu
+INSERT INTO ob3.t23 VALUES(98);
+ERROR HY000: The user specified as a definer ('tom'@'%') does not exist
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+INSERT INTO ob3.t23 VALUES(97);
+Note that we will see both values 97 and 98. trgu will not update value
+in t4 if user is not present.
+
+# Drop user in which event(e4) depends
+DROP USER tom@'%';
+BACKUP DATABASE ob3 TO 'ob3_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob3_missing.bak' OVERWRITE;
+backup_id
+#
+Warnings:
+# 1449 The user specified as a definer ('tom'@'%') does not exist
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+
+# Drop table(ob3.t23) in which trigger(ob3.trgu) depends
+DROP TABLE ob3.t23;
+BACKUP DATABASE ob3 TO 'ob3_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob3_missing.bak' OVERWRITE;
+backup_id
+#
+SHOW CREATE TRIGGER ob3.trgu;
+ERROR HY000: Trigger does not exist
+Note that trigger will not exist.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW CREATE TRIGGER ob3.trgu;
+Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation
+trgu CREATE DEFINER=`tom`@`%` TRIGGER ob3.trgu AFTER INSERT ON ob3.t23 FOR EACH ROW
+BEGIN
+UPDATE ob1.t4 SET salary=1000 WHERE payment_type='cash';
+END latin1 latin1_swedish_ci latin1_swedish_ci
+
+USE DEPENDENT TESTS
+===================
+
+# Drop the procedure(ob1.p1) in which procedure(ob2.p11) depends
+DROP PROCEDURE ob1.p1;
+BACKUP DATABASE ob2 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+CALL ob2.p11(-1);
+ERROR 42000: PROCEDURE ob1.p1 does not exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+
+# Drop the table(ob2.t13) in which a procedure(ob2.p11) depends
+NOTE: This is also test of procedure firing trigger.
+DROP TABLE ob2.t13;
+BACKUP DATABASE ob2 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+CALL ob2.p11(0);
+ERROR 42S02: Table 'ob2.t13' doesn't exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Procedure that alters table(ob1.p2 alter ob2.t13).
+DROP TABLE ob2.t13;
+BACKUP DATABASE ob1 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+CALL ob1.p2();
+ERROR 42S02: Table 'ob2.t13' doesn't exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Procedure that drops table(ob3.p22 drops ob3.tp).
+DROP TABLE ob3.tp;
+BACKUP DATABASE ob3 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+CALL ob3.p22();
+SHOW WARNINGS;
+Level Code Message
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Function that selects from table(ob1.f1 on ob1.t4)
+DROP TABLE ob1.t4;
+BACKUP DATABASE ob1 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+SELECT ob1.f1();
+ERROR 42S02: Table 'ob1.t4' doesn't exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Trigger that depends on table.(ob3.trgu on ob1.t4)
+DROP TABLE ob1.t4;
+BACKUP DATABASE ob3 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+INSERT INTO ob3.t23 VALUES(200);
+ERROR 42S02: Table 'ob1.t4' doesn't exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Trigger that fires another trigger(ob1.trg1 on ob2.trg12)
+DROP TABLE ob2.t13;
+BACKUP DATABASE ob1 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+SHOW CREATE TRIGGER ob2.trg12;
+ERROR HY000: Trigger does not exist
+Notice that ob2.trg12 will not exist.
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Trigger that calls procedure(ob2.trg11 on ob2.p13)
+DROP PROCEDURE ob2.p13;
+BACKUP DATABASE ob2 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+INSERT INTO ob2.t13 VALUES(200);
+ERROR 42000: PROCEDURE ob2.p13 does not exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+
+# Procedure that selects values from stored function(ob1.p1 on ob1.f1)
+CALL ob3.p22();
+DROP FUNCTION ob1.f1;
+BACKUP DATABASE ob1 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+CALL ob1.p1();
+ERROR 42000: FUNCTION ob1.f1 does not exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+# Event(ob3.e4) that depends on table(ob1.t1)
+DROP TABLE ob1.t1;
+BACKUP DATABASE ob3 TO 'ob_missing.bak';
+backup_id
+#
+RESTORE FROM 'ob_missing.bak' OVERWRITE;
+backup_id
+#
+DROP EVENT ob3.e4;
+CREATE EVENT ob3.e4 ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE
+DO DELETE FROM ob1.t1 WHERE id=12;
+Note there will be no effect in event even if table does not exist
+Perform restore again to get all objects back.
+RESTORE FROM 'ob.bak' OVERWRITE;
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+ob1
+ob2
+ob3
+test
+SHOW FULL TABLES FROM ob1;
+Tables_in_ob1 Table_type
+t1 BASE TABLE
+t2 BASE TABLE
+t3 BASE TABLE
+t4 BASE TABLE
+v1 VIEW
+v2 VIEW
+v3 VIEW
+v4 VIEW
+v5 VIEW
+v6 VIEW
+vp1 VIEW
+vv1 VIEW
+vv2 VIEW
+SHOW FULL TABLES FROM ob2;
+Tables_in_ob2 Table_type
+t11 BASE TABLE
+t12 BASE TABLE
+t13 BASE TABLE
+t14 BASE TABLE
+v14 VIEW
+v15 VIEW
+vv11 VIEW
+vv12 VIEW
+SHOW FULL TABLES FROM ob3;
+Tables_in_ob3 Table_type
+t21 BASE TABLE
+t23 BASE TABLE
+tp BASE TABLE
+CALL test.objects_ob1;
+routine_name routine_type
+f1 FUNCTION
+p1 PROCEDURE
+p2 PROCEDURE
+trg1 TRIGGER
+trg2 TRIGGER
+CALL test.objects_ob2;
+routine_name routine_type
+e3 EVENT
+f11 FUNCTION
+p11 PROCEDURE
+p12 PROCEDURE
+p13 PROCEDURE
+trg11 TRIGGER
+trg12 TRIGGER
+CALL test.objects_ob3;
+routine_name routine_type
+e1 EVENT
+e4 EVENT
+p21 PROCEDURE
+p22 PROCEDURE
+trgu TRIGGER
+SHOW CREATE EVENT ob3.e4;;
+Event e4
+sql_mode
+time_zone SYSTEM
+Create Event #
+character_set_client #
+collation_connection #
+Database Collation latin1_swedish_ci
+REVOKE ALL ON *.* FROM tom@'%';
+DROP USER tom@'%';
+FLUSH PRIVILEGES;
+DROP DATABASE ob1;
+DROP DATABASE ob2;
+DROP DATABASE ob3;
+SET GLOBAL EVENT_SCHEDULER=OFF;
=== added file 'mysql-test/suite/backup/t/backup_objects_dependency.test'
--- a/mysql-test/suite/backup/t/backup_objects_dependency.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_objects_dependency.test 2009-02-17 20:51:15 +0000
@@ -0,0 +1,832 @@
+##########################################################################
+# Author: Hema
+# Date: 2009-02-05
+# Purpose: This test is to verify the objects dependency and consistency for
+# mysql backup and restore feature.
+###############################################################################
+
+# Mysql Backup currently supports following objects:
+# Databases, tables, views, stored procedures / functions, triggers
+# and events.
+# We will include all these objects and check their dependency between
+# each other. To check the consistency, we need to ensure that all data,
+# and objects are properly restored and are intact.
+# To get more details of tests, please refer to WL#4225
+
+# Suppress warning about expected backup/restore errors
+disable_query_log;
+call mtr.add_suppression("Backup:");
+call mtr.add_suppression("Restore:");
+enable_query_log;
+
+--source include/not_embedded.inc
+
+let $bdir=`SELECT @@backupdir`;
+--error 0,1
+--remove_file $bdir/ob.bak
+
+--echo **START TEST**
+--echo **** TEST1 ****
+
+# Create database
+
+CREATE DATABASE IF NOT EXISTS ob1;
+CREATE DATABASE IF NOT EXISTS ob2;
+CREATE DATABASE IF NOT EXISTS ob3;
+CREATE USER 'tom'@'%';
+
+# Grant privileges to user tom
+
+GRANT ALL ON *.* TO tom@'%';
+
+# Create tables
+
+--echo ** create tables in 'ob1' database **
+--echo
+
+CREATE TABLE ob1.t1(
+ id INT,
+ name CHAR(20),
+ pay FLOAT(7,3)
+);
+INSERT INTO ob1.t1 VALUES
+(10, 'ab1', 2200.23),(12, 'ab2',2190.34),(14,'ab3',3123.45),
+(15,'ab4',1236.89),(18,'ab5',3890.78),(20,'ab6',1300);
+SELECT * FROM ob1.t1;
+
+CREATE TABLE ob1.t2(
+ scode TINYINT,
+ empcode CHAR(10),
+ join_date DATE
+);
+INSERT INTO ob1.t2 VALUES
+(10, 'E120','1997-10-09'),(12,'E145','1999-01-20'),(14,'C134','2001-09-08'),
+(15, 'C156','2005-09-11'),(18,'E250','2007-08-06');
+SELECT * FROM ob1.t2;
+
+CREATE TABLE ob1.t3(details VARCHAR(40));
+CREATE TABLE ob1.t4(payment_type VARCHAR(30), salary MEDIUMINT);
+INSERT INTO ob1.t4 VALUES('cash',4000),('cheque',5000);
+SELECT * FROM ob1.t4;
+
+--echo ** Create tables in 'ob2' database **
+--echo
+
+CREATE TABLE ob2.t11(
+ ecode CHAR(20),
+ company VARCHAR(20),
+ identity CHAR(20),
+ designation ENUM('Engineer','support','sales','lead')
+ );
+INSERT INTO ob2.t11 VALUES
+('E250','Mysql','ab2','Engineer'),('E120','SUN','ab3','Sales'),
+('E145','Nokia','ab4','Support'),('C134','Veritas','ab5','sales');
+SELECT * FROM ob2.t11;
+
+CREATE TABLE ob2.t12(
+ empcode CHAR(10),
+ bonus DECIMAL(5,2),
+ rating CHAR(20)
+);
+INSERT INTO ob2.t12 VALUES
+('E250','10.12','good'),('E120','7.23','average'),('E145','12','outstanding');
+SELECT * FROM ob2.t12;
+
+CREATE TABLE ob2.t13 AS SELECT 1 A UNION SELECT 2 UNION SELECT 3;
+CREATE TABLE ob2.t14 AS SELECT * FROM ob2.t13;
+SELECT * FROM ob2.t13 ORDER BY A;
+SELECT * FROM ob2.t14;
+
+--echo ** Create tables in 'ob3' database
+--echo
+
+CREATE TABLE ob3.t21(
+ ccode TINYINT,
+ gcode INT,
+ company_name CHAR(20),
+ company_stocks SMALLINT
+);
+INSERT INTO ob3.t21 VALUES
+(10, 102,'Nokia',2000),(12, 122,'Veritas',1500),(15,152,'Mysql',3000);
+
+CREATE TABLE ob3.t23(id INT);
+
+--echo
+--echo ------------------------------------------------------------
+--echo CREATE VIEWS, TRIGGERS, EVENTS, PROCEDURES AND FUNCTIONS
+--echo ------------------------------------------------------------
+--echo
+
+--echo ** creating views in 'ob1' database **
+--echo
+
+--echo ### Create views from 2 tables(same DB) ###
+CREATE DEFINER=tom@'%' VIEW ob1.v1 AS SELECT id, name, empcode, join_date
+ FROM ob1.t1, ob1.t2 WHERE id=scode;
+SELECT * FROM ob1.v1;
+
+--echo
+--echo ### Create views from 2 tables (same/different DB) ###
+CREATE VIEW ob1.v2 AS SELECT ecode, name, company, designation
+ FROM ob1.t1, ob2.t11 WHERE name=identity;
+SELECT * FROM ob1.v2;
+
+--echo
+--echo ### Create views from 2 tables (diff DB) ###
+CREATE VIEW ob1.v3 AS SELECT identity, bonus, rating
+ FROM ob2.t11, ob2.t12 WHERE ecode=empcode;
+SELECT * FROM ob1.v3;
+
+--echo
+--echo ### Create view based on view(same DB) ###
+CREATE VIEW ob1.vv1 AS SELECT * FROM ob1.v2;
+SELECT * FROM ob1.vv1;
+
+--echo
+--echo ### Create view from view, table (same DB) ###
+CREATE VIEW ob1.vv2 AS SELECT name, empcode, join_date
+ FROM ob1.v2, ob1.t2 WHERE empcode=ecode;
+SELECT * FROM ob1.vv2;
+
+--echo
+--echo ### Create view from 2 tables (diff DB) ###
+CREATE VIEW ob1.v4 AS SELECT ecode,ccode,gcode
+ FROM ob2.t11, ob3.t21 WHERE company=company_name;
+
+--echo
+--echo ### Create view from table (same DB) ###
+CREATE VIEW ob1.v5 AS SELECT SUM(pay), AVG(pay),MAX(pay),MIN(pay)
+ FROM ob1.t1;
+SELECT * FROM ob1.v5;
+
+--echo
+--echo ### Create view on tables (diff DB)###
+CREATE VIEW ob1.v6 AS SELECT CONCAT(name," ",empcode) AS emp_details
+ FROM ob1.t1, ob1.t2 WHERE id=scode;
+SELECT * FROM ob1.v6;
+
+--echo
+--echo ** creating views in 'ob2' database **
+
+--echo
+--echo ### Create view from views (diff DB) ###
+CREATE VIEW ob2.vv11 AS SELECT designation, bonus
+ FROM ob1.v2, ob1.v3 WHERE name=identity;
+SELECT * FROM ob2.vv11;
+
+--echo
+--echo ### Create view from view (same DB) ###
+CREATE VIEW ob2.vv12 AS SELECT * FROM ob2.vv11;
+SELECT * FROM ob2.vv12;
+
+--echo ### Create view from tables (diff DB) ###
+CREATE VIEW ob2.v14 AS SELECT id, designation FROM ob1.t1
+ JOIN ob2.t11 ON name=identity WHERE id>15;
+SELECT * FROM ob2.v14;
+
+--echo ### Create view from table (diff DB) ###
+CREATE VIEW ob2.v15 AS SELECT empcode FROM ob2.t12 WHERE EXISTS
+(SELECT empcode FROM ob1.t2);
+SELECT * FROM ob2.v15;
+
+--echo
+--echo ### Create a simple procedure ###
+DELIMITER ||;
+CREATE DEFINER=tom@'%' PROCEDURE ob2.p13(x INT, OUT y INT)
+BEGIN
+ DECLARE z INT;
+ SET z=x+1, y=z;
+END||
+
+--echo
+--echo ### Trigger calls procedure ###
+CREATE TRIGGER ob2.trg11 AFTER INSERT ON ob2.t13 FOR EACH ROW
+BEGIN
+ INSERT INTO ob1.t3 VALUES('TRIGGER FIRED FROM trg11');
+ INSERT INTO ob1.t4 VALUES('dd',2000);
+ CALL ob2.p13(10, @ob2.p13);
+END;||
+
+--echo
+--echo ### Trigger will in turn trigger trg12 ###
+CREATE TRIGGER ob1.trg1 BEFORE INSERT ON ob1.t1 FOR EACH ROW
+BEGIN
+ DELETE FROM ob2.t13 WHERE a < 2;
+END;||
+
+--echo
+--echo ### Trigger on function and trigger ###
+CREATE TRIGGER ob2.trg12 AFTER DELETE ON ob2.t13 FOR EACH ROW
+BEGIN
+ INSERT INTO ob1.t3 VALUES('TRIGGER FIRED FROM trg12');
+ INSERT INTO ob3.t23 SELECT ob2.f11();
+END;||
+
+--echo
+--echo ### Trigger trg2 will inturn trigger trg1 ###
+CREATE TRIGGER ob1.trg2 AFTER UPDATE ON ob1.t2 FOR EACH ROW
+BEGIN
+ INSERT INTO ob1.t1 VALUES(22,'ab7','4123.23');
+END;
+||
+
+--echo
+--echo ### Trigger trgu depends on user ###
+CREATE DEFINER=tom@'%' TRIGGER ob3.trgu AFTER INSERT ON ob3.t23 FOR EACH ROW
+BEGIN
+ UPDATE ob1.t4 SET salary=1000 WHERE payment_type='cash';
+END;
+||
+
+--echo
+--echo ### Procedure will trigger trg2 ###
+CREATE PROCEDURE ob2.p12()
+BEGIN
+ UPDATE ob1.t2 SET join_date='2008-08-08' WHERE empcode='E250';
+ SELECT * FROM ob1.v5;
+END;
+||
+
+--echo ### Procedure depends on function ###
+CREATE PROCEDURE ob1.p1()
+BEGIN
+ INSERT INTO ob2.t11 VALUES('E450', 'SUN','ab6','support');
+ CREATE VIEW ob1.vp1 AS SELECT 100 AS NUMBER;
+ SELECT ob1.f1();
+END;||
+
+--echo ### Function on table ###
+CREATE FUNCTION ob1.f1() RETURNS INT
+ RETURN (SELECT SUM(salary) FROM ob1.t4)||
+
+--echo ### Create function with definer ###
+CREATE DEFINER=tom@'%' FUNCTION ob2.f11() RETURNS INTEGER
+BEGIN
+ DECLARE retn INTEGER;
+ SELECT NUMBER FROM ob1.vp1 INTO retn;
+ RETURN retn;
+END;||
+
+--echo
+--echo ## Procedure calling procedure p1 and p12, trigger and function(f11) ##
+CREATE PROCEDURE ob2.p11(srno INT)
+IF srno < 0 THEN
+ DELETE FROM ob1.t1 WHERE name='ab6';
+ CALL ob1.p1();
+ELSEIF srno=0 THEN
+ INSERT INTO ob2.t13 VALUES(1),(20),(30);
+ELSE
+ SELECT ob2.f11();
+ CALL ob2.p12();
+END IF||
+
+--echo
+--echo ### Create procedure that alters tables ###
+CREATE PROCEDURE ob1.p2()
+BEGIN
+ ALTER TABLE ob2.t13 CONVERT TO CHARACTER SET latin7;
+ ALTER TABLE ob3.tp CHANGE id a VARCHAR(4);
+END;||
+
+--echo ### Procedure that creates table and calls ob1.p2 ###
+CREATE PROCEDURE ob3.p21(a CHAR(20))
+BEGIN
+ CREATE TABLE IF NOT EXISTS ob3.tp(id INT);
+ CALL ob1.p2();
+END;||
+
+--echo
+--echo ### Create procedure that drops table ###
+CREATE PROCEDURE ob3.p22()
+BEGIN
+ DROP TABLE IF EXISTS ob3.tp;
+ DROP VIEW ob1.vp1;
+END;||
+
+DELIMITER ;||
+SET GLOBAL EVENT_SCHEDULER=ON;
+CREATE DEFINER=tom@'%' EVENT ob3.e1 ON SCHEDULE AT NOW()
+ ON COMPLETION PRESERVE
+ DO INSERT INTO ob1.t4 VALUES('cash',0);
+
+--echo
+--echo ### Create an event that will fire trigger trg12 ###
+CREATE EVENT ob2.e3 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
+DO DELETE FROM ob2.t13 WHERE A=3;
+
+--echo
+--echo ### Create an event using definer clause ###
+CREATE DEFINER=tom@'%' EVENT ob3.e4 ON SCHEDULE EVERY 1 SECOND ON COMPLETION
+PRESERVE DO UPDATE ob1.t1 SET id=100 WHERE name='ab1';
+
+DELIMITER ||;
+--echo ### Create procedure to check objects in all databases ###
+CREATE PROCEDURE test.objects_ob1()
+BEGIN
+(SELECT routine_name , routine_type FROM information_schema.routines
+ WHERE routine_schema = 'ob1' )
+UNION (SELECT event_name, 'EVENT' FROM information_schema.events
+ WHERE event_schema = 'ob1' )
+UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
+ WHERE trigger_schema = 'ob1') ORDER BY routine_name, routine_type;
+END;||
+
+CREATE PROCEDURE test.objects_ob2()
+BEGIN
+(SELECT routine_name , routine_type FROM information_schema.routines
+ WHERE routine_schema = 'ob2')
+UNION (SELECT event_name, 'EVENT' FROM information_schema.events
+ WHERE event_schema = 'ob2')
+UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
+ WHERE trigger_schema = 'ob2') ORDER BY routine_name, routine_type;
+END;||
+
+CREATE PROCEDURE test.objects_ob3()
+BEGIN
+(SELECT routine_name , routine_type FROM information_schema.routines
+ WHERE routine_schema = 'ob3')
+UNION (SELECT event_name, 'EVENT' FROM information_schema.events
+ WHERE event_schema = 'ob3')
+UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
+ WHERE trigger_schema = 'ob3') ORDER BY routine_name, routine_type;
+END;||
+DELIMITER ;||
+
+# Excercise all objects
+--echo
+--echo Excercise procedure p11
+--echo p11(srno<0) will call procedure p1 -->create vp1 and calls f1()
+--echo Function f1 will show sum(salary) from t4
+CALL ob2.p11(-2);
+SELECT * FROM ob1.vp1;
+SELECT * FROM ob1.t1; # ab6 value will be deleted.
+SELECT * FROM ob2.t11; # New alue will be added 'ab6'
+
+--echo
+--echo p11(srno=0) will trigger trg11(by inserting values in t13) which
+--echo inturn will call another procedure p13
+CALL ob2.p11(0);
+
+--echo
+--echo t13 table will be added with 3 new values of 1, 20 and 30.
+--echo
+SELECT * FROM ob2.t13 ORDER BY A;
+SELECT * FROM ob2.t14;
+
+--echo Trigger trg11 will insert values in t3 and t4
+--echo t3 and t4 will have 3 new values
+--echo
+SELECT * FROM ob1.t3;
+SELECT * FROM ob1.t4;
+--echo Function f1 will show sum(salary) from ob1.t4
+SELECT ob1.f1();
+
+--echo select ob1@p13 should show 11
+SELECT @ob2.p13;
+
+--echo
+--echo p11(srno > 0) will call function f11 and p12
+--echo Procedure p12 will call trigger trg2 (by updating table t2)
+--echo Trigger trg2 will also trigger trg1 by inserting values in t1
+--echo Trigger trg1 in turn will trigger trg12 by deleting values from t13
+--echo
+CALL ob2.p11(10);
+
+--echo p12 will update table t2. The join_date column is updated for id=18
+--echo we will check all the dependencies of table t2.
+--echo
+SELECT * FROM ob1.t2;
+SELECT * FROM ob1.v1;
+SELECT * FROM ob1.vv2;
+SELECT * FROM ob1.v6;
+SELECT * FROM ob2.v15;
+
+--echo
+--echo Update of table t2 will trigger trg2
+--echo Table t1 will include additional row with id=22
+SELECT * FROM ob1.t1;
+SELECT * FROM ob1.v1;
+SELECT * FROM ob1.v2;
+SELECT * FROM ob1.vv2;
+SELECT * FROM ob1.vv1;
+SELECT * FROM ob2.v14;
+
+--echo Inserting values in t1 will trigger trg1
+--echo Values < 2 will be deleted from table t13.
+--echo
+SELECT * FROM ob2.t13 ORDER BY A;
+SELECT * FROM ob2.t14;
+
+--echo
+--echo Deletion of data t13 will trigger trg12
+SELECT * FROM ob1.t3;
+SELECT * FROM ob1.v6;
+SELECT * FROM ob3.t23;
+SELECT * FROM ob3.t21;
+
+--echo Verify that Insertion in t23 triggers trgu
+SELECT * FROM ob1.t4 WHERE payment_type='cash';
+
+--echo p21 will create table tp and calls procedure p2 which inturn
+--echo alters tables tp and t13
+
+CALL ob3.p21('TEST');
+SHOW CREATE TABLE ob3.tp;
+SHOW CREATE TABLE ob2.t13;
+SHOW CREATE TABLE ob3.tp;
+
+--echo checking tables and other objects in databases.
+--source suite/backup/include/objects_dependency.inc
+
+# Perform backup database of all databases and individual database
+
+--replace_column 1 #
+BACKUP DATABASE ob1, ob2, ob3 TO 'ob.bak';
+--replace_column 1 #
+BACKUP DATABASE ob1 TO 'ob1.bak';
+--replace_column 1 #
+BACKUP DATABASE ob2 TO 'ob2.bak';
+--replace_column 1 #
+BACKUP DATABASE ob3 TO 'ob3.bak';
+--echo dropping database.
+DROP DATABASE ob2;
+--echo restore ob2 database and check all objects are included
+--replace_column 1 #
+RESTORE FROM 'ob2.bak';
+
+--echo checking tables and other objects in databases are intact
+--source suite/backup/include/objects_dependency.inc
+
+--echo Drop all databases
+DROP DATABASE ob2;
+DROP DATABASE ob1;
+DROP DATABASE ob3;
+
+--echo Restore individual databases will fail because of view dependencies
+--echo **** 1. RESTORE only ob1 database.****
+
+--error ER_BACKUP_CANT_RESTORE_VIEW
+RESTORE FROM 'ob1.bak';
+
+--echo **** 2. Restore only ob2 database ****
+
+--error ER_BACKUP_CANT_RESTORE_VIEW
+RESTORE FROM 'ob2.bak';
+
+--echo Restore all databases together
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+
+--echo Verify consistency of all objects and data after restore.
+--source suite/backup/include/objects_dependency.inc
+
+--echo
+--echo Excercise objects
+
+--echo p22 will drop view vp1 and table tp
+CALL ob3.p22();
+
+--echo Excercise procedure p11 that calls p1 and inturn selects from f1
+CALL ob2.p11(-4);
+SELECT * FROM ob1.vp1; #vp1 will be created by ob1.p1
+SELECT * FROM ob2.t11; #New value ab6 will be inserted
+
+CALL ob2.p11(0);
+--echo
+--echo t13 table will be added with 3 new values of 1, 20 and 30.
+--echo
+SELECT * FROM ob2.t13 ORDER BY A;
+SELECT * FROM ob2.t14;
+
+--echo trg11 will be triggered by insertion of values in t13
+SELECT * FROM ob1.t3;
+SELECT * FROM ob1.t4;
+SELECT ob1.f1();
+--echo select ob1@p13 should show 11
+SELECT @ob2.p13;
+
+--echo
+--echo p11(srno > 0) will call function f11 and p12
+--echo Procedure p12 will call trigger trg2
+--echo Trigger trg2 will also trigger trg1 which in turn will trigger trg12
+--echo
+CALL ob2.p11(10);
+
+--echo p12 will update table t2. The join_date column is updated for id=18
+--echo we will check all the dependencies of table t2.
+--echo
+SELECT * FROM ob1.t2;
+SELECT * FROM ob1.v1;
+SELECT * FROM ob1.vv2;
+SELECT * FROM ob1.v6;
+
+--echo
+--echo When trigger trg2 is fired, t1 will include additional row with id=22
+SELECT * FROM ob1.t1;
+SELECT * FROM ob1.v1;
+SELECT * FROM ob1.v2;
+SELECT * FROM ob1.vv2;
+SELECT * FROM ob1.vv1;
+SELECT * FROM ob2.v14;
+
+--echo Inserting values in t1 will trigger trg1
+--echo Values < 2 will be deleted from table t13.
+--echo
+SELECT * FROM ob2.t13 ORDER BY A;
+
+--echo
+--echo Deletion of data t13 will trigger trg12
+SELECT * FROM ob1.t3;
+SELECT * FROM ob3.t23;
+SELECT * FROM ob1.v6;
+SELECT * FROM ob3.t21;
+SELECT * FROM ob1.v4;
+
+--echo p21 will create table tp and calls procedure p2 which inturn
+--echo alters tables.
+
+CALL ob3.p21('TEST');
+SHOW CREATE TABLE ob3.tp;
+SHOW CREATE TABLE ob2.t13;
+SHOW CREATE TABLE ob3.tp;
+
+--echo
+--echo CREATE DEPENDENT TESTS
+--echo ======================
+#
+# Check the response of backup database operation, if create-dependent
+# objects are missing.
+#
+--echo
+--echo # Drop the base table(ob1.t1) in which a view(ob1.v1) is dependent on.
+
+DROP TABLE ob1.t1;
+--error ER_BACKUP_CATALOG_ADD_VIEW
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+SELECT * FROM ob1.t1;
+SELECT * FROM ob1.v1;
+
+--error 0,1
+--remove_file $bdir/ob1_missing.bak;
+
+# View v3 depends on table t11(of diff database)
+DROP TABLE ob2.t11;
+--error ER_BACKUP_CATALOG_ADD_VIEW
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+SELECT * FROM ob1.v3;
+SELECT * FROM ob2.t11;
+
+--error 0,1
+--remove_file $bdir/ob1_missing.bak;
+
+--echo
+--echo # Drop view(ob1.v2), table(ob1.t2) in which a view(vv2) depends.
+
+DROP TABLE ob1.t2;
+DROP VIEW ob1.v2;
+--error ER_BACKUP_CATALOG_ADD_VIEW
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+SELECT * FROM ob1.v2;
+SELECT * FROM ob1.t2;
+SELECT * FROM ob1.vv2;
+
+--error 0,1
+--remove_file $bdir/ob1_missing.bak
+
+--echo
+--echo # Drop view(ob1.v2) in which a view(vv11) depends.
+
+DROP VIEW ob1.v2;
+--error ER_BACKUP_CATALOG_ADD_VIEW
+BACKUP DATABASE ob2 TO 'ob2_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+SELECT * FROM ob1.v2;
+SELECT * FROM ob2.vv11;
+
+--error 0,1
+--remove_file $bdir/ob2_missing.bak
+
+--echo
+--echo # Drop user(tom) in which view(ob1.v1) depends.
+
+DROP USER tom@'%';
+--error ER_BACKUP_CATALOG_ADD_VIEW
+BACKUP DATABASE ob1 TO 'ob1_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+SHOW FULL TABLES FROM ob1;
+--error ER_NO_SUCH_USER
+SELECT * FROM ob1.v1;
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+SELECT * FROM ob1.v1;
+--error 0,1
+--remove_file $bdir/ob1_missing.bak
+
+--echo
+--echo # Drop user in which procedure(ob2.p13) depends.
+
+DROP USER tom@'%';
+--replace_column 1 #
+BACKUP DATABASE ob2 TO 'ob2_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob2_missing.bak' OVERWRITE;
+--replace_column 2 #
+SHOW WARNINGS;
+SHOW CREATE PROCEDURE ob2.p13;
+--error ER_NO_SUCH_USER
+CALL ob2.p13(20, @ob2.p13);
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+
+--remove_file $bdir/ob2_missing.bak
+
+--echo
+--echo # Drop user in which function(ob2.f11) depends.
+
+DROP USER tom@'%';
+--replace_column 1 #
+BACKUP DATABASE ob2 TO 'ob2_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob2_missing.bak' OVERWRITE;
+--replace_column 2 #
+SHOW WARNINGS;
+SHOW CREATE FUNCTION ob2.f11;
+--error ER_NO_SUCH_USER
+SELECT ob2.f11();
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+--error 0,1
+--remove_file $bdir/ob2_missing.bak
+
+--echo
+--echo # Drop user in which trigger(trgu) depends on.
+
+DROP USER tom@'%';
+--replace_column 1 #
+BACKUP DATABASE ob3 TO 'ob3_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob3_missing.bak' OVERWRITE;
+SHOW CREATE TRIGGER ob3.trgu;
+--echo Fire trigger trgu
+--error ER_NO_SUCH_USER
+INSERT INTO ob3.t23 VALUES(98);
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+INSERT INTO ob3.t23 VALUES(97);
+--echo Note that we will see both values 97 and 98. trgu will not update value
+--echo in t4 if user is not present.
+
+--error 0,1
+--remove_file $bdir/ob3_missing.bak
+
+--echo
+--echo # Drop user in which event(e4) depends
+
+DROP USER tom@'%';
+--replace_column 1 #
+BACKUP DATABASE ob3 TO 'ob3_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob3_missing.bak' OVERWRITE;
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom@'%';
+--remove_file $bdir/ob3_missing.bak
+
+--echo
+--echo # Drop table(ob3.t23) in which trigger(ob3.trgu) depends
+
+DROP TABLE ob3.t23;
+--replace_column 1 #
+BACKUP DATABASE ob3 TO 'ob3_missing.bak';
+--replace_column 1 #
+RESTORE FROM 'ob3_missing.bak' OVERWRITE;
+--error ER_TRG_DOES_NOT_EXIST
+SHOW CREATE TRIGGER ob3.trgu;
+--echo Note that trigger will not exist.
+--replace_column 1 #
+RESTORE FROM 'ob.bak' OVERWRITE;
+SHOW CREATE TRIGGER ob3.trgu;
+--remove_file $bdir/ob3_missing.bak
+
+--echo
+--echo USE DEPENDENT TESTS
+--echo ===================
+#
+# Use dependency tests. Check the response of backup database operation,
+# if use-dependent objects are missing.
+#
+
+--echo
+--echo # Drop the procedure(ob1.p1) in which procedure(ob2.p11) depends
+Let $drop_object= PROCEDURE ob1.p1;
+Let $object_proc=ob2.p11(-1);
+Let $object=procedure;
+Let $db=ob2;
+--source suite/backup/include/objects_dependency_use.inc
+CALL test.objects_ob1;
+
+--echo
+--echo # Drop the table(ob2.t13) in which a procedure(ob2.p11) depends
+--echo NOTE: This is also test of procedure firing trigger.
+Let $drop_object= TABLE ob2.t13;
+Let $object_proc=ob2.p11(0);
+Let $object=table;
+Let $db=ob2;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Procedure that alters table(ob1.p2 alter ob2.t13).
+
+Let $drop_object= TABLE ob2.t13;
+Let $object_proc=ob1.p2();
+Let $object=table;
+Let $db=ob1;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Procedure that drops table(ob3.p22 drops ob3.tp).
+
+Let $drop_object= TABLE ob3.tp;
+Let $object_proc=ob3.p22();
+Let $object=table1;
+Let $db=ob3;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Function that selects from table(ob1.f1 on ob1.t4)
+
+Let $drop_object= TABLE ob1.t4;
+Let $object_fun=ob1.f1();
+Let $db=ob1;
+Let $object=function;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Trigger that depends on table.(ob3.trgu on ob1.t4)
+
+Let $drop_object= TABLE ob1.t4;
+Let $object_trg=ob3.t23;
+Let $object=trigger1;
+Let $db=ob3;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Trigger that fires another trigger(ob1.trg1 on ob2.trg12)
+
+Let $drop_object= TABLE ob2.t13;
+Let $object=trigger3;
+Let $db=ob1;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Trigger that calls procedure(ob2.trg11 on ob2.p13)
+
+Let $drop_object= PROCEDURE ob2.p13;
+Let $object=trigger2;
+Let $object_trg=ob2.t13;
+Let $db=ob2;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo
+--echo # Procedure that selects values from stored function(ob1.p1 on ob1.f1)
+CALL ob3.p22(); #Drops view ob1.vp1
+Let $drop_object= FUNCTION ob1.f1;
+Let $object_proc=ob1.p1();
+Let $object=procedure;
+Let $db=ob1;
+--source suite/backup/include/objects_dependency_use.inc
+
+--echo # Event(ob3.e4) that depends on table(ob1.t1)
+
+Let $drop_object= TABLE ob1.t1;
+Let $object=event;
+Let $db=ob3;
+--source suite/backup/include/objects_dependency_use.inc
+--replace_column 4 # 5 # 6 #
+--query_vertical SHOW CREATE EVENT ob3.e4;
+
+#
+# Note : An event depending on table and trigger will have same behaviour.
+# For both the cases, we need to drop the table and event will not have
+# effect.
+
+# Test cleanup section
+
+REVOKE ALL ON *.* FROM tom@'%';
+DROP USER tom@'%';
+FLUSH PRIVILEGES;
+DROP DATABASE ob1;
+DROP DATABASE ob2;
+DROP DATABASE ob3;
+SET GLOBAL EVENT_SCHEDULER=OFF;
+--remove_file $bdir/ob.bak
+--remove_file $bdir/ob1.bak
+--remove_file $bdir/ob2.bak
+--remove_file $bdir/ob3.bak
