MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Hema Sridharan Date:September 17 2009 9:04pm
Subject:bzr commit into mysql-6.0-backup branch (hema:2870) Bug#43549
View as plain text  
#At file:///export/home/tmp/Bug43549/mysql-6.0-backup/ based on revid:charles.bell@stripped

 2870 Hema Sridharan	2009-09-17
      BUG#43549 (backup_objects_dependency test is non
      deterministic). The new patch for this bug will remove all
      the dependencies of events and check for events execution. 
      modified:
        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/r/backup_objects_dependency.result
    Modified result file for backup_objects_dependency test
  mysql-test/suite/backup/t/backup_objects_dependency.test
    Modified backup_objects_dependency.test by removing events and their dependencies.
=== modified file 'mysql-test/suite/backup/r/backup_objects_dependency.result'
--- a/mysql-test/suite/backup/r/backup_objects_dependency.result	2009-08-20 18:27:04 +0000
+++ b/mysql-test/suite/backup/r/backup_objects_dependency.result	2009-09-17 21:04:18 +0000
@@ -97,13 +97,9 @@ company_stocks SMALLINT
 INSERT INTO ob3.t31 VALUES
 (10, 102,'Nokia',2000),(12, 122,'Veritas',1500),(15,152,'Mysql',3000);
 CREATE TABLE ob3.t32(id INT);
-# Table log will contain entries added by firing event.
-CREATE TABLE ob3.log(pos INT UNIQUE AUTO_INCREMENT, msg CHAR(32));
-# Table msg contains a message to be inserted into log by the event.
-CREATE TABLE ob3.msg(m CHAR(32)) AS SELECT (NULL);
 
 ------------------------------------------------------------
-CREATE VIEWS, TRIGGERS, EVENTS, PROCEDURES AND FUNCTIONS
+CREATE VIEWS, TRIGGERS, PROCEDURES AND FUNCTIONS
 ------------------------------------------------------------
 
 ** creating views in 'ob1' database **
@@ -310,18 +306,6 @@ BEGIN
 DROP TABLE IF EXISTS ob3.tp;
 DROP VIEW ob1.vp1;
 END;||
-CREATE DEFINER=tom@'%' EVENT ob3.e31 ON SCHEDULE AT NOW()
-ON COMPLETION PRESERVE
-DO INSERT INTO ob1.t14 VALUES('cash',0);
-
-### Create an event that will fire trigger trg12 ###
-CREATE EVENT ob2.e21 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
-DO DELETE FROM ob2.t23 WHERE A=3;
-
-### Create an event using definer clause ###
-CREATE DEFINER=tom@'%' EVENT ob3.ev ON SCHEDULE AT CURRENT_TIMESTAMP
-ON COMPLETION PRESERVE DISABLE
-DO INSERT INTO ob3.log(msg) SELECT m FROM msg LIMIT 1;
 ### Create procedure to check objects in all databases ###
 CREATE PROCEDURE test.show_objects(db CHAR(10))
 BEGIN
@@ -329,26 +313,16 @@ SELECT TABLE_NAME, TABLE_TYPE FROM infor
 WHERE TABLE_SCHEMA = db;
 (SELECT routine_name , routine_type FROM information_schema.routines
 WHERE routine_schema = db)
-UNION (SELECT event_name, 'EVENT' FROM information_schema.events
-WHERE event_schema = db)
 UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
 WHERE trigger_schema = db) ORDER BY routine_name, routine_type;
 END;||
-### Procedure that will fire an event by enabling it ###
-SET GLOBAL EVENT_SCHEDULER=ON;
-CREATE PROCEDURE ob3.fire(msg char(32)) 
-BEGIN 
-UPDATE ob3.msg SET m=msg;
-ALTER DEFINER=tom@'%' EVENT ob3.ev ENABLE;
-END;||
-CALL ob3.fire('user1 created');
 SELECT * FROM ob2.t23;
 A
 1
 2
+3
 SELECT * FROM ob1.t13;
 details
-TRIGGER FIRED FROM trg12
 SELECT * FROM ob3.t32;
 id
 CALL ob2.p23(-2);
@@ -358,7 +332,6 @@ SELECT * FROM ob1.t14;
 payment_type	salary
 cash	4000
 cheque	5000
-cash	0
 SELECT * FROM ob1.vp1;
 NUMBER
 100
@@ -382,6 +355,7 @@ A
 1
 1
 2
+3
 20
 30
 SELECT * FROM ob2.t24;
@@ -391,7 +365,6 @@ A
 3
 SELECT * FROM ob1.t13;
 details
-TRIGGER FIRED FROM trg12
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
@@ -399,7 +372,6 @@ SELECT * FROM ob1.t14;
 payment_type	salary
 cash	4000
 cheque	5000
-cash	0
 dd	2000
 dd	2000
 dd	2000
@@ -485,6 +457,7 @@ id	designation
 SELECT * FROM ob2.t23 ORDER BY A;
 A
 2
+3
 20
 30
 SELECT * FROM ob2.t24;
@@ -494,7 +467,6 @@ A
 3
 SELECT * FROM ob1.t13;
 details
-TRIGGER FIRED FROM trg12
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
@@ -519,7 +491,6 @@ ccode	gcode	company_name	company_stocks
 SELECT * FROM ob1.t14 WHERE payment_type='cash';
 payment_type	salary
 cash	1000
-cash	1000
 CALL ob3.p31('TEST');
 SHOW CREATE TABLE ob3.tp;
 Table	Create Table
@@ -536,9 +507,6 @@ Table	Create Table
 tp	CREATE TABLE `tp` (
   `a` varchar(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-SELECT * FROM ob3.log;
-pos	msg
-1	user1 created
 CALL show_objects('ob1');
 TABLE_NAME	TABLE_TYPE
 t11	BASE TABLE
@@ -571,7 +539,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -580,19 +547,13 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
-SET GLOBAL EVENT_SCHEDULER=OFF;
 BACKUP DATABASE ob1, ob2, ob3 TO 'ob.bak';
 backup_id
 #
@@ -644,7 +605,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -653,24 +613,17 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
-SET GLOBAL EVENT_SCHEDULER=ON;
-CALL ob3.fire('user2 created');
 CALL ob3.p32();
 CALL ob2.p23(-4);
 ob1.f11()
-13000
+12000
 SELECT * FROM ob1.vp1;
 NUMBER
 100
@@ -687,6 +640,7 @@ SELECT * FROM ob2.t23 ORDER BY A;
 A
 1
 2
+3
 20
 20
 30
@@ -698,7 +652,6 @@ A
 3
 SELECT * FROM ob1.t13;
 details
-TRIGGER FIRED FROM trg12
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
@@ -711,7 +664,6 @@ SELECT * FROM ob1.t14;
 payment_type	salary
 cash	1000
 cheque	5000
-cash	1000
 dd	2000
 dd	2000
 dd	2000
@@ -720,7 +672,7 @@ dd	2000
 dd	2000
 SELECT ob1.f11();
 ob1.f11()
-19000
+18000
 SELECT @ob2.p21;
 @ob2.p21
 11
@@ -796,13 +748,13 @@ id	designation
 SELECT * FROM ob2.t23 ORDER BY A;
 A
 2
+3
 20
 20
 30
 30
 SELECT * FROM ob1.t13;
 details
-TRIGGER FIRED FROM trg12
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
 TRIGGER FIRED FROM trg11
@@ -850,11 +802,6 @@ Table	Create Table
 tp	CREATE TABLE `tp` (
   `a` varchar(4) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-SELECT * FROM ob3.log;
-pos	msg
-1	user1 created
-2	user2 created
-SET GLOBAL EVENT_SCHEDULER=OFF;
 
 CREATE DEPENDENT TESTS
 ======================
@@ -908,7 +855,6 @@ support	12.00
 
 USE DEPENDENT TESTS
 ===================
-SET GLOBAL EVENT_SCHEDULER=ON;
 
 # Drop user(tom) in which view(ob1.v11) depends.
 DROP USER tom@'%';
@@ -916,7 +862,6 @@ BACKUP DATABASE ob1 TO 'ob1_missing.bak'
 ERROR HY000: Failed to add view `ob1`.`v11` to the catalog
 CREATE USER tom@'%';
 GRANT ALL ON *.* TO tom@'%';
-SET GLOBAL EVENT_SCHEDULER=OFF;
 
 # Drop user in which procedure(ob2.p21) and function(ob2.f21)depends.
 Perform restore again to get all objects back.
@@ -973,7 +918,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -982,15 +926,10 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1016,7 +955,7 @@ ERROR HY000: The user specified as a def
 CREATE USER tom@'%';
 GRANT ALL ON *.* TO tom@'%';
 
-# Drop user in which trigger(trg32) and event(ev) depends on.
+# Drop user in which trigger(trg32) depends on.
 Perform restore again to get all objects back.
 RESTORE FROM 'ob.bak' OVERWRITE;
 backup_id
@@ -1033,8 +972,6 @@ backup_id
 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
-Note	#	The user specified as a definer ('tom'@'%') does not exist
 SHOW DATABASES LIKE 'ob%';
 Database (ob%)
 ob1
@@ -1072,7 +1009,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -1081,15 +1017,10 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1106,7 +1037,6 @@ SELECT * FROM ob1.t14;
 payment_type	salary
 cash	1000
 cheque	5000
-cash	1000
 dd	2000
 dd	2000
 dd	2000
@@ -1118,11 +1048,6 @@ id	name	pay
 15	ab4	1236.890
 18	ab5	3890.780
 22	ab7	4123.230
-SET GLOBAL EVENT_SCHEDULER=ON;
-CALL ob#.fire('no user');
-Warnings:
-Note	#	The user specified as a definer ('tom'@'%') does not exist
-SET GLOBAL EVENT_SCHEDULER=OFF;
 CREATE USER tom@'%';
 GRANT ALL ON *.* TO tom@'%';
 INSERT INTO ob3.t32 VALUES(97);
@@ -1136,13 +1061,9 @@ SELECT * FROM ob1.t14;
 payment_type	salary
 cash	1000
 cheque	5000
-cash	1000
 dd	2000
 dd	2000
 dd	2000
-SELECT * FROM ob3.log;
-pos	msg
-1	user1 created
 
 # Drop the procedure(ob1.p11) in which procedure(ob2.p23) depends
 Perform restore again to get all objects back.
@@ -1196,7 +1117,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -1205,15 +1125,10 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1273,22 +1188,16 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
 p23	PROCEDURE
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1347,22 +1256,16 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
 p23	PROCEDURE
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1422,7 +1325,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -1431,14 +1333,9 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1498,7 +1395,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -1507,15 +1403,10 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1574,7 +1465,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -1583,15 +1473,10 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1651,7 +1536,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p22	PROCEDURE
 p23	PROCEDURE
@@ -1659,15 +1543,10 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
@@ -1726,7 +1605,6 @@ v22	VIEW
 v23	VIEW
 v24	VIEW
 routine_name	routine_type
-e21	EVENT
 f21	FUNCTION
 p21	PROCEDURE
 p22	PROCEDURE
@@ -1735,108 +1613,16 @@ trg23a	TRIGGER
 trg23b	TRIGGER
 CALL test.show_objects('ob3');
 TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-msg	BASE TABLE
 t31	BASE TABLE
 t32	BASE TABLE
 tp	BASE TABLE
 routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
 p31	PROCEDURE
 p32	PROCEDURE
 trg32	TRIGGER
 DROP VIEW ob1.vp1;
 CALL ob1.p11();
 ERROR 42000: FUNCTION ob1.f11 does not exist
-# Event(ob3.ev) that depends on table(ob3.msg)
-SET GLOBAL EVENT_SCHEDULER=ON;
-Perform restore again to get all objects back.
-RESTORE FROM 'ob.bak' OVERWRITE;
-backup_id
-#
-DROP TABLE ob3.msg;
-BACKUP DATABASE ob3 TO 'ob_missing.bak';
-backup_id
-#
-SHOW WARNINGS;
-Level	Code	Message
-RESTORE FROM 'ob_missing.bak' OVERWRITE;
-backup_id
-#
-SHOW WARNINGS;
-Level	Code	Message
-SHOW DATABASES LIKE 'ob%';
-Database (ob%)
-ob1
-ob2
-ob3
-CALL test.show_objects('ob1');
-TABLE_NAME	TABLE_TYPE
-t11	BASE TABLE
-t12	BASE TABLE
-t13	BASE TABLE
-t14	BASE TABLE
-v11	VIEW
-v12	VIEW
-v13	VIEW
-v14	VIEW
-v15	VIEW
-v16	VIEW
-v17	VIEW
-v18	VIEW
-vp1	VIEW
-routine_name	routine_type
-f11	FUNCTION
-p11	PROCEDURE
-p12	PROCEDURE
-trg11	TRIGGER
-trg12	TRIGGER
-CALL test.show_objects('ob2');
-TABLE_NAME	TABLE_TYPE
-t21	BASE TABLE
-t22	BASE TABLE
-t23	BASE TABLE
-t24	BASE TABLE
-v21	VIEW
-v22	VIEW
-v23	VIEW
-v24	VIEW
-routine_name	routine_type
-e21	EVENT
-f21	FUNCTION
-p21	PROCEDURE
-p22	PROCEDURE
-p23	PROCEDURE
-trg23a	TRIGGER
-trg23b	TRIGGER
-CALL test.show_objects('ob3');
-TABLE_NAME	TABLE_TYPE
-log	BASE TABLE
-t31	BASE TABLE
-t32	BASE TABLE
-tp	BASE TABLE
-routine_name	routine_type
-e31	EVENT
-ev	EVENT
-fire	PROCEDURE
-p31	PROCEDURE
-p32	PROCEDURE
-trg32	TRIGGER
-SHOW CREATE EVENT ob3.ev;;
-Event	ev
-sql_mode	
-time_zone	SYSTEM
-Create Event	#
-character_set_client	#
-collation_connection	#
-Database Collation	latin1_swedish_ci
-CALL ob3.fire('user3 created');
-ERROR 42S02: Table 'ob3.msg' doesn't exist
-SELECT * FROM ob3.log;
-pos	msg
-1	user1 created
 # Test cleanup section
 REVOKE ALL ON *.* FROM tom@'%';
 DROP USER tom@'%';
@@ -1845,4 +1631,3 @@ DROP DATABASE ob1;
 DROP DATABASE ob2;
 DROP DATABASE ob3;
 DROP PROCEDURE test.show_objects;
-SET GLOBAL EVENT_SCHEDULER=OFF;

=== modified file 'mysql-test/suite/backup/t/backup_objects_dependency.test'
--- a/mysql-test/suite/backup/t/backup_objects_dependency.test	2009-09-10 13:46:13 +0000
+++ b/mysql-test/suite/backup/t/backup_objects_dependency.test	2009-09-17 21:04:18 +0000
@@ -6,13 +6,22 @@
 ###############################################################################
 
 # Mysql Backup currently supports following objects:
-# Databases, tables, views, stored procedures / functions, triggers
-# and events.
+# Databases, tables, views, stored procedures / functions, triggers.
 # 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
 
+# NOTE: There is no proper way to verify the firing of events because of 
+# timing issues (Please see BUG#43549). Therefore this test will not include
+# the events and their dependencies to other objects.
+
+# In future, if proper reporting mechanism for execution of events is developed 
+# then events can be added to test along with dependencies to various other 
+# objects. See below for sample "CREATE EVENT" statements that can be included
+# in this test.
+# 
+
 # Suppress warning about expected backup/restore errors
 disable_query_log;
 call mtr.add_suppression("Backup: Failed to add view");
@@ -112,14 +121,10 @@ INSERT INTO ob3.t31 VALUES
 (10, 102,'Nokia',2000),(12, 122,'Veritas',1500),(15,152,'Mysql',3000);
 
 CREATE TABLE ob3.t32(id INT);
---echo # Table log will contain entries added by firing event.
-CREATE TABLE ob3.log(pos INT UNIQUE AUTO_INCREMENT, msg CHAR(32));
---echo # Table msg contains a message to be inserted into log by the event.
-CREATE TABLE ob3.msg(m CHAR(32)) AS SELECT (NULL);
 
 --echo
 --echo ------------------------------------------------------------
---echo    CREATE VIEWS, TRIGGERS, EVENTS, PROCEDURES AND FUNCTIONS
+--echo    CREATE VIEWS, TRIGGERS, PROCEDURES AND FUNCTIONS
 --echo ------------------------------------------------------------
 --echo
 
@@ -309,23 +314,6 @@ BEGIN
   DROP VIEW ob1.vp1;
 END;||
 
-DELIMITER ;||
-CREATE DEFINER=tom@'%' EVENT ob3.e31 ON SCHEDULE AT NOW()
- ON COMPLETION PRESERVE
- DO INSERT INTO ob1.t14 VALUES('cash',0);
-
---echo
---echo ### Create an event that will fire trigger trg12 ###
-CREATE EVENT ob2.e21 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
-DO DELETE FROM ob2.t23 WHERE A=3;
-
---echo
---echo ### Create an event using definer clause ###
-CREATE DEFINER=tom@'%' EVENT ob3.ev ON SCHEDULE AT CURRENT_TIMESTAMP
-  ON COMPLETION PRESERVE DISABLE
-  DO INSERT INTO ob3.log(msg) SELECT m FROM msg LIMIT 1; 
-
-DELIMITER ||;
 --echo ### Create procedure to check objects in all databases ###
 
 CREATE PROCEDURE test.show_objects(db CHAR(10))
@@ -334,33 +322,32 @@ SELECT TABLE_NAME, TABLE_TYPE FROM infor
     WHERE TABLE_SCHEMA = db;
 (SELECT routine_name , routine_type FROM information_schema.routines
     WHERE routine_schema = db)
-UNION (SELECT event_name, 'EVENT' FROM information_schema.events
-    WHERE event_schema = db)
 UNION (SELECT trigger_name, 'TRIGGER' FROM information_schema.triggers
     WHERE trigger_schema = db) ORDER BY routine_name, routine_type;
 END;||
 
---echo ### Procedure that will fire an event by enabling it ###
-
-SET GLOBAL EVENT_SCHEDULER=ON;
-CREATE PROCEDURE ob3.fire(msg char(32)) 
-BEGIN 
- UPDATE ob3.msg SET m=msg;
- ALTER DEFINER=tom@'%' EVENT ob3.ev ENABLE;
-END;||
 DELIMITER ;||
-CALL ob3.fire('user1 created');
-let $wait_condition=select count(*)=1 from ob3.log WHERE msg='user1 created';
---source include/wait_condition.inc
 
-# Excercise all objects
+#
+# Some of the "CREATE EVENT" statements that can be included in this test are
+#
+# 1. Event that will fire trigger trg12
+# CREATE EVENT ob2.e21 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
+# DO DELETE FROM ob2.t23 WHERE A=3;
+#
+# 2. Event using definer clause
+#  CREATE DEFINER=tom@'%' EVENT ob3.ev ON SCHEDULE AT CURRENT_TIMESTAMP
+#  ON COMPLETION PRESERVE DISABLE
+#  DO INSERT INTO ob3.log(msg) SELECT m FROM msg LIMIT 1;
+#
+
+# Exercise all objects
 
-# Verify event is fired
-SELECT * FROM ob2.t23; # A=3 will be deleted
+SELECT * FROM ob2.t23;
 SELECT * FROM ob1.t13;
 SELECT * FROM ob3.t32;
  
-# Excercise procedure p23
+# Exercise procedure p23
 # p23(srno<0) will call procedure p11 -->create vp1 and calls f11()
 # Function f11 will show sum(salary) from t14
 #
@@ -433,20 +420,11 @@ SHOW CREATE TABLE ob3.tp;
 SHOW CREATE TABLE ob2.t23;
 SHOW CREATE TABLE ob3.tp;
 
-# BUG#43549 This portion was disabled because the firing of the event is
-#           non-deterministic.
-#           The results are now made deterministic using wait_condition.inc
-#
-# Verify event ob3.ev is fired
-SELECT * FROM ob3.log;
-
 # Checking tables and other objects in databases.
 CALL show_objects('ob1');
 CALL show_objects('ob2');
 CALL show_objects('ob3');
 
-SET GLOBAL EVENT_SCHEDULER=OFF;
-
 # Perform backup database of all databases and individual database
 
 --replace_column 1 #
@@ -467,19 +445,12 @@ CALL test.show_objects('ob1');
 CALL test.show_objects('ob2');
 CALL test.show_objects('ob3');
 
-# Excercise objects
-
-SET GLOBAL EVENT_SCHEDULER=ON;
-
-# Verify event ob3.ev is fired
-CALL ob3.fire('user2 created');
-let $wait_condition=select count(*)=1 from ob3.log WHERE msg='user2 created';
---source include/wait_condition.inc
+# Exercise objects
 
 # p32 will drop view vp1 and table tp
 CALL ob3.p32();
 
-# Excercise procedure p23 that calls p11 and inturn selects from f11
+# Exercise procedure p23 that calls p11 and inturn selects from f11
 CALL ob2.p23(-4);
 SELECT * FROM ob1.vp1; #vp1 will be created by ob1.p11
 SELECT * FROM ob2.t21; #New value ab6 will be inserted
@@ -535,14 +506,6 @@ SHOW CREATE TABLE ob3.tp;
 SHOW CREATE TABLE ob2.t23;
 SHOW CREATE TABLE ob3.tp;
 
-# BUG#43549 This portion was disabled because the firing of the event is
-#           non-deterministic.
-#           The results are now made deterministic using wait_condition.inc
-#
-SELECT * FROM ob3.log;
-
-SET GLOBAL EVENT_SCHEDULER=OFF;
-
 --echo
 --echo CREATE DEPENDENT TESTS
 --echo ======================
@@ -593,7 +556,6 @@ SELECT * FROM ob2.v21;
 # Use dependency tests. Check the response of backup database operation,
 # if use-dependent objects are missing.
 #
-SET GLOBAL EVENT_SCHEDULER=ON;
 --echo
 --echo # Drop user(tom) in which view(ob1.v11) depends.
 # Because of bug#43168, BACKUP will fail with ER_BACKUP_CATALOG_ADD_VIEW when
@@ -620,7 +582,6 @@ CREATE USER tom@'%';
 GRANT ALL ON *.* TO tom@'%';
 # ======
 
-SET GLOBAL EVENT_SCHEDULER=OFF;
 --echo
 --echo # Drop user in which procedure(ob2.p21) and function(ob2.f21)depends.
 
@@ -637,7 +598,7 @@ CREATE USER tom@'%';
 GRANT ALL ON *.* TO tom@'%';
 
 --echo
---echo # Drop user in which trigger(trg32) and event(ev) depends on.
+--echo # Drop user in which trigger(trg32) depends on.
 
 Let $db=ob3;
 Let $drop_object= USER tom@'%';
@@ -648,25 +609,14 @@ SHOW CREATE TRIGGER ob3.trg32;
 INSERT INTO ob3.t32 VALUES(98);
 SELECT * FROM ob1.t14;
 SELECT * FROM ob1.t11;
-SET GLOBAL EVENT_SCHEDULER=ON;
---replace_regex /[0000-9999]+/#/
-CALL ob3.fire('no user');
-sleep 5;
-SET GLOBAL EVENT_SCHEDULER=OFF;
 CREATE USER tom@'%';
 GRANT ALL ON *.* TO tom@'%';
 INSERT INTO ob3.t32 VALUES(97);
 SELECT * FROM ob3.t32;
 SELECT * FROM ob1.t14;
 
-# BUG#43549 This portion was disabled because the firing of the event is
-#           non-deterministic.
-#           The results are now made deterministic using wait_condition.inc
-SELECT * FROM ob3.log;
-
 # Note that we will see both values 97 and 98. trg32 will not update value
 # in t14 if user is not present.
-# Note that if user is not present, event will not be fired
 
 --echo
 --echo # Drop the procedure(ob1.p11) in which procedure(ob2.p23) depends
@@ -740,23 +690,6 @@ DROP VIEW ob1.vp1;
 --error ER_SP_DOES_NOT_EXIST
 eval CALL ob1.p11();
 
---echo # Event(ob3.ev) that depends on table(ob3.msg)
-
-SET GLOBAL EVENT_SCHEDULER=ON;
-Let $drop_object= TABLE ob3.msg;
-Let $db=ob3;
---source suite/backup/include/objects_dependency_use.inc
---replace_column 4 # 5 # 6 #
---query_vertical SHOW CREATE EVENT ob3.ev;
-# Try to fire event
---error ER_NO_SUCH_TABLE
-CALL ob3.fire('user3 created');
-
-# BUG#43549 This portion was disabled because the firing of the event is
-#           non-deterministic.
-#           The results are now made deterministic using wait_condition.inc
-SELECT * FROM ob3.log;
-
 --echo # Test cleanup section
 
 REVOKE ALL ON *.* FROM tom@'%';
@@ -766,7 +699,6 @@ DROP DATABASE ob1;
 DROP DATABASE ob2;
 DROP DATABASE ob3;
 DROP PROCEDURE test.show_objects;
-SET GLOBAL EVENT_SCHEDULER=OFF;
 --remove_file $bdir/ob.bak
 --remove_file $bdir/ob1.bak
 --remove_file $bdir/ob2.bak

Thread
bzr commit into mysql-6.0-backup branch (hema:2870) Bug#43549Hema Sridharan17 Sep
  • Re: bzr commit into mysql-6.0-backup branch (hema:2870) Bug#43549Charles Bell18 Sep