php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #54864 Memory leak associated with mysql connector
Submitted: 2011-05-19 18:32 UTC Modified: 2015-09-03 17:18 UTC
Votes:7
Avg. Score:4.4 ± 0.7
Reproduced:5 of 5 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (20.0%)
From: jas at rephunter dot net Assigned:
Status: Re-Opened Package: MySQLi related
PHP Version: 5.3.6 OS: FreeBSD
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: jas at rephunter dot net
New email:
PHP Version: OS:

 

 [2011-05-19 18:32 UTC] 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]$ 


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2011-05-26 14:57 UTC] johannes@php.net
-Status: Open +Status: Feedback
 [2011-05-26 14:57 UTC] johannes@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-07-05 17:54 UTC] jas at rephunter dot net
-Status: Feedback +Status: Open
 [2011-07-05 17:54 UTC] 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-10-18 21:19 UTC] andrey@php.net
-Status: Open +Status: Feedback
 [2011-10-18 21:19 UTC] 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.
 [2013-02-18 00:34 UTC] 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.
 [2015-05-17 19:22 UTC] 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.
 [2015-05-17 19:26 UTC] pr0ger at free dot fr
Note : same leakage using mysql_fetch_row() or mysql_fetch_array()
 [2015-05-18 00:24 UTC] requinix@php.net
-Status: No Feedback +Status: Re-Opened
 [2015-09-02 20:53 UTC] supraguy at yandex dot com
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.
 [2015-09-03 17:18 UTC] jas at rephunter dot net
I am the OP for this ticket. Our system, now at PHP 5.6.9, is once again experiencing a memory leak, and in fact it is in the same script as in the original post.

I am planning to do some testing to see if unsetting things helps. But per the post from yesterday from supraguy, that does not work.
 [2015-09-10 13:51 UTC] jas at rephunter dot net
The problem seems to be worse than previously. The following results are on PHP 5.5.27.

On every loop while reading a table, memory is consumed, regardless of using gc_enable, unset, or mysqli_free_memory.

I started by dusting off the script that I originally provided in this bug ticket on 2011-07-05. However that complexity is not needed, and a very simple query of "SELECT * from user" gives the same result.

Here is the main loop of the simplified version, where the user table has about 70,000 rows:

$rs = mysqli_query($link, 'SELECT * FROM user');

if ($rs)
{
	// main loop
	$cnt = 0;
	echo 'after SQL mem=', memory_get_usage(true), LF;
	while($row = mysqli_fetch_row($rs))
	{
		if (++$cnt % 3000 == 0)
		{
			echo '   id=', $row[$userid_ix], '  mem=', memory_get_usage(true), LF;
			gc_collect_cycles();
			flush();
			ob_flush();	// documentation is inconclusive whether this is also needed
		}
		unset($row);
		// $row = null;
	}
}

Here is the output of the above script (please note that the echo statements are issued once for every 3000 rows):

Test Autoemail Memory Leak
Using mysqli_connect
start run mem=524288
Query
SELECT * FROM user
after SQL mem=24117248
   id=3043  mem=29884416
   id=6066  mem=35913728
   id=9041  mem=41680896
   id=12031  mem=47710208
   id=15015  mem=53477376
   id=17986  mem=59506688
   id=20984  mem=65273856
   id=24004  mem=71303168
   id=27065  mem=77070336
   id=30076  mem=83099648
   id=33115  mem=88866816
   id=36201  mem=94896128
   id=39095  mem=100663296
   id=42106  mem=106692608
   id=45112  mem=112459776
   id=48104  mem=118489088
   id=51085  mem=124518400
   id=54082  mem=130285568
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /Users/jas/Websites/RepHunter/current/test-autoemail-memory.php on line 44

The various attempts to reclaim memory (gc_enable(), unset(), gc_reclaim_cycles, and setting the $row to null, all have no effect. 

At this point, the workaround is to simply set memory large enough, and hope the application completes before exhausting memory. At present I am using ini_set('memory_limit', '384M');

The problem (on line 44) is while($row = mysqli_fetch_row($rs)).

Has anybody come up with an alternative to that construct that does not continue to consume memory?
 [2015-09-10 14:07 UTC] jas at rephunter dot net
Please note that in my above post with the simplified code, the flush() and ob_flush() are not directly related to the issue afaik, but have to do with attempts to display output in a browser.
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Wed Nov 14 13:01:25 2018 UTC