MySQL Bugs: #31161: Stored procedures should free prepared statements on exiting

Bug #31161	Stored procedures should free prepared statements on exiting
Submitted:	23 Sep 2007 18:54	Modified:	22 Feb 2008 18:22
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	bfsm_2007_10_18

Description:
If you create a simple procedure that uses prepared statements but does not DEALLOCATE PREPARE then there is a possibility for a memory leak.

This is important, because a malicious user could write a procedure that allocates but does not deallocate and the only way to free these statements is to restart the server.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, fld char(255),
hits INT NOT NULL
);
INSERT INTO t1 (fld) VALUES (md5(rand()));
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1;
INSERT INTO t1 (fld) SELECT md5(rand()) FROM t1; -- 2048

INSERT INTO t1 (fld) VALUES ('test1'),('test2'),('test3'),('test4'),
('test5'),('test6'),('test7'),('test8'),('test9'),('test10');

DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1 ()
BEGIN

SET @sql_statement_1 = "UPDATE t1 SET hits=hits+1 WHERE fld='test1'";
SET @sql_statement_2 = "UPDATE t1 SET hits=hits+1 WHERE fld='test2'";
SET @sql_statement_3 = "UPDATE t1 SET hits=hits+1 WHERE fld='test3'";
SET @sql_statement_4 = "UPDATE t1 SET hits=hits+1 WHERE fld='test4'";
SET @sql_statement_5 = "UPDATE t1 SET hits=hits+1 WHERE fld='test5'";
SET @sql_statement_6 = "UPDATE t1 SET hits=hits+1 WHERE fld='test6'";
SET @sql_statement_7 = "UPDATE t1 SET hits=hits+1 WHERE fld='test7'";
SET @sql_statement_8 = "UPDATE t1 SET hits=hits+1 WHERE fld='test8'";
SET @sql_statement_9 = "UPDATE t1 SET hits=hits+1 WHERE fld='test9'";
SET @sql_statement_10 = "UPDATE t1 SET hits=hits+1 WHERE fld='test10'";

PREPARE stmt_1 FROM @sql_statement_1;
PREPARE stmt_2 FROM @sql_statement_2;
PREPARE stmt_3 FROM @sql_statement_3;
PREPARE stmt_4 FROM @sql_statement_4;
PREPARE stmt_5 FROM @sql_statement_5;
PREPARE stmt_6 FROM @sql_statement_6;
PREPARE stmt_7 FROM @sql_statement_7;
PREPARE stmt_8 FROM @sql_statement_8;
PREPARE stmt_9 FROM @sql_statement_9;
PREPARE stmt_10 FROM @sql_statement_10;

EXECUTE stmt_1;
EXECUTE stmt_2;
EXECUTE stmt_3;
EXECUTE stmt_4;
EXECUTE stmt_5;
EXECUTE stmt_6;
EXECUTE stmt_7;
EXECUTE stmt_8;
EXECUTE stmt_9;
EXECUTE stmt_10;

END;
//

mysql> show global status like 'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_close | 0 | 
| Com_stmt_execute | 10 | 
| Com_stmt_fetch | 0 | 
| Com_stmt_prepare | 10 | 
| Com_stmt_reset | 0 | 
| Com_stmt_send_long_data | 0 | 
+-------------------------+-------+
6 rows in set (0.00 sec)

Suggested fix:
Either close the statement on completing the procedure or introduce a feature for closing all prepared statements (similar to the FLUSH commands).

test case

Attachment: bug31161.test (application/octet-stream, text), 320 bytes.

Thank you for the report.

Verified as described.

Even if this is expected, this should be documented in the user manual.

i tried a test. if i disconnect the client connection, everything is freed.  if you re-run the procedure twice in the same connection, it won't continue to use up more statements.  this is visible in the Prepared_stmt_count status variable.

in my tests, calling mysqli_change_user() also freed the statements. but, i found another bug in the status variables. noticed Com_stmt_close decreases sometimes!!!  How can that happen?

This is not a bug.
Prepared statements are connection global just like user variables.
One can free them after the stored procedure has finished using the  same name as used in the stored procedure.
In other words, statement names are not local to the stored procedure, they are local to the connection.
Out of memory situations can just as well be reached by creating many prepared statements with different names outside stored procedures.

The bug with statistics that Shane noticed needs to be reported and verified separately.

Finally, to limit the possibility of the out of memory attack, there is max_stmt_count global limit.

It has been suggested by Sveta that this behavior should be clarified in the documentation.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to SQLPS section:

"A prepared statement is specific to the connection in which it was
created. If you terminate a client session without deallocating a
previously prepared statement, the server deallocates it 
automatically.

A prepared statement is also global to the connection. If you create
a prepared statement within a stored routine, it is not deallocated
when the stored routine ends.

To guard against too many prepared statements being created
simultaneously, the max_prepared_stmt_count system variable can be
set."