Bug #41139 memory leak in prepare() , fetchall_arrayref()
Submitted: 30 Nov 2008 18:52 Modified: 1 Dec 2008 10:17
Reporter: Philip Stoev Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S1 (Critical)
Version:4.010 OS:Linux (64bit)
Assigned to: CPU Architecture:Any

[30 Nov 2008 18:52] Philip Stoev
Description:
DBD::mysql has a serious memory leak in prepare(). Programs which execute many prepare() calls are going to trash the machine badly.

How to repeat:
my $query = "SELECT 1 FROM DUAL";

my $dbh = DBI->connect (
    "dbi:mysql:database=test:host=127.0.0.1;port=19306",
    "root", "",
    { RaiseError => 1, PrintError => 0 },
    );

while (1) {
  my $s_q = $dbh->prepare($query);
  $s_q->execute();
  my @data = $s_q->fetchrow_array();
  $s_q->finish;

  $count++;

  print "ran $count queries\r";
}

Run this script and observe memory usage over time.
[1 Dec 2008 7:44] Philip Stoev
[philips@fedora10 ~]$ uname -a
Linux fedora10 2.6.27.5-117.fc10.x86_64 #1 SMP Tue Nov 18 11:58:53 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

[philips@fedora10 ~]$ cat /etc/issue.net
Fedora release 10 (Cambridge)

DBI 1.607 and DBD::mysql 4.010
[1 Dec 2008 7:52] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please check if you use server-side prepared statements occasionally and experience problem described at bug #31161.
[1 Dec 2008 8:10] Philip Stoev
This bug happens regardless of the mysql_server_prepare DBD::mysql setting.

Can you please check on a 64-bit machine? MySQL rpm is 5.0.67-2.fc10
[1 Dec 2008 8:44] Sveta Smirnova
I tested on 64-bit machine with MySQL built from sources. Will try to find exact same machine to test with RPM indicated.
[1 Dec 2008 8:53] Philip Stoev
Thank you. I am trying valgrind on the problematic setup.
[1 Dec 2008 10:17] Philip Stoev
It turns out that this is a bug in DBI and not in DBD::mysql . Using prepare_cached() appears to solve the issue.
[1 Dec 2008 16:24] Patrick Galbraith
Philip,

Thanks for the report. Where's the info about this being a problem in DBI? I'm a bit curious about it.
[1 Dec 2008 16:29] Philip Stoev
It appears there are two memory leaks:

1. In prepare(), likely in DBI

see this thread for a script to test. prepare_cached() appears to work.

http://www.mail-archive.com/dbi-dev@perl.org/msg05096.html

2. In fetchall_arrayref(), likely in DBD::mysql

fetchall_arrayref() on 100K resultset causes massive memory loss. The alternative, to loop through fetchrow_arrayref() works like charm.