From: Date: November 10 2005 11:16am Subject: bk commit into 5.0 tree (bell:1.1978) BUG#10100 List-Archive: http://lists.mysql.com/internals/32137 X-Bug: 10100 Message-Id: <20051110101624.4C459455F0C@sanja.is.com.ua> Below is the list of changes that have just been committed into a local 5.0 repository of bell. When bell 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.1978 05/11/10 12:16:16 bell@stripped +16 -0 recursion support made for SP (BUG#10100) sql/sql_class.h 1.273 05/11/10 12:16:05 bell@stripped +1 -0 new sp_recursion_levels sql/sql_base.cc 1.318 05/11/10 12:16:05 bell@stripped +5 -0 open_table consume a lot of stack space so we check free stack space before it. sql/sp_head.h 1.74 05/11/10 12:16:05 bell@stripped +3 -0 new sp_head variables - m_recursion_level, m_next_cached_sp sql/sp_head.cc 1.194 05/11/10 12:16:05 bell@stripped +13 -22 initialize new sp_head variables stack requirement for SP instruction is increased it is checked before mem root initialisation to avoid memory leak diferent errors for cases when recursion prohibited and when it is limited sql/sp.cc 1.97 05/11/10 12:16:05 bell@stripped +129 -92 refactor code for recursion. temporary LEX on stack, not on heap. sql/share/errmsg.txt 1.55 05/11/10 12:16:04 bell@stripped +2 -0 new error sql/set_var.cc 1.143 05/11/10 12:16:04 bell@stripped +4 -0 sp_recursion_levels system variable sql/mysqld.cc 1.517 05/11/10 12:16:04 bell@stripped +6 -0 recursion-levels option sql/item_func.cc 1.266 05/11/10 12:16:04 bell@stripped +10 -6 recursion support mysql-test/t/variables.test 1.51 05/11/10 12:16:04 bell@stripped +4 -0 tests for recursion mysql-test/t/trigger.test 1.28 05/11/10 12:16:04 bell@stripped +4 -0 ensure that triggers are not affected mysql-test/t/sp.test 1.163 05/11/10 12:16:04 bell@stripped +82 -0 tests for recursion mysql-test/r/variables.result 1.76 05/11/10 12:16:04 bell@stripped +8 -0 tests for recursion mysql-test/r/trigger.result 1.21 05/11/10 12:16:04 bell@stripped +3 -0 ensure that triggers are not affected mysql-test/r/sp.result 1.170 05/11/10 12:16:04 bell@stripped +74 -0 tests for recursion client/mysqltest.c 1.177 05/11/10 12:16:04 bell@stripped +23 -17 hide expected error messages from the log if disable_result_log is in force # 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: bell # Host: sanja.is.com.ua # Root: /home/bell/mysql/bk/work-bug7-5.0 --- 1.265/sql/item_func.cc 2005-11-03 09:05:27 +02:00 +++ 1.266/sql/item_func.cc 2005-11-10 12:16:04 +02:00 @@ -4689,15 +4689,19 @@ Item_func_sp::sp_result_field(void) const { Field *field; + THD *thd= current_thd; DBUG_ENTER("Item_func_sp::sp_result_field"); + DBUG_PRINT("info", ("sp: %s, flags: %x, level: %lu", + (m_sp ? "YES" : "NO"), + (m_sp ? m_sp->m_flags : (uint)0), + (m_sp ? m_sp->m_recursion_level : (ulong)0))); - if (!m_sp) + if ((!m_sp || (m_sp->m_flags & sp_head::IS_INVOKED) || + (thd->variables.sp_recursion_levels > m_sp->m_recursion_level)) && + !(m_sp= sp_find_function(thd, m_name, TRUE))) { - if (!(m_sp= sp_find_function(current_thd, m_name, TRUE))) - { - my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION", m_name->m_qname.str); - DBUG_RETURN(0); - } + my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION", m_name->m_qname.str); + DBUG_RETURN(0); } if (!dummy_table->s) { --- 1.516/sql/mysqld.cc 2005-11-04 10:03:27 +02:00 +++ 1.517/sql/mysqld.cc 2005-11-10 12:16:04 +02:00 @@ -4543,6 +4543,7 @@ OPT_OPTIMIZER_PRUNE_LEVEL, OPT_UPDATABLE_VIEWS_WITH_LIMIT, OPT_SP_AUTOMATIC_PRIVILEGES, + OPT_SP_RECURSION_LEVELS, OPT_AUTO_INCREMENT, OPT_AUTO_INCREMENT_OFFSET, OPT_ENABLE_LARGE_PAGES, OPT_TIMED_MUTEXES, @@ -5745,6 +5746,11 @@ (gptr*) &global_system_variables.read_buff_size, (gptr*) &max_system_variables.read_buff_size,0, GET_ULONG, REQUIRED_ARG, 128*1024L, IO_SIZE*2+MALLOC_OVERHEAD, ~0L, MALLOC_OVERHEAD, IO_SIZE, 0}, + {"recursion-levels", OPT_SP_RECURSION_LEVELS, + "Stored procedure recursion level", + (gptr*) &global_system_variables.sp_recursion_levels, + (gptr*) &max_system_variables.sp_recursion_levels, 0, GET_ULONG, + OPT_ARG, 0, 0, 255, 0, 1, 0 }, #ifdef HAVE_REPLICATION {"relay_log_purge", OPT_RELAY_LOG_PURGE, "0 = do not purge relay logs. 1 = purge them as soon as they are no more needed.", --- 1.317/sql/sql_base.cc 2005-11-03 16:28:10 +02:00 +++ 1.318/sql/sql_base.cc 2005-11-10 12:16:05 +02:00 @@ -1087,6 +1087,11 @@ /* find a unused table in the open table cache */ if (refresh) *refresh=0; + + /* an open table operation needs a lot of the stack space */ + if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (char *)&alias)) + return 0; + if (thd->killed) DBUG_RETURN(0); key_length= (uint) (strmov(strmov(key, table_list->db)+1, --- 1.272/sql/sql_class.h 2005-11-04 15:12:19 +02:00 +++ 1.273/sql/sql_class.h 2005-11-10 12:16:05 +02:00 @@ -534,6 +534,7 @@ ulong completion_type; /* Determines which non-standard SQL behaviour should be enabled */ ulong sql_mode; + ulong sp_recursion_levels; /* check of key presence in updatable view */ ulong updatable_views_with_limit; ulong default_week_format; --- 1.54/sql/share/errmsg.txt 2005-11-06 08:41:30 +02:00 +++ 1.55/sql/share/errmsg.txt 2005-11-10 12:16:04 +02:00 @@ -5421,3 +5421,5 @@ eng "Cannot add or update a child row: a foreign key constraint fails (%.192s)" ER_SP_BAD_VAR_SHADOW 42000 eng "Variable '%-.64s' must be quoted with `...`, or renamed" +ER_SP_RECURSION_LIMIT + eng "Recursive limit %d (as set by the recursions_levels variable) reached for routine %.64s" --- 1.20/mysql-test/r/trigger.result 2005-09-03 02:13:09 +03:00 +++ 1.21/mysql-test/r/trigger.result 2005-11-10 12:16:04 +02:00 @@ -703,8 +703,11 @@ for each row insert into t2 values (new.f1+1); create trigger t2_ai after insert on t2 for each row insert into t1 values (new.f2+1); +set @SAVE_SP_RECURSION_LEVELS=@@sp_recursion_levels; +set @@sp_recursion_levels=100; insert into t1 values (1); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +set @@sp_recursion_levels=@SAVE_SP_RECURSION_LEVELS; select * from t1; f1 1 --- 1.27/mysql-test/t/trigger.test 2005-09-15 02:56:03 +03:00 +++ 1.28/mysql-test/t/trigger.test 2005-11-10 12:16:04 +02:00 @@ -743,8 +743,12 @@ for each row insert into t2 values (new.f1+1); create trigger t2_ai after insert on t2 for each row insert into t1 values (new.f2+1); +# Allow SP resursion to be show that it has not influence here +set @SAVE_SP_RECURSION_LEVELS=@@sp_recursion_levels; +set @@sp_recursion_levels=100; --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG insert into t1 values (1); +set @@sp_recursion_levels=@SAVE_SP_RECURSION_LEVELS; select * from t1; select * from t2; drop trigger t1_ai; --- 1.75/mysql-test/r/variables.result 2005-10-19 23:47:02 +03:00 +++ 1.76/mysql-test/r/variables.result 2005-11-10 12:16:04 +02:00 @@ -351,6 +351,14 @@ set global server_id=100; set global slow_launch_time=100; set sort_buffer_size=100; +set @@sp_recursion_levels=10; +select @@sp_recursion_levels; +@@sp_recursion_levels +10 +set @@sp_recursion_levels=0; +select @@sp_recursion_levels; +@@sp_recursion_levels +0 set sql_auto_is_null=1; select @@sql_auto_is_null; @@sql_auto_is_null --- 1.50/mysql-test/t/variables.test 2005-10-19 23:47:02 +03:00 +++ 1.51/mysql-test/t/variables.test 2005-11-10 12:16:04 +02:00 @@ -237,6 +237,10 @@ set global server_id=100; set global slow_launch_time=100; set sort_buffer_size=100; +set @@sp_recursion_levels=10; +select @@sp_recursion_levels; +set @@sp_recursion_levels=0; +select @@sp_recursion_levels; set sql_auto_is_null=1; select @@sql_auto_is_null; set @@sql_auto_is_null=0; --- 1.142/sql/set_var.cc 2005-09-21 18:42:26 +03:00 +++ 1.143/sql/set_var.cc 2005-11-10 12:16:04 +02:00 @@ -365,6 +365,8 @@ &SV::table_type); sys_var_thd_storage_engine sys_storage_engine("storage_engine", &SV::table_type); +sys_var_thd_ulong sys_sp_recursion_levels("sp_recursion_levels", + &SV::sp_recursion_levels); #ifdef HAVE_REPLICATION sys_var_sync_binlog_period sys_sync_binlog_period("sync_binlog", &sync_binlog_period); sys_var_thd_ulong sys_sync_replication("sync_replication", @@ -687,6 +689,7 @@ &sys_sql_warnings, &sys_sql_notes, &sys_storage_engine, + &sys_sp_recursion_levels, #ifdef HAVE_REPLICATION &sys_sync_binlog_period, &sys_sync_replication, @@ -980,6 +983,7 @@ {"sql_notes", (char*) &sys_sql_notes, SHOW_BOOL}, {"sql_warnings", (char*) &sys_sql_warnings, SHOW_BOOL}, {sys_storage_engine.name, (char*) &sys_storage_engine, SHOW_SYS}, + {sys_sp_recursion_levels.name, (char*) &sys_sp_recursion_levels, SHOW_SYS}, #ifdef HAVE_REPLICATION {sys_sync_binlog_period.name,(char*) &sys_sync_binlog_period, SHOW_SYS}, #endif --- 1.176/client/mysqltest.c 2005-11-01 18:40:49 +02:00 +++ 1.177/client/mysqltest.c 2005-11-10 12:16:04 +02:00 @@ -3314,20 +3314,23 @@ ((q->expected_errno[i].type == ERR_SQLSTATE) && (strcmp(q->expected_errno[i].code.sqlstate, err_sqlstate) == 0))) { - if (q->expected_errors == 1) + if (!disable_result_log) { - /* Only log error if there is one possible error */ - dynstr_append_mem(ds, "ERROR ", 6); - replace_dynstr_append(ds, err_sqlstate); - dynstr_append_mem(ds, ": ", 2); - replace_dynstr_append(ds, err_error); - dynstr_append_mem(ds,"\n",1); + if (q->expected_errors == 1) + { + /* Only log error if there is one possible error */ + dynstr_append_mem(ds, "ERROR ", 6); + replace_dynstr_append(ds, err_sqlstate); + dynstr_append_mem(ds, ": ", 2); + replace_dynstr_append(ds, err_error); + dynstr_append_mem(ds,"\n",1); + } + /* Don't log error if we may not get an error */ + else if (q->expected_errno[0].type == ERR_SQLSTATE || + (q->expected_errno[0].type == ERR_ERRNO && + q->expected_errno[0].code.errnum != 0)) + dynstr_append(ds,"Got one of the listed errors\n"); } - /* Don't log error if we may not get an error */ - else if (q->expected_errno[0].type == ERR_SQLSTATE || - (q->expected_errno[0].type == ERR_ERRNO && - q->expected_errno[0].code.errnum != 0)) - dynstr_append(ds,"Got one of the listed errors\n"); /* OK */ DBUG_RETURN(0); } @@ -3335,11 +3338,14 @@ DBUG_PRINT("info",("i: %d expected_errors: %d", i, q->expected_errors)); - dynstr_append_mem(ds, "ERROR ",6); - replace_dynstr_append(ds, err_sqlstate); - dynstr_append_mem(ds, ": ", 2); - replace_dynstr_append(ds, err_error); - dynstr_append_mem(ds, "\n", 1); + if (!disable_result_log) + { + dynstr_append_mem(ds, "ERROR ",6); + replace_dynstr_append(ds, err_sqlstate); + dynstr_append_mem(ds, ": ", 2); + replace_dynstr_append(ds, err_error); + dynstr_append_mem(ds, "\n", 1); + } if (i) { --- 1.169/mysql-test/r/sp.result 2005-11-03 13:20:07 +02:00 +++ 1.170/mysql-test/r/sp.result 2005-11-10 12:16:04 +02:00 @@ -3617,4 +3617,78 @@ drop table t3, t4| drop procedure bug14210| set @@session.max_heap_table_size=default| +drop function if exists bug10100f| +drop procedure if exists bug10100p| +drop procedure if exists bug10100t| +drop procedure if exists bug10100pt| +create function bug10100f(prm int) returns int +begin +if prm > 1 then +return prm * bug10100f(prm - 1); +end if; +return 1; +end| +create procedure bug10100p(prm int, inout res int) +begin +set res = res * prm; +if prm > 1 then +call bug10100p(prm - 1, res); +end if; +end| +create procedure bug10100t(prm int) +begin +declare res int; +set res = 1; +call bug10100p(prm, res); +select res; +end| +create table t3 (a int)| +insert into t3 values (0)| +create procedure bug10100pt(level int) +begin +if level < 255 then +update t3 set a=level; +FLUSH TABLES; +call bug10100pt(level+1); +else +select sleep(1); +end if; +end| +set @@sp_recursion_levels=4| +select @@sp_recursion_levels| +@@sp_recursion_levels +4 +select bug10100f(3)| +bug10100f(3) +6 +select bug10100f(4)| +bug10100f(4) +24 +select bug10100f(5)| +bug10100f(5) +120 +select bug10100f(6)| +ERROR HY000: Recursive limit 4 (as set by the recursions_levels variable) reached for routine bug10100f +call bug10100t(5)| +res +120 +set @@sp_recursion_levels=0| +select @@sp_recursion_levels| +@@sp_recursion_levels +0 +select bug10100f(5)| +ERROR HY000: Recursive stored routines are not allowed. +call bug10100t(5)| +ERROR HY000: Recursive stored routines are not allowed. +set @@sp_recursion_levels=255| +set @var=1| +call bug10100p(255, @var)| +call bug10100pt(1)| +select bug10100f(255)| +set @@sp_recursion_levels=0| +drop function bug10100f| +drop procedure bug10100p| +drop procedure bug10100t| +drop procedure bug10100pt| +drop table t3| drop table t1,t2; --- 1.162/mysql-test/t/sp.test 2005-11-03 13:20:07 +02:00 +++ 1.163/mysql-test/t/sp.test 2005-11-10 12:16:04 +02:00 @@ -4542,6 +4542,88 @@ set @@session.max_heap_table_size=default| # +# BUG#10100: function (and stored procedure?) recursivity problem +# +--disable_warnings +drop function if exists bug10100f| +drop procedure if exists bug10100p| +drop procedure if exists bug10100t| +drop procedure if exists bug10100pt| +--enable_warnings +# routines with simple recursion +create function bug10100f(prm int) returns int +begin + if prm > 1 then + return prm * bug10100f(prm - 1); + end if; + return 1; +end| +create procedure bug10100p(prm int, inout res int) +begin + set res = res * prm; + if prm > 1 then + call bug10100p(prm - 1, res); + end if; +end| +create procedure bug10100t(prm int) +begin + declare res int; + set res = 1; + call bug10100p(prm, res); + select res; +end| + +# a procedure which use tables and recursion +create table t3 (a int)| +insert into t3 values (0)| +create procedure bug10100pt(level int) +begin + if level < 255 then + update t3 set a=level; + FLUSH TABLES; + call bug10100pt(level+1); + else + select sleep(1); + end if; +end| + +set @@sp_recursion_levels=4| +select @@sp_recursion_levels| +select bug10100f(3)| +select bug10100f(4)| +select bug10100f(5)| +-- error ER_SP_RECURSION_LIMIT +select bug10100f(6)| +call bug10100t(5)| +set @@sp_recursion_levels=0| +select @@sp_recursion_levels| +-- error ER_SP_NO_RECURSION +select bug10100f(5)| +-- error ER_SP_NO_RECURSION +call bug10100t(5)| + +#end of the stack checking +set @@sp_recursion_levels=255| +set @var=1| +#disable log because error about stack overrun contains numbers which +#depend on a system +-- disable_result_log +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100p(255, @var)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pt(1)| +-- error ER_STACK_OVERRUN_NEED_MORE +select bug10100f(255)| +-- enable_result_log +set @@sp_recursion_levels=0| + +drop function bug10100f| +drop procedure bug10100p| +drop procedure bug10100t| +drop procedure bug10100pt| +drop table t3| + +# # BUG#NNNN: New bug synopsis # #--disable_warnings --- 1.96/sql/sp.cc 2005-10-07 03:37:20 +03:00 +++ 1.97/sql/sp.cc 2005-11-10 12:16:05 +02:00 @@ -29,6 +29,14 @@ const char *returns, ulong returnslen, const char *body, ulong bodylen, st_sp_chistics *chistics); +static int +db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp, + ulong sql_mode, const char *params, const char *returns, + const char *body, st_sp_chistics &chistics, + const char *definer, longlong created, longlong modified); +static sp_head * +sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp, + bool cache_only); /* * @@ -377,83 +385,77 @@ close_proc_table(thd, &open_tables_state_backup); table= 0; - { - String defstr; - LEX *oldlex= thd->lex; - char olddb[128]; - bool dbchanged; - enum enum_sql_command oldcmd= thd->lex->sql_command; - ulong old_sql_mode= thd->variables.sql_mode; - ha_rows select_limit= thd->variables.select_limit; - - thd->variables.sql_mode= sql_mode; - thd->variables.select_limit= HA_POS_ERROR; - - defstr.set_charset(system_charset_info); - if (!create_string(thd, &defstr, - type, - name, - params, strlen(params), - returns, strlen(returns), - body, strlen(body), - &chistics)) - { - ret= SP_INTERNAL_ERROR; - goto done; - } + ret= db_load_routine(thd, type, name, sphp, + sql_mode, params, returns, body, chistics, + definer, created, modified); + + done: + if (table) + close_proc_table(thd, &open_tables_state_backup); + DBUG_RETURN(ret); +} - dbchanged= FALSE; - if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb), - 1, &dbchanged))) - goto done; - { - /* This is something of a kludge. We need to initialize some fields - * in thd->lex (the unit and master stuff), and the easiest way to - * do it is, is to call mysql_init_query(), but this unfortunately - * resets teh value_list where we keep the CALL parameters. So we - * copy the list and then restore it. (... and found_semicolon too). - */ - List tmpvals= thd->lex->value_list; - char *tmpfsc= thd->lex->found_semicolon; - - lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length()); - thd->lex->value_list= tmpvals; - thd->lex->found_semicolon= tmpfsc; - } +static int +db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp, + ulong sql_mode, const char *params, const char *returns, + const char *body, st_sp_chistics &chistics, + const char *definer, longlong created, longlong modified) +{ + LEX *oldlex= thd->lex, newlex; + String defstr; + char olddb[128]; + bool dbchanged; + ulong old_sql_mode= thd->variables.sql_mode; + ha_rows select_limit= thd->variables.select_limit; + int ret= SP_INTERNAL_ERROR; + + thd->variables.sql_mode= sql_mode; + thd->variables.select_limit= HA_POS_ERROR; + + thd->lex= &newlex; + newlex.current_select= NULL; + + defstr.set_charset(system_charset_info); + if (!create_string(thd, &defstr, + type, + name, + params, strlen(params), + returns, strlen(returns), + body, strlen(body), + &chistics)) + goto end; - if (yyparse(thd) || thd->is_fatal_error || thd->lex->sphead == NULL) - { - LEX *newlex= thd->lex; - sp_head *sp= newlex->sphead; + dbchanged= FALSE; + if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb), + 1, &dbchanged))) + goto end; - if (dbchanged && (ret= mysql_change_db(thd, olddb, 1))) - goto done; - if (sp) - { - delete sp; - newlex->sphead= NULL; - } - ret= SP_PARSE_ERROR; - } - else - { - if (dbchanged && (ret= mysql_change_db(thd, olddb, 1))) - goto done; - *sphp= thd->lex->sphead; - (*sphp)->set_info((char *)definer, (uint)strlen(definer), - created, modified, &chistics, sql_mode); - (*sphp)->optimize(); - } - thd->lex->sql_command= oldcmd; - thd->variables.sql_mode= old_sql_mode; - thd->variables.select_limit= select_limit; - } + lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length()); - done: - if (table) - close_proc_table(thd, &open_tables_state_backup); - DBUG_RETURN(ret); + if (yyparse(thd) || thd->is_fatal_error || newlex.sphead == NULL) + { + sp_head *sp= newlex.sphead; + + if (dbchanged && (ret= mysql_change_db(thd, olddb, 1))) + goto end; + delete sp; + ret= SP_PARSE_ERROR; + } + else + { + if (dbchanged && (ret= mysql_change_db(thd, olddb, 1))) + goto end; + *sphp= newlex.sphead; + (*sphp)->set_info((char *)definer, (uint)strlen(definer), + created, modified, &chistics, sql_mode); + (*sphp)->optimize(); + } + thd->variables.sql_mode= old_sql_mode; + thd->variables.select_limit= select_limit; +end: + thd->lex= oldlex; + return ret; } @@ -908,11 +910,6 @@ cache_only - if true perform cache-only lookup (Don't look in mysql.proc). - TODO - We should consider merging of sp_find_procedure() and - sp_find_function() into one sp_find_routine() function - (the same applies to other similarly paired functions). - RETURN VALUE Non-0 pointer to sp_head object for the procedure, or 0 - in case of error. @@ -926,17 +923,66 @@ DBUG_PRINT("enter", ("name: %.*s.%.*s", name->m_db.length, name->m_db.str, name->m_name.length, name->m_name.str)); + sp= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name, &thd->sp_proc_cache, + cache_only); + DBUG_RETURN(sp); +} + +static sp_head * +sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp, + bool cache_only) +{ + sp_head *sp; + DBUG_ENTER("sp_find_routine"); + DBUG_PRINT("enter", ("name: %*s, type: %d, cache only %d", + name->m_name.length, name->m_name.str, + type, cache_only)); - if (!(sp= sp_cache_lookup(&thd->sp_proc_cache, name)) && !cache_only) + if (!(sp= sp_cache_lookup(cp, name)) && !cache_only) { - if (db_find_routine(thd, TYPE_ENUM_PROCEDURE, name, &sp) == SP_OK) - sp_cache_insert(&thd->sp_proc_cache, sp); + if (db_find_routine(thd, type, name, &sp) == SP_OK) + sp_cache_insert(cp, sp); } + else + { + DBUG_PRINT("info", ("found: 0x%lx", (ulong)sp)); + for (sp_head *sp2= sp; sp2; sp2= sp2->m_next_cached_sp) + { + DBUG_PRINT("info", ("test 0x%lx, level: %lu, flags %x", + (ulong)sp2, sp2->m_recursion_level, + sp2->m_flags)); + if (sp2->m_recursion_level >= thd->variables.sp_recursion_levels) + DBUG_RETURN(sp2); + if (!(sp2->m_flags & sp_head::IS_INVOKED)) + DBUG_RETURN(sp2); + if (sp2->m_next_cached_sp) + continue; + const char *returns= ""; + char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2]; + String retstr(64); + strxmov(definer, sp->m_definer_user.str, "@", + sp->m_definer_host.str, NullS); + if (type == TYPE_ENUM_FUNCTION) + { + sp_returns_type(thd, retstr, sp); + returns= retstr.ptr(); + } + if (db_load_routine(thd, type, name, &sp, + sp->m_sql_mode, sp->m_params.str, returns, + sp->m_body.str, *sp->m_chistics, definer, + sp->m_created, sp->m_modified) == SP_OK) + { + sp2->m_next_cached_sp= sp; + sp->m_recursion_level= 1 + sp2->m_recursion_level; + } + } + } DBUG_RETURN(sp); } + int sp_exists_routine(THD *thd, TABLE_LIST *tables, bool any, bool no_error) { @@ -1075,12 +1121,9 @@ DBUG_ENTER("sp_find_function"); DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - if (!(sp= sp_cache_lookup(&thd->sp_func_cache, name)) && - !cache_only) - { - if (db_find_routine(thd, TYPE_ENUM_FUNCTION, name, &sp) == SP_OK) - sp_cache_insert(&thd->sp_func_cache, sp); - } + + sp= sp_find_routine(thd, TYPE_ENUM_FUNCTION, name, &thd->sp_func_cache, + cache_only); DBUG_RETURN(sp); } @@ -1442,10 +1485,6 @@ &thd->sp_func_cache : &thd->sp_proc_cache), &name))) { - LEX *oldlex= thd->lex; - LEX *newlex= new st_lex; - thd->lex= newlex; - newlex->current_select= NULL; name.m_name.str= strchr(name.m_qname.str, '.'); name.m_db.length= name.m_name.str - name.m_qname.str; name.m_db.str= strmake_root(thd->mem_root, name.m_qname.str, @@ -1460,8 +1499,6 @@ else sp_cache_insert(&thd->sp_proc_cache, sp); } - delete newlex; - thd->lex= oldlex; } if (sp) { --- 1.193/sql/sp_head.cc 2005-10-21 14:07:50 +03:00 +++ 1.194/sql/sp_head.cc 2005-11-10 12:16:05 +02:00 @@ -437,7 +437,7 @@ sp_head::sp_head() :Query_arena(&main_mem_root, INITIALIZED_FOR_SP), - m_flags(0), m_returns_cs(NULL) + m_flags(0), m_returns_cs(NULL), m_recursion_level(0), m_next_cached_sp(NULL) { extern byte * sp_table_key(const byte *ptr, uint *plen, my_bool first); @@ -615,6 +615,7 @@ sp_head::~sp_head() { destroy(); + delete m_next_cached_sp; if (m_thd) restore_thd_mem_root(m_thd); } @@ -869,34 +870,24 @@ Item_change_list old_change_list; String old_packet; - /* init per-instruction memroot */ - init_alloc_root(&execute_mem_root, MEM_ROOT_BLOCK_SIZE, 0); - - /* Use some extra margin for possible SP recursion and functions */ - if (check_stack_overrun(thd, 4*STACK_MIN_SIZE, olddb)) + if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (char*)&old_packet)) { DBUG_RETURN(-1); } + /* init per-instruction memroot */ + init_alloc_root(&execute_mem_root, MEM_ROOT_BLOCK_SIZE, 0); + if (m_flags & IS_INVOKED) { - /* - We have to disable recursion for stored routines since in - many cases LEX structure and many Item's can't be used in - reentrant way now. - - TODO: We can circumvent this problem by using separate - sp_head instances for each recursive invocation. - - NOTE: Theoretically arguments of procedure can be evaluated - before its invocation so there should be no problem with - recursion. But since we perform cleanup for CALL statement - as for any other statement only after its execution, its LEX - structure is not reusable for recursive calls. Thus we have - to prohibit recursion for stored procedures too. - */ - my_error(ER_SP_NO_RECURSION, MYF(0)); + THD *thd= current_thd; + if (thd->variables.sp_recursion_levels > 0) + my_error(ER_SP_RECURSION_LIMIT, MYF(0), + thd->variables.sp_recursion_levels, + m_name); + else + my_error(ER_SP_NO_RECURSION, MYF(0)); DBUG_RETURN(-1); } m_flags|= IS_INVOKED; --- 1.73/sql/sp_head.h 2005-09-22 23:46:49 +03:00 +++ 1.74/sql/sp_head.h 2005-11-10 12:16:05 +02:00 @@ -140,6 +140,9 @@ LEX_STRING m_definer_host; longlong m_created; longlong m_modified; + ulong m_recursion_level; + sp_head *m_next_cached_sp; + /* Set containing names of stored routines used by this routine. Note that unlike elements of similar set for statement elements of this