Edit report at https://bugs.php.net/bug.php?id=54864&edit=1
ID: 54864
Updated by: and...@php.net
Reported by: jas at rephunter dot net
Summary: Memory leak associated with mysql connector
-Status: Open
+Status: Feedback
Type: Bug
Package: MySQLi related
Operating System: FreeBSD
PHP Version: 5.3.6
Block user comment: N
Private report: N
New Comment:
Hi,
I suppose there is no problem but the following occurs. Before 5.3 PHP used
libmysql as underlying library. Since 5.3 there is an option to use PHP's
implementation of the client/server protocol, the mysqlnd library. This library
uses PHP's memory allocation functions, thus affects memory_get_usage(). When
you used libmysql and it allocated memory, this memory wasn't reported by
memory_get_usage() because the latter counts only the memory allocated by the
PHP's memory allocator.
To see if there is really a leak, you have to free your result sets and then
dereference all the variables which point to the result set. If the memory
usage is still high, there could be a problem. There is no problem, if you have
created big SQL statement and later the reported used memory is still high,
because mysqlnd has a buffer onto which data packets are created. When big SQL
statement comes, the buffer needs to be enlarged and later it is not made
smaller.
Previous Comments:
------------------------------------------------------------------------
[2011-07-05 17:54:56] jas at rephunter dot net
A complete script was requested. To run the test you will need some mysql
tables. I can provide sanitized versions of production data. Please advise as
to how to upload.
Here is the script.
<?php
/**
* Title: Test Autoemail Memory
* Author: JAS
* Date: 11-May-10
* Project: RepHunter
* Purpose: Testing memory leak
*
*/
define('MYSQL_ENHANCED', true);
require('../site.php'); // site specific parameter file outside the web root
$link = open_db($DBNAME, $DBHOSTNAME, $DBUSER, $DBPASSWORD);
echo "Test Autoemail Memory Leak\n";
echo 'Using ' . (MYSQL_ENHANCED ? 'mysqli_connect' : 'mysql_connect') . "\n";
echo 'start run mem=' . memory_get_usage(true) . "\n";
$query = get_query();
$rs = SQL($link, $query);
if ($rs)
{
// main loop
$cnt = 0;
echo 'after SQL mem=' . memory_get_usage(true) . "\n";
$func = (MYSQL_ENHANCED) ? 'mysqli_fetch_row' : 'mysql_fetch_row';
while($row = $func($rs))
{
if (++$cnt % 3000 == 0)
{
echo ' id=' . $row[1] . ' mem=' .
memory_get_usage(true) . "\n";
// gc_collect_cycles();
}
// unset($row);
// $row = null;
}
echo "EOJ\n";
}
else
{
echo $errmsg;
}
function open_db($DBNAME, $DBHOSTNAME, $DBUSER, $DBPASSWORD)
{
if (MYSQL_ENHANCED)
{
$link = mysqli_connect($DBHOSTNAME, $DBUSER, $DBPASSWORD,
$DBNAME);
if(!$link)
{
trigger_error('Cannot connect to mysql', E_USER_ERROR);
}
}
else
{
$link = mysql_connect($DBHOSTNAME, $DBUSER, $DBPASSWORD);
if(!$link)
{
trigger_error('Cannot connect to mysql', E_USER_ERROR);
}
$db_selected = mysql_select_db($DBNAME, $link);
if (!$db_selected)
{
trigger_error('Cannot use ' . $DBNAME . ': '.
mysql_error($link), E_USER_ERROR);
}
}
return $link;
}
function SQL($link, $query)
{
global $errmsg;
if (MYSQL_ENHANCED)
{
$rs = mysqli_query($link, $query);
if (!$rs)
{
$errmsg = mysqli_errno($link) . ': ' .
mysqli_error($link);
return false;
}
}
else
{
$rs = mysql_query($query, $link);
if (!$rs)
{
$errmsg = mysql_errno($link) . ': ' .
mysql_error($link);
return false;
}
}
return $rs;
}
function get_query()
{
return <<<SQL
SELECT
u.usertypeid,
u.userid,
opt_out,
DATE_FORMAT(dateentry, '%Y/%m/%d'),
DATE_FORMAT(dateentry, '%m/%d/%Y'),
DATE_FORMAT(dateupdate, '%Y/%m/%d'),
DATE_FORMAT(dateupdate, '%m/%d/%Y %h:%i %p'),
referrerid,
referralcnt,
phone1,
email1,
u.status,
DATE_FORMAT(datestatus, '%Y/%m/%d'),
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '','','','','','','','','',''
, fname, lname, address1, address2, city, state, postal
FROM user u
WHERE u.status NOT IN ('R')
UNION
SELECT
6 AS usertypeid,
c.repid,
opt_out,
DATE_FORMAT(dateentry, '%Y/%m/%d'),
DATE_FORMAT(dateentry, '%m/%d/%Y'),
DATE_FORMAT(dateupdate, '%Y/%m/%d'),
DATE_FORMAT(dateupdate, '%m/%d/%Y %h:%i %p'),
referrerid,
referralcnt,
phone1,
email1,
u.status,
DATE_FORMAT(datestatus, '%Y/%m/%d'),
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '','','','','','','','','',''
, '', '', '', '', '', '', ''
FROM contactrequest c
LEFT OUTER JOIN user u ON u.userid = c.repid
WHERE Response = ''
AND initiatedby = 2
AND u.status NOT IN ('R', 'I', 'U')
GROUP BY c.repid, email1, fname, u.userid, referrerid, referralcnt
UNION
SELECT
7 as usertypeid,
c.principalid,
opt_out,
DATE_FORMAT(dateentry, '%Y/%m/%d'),
DATE_FORMAT(dateentry, '%m/%d/%Y'),
DATE_FORMAT(dateupdate, '%Y/%m/%d'),
DATE_FORMAT(dateupdate, '%m/%d/%Y %h:%i %p'),
referrerid,
referralcnt,
phone1,
email1,
u.status,
DATE_FORMAT(datestatus, '%Y/%m/%d'),
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '','','','','','','','','',''
, '', '', '', '', '', '', ''
FROM contactrequest c
LEFT OUTER JOIN user u ON u.userid = c.principalid
WHERE Response = ''
AND initiatedby = 1
AND u.status NOT IN ('R', 'I', 'U')
GROUP BY c.principalid, email1, fname, u.userid, referrerid, referralcnt
UNION
SELECT
8 AS usertypeid,
u.userid,
opt_out,
DATE_FORMAT(dateentry, '%Y/%m/%d'),
DATE_FORMAT(dateentry, '%m/%d/%Y'),
DATE_FORMAT(dateupdate, '%Y/%m/%d'),
DATE_FORMAT(dateupdate, '%m/%d/%Y %h:%i %p'),
referrerid,
referralcnt,
phone1,
email1,
status,
DATE_FORMAT(datestatus, '%Y/%m/%d'),
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '','','','','','','','','',''
, fname, lname, address1, address2, city, state, postal
FROM paidpridtl d
INNER JOIN user u ON u.userid = d.userid
WHERE planid IN (103, 104, 94, 1, 93)
AND d.datestart > DATE_ADD(CURDATE(), INTERVAL 5 - 1 DAY)
AND d.datestart <= DATE_ADD(CURDATE(), INTERVAL 5 DAY)
UNION
SELECT
12 as usertypeid,
u.userid,
opt_out,
DATE_FORMAT(dateentry, '%Y/%m/%d'),
DATE_FORMAT(dateentry, '%m/%d/%Y'),
DATE_FORMAT(dateupdate, '%Y/%m/%d'),
DATE_FORMAT(dateupdate, '%m/%d/%Y %h:%i %p'),
referrerid,
referralcnt,
phone1,
email1,
u.status,
DATE_FORMAT(datestatus, '%Y/%m/%d'),
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '','','','','','','','','',''
, fname, lname, address1, address2, city, state, postal
FROM user u
WHERE u.status NOT IN ('R')
AND profile_complete & 7 <> 7 ORDER BY userid DESC
SQL;
}
------------------------------------------------------------------------
[2011-05-26 14:57:36] johan...@php.net
Please provide a _complete_ script for testing. Also mind that increasing
memory_get_usage() values don't necessarily represent memory leaks but includes
different cache data or memory which will be re-used.
------------------------------------------------------------------------
[2011-05-19 18:32:45] jas at rephunter dot net
Description:
------------
I have found a memory leak in connection with several production PHP scripts.
While there are many bug reports relating to memory leaks, I did not find
anything similar to our situation, which is very easy to reproduce.
The scripts that have been affected by this memory leak have been in continuous
production use since 2006. We did not notice a memory leak prior to when we
first upgraded to PHP 5.3.5. It is possible that there was a smaller leak prior
to this time that merely escaped notice. However, before reporting the problem,
we upgraded to 5.3.6 to make sure it had not been corrected. The results in
this
ticket are thus for 5.3.6.
Below I have given the main loop of a "small reproducible code." As you can
see,
the only thing done in this test is to fetch the rows, and print out memory
usage every 3000 rows.
Regarding the mysql connector: originally the test was run with mysqli_connect.
It was suggested via Experts-Exchange to try the mysql_connector. This was done
but the results were identical. The full script can works with both
mysql_connect and mysqli_connect, controlled by a define.
The bug signs:
1. On 5.3.6, the "after SQL" memory usage jumps to 13MB, whereas on 5.2.4 it
stays at the initial low value (262144 on 5.2.4 but 786432 on 5.3.6).
2. On 5.3.6, the memory usage grows dramatically, whereas on 5.2.4 it does not
(the "id" lines are displayed after each 3000 rows, where the id is the primary
key for the row). In the production scripts, this leads to a crash when the
memory limit is exceeded.
Please note:
1. the SQL statement is composed of a UNION of 5 relatively simple SELECTs,
making the single statement relatively complex.
2. The expected and actual results shown below are achieved by connecting to
the
same database.
I you would like the URL of the actual script, which has the connection
routines
and the SQL (which is relatively complex), please let me know as I would have
to
remove the passwords, etc. I could also send a mysqldump of a sanitized version
of the database. The bzip2 of the dump file is about 35MB.
Test script:
---------------
echo "Test Autoemail Memory Leak\n";
echo 'start run mem=' . memory_get_usage(true) . "\n";
$query = get_query();
$rs = SQL($link, $query);
if ($rs)
{
// main loop
$cnt = 0;
echo 'after SQL mem=' . memory_get_usage(true) . "\n";
$func = (MYSQL_ENHANCED) ? 'mysqli_fetch_row' : 'mysql_fetch_row';
while($row = $func($rs))
{
if (++$cnt % 3000 == 0)
{
echo ' id=' . $row[1] . ' mem=' .
memory_get_usage(true) . "\n";
}
}
echo "EOJ\n";
}
else
{
echo $errmsg;
}
Full script is at http://www.rephunter.net/test-autoemail-memory.php. The web
server will execute the script if accessed in a browser.
Expected result:
----------------
Showing no leak on Windows VM PHP 5.2.4 connecting to same database
F:\Websites\RepHunter\current>php -v
PHP 5.2.4 (cli) (built: Oct 1 2007 20:06:42)
Copyright (c) 1997-2007 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies
with Zend Core v2.5.0, Copyright (c) 1998-2006, by Zend Technologies
with Zend Extension Manager v1.2.0, Copyright (c) 2003-2006, by Zend Technol
ogies
with Zend Optimizer v3.3.1, Copyright (c) 1998-2007, by Zend Technologies
with Zend Debugger v5.2.10, Copyright (c) 1999-2007, by Zend Technologies
F:\Websites\RepHunter\current>php test-autoemail-memory.php
Test Autoemail Memory Leak
Using mysqli_connect
start run mem=262144
after SQL mem=262144
id=43655 mem=262144
id=40250 mem=262144
id=37355 mem=262144
id=34419 mem=262144
id=31544 mem=262144
id=28915 mem=262144
id=26168 mem=262144
id=21461 mem=262144
id=16550 mem=262144
id=13074 mem=262144
id=9140 mem=262144
id=3892 mem=262144
EOJ
F:\Websites\RepHunter\current>
Actual result:
--------------
Showing the leak on 5.3.6
[jas1@www /var/www/rephunter/www/webroot]$ php -v
PHP 5.3.6 with Suhosin-Patch (cli) (built: May 13 2011 21:58:30)
Copyright (c) 1997-2011 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2011 Zend Technologies
[jas1@www /var/www/rephunter/www/webroot]$ php ./test-autoemail-memory.php
Test Autoemail Memory Leak
Using mysqli_connect
start run mem=786432
after SQL mem=13631488
id=43655 mem=23592960
id=40250 mem=33292288
id=37355 mem=43253760
id=34419 mem=52953088
id=31544 mem=62914560
id=28915 mem=72613888
id=26168 mem=82575360
id=21461 mem=92274688
id=16550 mem=102236160
id=13074 mem=112197632
id=9140 mem=121896960
id=3892 mem=131858432
EOJ
[jas1@www /var/www/rephunter/www/webroot]$
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=54864&edit=1
