List:Commits« Previous MessageNext Message »
From:Rafal Somla Date:February 25 2009 8:35am
Subject:Re: bzr commit into mysql-6.0-backup branch (hema:2772) WL#4225
View as plain text  
Hi Hema,

STATUS
------
Changes requested.

REQUESTS
--------

1. Change the objects_dependency_use.inc file [2].

2. Suppress only these messages which are generated by test [4].

3. Remove test scenarios which are already included in existing tests [7,8,9,10,11].

4. Move scenarios which test dependency on definer to use-dependencies section 
[14,16,17,18,19].

5. Not possible to create a scenario with missing create-dependency of trigger 
on table - remove it [20].

6. Mask error numbers in warnings from BACKUP/RESTORE [3].

7. Remove spurious call to test.objects_ob1 [21].

8. Fix or remove scenario of trigger use-depending on another trigger [22].

9. Explain or remove call to ob3.p22 [23].

10. Explain or refactor or remove test scenario for event use-depending on table 
[24,25].

SUGGESTIONS
-----------

11. Add pattern to SHOW DATABASES [0].

12. Replace objects_dependency.inc by a stored procedure [1].

13. Instead of having three procedures objects_ob*(), use a single one with 
database name passed as a parameter [5].

14. Prefix all strings echoed in the test with '#' - makes it easier to analyse 
result file [6].

15. Move the remaining create-dependency test scenarios to backup_views test 
[12,13].

COMMENTS
--------
I think it is wrong that BACKUP fails if database contains a view with 
non-existent definer. I reported BUG#43186 on this.

To me the dependency of a view on its definer is not a create-dependency, 
because it is possible to create a view with non-existent definer. Thus it is 
only a use-dependency and should be tested together with other use dependencies. 
  Until the bug is fixed I'd comment-out this particular test scenario with 
appropriate note.

DETAILS
-------
Hema Sridharan wrote:
> #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;

[0] Suggestion: Change to "SHOW DATABASES LIKE 'ob%'" so that internal databases 
are not listed.

> +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;
> 

[1] Suggestion: define a stored procedure (let's call it show_objects) instead 
of using inlcude file. Then, in the test, instead of

--source suite/backup/include/objects_dependency.inc

you could simply do:

CALL test.show_objects();

> === 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
> 

[2] I think it is a bad design to exercise objects after restore in this include 
file. This makes it hard to see what is going on from within the main test. 
Also, the include file is not generic enough to justify its existence.

I propose to leave here only parts which are common to all scenarions and do 
exercising of objects outside this file and in the main test. This file can be 
changed to (note added SHOW WARNINGS to see feedback if objects with missing 
use-dependencies are backed-up and restored):

==============================================
--replace_column 1 #
RESTORE FROM 'ob.bak' OVERWRITE;

eval DROP $drop_object;

--replace_column 1 #
eval BACKUP DATABASE $db TO 'ob_missing.bak';
--replace column 2 #
SHOW WARNINGS;
--replace_column 1 #
RESTORE FROM 'ob_missing.bak' OVERWRITE;
--replace column 2 #
SHOW WARNINGS;

--source suite/backup/include/objects_dependency.inc
--remove_file $bdir/ob_missing.bak
==============================================

Then it can be used from the main test as follows:

let $db= ob2;
let $drop_object= PROCEDURE ob1.p1;
--source suite/backup/unclude/objects_dependency_use.inc
--error ER_SP_DOES_NOT_EXIST
CALL ob1.p1(-1);

That is, the call to ob1.p1 (with correct expected error) is done in the main 
test, after sourcing the include file. Below I show how all places where 
objects_dependency_use.inc is used would be changed.


> === added file 'mysql-test/suite/backup/r/backup_objects_dependency.result'
...
> +# 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

[3] Error numbers should be masked.

...
> === 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:");

[4] Suppress only these errors which are actually generated (and expected) by 
this test.

...
> +--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 ;||
> +

[5] Suggestion: Use only one procedure, say show_objects, which takes database 
name as a parameter. Like this:

CREATE PROCEDURE test.show_objects(db CHAR(8))
BEGIN
   (SELECT routine_name, routine_type FROM information_schema.routines
     WHERE routine_schema = db )
   UNION ...
END;||

> +# 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

[6] Suggestion: Add '#' in front of each echoed string - this will make the 
result file much easier to analyse.

...
> +# 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';

[7] This scenario is already tested in backup_views test.

> +
> +--echo **** 2. Restore only ob2 database ****
> +
> +--error ER_BACKUP_CANT_RESTORE_VIEW
> +RESTORE FROM 'ob2.bak';
> +

[8] This scenario is already tested in backup_views test.

> +--echo Restore all databases together
> +--replace_column 1 # 
> +RESTORE FROM 'ob.bak' OVERWRITE;
> +

[9] This scenario is already tested in backup_views and backup_objects tests.

...
> +--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. 
> +

[10] This scenario is already tested in backup_views test.

> +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)

[11] This scenario is already tested in backup_views test.

> +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;
> +

[12] Suggestion: move this scenario to backup_views test.

> +--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;
> +

[13] Suggestion: move this scenario to backup_views test.

> +--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;

[14] It *is* possible to create a view even if the specifier definer does not 
exist. Hence the dependency of view on a user is not a create-dependency but a 
use-dependency. Hence this scenario should not be included here but together 
with all other use-dependencies below.

[15] I think it is an error that BACKUP fails in this situation. I'll report a 
bug on this and until it is fixed, this test scenario should be commented out 
with information that BACKUP and RESTORE should pass in this case.

> +--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@'%';
> +

[16] This is use-dependency - test it together with other use-dependencies.

> +--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@'%';

[17] This is use-dependency - test it together with other use-dependencies.

> +--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.
> +

[18] This is use-dependency - test it together with other use-dependencies.

> +--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
> +

[19] This is use-dependency - test it together with other use-dependencies.

> +--echo
> +--echo # Drop table(ob3.t23) in which trigger(ob3.trgu) depends
> +
> +DROP TABLE ob3.t23;

[20] It is not possible to create a situation where this dependency is broken, 
because removing the table also removes the trigger. After dropping ob3.t23 
triger ob3.trgu does not exist any more and the test below does not test 
anything interesting.
However, trigger ob3.trgu use-depends on table ob1.t4 and this dpendency could 
be tested together with other use-dependencies.

> +--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;

[21] Procedures test.objects_obX are called from objects_dependency.inc which is 
sourced in objects_dependency_use.inc - no need to call it here again.

> +

When objects_dependency_use.inc is changed as I suggested, then the above 
snipped will look like this:

let $db= ob2;
let $drop_object= PROCEDURE ob1.p1;
--source suite/backup/include/objects_dependency_use.inc
--error ER_SP_DOES_NOT_EXIST
CALL ob2.p11(-1);

Below I show the change also in all other places.

> +--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
> +

let $db= ob2;
let $drop_object= TABLE ob2.t13;
--source suite/backup/include/objects_dependency_use.inc
--error ER_NO_SUCH_TABLE
CALL ob2.p11(0);

> +--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
> +

let $db= ob1;
let $drop_object= TABLE ob2.t13;
--source suite/backup/include/objects_dependency_use.inc
--error ER_NO_SUCH_TABLE
CALL ob1.p2();

> +--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
> +

let $db= ob3;
let $drop_object= TABLE ob3.tp;
--source suite/backup/include/objects_dependency_use.inc
CALL ob3.p22(0);
SHOW WARNINGS;

Note: need to mask error numbers in SHOW WARNINGS.

> +--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
> +

let $db= ob1;
let $drop_object= TABLE ob1.t4;
--source suite/backup/include/objects_dependency_use.inc
--error ER_NO_SUCH_TABLE
SELECT ob1.f1();

> +--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
> +

let $db= ob3;
let $drop_object= TABLE ob1.t4;
--source suite/backup/include/objects_dependency_use.inc
--error ER_NO_SUCH_TABLE
INSERT INTO ob3.t23 VALUES (200);

> +--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
> +

let $db= ob1;
let $drop_object= TABLE ob2.t13;
--source suite/backup/include/objects_dependency_use.inc
--error ER_TRG_DOES_NOT_EXIST
SHOW CREATE TRIGGER ob2.trg12;
--echo Notice that ob2.trg12 will not exist.

[22] I understand that here you want to test use-dependency of ob1.trg1 on 
ob2.trg12:

+### 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 ob1.trg1 deletes from ob2.t13, which should trigger ob2.trg12. If you 
remove ob2.t13 then you can not test this use-dependency, because the dendency 
of ob1.trg1 on the table is not satisfied in the first place (and dependency of 
trigger on a table was just tested above).

I think to test the dependency you claim you want to test here you need to do 
the following:
- drop the trigger ob2.trg12 (not the table ob2.t13)
- after restore, insert into ob1.t1 so that ob1.trg1 fires.

Now, since ob2.trg12 would not exist after restore, nothing will happen when 
ob1.t1 fires and deletes from ob2.t13 (as no trigger would be defined on 
ob2.t13, as was the case at backup time). This makes me thing that ob1.trg1 does 
not really use-depend on ob2.trg12 and hence there is nothing to test here.


> +--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
> +

let $db= ob2;
let $drop_object= PROCEDURE ob2.p13;
--source suite/backup/include/objects_dependency_use.inc
--error ER_SP_DOES_NOT_EXIST
INSERT INTO ob2.t13 VALUES(200);

> +--echo
> +--echo # Procedure that selects values from stored function(ob1.p1 on ob1.f1)
> +CALL ob3.p22(); #Drops view ob1.vp1

[23] Why this call?

> +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
> +

let $db= ob1;
let $drop_object= FUNCTION ob1.f1;
--source suite/backup/include/objects_dependency_use.inc
--error ER_SP_DOES_NOT_EXIST
CALL ob1.p1();

> +--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

let $db= ob3;
let $drop_object= TABLE ob1.t1;
--source suite/backup/include/objects_dependency_use.inc
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

[24] I don't understand what is done here. I thought you want to test what 
happens when event ob3.e4, which uses dropped table ob1.t1 is backed-up and then 
restored. But here you drop the event after restoring it and create another one 
with the same name. What is it supposed to test?

> +--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. 

[25] I don't understand this comment.

Rafal
Thread
bzr commit into mysql-6.0-backup branch (hema:2772) WL#4225Hema Sridharan17 Feb
  • Re: bzr commit into mysql-6.0-backup branch (hema:2772) WL#4225Rafal Somla25 Feb