MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Chad MILLER Date:February 27 2006 6:40pm
Subject:bk commit into 5.1 tree (cmiller:1.2185) BUG#17714
View as plain text  
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.2185 06/02/27 13:40:46 cmiller@zippy.(none) +4 -0
  Added a mysqldump parameter, --events, and code to make it dump events from 
  the database. (Bug#16853)
    
  Change 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/27 13:38:50 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/27 13:38:50 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/27 13:38:50 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/27 13:38:50 cmiller@zippy.(none) +157 -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_pass2

--- 1.229/client/mysqldump.c	2006-02-23 10:11:41 -05:00
+++ 1.230/client/mysqldump.c	2006-02-27 13:38:50 -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,
@@ -1238,6 +1241,145 @@
   check_io(xml_file);
 }
 
+
+/*
+ * create_delimiter
+ * -- fill a given buffer with a string that is not found in the given query.  This should
+ *  guarantee a valid delimiter.
+ *
+ * This is quite dumb, 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 way more than 
+ * adequate for constructing a delimiter.
+ *
+ * RETURN
+ *   ptr to buffer  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 += 1) {
+
+    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 string. */
+       return delimiter_buff;
+    }
+
+  }
+  return NULL;  /* but if we run out of space, return nothing at all. */
+}
+  
+    
+
+/*
+  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;
+  char       delimiter[500], *delimit_test;
+  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.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!
+        */
+        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 */);
+
+          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);
+          fprintf(sql_file, "DELIMITER ;\n");
+          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
@@ -1294,9 +1436,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 +2642,6 @@
 {
   MYSQL_ROW row;
   MYSQL_RES *tableres;
-  int result=0;
   char buf[FN_REFLEN];
   int first;
 
@@ -2798,6 +2939,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 +3154,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-27 13:38:50 -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-27 13:38:50 -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-27 13:38:50 -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.
Thread
bk commit into 5.1 tree (cmiller:1.2185) BUG#17714Chad MILLER27 Feb