MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively

Problem:

push_cursor() created sp_cursor instances on THD::main_mem_root,
which is freed only after the SP instructions loop.

Changes:
- Moving sp_cursor declaration from sp_rcontext.h to sql_class.h
- Deriving sp_instr_cpush from sp_cursor. So now sp_cursor is created
  only once (at the SP parse time) and then reused on all loop iterations
- Adding a new method reset() into sp_cursor (and its parent classes)
  to reset an sp_cursor instance before reuse.
- Moving former sp_cursor members m_fetch_count, m_row_count, m_found
  into a separate class sp_cursor_statistics. This helps to reuse
  the code in sp_cursor constructors, and in sp_cursor::reset()
- Adding a helper method sp_rcontext::pop_cursor().
- Adding "THD*" parameter to so_rcontext::pop_cursors() and pop_all_cursors()
- Removing "new" and "delete" from sp_rcontext::push_cursor() and
  sp_rconext::pop_cursor().
- Fixing sp_cursor not to derive from Sql_alloc, as it's now allocated
  only as a part of sp_instr_cpush (and not allocated separately).
- Moving lex_keeper->disable_query_cache() from sp_cursor::sp_cursor()
  to sp_instr_cpush::execute().
- Adding tests

2 files changed, 63 insertions, 0 deletions

diff --git a/mysql-test/main/sp-cursor.result b/mysql-test/main/sp-cursor.result
index 42d6e455109..f1dd8ed5eaa 100644
--- a/mysql-test/main/sp-cursor.result
+++ b/mysql-test/main/sp-cursor.result
@@ -684,3 +684,32 @@ rec.a
 1
 rec.a
 1
+#
+# MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE mem_used_old BIGINT UNSIGNED DEFAULT
+(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME='MEMORY_USED');
+DECLARE i INT DEFAULT 1;
+WHILE i <= 5000
+DO
+BEGIN
+DECLARE msg TEXT;
+DECLARE mem_used_cur BIGINT UNSIGNED DEFAULT
+(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME='MEMORY_USED');
+DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
+IF (mem_used_cur >= mem_used_old * 2) THEN
+SHOW STATUS LIKE 'Memory_used';
+SET msg=CONCAT('Memory leak detected: i=', i, ' mem_used_old=',mem_used_old,' mem_used_cur=', mem_used_cur);
+SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=msg;
+END IF;
+END;
+SET i=i+1;
+END WHILE;
+END;
+$$
+CALL p1;
+DROP PROCEDURE p1;
diff --git a/mysql-test/main/sp-cursor.test b/mysql-test/main/sp-cursor.test
index bc352872075..735514ff376 100644
--- a/mysql-test/main/sp-cursor.test
+++ b/mysql-test/main/sp-cursor.test
@@ -689,3 +689,37 @@ label2:
 END;
 $$
 DELIMITER ;$$
+
+
+--echo #
+--echo # MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE mem_used_old BIGINT UNSIGNED DEFAULT
+    (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
+     WHERE VARIABLE_NAME='MEMORY_USED');
+  DECLARE i INT DEFAULT 1;
+  WHILE i <= 5000
+  DO
+    BEGIN
+      DECLARE msg TEXT;
+      DECLARE mem_used_cur BIGINT UNSIGNED DEFAULT
+        (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
+         WHERE VARIABLE_NAME='MEMORY_USED');
+      DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
+      IF (mem_used_cur >= mem_used_old * 2) THEN
+        SHOW STATUS LIKE 'Memory_used';
+        SET msg=CONCAT('Memory leak detected: i=', i, ' mem_used_old=',mem_used_old,' mem_used_cur=', mem_used_cur);
+        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=msg;
+      END IF;
+    END;
+    SET i=i+1;
+  END WHILE;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;