bk commit into 5.1 tree (cmiller:1.2174) 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.2174 06/02/25 00:41:29 cmiller@zippy.(none) +4 -0
  Added a mysqldump parameter, --events, and code to make it dump events from 
  the database. (Bug#16853)
  
  Tweak SHOW CREATE EVENT to yield *unqualified* event names, so that events can 
  be read into any database. (Bug#17714)

  sql/event_timed.cc
    1.39 06/02/25 00:35:38 cmiller@zippy.(none) +0 -2
    Don't qualify the event name from SHOW CREATE EVENT with the database name, 
    so the output can be read into another database.  (Bug#17714)

  mysql-test/t/mysqldump.test
    1.90 06/02/25 00:35:38 cmiller@zippy.(none) +20 -0
    Added test for dumping events via mysqldump (Bug#16853), and a check for 
    reading that dump back in.

  mysql-test/r/mysqldump.result
    1.99 06/02/25 00:35:38 cmiller@zippy.(none) +14 -0
    Added test for dumping events via mysqldump (Bug#16853), and a check for 
    reading that dump back in.

  client/mysqldump.c
    1.230 06/02/25 00:35:38 cmiller@zippy.(none) +108 -3
    Added new command-line parameter, --events, which turns on dumping of 
    timed events.  (Bug#16853)
    
    minor: Fixed possible cosmetic bug (out-of-order statements) in comment 
    for dumping routines.
    
    minor: Removed extraneous variable declaration that caused compiler 
    warning.

# 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:	zippy.(none)
# Root:	/home/cmiller/work/mysql/2006007/mysql-5.1-new__bug16853

--- 1.229/client/mysqldump.c	2006-02-23 10:11:41 -05:00
+++ 1.230/client/mysqldump.c	2006-02-25 00:35:38 -05:00
@@ -95,7 +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_alltspcs=0;
+                opt_alltspcs=0, opt_events=0;
 static ulong opt_max_allowed_packet, opt_net_buffer_length;
 static MYSQL mysql_connection,*sock=0;
 static my_bool insert_pat_inited=0;
@@ -231,6 +231,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 event items.",
+     (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,
@@ -1239,6 +1242,96 @@
 }
 
 /*
+  dump_events_for_db
+  -- retrievs list of events for a given db, and prints out
+  the CREATE EVENT definition into the output (the dump).
+
+  RETURN
+    0  Success
+    1  Error
+*/
+static uint dump_events_for_db(char *db)
+{
+  char       query_buff[512];
+  char       db_name_buff[NAME_LEN*2+3], name_buff[NAME_LEN*2+3];
+  char       *event_name = NULL;
+  FILE       *sql_file = md_result_file;
+  MYSQL_RES  *event_res = NULL, *event_list_res = NULL;
+  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.proc.
+  */
+  if (lock_tables)
+    mysql_query(sock, "LOCK TABLES mysql.proc READ");
+
+  if (mysql_query_with_error_report(sock, &event_list_res, "show events"))
+  {
+    safe_exit(EX_MYSQLERR);
+    DBUG_RETURN(0);
+  }
+
+  fprintf(sql_file, "DELIMITER ;;\n");
+
+  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 see routine names, but NOT the
+          routine body of other routines that are not the creator of!
+        */
+        DBUG_PRINT("info",("length of body for %s row[2] '%s' is %d",
+                           event_name, row[2], strlen(row[2])));
+        if (strlen(row[2]) != 0)
+        {
+          if (opt_drop)
+            fprintf(sql_file, "/*!50106 DROP EVENT IF EXISTS %s */;;\n", event_name);
+          /*
+            we need to change sql_mode only for the CREATE
+            PROCEDURE/FUNCTION otherwise we may need to re-quote routine_name
+          */;
+          fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/;;\n",
+                  row[1] /* sql_mode */);
+          fprintf(sql_file, "/*!50003 %s */;;\n", row[2]);
+          fprintf(sql_file,
+                  "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/"
+                  ";;\n");
+        }
+      } /* end of event printing */
+    } /* end of list of events */
+    mysql_free_result(event_res);
+  }
+  mysql_free_result(event_list_res);
+
+  /* set the delimiter back to ';' */
+  fprintf(sql_file, "DELIMITER ;\n");
+
+  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
   the CREATE PROCEDURE definition into the output (the dump).
@@ -1294,9 +1387,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);
 
@@ -2500,7 +2593,6 @@
 {
   MYSQL_ROW row;
   MYSQL_RES *tableres;
-  int result=0;
   char buf[FN_REFLEN];
   int first;
 
@@ -2798,6 +2890,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 +3105,13 @@
       table_name= hash_element(&dump_tables, i);
       get_view_structure(table_name, db);
     }
+  }
+  /* obtain dump of events */
+  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.38/sql/event_timed.cc	2006-02-24 08:02:14 -05:00
+++ 1.39/sql/event_timed.cc	2006-02-25 00:35:38 -05:00
@@ -1030,8 +1030,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.98/mysql-test/r/mysqldump.result	2006-02-23 04:49:36 -05:00
+++ 1.99/mysql-test/r/mysqldump.result	2006-02-25 00:35:38 -05:00
@@ -2718,6 +2718,20 @@
 drop trigger tr1;
 drop trigger tr2;
 drop table t1, t2;
+create database db2;
+use db2;
+create event ee on schedule every 1 year do set @a=5;
+show events;
+Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
+db2	ee	root@localhost	RECURRING	NULL	1	YEAR	0000-00-00 00:00:00	0000-00-00
00:00:00	ENABLED
+drop database db2;
+create database db1;
+use db1;
+show events;
+Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
+db1	ee	root@localhost	RECURRING	NULL	1	YEAR	0000-00-00 00:00:00	0000-00-00
00:00:00	ENABLED
+drop database db1;
+use test;
 /*!50003 CREATE FUNCTION `f`() RETURNS bigint(20)
 return 42 */|
 /*!50003 CREATE PROCEDURE `p`()

--- 1.89/mysql-test/t/mysqldump.test	2006-02-24 07:56:35 -05:00
+++ 1.90/mysql-test/t/mysqldump.test	2006-02-25 00:35:38 -05:00
@@ -1111,6 +1111,26 @@
 drop trigger tr2;
 drop table t1, t2;
 
+#
+# BUG# 16853: mysqldump doesn't show events
+#
+# Add test that we can read them back in, too.
+#
+create database db2;
+use db2;
+create event ee on schedule every 1 year do set @a=5;
+show events;
+--exec $MYSQL_DUMP --events db2 > $MYSQLTEST_VARDIR/tmp/bug16853.sql
+drop database db2;
+
+# create db1 and reload dump
+create database db1;
+use db1;
+--exec $MYSQL db1 < $MYSQLTEST_VARDIR/tmp/bug16853.sql
+show events;
+drop database db1;
+
+use test;
 
 #
 # Bug#14857 Reading dump files with single statement stored routines fails.