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

[16 Jul 2007 21:10] Harrison Fisk
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.
[16 Jul 2007 21:10] Harrison Fisk
Test case to cause memory increase

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

[19 Jul 2007 23:36] Bugs System
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.
[28 Jul 2007 10:38] Bugs System
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.
[28 Jul 2007 17:05] Bugs System
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.
[28 Jul 2007 17:52] Bugs System
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.
[2 Aug 2007 19:13] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:15] Bugs System
Pushed into 5.0.48
[3 Aug 2007 15:56] Paul DuBois
Noted in 5.0.48, 5.1.21 changelog.

Use of a view within a stored procedure caused a memory leak.
[7 Aug 2007 9:17] Sergei Golubchik
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.
[7 Aug 2007 15:48] Paul DuBois
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.