bk commit into 5.1 tree (cmiller:1.2160) BUG#17714

[Chad MILLER]

Below is the list of changes that have just been committed into a local
5.1 repository of cmiller. When cmiller does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2160 06/03/08 22:27:00 cmiller@stripped +4 -0
  Added code to mysqldump to dump timed events when instructed to do so, with
  the '-E' or '--events' flag.  (Closes Bug#16853 and Bug#17714.)
  
  
  WARNING:
  
  At present, these tests fail due to b*g number 18078.

  sql/event_timed.cc
    1.46 06/03/08 22:26:48 cmiller@stripped +0 -2
    No longer qualify SHOW CREATE EVENT names with the database name.

  mysql-test/t/mysqldump.test
    1.91 06/03/08 22:26:47 cmiller@stripped +40 -0
    Added a test to create an event, dump it, restore it, add more events, dump
    all of them, and restore all of them.

  mysql-test/r/mysqldump.result
    1.101 06/03/08 22:26:46 cmiller@stripped +37 -0
    Added a test to create an event, dump it, restore it, add more events, dump
    all of them, and restore all of them.

  client/mysqldump.c
    1.230 06/03/08 22:26:45 cmiller@stripped +148 -6
    Added code to dump events, when asked to do so via the --events parameter.
    
    Also cleaned up some surrounding code.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	cmiller
# Host:	calliope.local
# Root:	/Users/cmiller/work/src/mysql-5.1-new__cleanup_mysqldump

--- 1.229/client/mysqldump.c	2006-02-23 10:11:41 -05:00
+++ 1.230/client/mysqldump.c	2006-03-08 22:26:45 -05:00
@@ -95,6 +95,7 @@
                 opt_complete_insert= 0, opt_drop_database= 0,
                 opt_replace_into= 0,
                 opt_dump_triggers= 0, opt_routines=0, opt_tz_utc=1,
+                opt_events= 0,
                 opt_alltspcs=0;
 static ulong opt_max_allowed_packet, opt_net_buffer_length;
 static MYSQL mysql_connection,*sock=0;
@@ -231,6 +232,9 @@
   {"disable-keys", 'K',
    "'/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and '/*!40000 ALTER TABLE tb_name
ENABLE KEYS */; will be put in the output.", (gptr*) &opt_disable_keys,
    (gptr*) &opt_disable_keys, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0},
+  {"events", 'E', "Dump events.",
+     (gptr*) &opt_events, (gptr*) &opt_events, 0, GET_BOOL,
+     NO_ARG, 0, 0, 0, 0, 0, 0},
   {"extended-insert", 'e',
    "Allows utilization of the new, much faster INSERT syntax.",
    (gptr*) &extended_insert, (gptr*) &extended_insert, 0, GET_BOOL, NO_ARG,
@@ -1238,9 +1242,136 @@
   check_io(xml_file);
 }
 
+
+/*
+ create_delimiter
+ Generate a new (null-terminated) string that does not exist in  query 
+ and is therefore suitable for use as a query delimiter.  Store this
+ delimiter in  delimiter_buff .
+ 
+ This is quite simple in that it doesn't even try to parse statements as an
+ interpreter would.  It merely returns a string that is not in the query, which
+ is much more than adequate for constructing a delimiter.
+
+ RETURN
+   ptr to the delimiter  on Success
+   NULL                  on Failure
+*/
+static char *create_delimiter(char *query, char *delimiter_buff, 
+                              int delimiter_max_size) 
+{
+  int proposed_length;
+  char *presence;
+
+  delimiter_buff[0]= ';';  /* start with one semicolon, and */
+
+  for (proposed_length= 2; proposed_length < delimiter_max_size; 
+      delimiter_max_size++) {
+
+    delimiter_buff[proposed_length-1]= ';';  /* add semicolons, until */
+    delimiter_buff[proposed_length]= '\0';
+
+    presence = strstr(query, delimiter_buff);
+    if (presence == NULL) { /* the proposed delimiter is not in the query. */
+       return delimiter_buff;
+    }
+
+  }
+  return NULL;  /* but if we run out of space, return nothing at all. */
+}
+
+
+/*
+  dump_events_for_db
+  -- retrieves list of events for a given db, and prints out
+  the CREATE EVENT statement into the output (the dump).
+
+  RETURN
+    0  Success
+    1  Error
+*/
+static uint dump_events_for_db(char *db)
+{
+  char       query_buff[QUERY_LENGTH];
+  char       db_name_buff[NAME_LEN*2+3], name_buff[NAME_LEN*2+3];
+  char       *event_name;
+  char       delimiter[QUERY_LENGTH], *delimit_test;
+  FILE       *sql_file= md_result_file;
+  MYSQL_RES  *event_res, *event_list_res;
+  MYSQL_ROW  row, event_list_row;
+  DBUG_ENTER("dump_events_for_db");
+  DBUG_PRINT("enter", ("db: '%s'", db));
+
+  mysql_real_escape_string(sock, db_name_buff, db, strlen(db));
+
+  /* nice comments */
+  if (opt_comments)
+    fprintf(sql_file, "\n--\n-- Dumping events for database '%s'\n--\n", db);
+
+  /*
+    not using "mysql_query_with_error_report" because we may have not
+    enough privileges to lock mysql.events.
+  */
+  if (lock_tables)
+    mysql_query(sock, "LOCK TABLES mysql.event READ");
+
+  if (mysql_query_with_error_report(sock, &event_list_res, "show events"))
+  {
+    safe_exit(EX_MYSQLERR);
+    DBUG_RETURN(0);
+  }
+
+  strcpy(delimiter, ";");
+  if (mysql_num_rows(event_list_res) > 0)
+  {
+    while ((event_list_row= mysql_fetch_row(event_list_res)) != NULL)
+    {
+      event_name= quote_name(event_list_row[1], name_buff, 0);
+      DBUG_PRINT("info", ("retrieving CREATE EVENT for %s", name_buff));
+      my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE EVENT %s", 
+          event_name);
+
+      if (mysql_query_with_error_report(sock, &event_res, query_buff))
+        DBUG_RETURN(1);
+
+      while ((row= mysql_fetch_row(event_res)) != NULL)
+      {
+        /*
+          if the user has EXECUTE privilege he can see event names, but not the
+          event body!
+        */
+        if (strlen(row[2]) != 0)
+        {
+          if (opt_drop)
+            fprintf(sql_file, "/*!50106 DROP EVENT IF EXISTS %s */%s\n", 
+                event_name, delimiter);
+
+          delimit_test= create_delimiter(row[2], delimiter, sizeof(delimiter)); 
+          if (delimit_test == NULL) {
+            fprintf(stderr, "%s: Warning: Can't dump event '%s'\n", 
+                event_name, my_progname);
+            DBUG_RETURN(1);
+          }
+
+          fprintf(sql_file, "DELIMITER %s\n", delimiter);
+          fprintf(sql_file, "/*!50106 %s */ %s\n", row[2], delimiter);
+        }
+      } /* end of event printing */
+    } /* end of list of events */
+    fprintf(sql_file, "DELIMITER ;\n");
+    mysql_free_result(event_res);
+  }
+  mysql_free_result(event_list_res);
+
+  if (lock_tables)
+    VOID(mysql_query_with_error_report(sock, 0, "UNLOCK TABLES"));
+  DBUG_RETURN(0);
+}
+
+
 /*
   dump_routines_for_db
-  -- retrievs list of routines for a given db, and prints out
+  -- retrieves list of routines for a given db, and prints out
   the CREATE PROCEDURE definition into the output (the dump).
 
   This function has logic to print the appropriate syntax depending on whether
@@ -1253,7 +1384,7 @@
 
 static uint dump_routines_for_db(char *db)
 {
-  char       query_buff[512];
+  char       query_buff[QUERY_LENGTH];
   const char *routine_type[]= {"FUNCTION", "PROCEDURE"};
   char       db_name_buff[NAME_LEN*2+3], name_buff[NAME_LEN*2+3];
   char       *routine_name;
@@ -1294,9 +1425,9 @@
 
       while ((routine_list_row= mysql_fetch_row(routine_list_res)))
       {
+        routine_name= quote_name(routine_list_row[1], name_buff, 0);
         DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type[i],
                             name_buff));
-        routine_name= quote_name(routine_list_row[1], name_buff, 0);
         my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s",
                     routine_type[i], routine_name);
 
@@ -1364,7 +1495,7 @@
   char	     *result_table, *opt_quoted_table;
   const char *insert_option;
   char	     name_buff[NAME_LEN+3],table_buff[NAME_LEN*2+3];
-  char	     table_buff2[NAME_LEN*2+3], query_buff[512];
+  char	     table_buff2[NAME_LEN*2+3], query_buff[QUERY_LENGTH];
   FILE       *sql_file = md_result_file;
   int        len;
   MYSQL_RES  *result;
@@ -1840,7 +1971,7 @@
 {
   char	     *result_table;
   char	     name_buff[NAME_LEN*4+3], table_buff[NAME_LEN*2+3];
-  char       query_buff[512];
+  char       query_buff[QUERY_LENGTH];
   uint old_opt_compatible_mode=opt_compatible_mode;
   FILE       *sql_file = md_result_file;
   MYSQL_RES  *result;
@@ -2500,7 +2631,6 @@
 {
   MYSQL_ROW row;
   MYSQL_RES *tableres;
-  int result=0;
   char buf[FN_REFLEN];
   int first;
 
@@ -2798,6 +2928,12 @@
         dump_triggers_for_table(table, database);
     }
   }
+  if (opt_events && !opt_xml &&
+      mysql_get_server_version(sock) >= 50106)
+  {
+    DBUG_PRINT("info", ("Dumping events for database %s", database));
+    dump_events_for_db(database);
+  }
   if (opt_routines && !opt_xml &&
       mysql_get_server_version(sock) >= 50009)
   {
@@ -3007,6 +3143,12 @@
       table_name= hash_element(&dump_tables, i);
       get_view_structure(table_name, db);
     }
+  }
+  if (opt_events && !opt_xml &&
+      mysql_get_server_version(sock) >= 50106)
+  {
+    DBUG_PRINT("info", ("Dumping events for database %s", db));
+    dump_events_for_db(db);
   }
   /* obtain dump of routines (procs/functions) */
   if (opt_routines  && !opt_xml &&

--- 1.45/sql/event_timed.cc	2006-03-02 15:01:56 -05:00
+++ 1.46/sql/event_timed.cc	2006-03-08 22:26:48 -05:00
@@ -1063,8 +1063,6 @@
     DBUG_RETURN(EVEX_MICROSECOND_UNSUP);
 
   buf->append(STRING_WITH_LEN("CREATE EVENT "));
-  append_identifier(thd, buf, dbname.str, dbname.length);
-  buf->append(STRING_WITH_LEN("."));
   append_identifier(thd, buf, name.str, name.length);
 
   buf->append(STRING_WITH_LEN(" ON SCHEDULE "));

--- 1.100/mysql-test/r/mysqldump.result	2006-03-06 05:35:33 -05:00
+++ 1.101/mysql-test/r/mysqldump.result	2006-03-08 22:26:46 -05:00
@@ -2907,3 +2907,40 @@
 drop table t1;
 drop table t2;
 drop table words2;
+create database first;
+use first;
+set time_zone = 'UTC';
+create event ee1 on schedule at '2035-12-31 20:01:23' do set @a=5;
+show events;
+Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
+first	ee1	root@localhost	ONE TIME	2035-12-31 20:01:23	NULL	NULL	NULL	NULL	ENABLED
+show create event ee1;
+Event	sql_mode	Create Event
+ee1		CREATE EVENT `ee1` ON SCHEDULE AT '2035-12-31 20:01:23' ON COMPLETION NOT PRESERVE
ENABLE DO set @a=5
+drop database first;
+create database second;
+use second;
+show events;
+Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
+second	ee1	root@localhost	ONE TIME	2035-12-31 20:01:23	NULL	NULL	NULL	NULL	ENABLED
+show create event ee1;
+Event	sql_mode	Create Event
+ee1	NO_AUTO_VALUE_ON_ZERO	CREATE EVENT `ee1` ON SCHEDULE AT '2035-12-31 20:01:23' ON
COMPLETION NOT PRESERVE ENABLE DO set @a=5
+create event ee2 on schedule at '2018-12-31 21:01:23' do set @a=5;
+create event ee3 on schedule at '2030-12-31 22:01:23' do set @a=5;
+show events;
+Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
+second	ee1	root@localhost	ONE TIME	2035-12-31 20:01:23	NULL	NULL	NULL	NULL	ENABLED
+second	ee2	root@localhost	ONE TIME	2018-12-31 21:01:23	NULL	NULL	NULL	NULL	ENABLED
+second	ee3	root@localhost	ONE TIME	2030-12-31 22:01:23	NULL	NULL	NULL	NULL	ENABLED
+drop database second;
+create database third;
+use third;
+show events;
+Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
+third	ee1	root@localhost	ONE TIME	2035-12-31 20:01:23	NULL	NULL	NULL	NULL	ENABLED
+third	ee2	root@localhost	ONE TIME	2018-12-31 21:01:23	NULL	NULL	NULL	NULL	ENABLED
+third	ee3	root@localhost	ONE TIME	2030-12-31 22:01:23	NULL	NULL	NULL	NULL	ENABLED
+drop database third;
+set time_zone = 'SYSTEM';
+use test;

--- 1.90/mysql-test/t/mysqldump.test	2006-02-28 17:22:05 -05:00
+++ 1.91/mysql-test/t/mysqldump.test	2006-03-08 22:26:47 -05:00
@@ -1166,3 +1166,43 @@
 drop table t2;
 
 drop table words2;
+
+#
+# BUG# 16853: mysqldump doesn't show events
+#
+create database first;
+use first;
+set time_zone = 'UTC';
+
+## prove one works
+create event ee1 on schedule at '2035-12-31 20:01:23' do set @a=5;
+show events;
+show create event ee1;
+--exec $MYSQL_DUMP --events first > $MYSQLTEST_VARDIR/tmp/bug16853-1.sql
+drop database first;
+
+create database second;
+use second;
+--exec $MYSQL second < $MYSQLTEST_VARDIR/tmp/bug16853-1.sql
+show events;
+show create event ee1;
+
+## prove three works
+# start with one from the previous restore
+create event ee2 on schedule at '2018-12-31 21:01:23' do set @a=5;
+create event ee3 on schedule at '2030-12-31 22:01:23' do set @a=5;
+show events;
+--exec $MYSQL_DUMP --events second > $MYSQLTEST_VARDIR/tmp/bug16853-2.sql
+drop database second;
+
+create database third;
+use third;
+--exec $MYSQL third < $MYSQLTEST_VARDIR/tmp/bug16853-2.sql
+show events;
+drop database third;
+
+# revert back to normal settings
+set time_zone = 'SYSTEM';
+use test;
+
+#####