bzr push into mysql-6.0-backup branch (rsomla:2639)

[Rafal Somla]

 2639 Rafal Somla	2008-06-26 [merge]
      Auto merge.
modified:
  sql/backup/stream.cc
  sql/sql_yacc.yy

=== 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-06-25 13:30:04 +0000
+++ b/sql/backup/backup_kernel.h	2008-06-26 12:10:46 +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-06-25 13:30:04 +0000
+++ b/sql/backup/kernel.cc	2008-06-26 12:10:46 +0000
@@ -772,6 +772,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.
@@ -833,6 +908,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);
@@ -1439,6 +1529,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)