#At file:///ext/mysql/bzr/backup/wl4326/
2637 Rafal Somla 2008-06-25
WL#4326 (Disable events and triggers during restore)
This patch implements design described in the WL. The events and triggers are not created
when the metadata section of backup image is read but stored in a restore catalogue.
They are re-created after all other objects and all table data is restored.
added:
mysql-test/r/backup_triggers_and_events.result
mysql-test/t/backup_triggers_and_events.test
modified:
sql/backup/backup_kernel.h
sql/backup/data_backup.cc
sql/backup/kernel.cc
per-file messages:
mysql-test/t/backup_triggers_and_events.test
New test checking behaviour of events and triggers during restore process.
sql/backup/backup_kernel.h
Added new helper method restore_triggers_and_events().
sql/backup/data_backup.cc
Added synchronization point in restore_table_data(), after all restore drivers have finished
their job and before they are shut down.
sql/backup/kernel.cc
- Implementation of Backup_restore_ctx::restore_triggers_and_events()
- In do_restore(), re-create triggers and events after table data has been restored.
- In bcat_create_item() (called when metadata is read) do not re-create events and triggers,
only store them in the catalogue.
=== added file 'mysql-test/r/backup_triggers_and_events.result'
--- a/mysql-test/r/backup_triggers_and_events.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/backup_triggers_and_events.result 2008-06-25 10:03:42 +0000
@@ -0,0 +1,202 @@
+SET GLOBAL event_scheduler=off;
+SET DEBUG_SYNC = 'RESET';
+Creating log table.
+DROP TABLE IF EXISTS test.logt;
+CREATE TABLE test.logt(ts timestamp, db char(8), msg text);
+Creating database db and its objects.
+DROP DATABASE IF EXISTS db;
+CREATE DATABASE db;
+USE db;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6);
+CREATE EVENT ev ON SCHEDULE EVERY 1 second DO
+BEGIN
+INSERT INTO test.logt(db, msg) VALUES ('db','Db event fired!');
+END;
+||
+CREATE PROCEDURE trg_msg(a int)
+BEGIN
+INSERT INTO test.logt(db, msg) VALUES ('db','Db trigger fired!');
+END;
+||
+CREATE TRIGGER after_ins AFTER INSERT ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+CREATE TRIGGER after_upd AFTER UPDATE ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+CREATE TRIGGER after_del AFTER DELETE ON t1 FOR EACH ROW
+CALL trg_msg(OLD.a);
+||
+CREATE TRIGGER before_ins BEFORE INSERT ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+CREATE TRIGGER before_upd BEFORE UPDATE ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+CREATE TRIGGER before_del BEFORE DELETE ON t1 FOR EACH ROW
+CALL trg_msg(OLD.a);
+||
+USE test||
+DROP EVENT IF EXISTS ev||
+Warnings:
+Note 1305 Event ev does not exist
+DROP TABLE IF EXISTS t1||
+Warnings:
+Note 1051 Unknown table 't1'
+DROP TRIGGER IF EXISTS trg||
+Warnings:
+Note 1360 Trigger does not exist
+CREATE EVENT ev ON SCHEDULE EVERY 1 second DO
+BEGIN
+INSERT INTO test.logt(db, msg) VALUES ('test','Test event fired!');
+END;
+||
+CREATE TABLE t1 (a int)||
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+INSERT INTO test.logt(db, msg) VALUES ('test','Test trigger fired');
+END;
+||
+Backing-up database db and dropping it.
+BACKUP DATABASE db TO 'db.bak';
+backup_id
+#
+DROP DATABASE db;
+Enabling event scheduler.
+SET GLOBAL event_scheduler=on;
+con1: clearing log table and starting RESTORE operation.
+con1: RESTORE will pause after restoring table data.
+SET DEBUG_SYNC = 'restore_table_data_before_end SIGNAL waiting WAIT_FOR continue';
+DELETE FROM test.logt;
+RESTORE FROM 'db.bak';
+SELECT now() INTO @start;
+con2: checking that there are no triggers and events at the end of RESTORE execution.
+SET DEBUG_SYNC = 'now WAIT_FOR waiting';
+SHOW TRIGGERS FROM db;
+SHOW EVENTS IN db;
+con2: activating trigger in test database.
+INSERT INTO test.t1 VALUES (1);
+con2: ensuring that RESTORE takes at least 3 secs.
+SET DEBUG_SYNC = 'now SIGNAL continue';
+con1: finishing RESTORE operation.
+backup_id
+#
+SET GLOBAL event_scheduler=off;
+con2: checking that RESTORE took more than 2 secs.
+SELECT timediff(now(),@start) > 2;
+timediff(now(),@start) > 2
+1
+Checking that objects have been restored.
+USE db;
+SHOW TABLES IN db;
+Tables_in_db
+t1
+SELECT count(*) FROM db.t1;
+count(*)
+7
+SHOW TRIGGERS FROM db;
+Trigger before_ins
+Event INSERT
+Table t1
+Statement CALL trg_msg(NEW.a)
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client #
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger after_ins
+Event INSERT
+Table t1
+Statement CALL trg_msg(NEW.a)
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client #
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger before_upd
+Event UPDATE
+Table t1
+Statement CALL trg_msg(NEW.a)
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client #
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger after_upd
+Event UPDATE
+Table t1
+Statement CALL trg_msg(NEW.a)
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client #
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger before_del
+Event DELETE
+Table t1
+Statement CALL trg_msg(OLD.a)
+Timing BEFORE
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client #
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Trigger after_del
+Event DELETE
+Table t1
+Statement CALL trg_msg(OLD.a)
+Timing AFTER
+Created NULL
+sql_mode
+Definer root@localhost
+character_set_client #
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW EVENTS IN db;
+Db db
+Name ev
+Definer root@localhost
+Time zone SYSTEM
+Type RECURRING
+Execute at NULL
+Interval value 1
+Interval field SECOND
+Starts #
+Ends NULL
+Status ENABLED
+Originator 1
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Checking that no db event or trigger fired during RESTORE.
+SELECT * FROM test.logt WHERE db = 'db' AND timediff(ts,@start) < 2;
+ts db msg
+Checking that test event and trigger could fire.
+SELECT count(*) > 0 FROM test.logt
+WHERE db = 'test'
+AND msg LIKE '%trigger fired%'
+AND timediff(ts,@start) < 2;
+count(*) > 0
+1
+SELECT count(*) > 0 FROM test.logt
+WHERE db = 'test'
+AND msg LIKE '%event fired%'
+AND timediff(ts,@start) < 2;
+count(*) > 0
+1
+Cleaning up.
+DROP EVENT test.ev;
+DROP TRIGGER test.trg;
+DROP TABLE test.logt;
+DROP TABLE test.t1;
+DROP DATABASE db;
=== added file 'mysql-test/t/backup_triggers_and_events.test'
--- a/mysql-test/t/backup_triggers_and_events.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/backup_triggers_and_events.test 2008-06-25 10:03:42 +0000
@@ -0,0 +1,214 @@
+--source include/have_debug_sync.inc
+--source include/not_embedded.inc
+
+# This test checks that re-created events or triggers are not fired during
+# RESTORE operation.
+#
+# Author: Rafal Somla
+
+--disable_warnings
+--error 0,1
+--remove_file $MYSQL_TEST_DIR/var/master-data/db.bak
+--enable_warnings
+
+SET GLOBAL event_scheduler=off;
+SET DEBUG_SYNC = 'RESET';
+
+# We need a separate connection to measure timing for RESTORE command. This is
+# because of BUG#35806: time stops in a thread executing RESTORE command.
+
+connect(con1, localhost, root,,);
+connect(con2, localhost, root,,);
+
+--connection con1
+
+# Events and triggers will insert entries into a log table so that we can see
+# if they have fired.
+
+--echo Creating log table.
+
+--disable_warnings
+DROP TABLE IF EXISTS test.logt;
+--enable_warnings
+
+CREATE TABLE test.logt(ts timestamp, db char(8), msg text);
+
+--echo Creating database db and its objects.
+
+--disable_warnings
+DROP DATABASE IF EXISTS db;
+--enable_warnings
+
+CREATE DATABASE db;
+USE db;
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6);
+
+delimiter ||;
+
+CREATE EVENT ev ON SCHEDULE EVERY 1 second DO
+BEGIN
+ INSERT INTO test.logt(db, msg) VALUES ('db','Db event fired!');
+END;
+||
+
+CREATE PROCEDURE trg_msg(a int)
+BEGIN
+ INSERT INTO test.logt(db, msg) VALUES ('db','Db trigger fired!');
+END;
+||
+
+CREATE TRIGGER after_ins AFTER INSERT ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+
+CREATE TRIGGER after_upd AFTER UPDATE ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+
+CREATE TRIGGER after_del AFTER DELETE ON t1 FOR EACH ROW
+CALL trg_msg(OLD.a);
+||
+
+CREATE TRIGGER before_ins BEFORE INSERT ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+
+CREATE TRIGGER before_upd BEFORE UPDATE ON t1 FOR EACH ROW
+CALL trg_msg(NEW.a);
+||
+
+CREATE TRIGGER before_del BEFORE DELETE ON t1 FOR EACH ROW
+CALL trg_msg(OLD.a);
+||
+
+# Create an event and trigger in test database to see that they are not
+# affected by RESTORE of another database.
+
+USE test||
+
+--disable_warnigns
+DROP EVENT IF EXISTS ev||
+DROP TABLE IF EXISTS t1||
+DROP TRIGGER IF EXISTS trg||
+--enable_warnings
+
+CREATE EVENT ev ON SCHEDULE EVERY 1 second DO
+BEGIN
+ INSERT INTO test.logt(db, msg) VALUES ('test','Test event fired!');
+END;
+||
+
+CREATE TABLE t1 (a int)||
+
+CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+ INSERT INTO test.logt(db, msg) VALUES ('test','Test trigger fired');
+END;
+||
+
+delimiter ;||
+
+--echo Backing-up database db and dropping it.
+
+--replace_column 1 #
+BACKUP DATABASE db TO 'db.bak';
+DROP DATABASE db;
+
+--echo Enabling event scheduler.
+SET GLOBAL event_scheduler=on;
+
+--connection con1
+
+--echo con1: clearing log table and starting RESTORE operation.
+--echo con1: RESTORE will pause after restoring table data.
+
+# Synchronization point 'restore_table_data_before_end' is inside RESTORE code,
+# after restore drivers have finished their job but before they have been shoot
+# down.
+
+SET DEBUG_SYNC = 'restore_table_data_before_end SIGNAL waiting WAIT_FOR continue';
+DELETE FROM test.logt;
+--send RESTORE FROM 'db.bak'
+
+--connection con2
+
+# Record the time when RESTORE has started.
+SELECT now() INTO @start;
+
+--echo con2: checking that there are no triggers and events at the end of RESTORE execution.
+
+# Wait until RESTORE reaches the moment when all table data is restored.
+SET DEBUG_SYNC = 'now WAIT_FOR waiting';
+# There should be no triggers and no events at this moment (they are created
+# after table data is restored)
+--query_vertical SHOW TRIGGERS FROM db
+--query_vertical SHOW EVENTS IN db
+
+--echo con2: activating trigger in test database.
+INSERT INTO test.t1 VALUES (1);
+
+--echo con2: ensuring that RESTORE takes at least 3 secs.
+
+# This is so that db.ev event has chance to fire if it is not correctly handled
+# (e.g. enabled during table data restore).
+--sleep 3
+SET DEBUG_SYNC = 'now SIGNAL continue';
+
+--connection con1
+
+--echo con1: finishing RESTORE operation.
+--replace_column 1 #
+--reap
+SET GLOBAL event_scheduler=off;
+
+--connection con2
+
+-- echo con2: checking that RESTORE took more than 2 secs.
+
+SELECT timediff(now(),@start) > 2;
+
+--echo Checking that objects have been restored.
+
+USE db;
+
+SHOW TABLES IN db;
+SELECT count(*) FROM db.t1;
+--replace_column 9 #
+--query_vertical SHOW TRIGGERS FROM db
+--replace_column 9 #
+--query_vertical SHOW EVENTS IN db
+
+--echo Checking that no db event or trigger fired during RESTORE.
+
+# There should be no entries in the log table from the time when RESTORE
+# was running (but there could be entries inserted by event firing *after*
+# RESTORE has completed). We know that RESTORE took at least 3 sec and we
+# take 2 sec window form the beginning of the operation. This is enough
+# to see db.ev in case it fired during RESTORE operation (this event is sheduled
+# to fire every second).
+
+SELECT * FROM test.logt WHERE db = 'db' AND timediff(ts,@start) < 2;
+
+--echo Checking that test event and trigger could fire.
+
+# Checking that the trigger has fired.
+SELECT count(*) > 0 FROM test.logt
+WHERE db = 'test'
+AND msg LIKE '%trigger fired%'
+AND timediff(ts,@start) < 2;
+
+# Checking that the event has fired.
+SELECT count(*) > 0 FROM test.logt
+WHERE db = 'test'
+AND msg LIKE '%event fired%'
+AND timediff(ts,@start) < 2;
+
+--echo Cleaning up.
+DROP EVENT test.ev;
+DROP TRIGGER test.trg;
+DROP TABLE test.logt;
+DROP TABLE test.t1;
+DROP DATABASE db;
+--remove_file $MYSQL_TEST_DIR/var/master-data/db.bak
=== modified file 'sql/backup/backup_kernel.h'
--- a/sql/backup/backup_kernel.h 2008-05-05 15:06:40 +0000
+++ b/sql/backup/backup_kernel.h 2008-06-25 10:03:42 +0000
@@ -113,6 +113,7 @@ class Backup_restore_ctx: public backup:
int prepare(LEX_STRING location);
void disable_fkey_constraints();
+ int restore_triggers_and_events();
friend class Backup_info;
friend class Restore_info;
=== modified file 'sql/backup/data_backup.cc'
--- a/sql/backup/data_backup.cc 2008-06-05 12:26:31 +0000
+++ b/sql/backup/data_backup.cc 2008-06-25 10:03:42 +0000
@@ -1569,6 +1569,8 @@ int restore_table_data(THD*, Restore_inf
DBUG_PRINT("restore",("state is %d", state));
}
+ DEBUG_SYNC(::current_thd, "restore_table_data_before_end");
+
{ // Shutting down drivers
String bad_drivers;
=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc 2008-05-21 10:45:55 +0000
+++ b/sql/backup/kernel.cc 2008-06-25 10:03:42 +0000
@@ -769,6 +769,81 @@ int Backup_restore_ctx::do_backup()
DBUG_RETURN(0);
}
+/**
+ Create all triggers and events from restore catalogue.
+
+ This helper method iterates over all triggers and events stored in the
+ restore catalogue and creates them. When metadata section of the backup image
+ is read, trigger and event objects are materialized and stored in the
+ catalogue but they are not executed then (see @c bcat_create_item()).
+ This method can be used to re-create the corresponding server objects after
+ all other objects and table data have been restored.
+
+ Note that we first restore all triggers and then the events.
+
+ @returns 0 on success, error code otherwise.
+*/
+int Backup_restore_ctx::restore_triggers_and_events()
+{
+ using namespace backup;
+
+ DBUG_ASSERT(m_catalog);
+
+ Image_info::Iterator *dbit= m_catalog->get_dbs();
+ Image_info::Obj *obj;
+ List<Image_info::Obj> events;
+ Image_info::Obj::describe_buf buf;
+
+ DBUG_ENTER("restore_triggers_and_events");
+
+ // create all trigers and collect events in the events list
+
+ while ((obj= (*dbit)++))
+ {
+ Image_info::Iterator *it=
+ m_catalog->get_db_objects(*static_cast<Image_info::Db*>(obj));
+
+ while ((obj= (*it)++))
+ switch (obj->type()) {
+
+ case BSTREAM_IT_EVENT:
+ DBUG_ASSERT(obj->m_obj_ptr);
+ events.push_back(obj);
+ break;
+
+ case BSTREAM_IT_TRIGGER:
+ DBUG_ASSERT(obj->m_obj_ptr);
+ if (obj->m_obj_ptr->execute(m_thd))
+ {
+ delete it;
+ delete dbit;
+ fatal_error(ER_BACKUP_CANT_RESTORE_TRIGGER,obj->describe(buf));
+ DBUG_RETURN(m_error);
+ }
+ break;
+
+ default: break;
+ }
+
+ delete it;
+ }
+
+ delete dbit;
+
+ // now create all events
+
+ List_iterator<Image_info::Obj> it(events);
+ Image_info::Obj *ev;
+
+ while ((ev= it++))
+ if (ev->m_obj_ptr->execute(m_thd))
+ {
+ fatal_error(ER_BACKUP_CANT_RESTORE_EVENT,ev->describe(buf));
+ DBUG_RETURN(m_error);
+ };
+
+ DBUG_RETURN(0);
+}
/**
Restore objects saved in backup image.
@@ -830,6 +905,21 @@ int Backup_restore_ctx::do_restore()
DBUG_RETURN(m_error);
}
+ /*
+ Re-create all triggers and events (it was not done in @c bcat_create_item()).
+ */
+
+ if (restore_triggers_and_events())
+ DBUG_RETURN(ER_BACKUP_RESTORE);
+
+ /*
+ FIXME: this call is here because object services doesn't clean the
+ statement execution context properly, which leads to assertion failure.
+ It should be fixed inside object services implementation and then the
+ following line should be removed.
+ */
+ m_thd->main_da.reset_diagnostics_area();
+
report_stats_post(info);
DBUG_RETURN(0);
@@ -1436,6 +1526,22 @@ int bcat_create_item(st_bstream_image_he
return BSTREAM_ERROR;
}
+ /*
+ If the item we are creating is an event or trigger, we don't execute it
+ yet. It will be done in @c Backup_restore_ctx::do_restore() after table
+ data has been restored.
+ */
+
+ switch (item->type) {
+
+ case BSTREAM_IT_EVENT:
+ case BSTREAM_IT_TRIGGER:
+ return BSTREAM_OK;
+
+ default: break;
+
+ }
+
// If we are to create a tablespace, first check if it already exists.
if (item->type == BSTREAM_IT_TABLESPACE)
