Below is the list of changes that have just been committed into a local
5.0 repository of serg. When serg 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/in...urce-tree.html

ChangeSet
1.1945 05/09/13 19:32:37 serg (AT) serg (DOT) mylan +3 -0
after pull review - many bugs in mysqldump --routines

mysql-test/t/mysqldump.test
1.60 05/09/13 19:32:10 serg (AT) serg (DOT) mylan +23 -22
cleanup, new test

mysql-test/r/mysqldump.result
1.67 05/09/13 19:32:10 serg (AT) serg (DOT) mylan +27 -21
updated

client/mysqldump.c
1.199 05/09/13 19:32:09 serg (AT) serg (DOT) mylan +24 -39
after pull review - many bugs in mysqldump --routines

# 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: serg
# Host: serg.mylan
# Root: /usr/home/serg/Abk/mysql-5.0

--- 1.198/client/mysqldump.c Sat Sep 10 00:25:14 2005
+++ 1.199/client/mysqldump.c Tue Sep 13 19:32:09 2005
@@ -1193,12 +1193,11 @@ static void print_xml_row(FILE *xml_file

static uint dump_routines_for_db (char *db)
{
- char query_buff[512], routine_type[10];
- char db_name_buff[NAME_LEN+3], name_buff[NAME_LEN+3];
+ char query_buff[512], *routine_type[]={"FUNCTION", "PROCEDURE"};
+ char db_name_buff[NAME_LEN*2+3], name_buff[NAME_LEN*2+3], *routine_name;
int i;
FILE *sql_file = md_result_file;
- MYSQL_RES *routine_res= NULL;
- MYSQL_RES *routine_list_res= NULL;
+ MYSQL_RES *routine_res, *routine_list_res;
MYSQL_ROW row, routine_list_row;

DBUG_ENTER("dump_routines_for_db");
@@ -1211,23 +1210,20 @@ static uint dump_routines_for_db (char *
fprintf(sql_file, "\n--\n-- Dumping routines for database '%s'\n--\n", db);

/*
- not using "mysql_query_with_error_report" because of privileges
+ not using "mysql_query_with_error_report" because we may have not
+ enough privileges to lock mysql.proc.
*/
- if (opt_lock)
+ if (lock_tables)
mysql_query(sock, "LOCK TABLES mysql.proc READ");

- fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n");
fprintf(sql_file, "DELIMITER //\n");

/* 0, retrieve and dump functions, 1, procedures */
for (i=0; i <= 1; i++)
{
- my_snprintf(routine_type, sizeof(routine_type),
- "%s", i == 0 ? "FUNCTION" : "PROCEDURE");
-
my_snprintf(query_buff, sizeof(query_buff),
"SHOW %s STATUS WHERE Db = '%s'",
- routine_type, db_name_buff);
+ routine_type[i], db_name_buff);

if (mysql_query_with_error_report(sock, &routine_list_res, query_buff))
DBUG_RETURN(1);
@@ -1237,11 +1233,11 @@ static uint dump_routines_for_db (char *

while((routine_list_row= mysql_fetch_row(routine_list_res)))
{
- DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type, name_buff));
- mysql_real_escape_string(sock, name_buff,
- routine_list_row[1], strlen(routine_list_row[1]));
+ 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, name_buff);
+ routine_type[i], routine_name);

if (mysql_query_with_error_report(sock, &routine_res, query_buff))
DBUG_RETURN(1);
@@ -1249,41 +1245,36 @@ static uint dump_routines_for_db (char *
while ((row=mysql_fetch_row(routine_res)))
{
/*
- the user can see routine names, but NOT the routine body of other
- routines that are not the creator of!
+ 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",
- name_buff, row[2], strlen(row[2])));
+ routine_name, row[2], strlen(row[2])));
if (strlen(row[2]))
{
- fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/ //\n",
- row[1] /* sql_mode */);
-
if (opt_drop)
fprintf(sql_file, "/*!50003 DROP %s IF EXISTS %s */ //\n",
- routine_type, name_buff);
+ routine_type[i], routine_name);
/*
- the i==0 is temporary until we can figure out why functions
- can't be in comments
- */
- /* create proc/func body */;
+ 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 routine printing */
} /* end of list of routines */
mysql_free_result(routine_res);
- routine_res=NULL;
}
mysql_free_result(routine_list_res);
- routine_list_res=NULL;
} /* end of for i (0 .. 1) */
/* set the delimiter back to ';' */
fprintf(sql_file, "DELIMITER ;\n");
- fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;\n");

- /* again, no error report due to permissions */
- if (opt_lock)
- mysql_query(sock, "UNLOCK TABLES");
+ if (lock_tables)
+ mysql_query_with_error_report(sock, 0, "UNLOCK TABLES");
DBUG_RETURN(0);
}

@@ -1739,12 +1730,6 @@ continue_xml:
the tables have been dumped in case a trigger depends on the existence
of a table

- INPUT
- char * tablename and db name
- RETURNS
- 0 Failure
- 1 Succes
-
*/

static void dump_triggers_for_table (char *table, char *db)
@@ -1752,7 +1737,7 @@ static void dump_triggers_for_table (cha
MYSQL_RES *result;
MYSQL_ROW row;
char *result_table;
- char name_buff[NAME_LEN+3], table_buff[NAME_LEN*2+3];
+ char name_buff[NAME_LEN*4+3], table_buff[NAME_LEN*2+3];
char query_buff[512];
FILE *sql_file = md_result_file;


--- 1.66/mysql-test/r/mysqldump.result Mon Sep 12 09:09:39 2005
+++ 1.67/mysql-test/r/mysqldump.result Tue Sep 13 19:32:10 2005
@@ -1908,28 +1908,28 @@ a2
DROP TRIGGER testref;
DROP TABLE test1;
DROP TABLE test2;
-CREATE TABLE t1 (id int);
-INSERT INTO t1 VALUES(1);
-INSERT INTO t1 VALUES(2);
-INSERT INTO t1 VALUES(3);
-INSERT INTO t1 VALUES(4);
-INSERT INTO t1 VALUES(5);
+DROP TABLE IF EXISTS t1;
DROP FUNCTION IF EXISTS bug9056_func1;
-CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
-RETURN a+b //
+DROP FUNCTION IF EXISTS bug9056_func2;
+DROP PROCEDURE IF EXISTS bug9056_proc1;
+DROP PROCEDURE IF EXISTS bug9056_proc2;
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES(1), (2), (3), (4), (5);
+CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b //
CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
BEGIN SELECT a+b INTO c; end //
-DROP FUNCTION IF EXISTS bug9056_func2 //
create function bug9056_func2(f1 char binary) returns char binary
begin
set f1= concat( 'hello', f1 );
return f1;
end //
-DROP PROCEDURE IF EXISTS bug9056_proc2 //
CREATE PROCEDURE bug9056_proc2(OUT a INT)
BEGIN
select sum(id) from t1 into a;
END //
+set sql_mode='ansi';
+create procedure `a'b` () select 1;
+set sql_mode='';

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
@@ -1954,32 +1954,38 @@ LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5);
UNLOCK TABLES;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
-
-/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER //
+/*!50003 DROP FUNCTION IF EXISTS `bug9056_func1` */ //
/*!50003 SET SESSION SQL_MODE=""*/ //
-/*!50003 DROP FUNCTION IF EXISTS bug9056_func1 */ //
/*!50003 CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
RETURN a+b */ //
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/ //
+/*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */ //
/*!50003 SET SESSION SQL_MODE=""*/ //
-/*!50003 DROP FUNCTION IF EXISTS bug9056_func2 */ //
/*!50003 CREATE FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1)
begin
set f1= concat( 'hello', f1 );
return f1;
end */ //
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/ //
+/*!50003 DROP PROCEDURE IF EXISTS `a'b` */ //
+/*!50003 SET SESSION SQL_MODE="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTE S,IGNORE_SPACE,ANSI"*/ //
+/*!50003 CREATE PROCEDURE "a'b"()
+select 1 */ //
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/ //
+/*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc1` */ //
/*!50003 SET SESSION SQL_MODE=""*/ //
-/*!50003 DROP PROCEDURE IF EXISTS bug9056_proc1 */ //
/*!50003 CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
BEGIN SELECT a+b INTO c; end */ //
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/ //
+/*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc2` */ //
/*!50003 SET SESSION SQL_MODE=""*/ //
-/*!50003 DROP PROCEDURE IF EXISTS bug9056_proc2 */ //
/*!50003 CREATE PROCEDURE `bug9056_proc2`(OUT a INT)
BEGIN
select sum(id) from t1 into a;
END */ //
+/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/ //
DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
@@ -1989,8 +1995,8 @@ DELIMITER ;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-DROP PROCEDURE IF EXISTS bug9056_func1;
-DROP PROCEDURE IF EXISTS bug9056_func2;
-DROP PROCEDURE IF EXISTS bug9056_proc1;
-DROP PROCEDURE IF EXISTS bug9056_proc2;
+DROP FUNCTION bug9056_func1;
+DROP FUNCTION bug9056_func2;
+DROP PROCEDURE bug9056_proc1;
+DROP PROCEDURE bug9056_proc2;
drop table t1;

--- 1.59/mysql-test/t/mysqldump.test Mon Sep 12 09:09:39 2005
+++ 1.60/mysql-test/t/mysqldump.test Tue Sep 13 19:32:10 2005
@@ -811,34 +811,32 @@ DROP TRIGGER testref;
DROP TABLE test1;
DROP TABLE test2;

-CREATE TABLE t1 (id int);
-INSERT INTO t1 VALUES(1);
-INSERT INTO t1 VALUES(2);
-INSERT INTO t1 VALUES(3);
-INSERT INTO t1 VALUES(4);
-INSERT INTO t1 VALUES(5);
+#
+# BUG#9056 - mysqldump does not dump routines
+#
+
--disable_warnings
+DROP TABLE IF EXISTS t1;
DROP FUNCTION IF EXISTS bug9056_func1;
-DELIMITER //;
+DROP FUNCTION IF EXISTS bug9056_func2;
+DROP PROCEDURE IF EXISTS bug9056_proc1;
+DROP PROCEDURE IF EXISTS bug9056_proc2;
--enable_warnings
-CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
-RETURN a+b //
+
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES(1), (2), (3), (4), (5);
+
+DELIMITER //;
+CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b //
CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
BEGIN SELECT a+b INTO c; end //

---disable_warnings
-DROP FUNCTION IF EXISTS bug9056_func2 //
---enable_warnings
-
create function bug9056_func2(f1 char binary) returns char binary
begin
set f1= concat( 'hello', f1 );
return f1;
end //

---disable_warnings
-DROP PROCEDURE IF EXISTS bug9056_proc2 //
---enable_warnings
CREATE PROCEDURE bug9056_proc2(OUT a INT)
BEGIN
select sum(id) from t1 into a;
@@ -846,14 +844,17 @@ END //

DELIMITER ;//

+set sql_mode='ansi';
+create procedure `a'b` () select 1; # to fix syntax highlighting :')
+set sql_mode='';
+
# Dump the DB and ROUTINES
--exec $MYSQL_DUMP --skip-comments --routines --databases test

# ok, now blow it all away
---disable_warnings
-DROP PROCEDURE IF EXISTS bug9056_func1;
-DROP PROCEDURE IF EXISTS bug9056_func2;
-DROP PROCEDURE IF EXISTS bug9056_proc1;
-DROP PROCEDURE IF EXISTS bug9056_proc2;
+DROP FUNCTION bug9056_func1;
+DROP FUNCTION bug9056_func2;
+DROP PROCEDURE bug9056_proc1;
+DROP PROCEDURE bug9056_proc2;
drop table t1;
---enable-warnings
+

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw