MDEV-20699 mysqldump of routines causes MariaDB to get killed by oom-killerbb-10.3-wlad-MDEV-20699

The reason for this behavior is that SP get cached, per connection.
The stored_program_cache is size of this cache, which amounts to 256
routines by default. A compiled stored procedure can easily be several
megabytes in size. Thus calling SHOW CREATE PROCEDURE for all stored
procedures, like mysqldump does, can require significant amount of memory.

Fixed by bypassing the cache for "SHOW CREATE". This should normally be
fine also perfomance-wise, as cache is meant to be used for repeated
execution, not repeated SHOW CREATEs.

Added a test to verify that CREATE PROCEDURE + SHOW CREATE PROCEURE do not
cache, i.e amount of allocated memory does not change.

Note, there is a change in existing behavior in an edge case :
If "SHOW CREATE PROCEDURE p1" called from p1, after p1 was altered, now
this will now return altered code. Previour behavior - relied on caching
and would return old code. The previous behavior might was not necessarily
correct.

1 files changed, 0 insertions, 3 deletions

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()