Edit report at https://bugs.php.net/bug.php?id=54864&edit=1
ID: 54864
Comment by: supraguy at yandex dot com
Reported by: jas at rephunter dot net
Summary: Memory leak associated with mysql connector
Status: Re-Opened
Type: Bug
Package: MySQLi related
Operating System: FreeBSD
PHP Version: 5.3.6
Block user comment: N
Private report: N
New Comment:
I run into the same problem as well. I use mysqli_query() and on each execution it increases by 50
or so bytes. I unset the crap out of everything and do mysqli_free_result() but still the number
keeps rising.
Previous Comments:
------------------------------------------------------------------------
[2015-05-17 19:26:24] pr0ger at free dot fr
Note : same leakage using mysql_fetch_row() or mysql_fetch_array()
------------------------------------------------------------------------
[2015-05-17 19:22:00] pr0ger at free dot fr
Reproduced the memory leakage in php 5.5.0 + win7
had to fetch through 10M lines of TINYTEXT string (randoms from 4 to 250 utf8 chars)
$pt = mysql_query("SELECT tinytext_data, int_primkey FROM buffer");
while ($line = mysql_fetch_array($pt))
{
//doesnt need to do anything
echo "\r\n". memory_get_usage();
}
at each step, it increase the mem usage by 64 bytes, never freeing them.
------------------------------------------------------------------------
[2013-02-18 00:34:51] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.
------------------------------------------------------------------------
[2011-10-18 21:19:34] andrey@php.net
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.
------------------------------------------------------------------------
[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;
}
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=54864
--
Edit this bug report at https://bugs.php.net/bug.php?id=54864&edit=1