Bug #54864 [Com]: Memory leak associated with mysql connector

Edit report at https://bugs.php.net/bug.php?id=54864&edit=1 ID: 54864 Comment by: pr0ger at free dot fr Reported by: jas at rephunter dot net Summary: Memory leak associated with mysql connector Status: No Feedback Type: Bug Package: MySQLi related Operating System: FreeBSD PHP Version: 5.3.6 Block user comment: N Private report: N New Comment: Note : same leakage using mysql_fetch_row() or mysql_fetch_array() Previous Comments: ------------------------------------------------------------------------ [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; } ------------------------------------------------------------------------ [2011-05-26 14:57:36] 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. ------------------------------------------------------------------------ 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