diff options
| -rw-r--r-- | mysql-test/main/sp-bugs.result | 4 | ||||
| -rw-r--r-- | mysql-test/main/sp-lock.result | 8 | ||||
| -rw-r--r-- | mysql-test/main/sp-lock.test | 3 | ||||
| -rw-r--r-- | mysql-test/main/sp.result | 22 | ||||
| -rw-r--r-- | mysql-test/main/sp.test | 17 | ||||
| -rw-r--r-- | sql/sp.cc | 16 |
6 files changed, 49 insertions, 21 deletions
diff --git a/mysql-test/main/sp-bugs.result b/mysql-test/main/sp-bugs.result index f88b3b137d3..d758981b6ba 100644 --- a/mysql-test/main/sp-bugs.result +++ b/mysql-test/main/sp-bugs.result @@ -182,7 +182,7 @@ RETURN 0; END latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION TESTF_bug11763507; Function sql_mode Create Function character_set_client collation_connection Database Collation -testf_bug11763507 CREATE DEFINER=`root`@`localhost` FUNCTION `testf_bug11763507`() RETURNS int(11) +TESTF_bug11763507 CREATE DEFINER=`root`@`localhost` FUNCTION `TESTF_bug11763507`() RETURNS int(11) BEGIN RETURN 0; END latin1 latin1_swedish_ci latin1_swedish_ci @@ -212,7 +212,7 @@ SELECT "PROCEDURE testp_bug11763507"; END latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE PROCEDURE TESTP_bug11763507; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -testp_bug11763507 CREATE DEFINER=`root`@`localhost` PROCEDURE `testp_bug11763507`() +TESTP_bug11763507 CREATE DEFINER=`root`@`localhost` PROCEDURE `TESTP_bug11763507`() BEGIN SELECT "PROCEDURE testp_bug11763507"; END latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/main/sp-lock.result b/mysql-test/main/sp-lock.result index acf951f6676..ec8d8970ae3 100644 --- a/mysql-test/main/sp-lock.result +++ b/mysql-test/main/sp-lock.result @@ -703,9 +703,6 @@ connection default; # # SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered # -# We are just covering the existing behaviour with tests. The -# results are not necessarily correct." -# CREATE PROCEDURE p1() BEGIN SELECT get_lock("test", 10); @@ -736,10 +733,7 @@ get_lock("test", 10) 1 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() -BEGIN -SELECT get_lock("test", 10); -SHOW CREATE PROCEDURE p1; -END latin1 latin1_swedish_ci latin1_swedish_ci +BEGIN END latin1 latin1_swedish_ci latin1_swedish_ci connection con3; disconnect con3; connection con2; diff --git a/mysql-test/main/sp-lock.test b/mysql-test/main/sp-lock.test index 83ea07d4bda..703c59e8f28 100644 --- a/mysql-test/main/sp-lock.test +++ b/mysql-test/main/sp-lock.test @@ -807,9 +807,6 @@ connection default; --echo # --echo # SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered --echo # ---echo # We are just covering the existing behaviour with tests. The ---echo # results are not necessarily correct." ---echo # delimiter |; CREATE PROCEDURE p1() diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 6510eaf77f5..694c7bcd20e 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -8893,4 +8893,26 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp BEGIN RETURN ''; END' at line 2 +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp0() SELECT 1; +SHOW CREATE PROCEDURE sp0; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp0 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` PROCEDURE `sp0`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP PROCEDURE sp0; +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp1() SELECT 1; +SHOW CREATE PROCEDURE sp1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp1 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE-@local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +VARIABLE_VALUE-@local_mem_used +0 +SELECT VARIABLE_VALUE-@global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +VARIABLE_VALUE-@global_mem_used +0 +DROP PROCEDURE sp1; # End of 10.3 tests diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test index d6fba77b854..50ae78c8431 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -10431,4 +10431,21 @@ END; $$ DELIMITER ;$$ +# MDEV-20699 do not cache SP in SHOW CREATE +# Warmup round, this might allocate some memory for session variable +# and the output +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp0() SELECT 1; +SHOW CREATE PROCEDURE sp0; +DROP PROCEDURE sp0; + +#Check that CREATE/SHOW does not use memory in caches. +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp1() SELECT 1; +SHOW CREATE PROCEDURE sp1; +SELECT VARIABLE_VALUE-@local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE-@global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +DROP PROCEDURE sp1; --echo # End of 10.3 tests diff --git a/sql/sp.cc b/sql/sp.cc index a4c4ca58414..fdfd9a79fef 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1890,8 +1890,6 @@ bool Sp_handler::sp_show_create_routine(THD *thd, const Database_qualified_name *name) const { - sp_head *sp; - DBUG_ENTER("sp_show_create_routine"); DBUG_PRINT("enter", ("type: %s name: %.*s", type_str(), @@ -1904,20 +1902,20 @@ Sp_handler::sp_show_create_routine(THD *thd, It is "safe" to do as long as it doesn't affect the results of the binary log or the query cache, which currently it does not. */ - if (sp_cache_routine(thd, name, false, &sp)) - DBUG_RETURN(TRUE); - - if (sp == NULL || sp->show_create_routine(thd, this)) + sp_head *sp= 0; + bool free_sp= db_find_routine(thd, name, &sp) == SP_OK; + bool ret= !sp || sp->show_create_routine(thd, this); + if (ret) { /* If we have insufficient privileges, pretend the routine does not exist. */ my_error(ER_SP_DOES_NOT_EXIST, MYF(0), type_str(), name->m_name.str); - DBUG_RETURN(TRUE); } - - DBUG_RETURN(FALSE); + if (free_sp) + sp_head::destroy(sp); + DBUG_RETURN(ret); } |