Patch looks good. OK to push.
Rafal Somla wrote:
> #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)
>
>
--
Jørgen Løland
