MySQL Bugs: #29834: Accessing a view in a stored procedure causes a small memory consumption

Bug #29834	Accessing a view in a stored procedure causes a small memory consumption
Submitted:	16 Jul 2007 21:10	Modified:	7 Aug 2007 15:49
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.44	OS:	Linux
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	memory leak, stored procedure, VIEW

Description:
When you repeatedly access a view in a stored procedure (such as a loop), the amount of memory used by mysqld will increase.  The memory is accumulated relatively slowly and is deallocated on disconnect.  This prevents the use of long running stored procedures that make repeated access of views.

If the view is replaced by a table in the simple query, then memory does not grow.

I have attached a simple test case which accesses an empty view over and over in a loop and will cause mysqld to grow.

How to repeat:
1.  See how much memory mysqld is using.
2.  Run the attached test case, wait a few minutes.
3.  Watch memory slowly grow and grow and grow (it is quite slow, takes a minute or two per meg).
4.  Kill the connection, memory freed.

Suggested fix:
Do not continue to allocate memory.

Test case to cause memory increase

Attachment: bug_test_case.sql (text/x-sql), 481 bytes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31206

ChangeSet@1.2527, 2007-07-20 04:49:29+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29834.
  Repetitive access to the view column by it's name in a stored
  procedure caused a memory leak.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31761

ChangeSet@1.2527, 2007-07-28 15:56:02+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29834.
  Repetitive access to a view column by it's name during
  executions of SP/PS caused a memory leak.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31767

ChangeSet@1.2527, 2007-07-28 22:23:24+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29834.
  Calling view columns by their names during an execution of
  a prepared SELECT statement or a SELECT statement inside
  a SP caused a memory leak.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31768

ChangeSet@1.2527, 2007-07-28 23:10:38+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29834.
  Using view columns by their names during an execution of
  a prepared SELECT statement or a SELECT statement inside
  a SP caused a memory leak.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelog.

Use of a view within a stored procedure caused a memory leak.

This is not a memory leak. In a true memory leak the memory is lost, the only way to free is to restart the process. Here the memory is freed on disconnect. Still a problem, but not as critical as the current changelog text implies.

Corrected changelog entry:

Repeatedly accessing a view in a stored procedure (for example, in a
loop) caused a small amount of memory to be allocated per access.
Although this memory is deallocated on disconnect, it could be a
problem for a long running stored procedures that make repeated
access of views.