From: Date: February 27 2006 7:40pm Subject: bk commit into 5.1 tree (cmiller:1.2185) BUG#17714 List-Archive: http://lists.mysql.com/commits/3211 X-Bug: 17714 Message-Id: <20060227184054.777625C05D@zippy> 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.